/build/static/layout/Breadcrumb_cap_w.png

Save rule change for service tickets

I have noticed with a rule we have in place for our service tickets, when a ticket is changed to OPEN, it timestamps it with the current date and time in the TIME_OPENED field.  We are trying to do reporting on this opened timestamp based on the time the ticket is opened and the ticket was created.  Unfortunately, every time the ticket is moved from a stalled state to an opened state, it is stamped again with the current date and time in the TIME_OPENED field.  I was hoping to only update the TIME_OPENED field when the field is in a NULL state, I have attempted to changed the UPDATE section of the rule by add the NULL when marking the field, but it did not change:

update HD_TICKET as T, HD_STATUS as STATUS
set T.HD_STATUS_ID = 1,   <--status ID for Opened
T.TIME_OPENED  = IF(((STATUS.STATE = 'opened') and (T.TIME_OPENED is NULL)), NOW(), T.TIME_OPENED),
T.TIME_CLOSED  = IF(STATUS.STATE = 'closed', NOW(), T.TIME_CLOSED),
T.TIME_STALLED = IF(STATUS.STATE = 'stalled', NOW(), T.TIME_STALLED)

where T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID
and (T.ID in (<TICKET_IDS>))

looking for help in setting this up or if there is a better way.

Thanks,

Bill

4 Comments   [ + ] Show comments
  • What exactly are you trying to report on? - chucksteel 9 years ago
  • My guess is that you are trying to capture the first time that a ticket changed from a Null (New) state into an opened state, but as the rule keeps updating and overwriting you are losing that data? If that is the case I would implement a switch field with a zero value that is set to '1' when the first change happens and a rule that copies the value in that field to a custom field. Using this method would mean that you are able to capture and report on that initial change.

    This is a method we have successfully used in the past for customers (if that is what you need?) - Hobbsy 9 years ago
  • Thank you Hobbsy. That is exactly what we are attempting to do. I will set that up. - billmh 9 years ago
  • Cool only too pleased to help, mark that one up to me as an answer ;o) - Hobbsy 9 years ago

Answers (1)

Posted by: chucksteel 9 years ago
Red Belt
0
You can also capture this data using the HD_TICKET_CHANGE table. For example, here's a report that shows tickets that had a status of "New" for over one hour in the past 30 days:
SELECT HD_TICKET.ID, HD_TICKET.CREATED,
HD_TICKET_CHANGE.TIMESTAMP,
TIMEDIFF(HD_TICKET_CHANGE.TIMESTAMP, HD_TICKET.CREATED),
HD_TICKET_CHANGE.DESCRIPTION
FROM ORG1.HD_TICKET
JOIN HD_TICKET_CHANGE on HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID 
    and HD_TICKET_CHANGE.DESCRIPTION like 'Changed ticket status from "New"%'
WHERE TIME_TO_SEC(TIMEDIFF(HD_TICKET_CHANGE.TIMESTAMP, HD_TICKET.CREATED)) > 3600
and HD_TICKET.CREATED > NOW() - INTERVAL 30 DAY


Comments:
  • Nice Work ;o) - Hobbsy 9 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ