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,
                        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
  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?

Answer Summary:
0 Comments   [ - ] Hide Comments


Please log in to comment

Answer Chosen by the Author


You aren't joining to the HD_TICKET_CHANGE table so you can't reference it you need to add the following join:

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.

Answered 07/21/2016 by: chucksteel
Red Belt

  • Awesome! Worked perfectly. Thanks!
  • 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.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.
    • 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'
      • The simplest solutions... Thanks again!
Please log in to comment
Answer this question or Comment on this question for clarity