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
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
9 years ago
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
This is a method we have successfully used in the past for customers (if that is what you need?) - Hobbsy 9 years ago