We have all incoming tickets assigned to .DISPATCH and then there are couple people that move them to the correct owner groups.  I would like to have that action set the ticket status to opened but the article that should tell me how to do it has been (re)moved during some reorganization of the kace site. faq 613 Does anyone know where this documentation went?

3 Comments   [ + ] Show Comments

Comments

  • I don't know where the documentation went but I have a rule that is similar. I can post it if that would help.
  • I would really appreciate it if you could post it. Thanks
  • Thanks for posting this.
Please log in to comment

Answers

0
Here are the settings for a rule which I use that sends an email to the owner and sets the status of the ticket when the owner is changed:

SQL Select Statement:
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 HD_STATUS.NAME = 'New'  
                        and HD_TICKET.OWNER_ID != HD_TICKET.SUBMITTER_ID

Note that this rule is configured to only select tickets with a status of New and doesn't technically look at tickets where the previous owner was the default.

For the email options the owner's email is in the OWNER_EMAIL column.

Update query:
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>))
Answered 11/14/2014 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share