/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


I am trying to create a ticket rule that will send an email to the submitter when that ticket is assinged.

05/02/2016 429 views
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

All 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

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