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

Comments

  • What exactly are you trying to report on?
  • 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?)
  • Thank you Hobbsy. That is exactly what we are attempting to do. I will set that up.
  • Cool only too pleased to help, mark that one up to me as an answer ;o)
Please log in to comment

Answers

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

Answered 06/16/2015 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share