KACE Product Support

Hey guys,

Hope someone finds this useful! Troy Grey (from Sydney, Aus Support) and I have both been working on this for a day or so trying to get syntaxes right and we finally nutted something really quite functional out.

This ticket rule (actually two) uses two different fields in order to show you the Days (D) Hours (H) and Minutes (M) since last modification by user or staff. Ie, something that was edited 4 hours and 3 minutes ago would have a 'Since Modified: 0D 4H 3M' in the ticket view which makes this very useful for overviews and seeing how long since a ticket has been modified.

The parts required:
Ticket Fields -
2x Free Custom Fields - One to add the latest modified time to (Hidden from all users), and one to store the %D %H %M value for viewing (visible but read only to all).

Ticket Rule 1 - On Save Event - This sets the default value of 0D 0H 0M (meaning just edited) and also sets the current time of the chosen custom field (I used CUSTOM_FIELD_VALUE14 to store the time and CUSTOM_FIELD_VALUE2 to store the DHM) - REMEMBER TO CHANGE THE FIELDS TO THE ONES YOU'LL BE USING!

Select (Made using the Query Wizard in the K1):
select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', ')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (( HD_STATUS.NAME != 'Closed') and HD_TICKET.HD_QUEUE_ID = 1 )

Update statement:
update HD_TICKET as HDT
set HDT.CUSTOM_FIELD_VALUE14 = CURRENT_TIMESTAMP(), HDT.CUSTOM_FIELD_VALUE2 = "0D 0H 0M"
where
(HDT.ID in (<TICKET_IDS>))

Ticket Rule 2 - This rule is used to (every 15 minutes) perform a timestampdiff on the field you created at the On Save Ticket rule above and the current Timestamp at the time of run. It then updates the %D %H %M - REMEMBER TO CHANGE THE FIELDS TO THE ONES YOU'LL BE USING!

Select:
Select
HD_TICKET.ID,
Concat(DayOfYear(Current_TimeStamp()) -
DayOfYear(Max(HD_TICKET_CHANGE.TIMESTAMP)), 'D ',
Date_Format(AddTime("2000-00-00 00:00:00",
Sec_To_Time(Time_To_Sec(Current_TimeStamp()) -
Time_To_Sec(Max(HD_TICKET_CHANGE.TIMESTAMP)))), '%kH %iM')) As SINCEMOD,
HD_TICKET_CHANGE.HD_TICKET_ID,
HD_TICKET.CUSTOM_FIELD_VALUE2
From
HD_TICKET Join
HD_CATEGORY On HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID Join
HD_STATUS On HD_TICKET.HD_STATUS_ID = HD_STATUS.ID Inner Join
HD_TICKET_CHANGE On HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
Where
HD_STATUS.NAME Not Like 'Closed%' And
HD_TICKET.HD_QUEUE_ID = '1'
Group By
HD_TICKET.ID
Update:
Update HD_TICKET as HDT
LEFT JOIN HD_TICKET_CHANGE as HDTC
ON HDT.ID = HDTC.HD_TICKET_ID
SET HDT.CUSTOM_FIELD_VALUE2 = CONCAT(DayOfYear(Current_TimeStamp()) - DayOfYear(HDT.Custom_Field_Value14), 'D ', Date_Format(AddTime("2000-00-00 00:00:00", Sec_To_Time(TIMESTAMPDIFF(Second,HDT.Custom_Field_Value14,Current_TimeStamp()))), '%kH %iM'))
where
(HDT.ID in (<TICKET_IDS>))


Reasons Why We Did What We Did
The reason we didnt just use HD_TICKET.MODIFIED is because this changes every time a ticket rule runs on something, so our DHM counters would never get higher than 14 or 15 before being reset due to the 15min ticket rule

The reason we didnt use the HD_TICKET_CHANGED.MODIFIED field and chose instead to populate our own is because we were having issues where the ticket rule a) would always select the first modified and b) we had all kinds of trouble running a sub select statement and using grouping, this just made it a little simpler for us

All in all its proved quite useful for us so far, though we only got it working today so I'm asking for anyone else out there to test and see how they go and let me know any issues with it.

Any one else who also has any improvements to the idea (ie can get rid of the need for multiple rules and/or can get rid of the two fields being needed) then you're a space cadet and I'd love to hear them, I'm no sql whiz but did enjoy learning a lot more while doing this so I'm happy for input.

Also, yes the queries could be cleaned up a lot, the idea is what I'm mostly writing this for and the second update query which does most of the work.


Cheers in advance everyone hope it helps!

Col