I'm trying to get a rule created when I have new service desk tickets in place and they are unassigned.  Once they become assigned I would like to send an email to the submitter that there issue is being worked on.  Thanks in advance.
1 Comment   [ + ] Show Comment

Comments

  • I really don't want to use the out of the box can rule. If there is a rule I can write via sql this is what I have so far but not sure if this is everything. I would like to have it run when it is updated. And would like to have it send specifc information when the correct owner is set.

    SELECT
    HD_TICKET.ID AS TICKNUM,
    (SELECT FULL_NAME FROM USER WHERE USER.ID=HD_TICKET.SUBMITTER_ID) AS SUBMITTER,
    HD_TICKET.TITLE AS TITLE,
    HD_TICKET.CREATED AS CREATED,
    U.FULL_NAME AS OWNER,
    U.EMAIL AS NEWTICKETEMAIL,
    TQ.NAME AS QNAME,
    TS.NAME AS STATUSNAME,
    TCF.FIELD_CHANGED,
    TCF.BEFORE_VALUE,
    TCF.AFTER_VALUE,
    TCF.ID AS CHANGEID
    FROM
    HD_TICKET
    LEFT JOIN USER U ON U.ID = HD_TICKET.OWNER_ID
    LEFT JOIN HD_STATUS TS ON TS.ID = HD_TICKET.HD_STATUS_ID
    LEFT JOIN HD_QUEUE TQ ON TQ.ID = HD_TICKET.HD_QUEUE_ID
    LEFT JOIN HD_TICKET_CHANGE TC ON TC.HD_TICKET_ID = HD_TICKET.ID
    LEFT JOIN HD_TICKET_CHANGE_FIELD TCF ON TCF.HD_TICKET_CHANGE_ID = TC.ID
    WHERE
    TS.NAME != 'Closed'
    AND TCF.FIELD_CHANGED = 'OWNER_ID'
    and TQ.NAME = 'Customer Service' and TS.NAME = 'New - Not Started' AND U.FULL_NAME = 0
Please log in to comment

Answers

0

This query should match tickets where the owner was changed.

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,
                        SUBSTRING(U2.FULL_NAME, (LOCATE(',', U2.FULL_NAME)+2)) AS SUBMITTER_FIRSTNAME,
                        U2.EMAIL as SUBMITTER_EMAIL,
                        SUBSTRING(TECHNICIAN.FULL_NAME, (LOCATE(',', TECHNICIAN.FULL_NAME)+2)) AS TECHNICIAN_FIRSTNAME,
                        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 TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID 
 and TICKETCHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.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 USER TECHNICIAN on TECHNICIAN.ID = TICKETCHANGE.USER_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_TICKET_CHANGE_FIELD.FIELD_CHANGED='OWNER_ID'

It is based on a rule created by the wizard so there are a lot of extra columns being selected that you might not need. When creating your rule use SUBMITTER_EMAIL as the column containing the email address for the Email each recipient the query results settings.

Answered 05/03/2016 by: chucksteel
Red Belt

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