/build/static/layout/Breadcrumb_cap_w.png

K1000 Service Desk - Custom Ticket Rule

I'm interested in a rule that would change a ticket status from New to Open if an owner makes a comment. Assistance?

1 Comment   [ + ] Show comment
  • Actually - I could even simplify this to when the ticket is assigned. What is the best way to identify a ticket that is not unassigned? SQL is not my thing... - jegolf 8 years ago
    • I ticket that is "not unassigned" will have an OWNER_ID != 0 (not equal to 0). - chucksteel 8 years ago

Answers (1)

Posted by: chucksteel 8 years ago
Red Belt
1
Here is the rule that we use for this case. It will match tickets that are assigned to a technician with a status of new:
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,
HD_TICKET_CHANGE.COMMENT AS COMMENT                    
                        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 (((  (1  in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.FULL_NAME is not null)) ) AND HD_STATUS.NAME = 'New') and HD_TICKET.HD_QUEUE_ID = 2 and HD_TICKET.OWNER_ID != HD_TICKET.SUBMITTER_ID)
Note that since we also use this rule to email the submitter that their ticket has been assigned to a technician that it will not match if the owner is the submitter. This suppresses the notifications for tickets that you may open for yourself, which is something that our technicians do when tracking projects. Also, be sure to update the HD_QUEUE_ID = 2 to match your queue's ID.

Here is the update query that changes the ticket status to Open:
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>))

Don't be a Stranger!

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

Sign up! or login

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