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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Roonerspism, if yours works that's great. But I've seen many attempts rise and fall. I've got a version in the archives that:
  • incorporates the concept of helpesk hours
  • is very accurrate for reporting purposes
  • tracks time opened and time stalled
  • handles quick changes to states and status
  • facilitate interference from your other rules that may be manipulating status
  • discusses why the OEM appliance cannot do this
  • requires 3 (or 4 if you want opened and stalled) custom fields :( (but with unlimited fields coming in 5.4 this shouldn't be a big deal)
ref: http://itninja.com/question/servers-installation-help03&mpage=1&key=&#

At the very least it will educate you on all the edge cases that need to be considered. Let me know if you would like a copy of it. I would love someone to test it (outside of my bias) so I can publish it.
Answered 11/23/2011 by: GillySpy
Seventh Degree Black Belt

  • This would be great if "404 Page not found."
Please log in to comment
0
Troy and Roonerspism

Thank you. This is brilliant and just what we were after. I got it working after a hour of playing. For some reason the custom_field_value2 actually maps to custom_3 in the ticket layout config. Not sure why this is but SQL Workbench helped my find it.
The only change I made was to move the D, H and M letters to lowercase to match the way the kbox displays the Time Open.

Thanks again for publishing.

Trav
Answered 12/14/2011 by: travang
Yellow Belt

Please log in to comment
0
travang, can you confirm that ALL of the following are true in your helpdesk?:
  • you are running a 24x7x365 helpdesk
  • you do not have nor plan to have any ticket rules that modify states. e.g. a rule that reopens a ticket
  • stalled and opened states have identical meaning in your environment. e.g. "waiting on customer" is the same as "opened".
  • tickets never re-open
  • what is your acceptable margin of error? The margin of error on the above is 1% for tickets in a typical 24x7 helpdesk.
If all of those are true then the posted method should work.

The item about custom fields in the database starting at 0 but 1 in the ui is expected here. i.e. HD_TICKET.CUSTOM_FIELD_VALUE0=CUSTOM_1
Answered 12/15/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0

Hello, 

Has anyone tried this yet in 5.4 version of the k1000?

I tried to use this, but there's a query error in the custom query on the ticket save.  It looks like on line 29 there's an extra ,'  in there for the query. I've removed that, but doing so doesn't update any of my custom fields after the fact when tickets are saved.  

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Closed') and HD_TICKET.HD_QUEUE_ID = 1 )' at line 29

Thanks!
Answered 01/30/2013 by: jwhite6102
White Belt

  • Unfortunately I havent had a chance to upgrade to 5.4 yet, I had heard that it 'breaks' a number of custom queries and scripts. I'll be doing it this weekend, seeing this, I'm thinking I might have to develop a new version of this. I hate breaking things that arent broken haha (mind you there's probably better ways to do it by now too)
  • Greetings! Any luck on your upgrade? Any updates for the custom code here? Thanks!
Please log in to comment
Answer this question or Comment on this question for clarity

Share