/build/static/layout/Breadcrumb_cap_w.png

Email Sent On Ticket Creation

So I'm having an issue with KACE generating a one time email to be sent to our department managers when certain criteria are met in a help desk ticket. Currently, my code looks like this:

select HD_TICKET.*,
                        HD_TICKET.ID AS TICKNUM, -- $ticknum
                        'MPhillips@strawbridge.net' AS RECIPIENT,
                        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_TICKET.CUSTOM_FIELD_VALUE0 like '%Imaging%')
                        and ((  HD_CATEGORY.NAME like '%User Account::TrackLynx/SeniorLynx Account%') 
                        or (  HD_CATEGORY.NAME like '%Software::Adobe%')
                        or (  HD_CATEGORY.NAME like '%Software::Installation Request%')
                        or (  HD_CATEGORY.NAME like '%FileMaker::New Account%')
                        or (  HD_CATEGORY.NAME like '%FileMaker::New Request%')
                        or (  HD_CATEGORY.NAME like '%Hardware::Monitor%')
                        or (  HD_CATEGORY.NAME like '%Hardware::Physical Desktop%')
                        or (  HD_CATEGORY.NAME like '%Other%') and HD_TICKET.HD_QUEUE_ID = 2 ))

That sends the email that I want generated and at the same time sets the department manager as the approver via this update query:

update HD_TICKET
    set HD_TICKET.APPROVER_ID = 260, HD_TICKET.APPROVE_STATE = "opened"
  where HD_TICKET.ID in ()

Now, my problem is that with my code being set to "On Ticket Save" the department manager gets the same approval email every time a change is made to the ticket, which I obviously don't want. I've tried adding C.DESCRIPTION LIKE 'TICKET CREATED%' as well as HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%' but both of those lines break the rule entirely and do not generate an email to the department manager.

I've also tried setting HD_TICKET.APPROVER_ID != 260 as a catch after the first time the rule runs, but also to no avail.

Any suggestions?


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 7 years ago
Red Belt
1

Top Answer

You aren't joining to the HD_TICKET_CHANGE table so you can't reference it you need to add the following join:
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID 
 and C.ID=<CHANGE_ID>

KACE will insert the change ID where <CHANGE_ID> is listed at run time. 

You can now add the C.DESCRIPTION LIKE '%Ticket Created%'  to the criteria.

Also, make sure that your update query includes the <TICKET_IDS> in the where HD_TICKET.ID in (<TICKET_IDS>). It's possible that this website remove it because it looks like a HTML tag.


Comments:
  • Awesome! Worked perfectly. Thanks! - michaelsdk 7 years ago
  • Follow up question:

    In the Run Update Query, I'm also trying to set a CC list. I have it currently as:

    update HD_TICKET
    set HD_TICKET.APPROVER_ID = 214, HD_TICKET.APPROVE_STATE = "opened"
    set HD_TICKET.CC_LIST = 'itdept@strawbridge.net'
    where HD_TICKET.ID in (<TICKET_IDS>)

    Now my approver ID is not being set (worked before) and it's not updating my CC list. I tried "and" instead of "set" for that line, but then only my approver ID was set. - michaelsdk 7 years ago
    • The fields that you want to update should be in a comma separated list. You already have two fields that you are updating APPROVER_ID and APPROVE_STATE, so to add a third to the list use a comma and CC_LIST = 'email':

      set HD_TICKET.APPROVER_ID = 214, HD_TICKET.APPROVE_STATE = "opened", HD_TICKET.CC_LIST = 'itdept@strawbridge.net' - chucksteel 7 years ago
      • The simplest solutions... Thanks again! - michaelsdk 7 years ago

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