How do I change the status of a ticket automatically if it gets updated?

Question

When a new ticket is created, it states, "NEW" for its status which makes sense. However, I'd like to make it so that if it gets updated in its comments section, it changes it to opened so that way we know that it is being worked on. It would be nice if this was automatic.

If anyone can tell me or point me in the right direction to make this happen, let me know! Thanks!

Answer

Here is a rule that we run that changes the status from NEW to OPENED on ticket save. Be warned that this is SQL created for our queue setup and may not work correctly on your KACE appliance.

STATUS CHANGE - NEW TO OPENED:

-------------------------------------------------------------------------------------------------------

select HD_TICKET.*,

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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,

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 = 'New') and HD_TICKET.HD_QUEUE_ID = 8 )

----------------------------------------------------------------------------------------------------------

Answer

There are two parts, DirtySoc posted a select statement that will work, but you also need the update statement.

Here is the select statement I use:

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)
                        JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 
                         and HD_TICKET_CHANGE.ID=<CHANGE_ID>
                        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 = 'New') AND HD_TICKET_CHANGE.DESCRIPTION not like '%Created%') and HD_TICKET.HD_QUEUE_ID = 7 )

Be sure to change the HD_QUEUE_ID = 7 to match your queue ID.

Here is the update statement:

update HD_TICKET as T, HD_STATUS as T5
    set T.HD_STATUS_ID = T5.ID, 
T.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED), 
T.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED), 
T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED), 
T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING), 
T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)
  where T5.NAME = 'Open' and 
T.HD_QUEUE_ID = T5.HD_QUEUE_ID and 
        (T.ID in (<TICKET_IDS>))

Comments:

I appreciate your response. I'm not too familiar with SQL to understand what is going on with this statement so I have a couple questions. Excluding the update statement, does this statement do the same thing that DirtySoc statement does? What does the update statement do? Why do I need such a big statement just to change the status from new to opened on Ticket Save. - kyleg4231 7 years ago
- My select statement is basically the same as DirtySoc's but it only matches tickets that are changed but not on initial ticket creation. His where clause only matches tickets with a status of "New", whereas mine matches tickets with a status of "New" that have a change that doesn't include the word "Created". The net effect is that my rule will change the status once a change is made to the ticket.
  
  You're correct that neither of these statements need to be as long as they are. The select statement includes a large number of columns that can be used as variables if you are sending an email along with this ticket change. The update statement is the output from the wizard and it includes extra statements related to update the columns related to when a ticket is opened, closed and stalled, plus the satisfaction rating. - chucksteel 7 years ago

Answer

Have you tried building a fresh rule with the wizard, say if the status is equal to new And the comments are not null then set the status to opened?

How do I change the status of a ticket automatically if it gets updated?

Answers (3)

Posted by:

View more:

Related Questions

Related Links

Related Posts

Software

How do I change the status of a ticket automatically if it gets updated?

Answers (3)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share