/build/static/layout/Breadcrumb_cap_w.png

Can KACE Service Desk kick out email to owner each time owner is changed?

It should be simple and should be a canned rule inside of KACE. I want one rule to notify the owner that a ticket has been assigned to them.  I don't want this just to trigger on ticket creation (which I have found), I want an email to the owner each time the owner is changed.

To reiterate, each time a new owner is selected and the ticket is saved, I want that particular owner to get an email notifying them that there is a ticket assigned to them. Why is this sophisticated software not able to do this simple task out of the box?  



2 Comments   [ + ] Show comments
  • This is a script I use for doing exactly what you are after:

    SELECT
    HD_TICKET.ID,
    HD_TICKET.ID AS TICKNUM,
    HD_TICKET.TITLE,
    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,
    C.COMMENT,
    C.DESCRIPTION,
    GROUP_CONCAT(CONCAT('----- Change by ',
    UPDATER.EMAIL,
    ' at ',
    H.TIMESTAMP,
    ' -----
    ',
    H.DESCRIPTION,
    '
    ',
    H.COMMENT,
    '

    Please see your ticket at http://kbox/userui/ticket.php?ID=',
    H.HD_TICKET_ID,
    '
    ')
    ORDER BY H.ID DESC
    SEPARATOR '
    ') HISTORY,
    UPDATER.USER_NAME AS UPDATER_UNAME,
    UPDATER.FULL_NAME AS UPDATER_FNAME,
    UPDATER.EMAIL AS UPDATER_EMAIL,
    IF(UPDATER.FULL_NAME = '',
    UPDATER.USER_NAME,
    UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL,
    OWNER.USER_NAME AS OWNER_UNAME,
    OWNER.FULL_NAME AS OWNER_FNAME,
    OWNER.EMAIL AS OWNER_EMAIL,
    IFNULL(OWNER.USER_NAME, 'Unassigned') OWNER_USER,
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME,
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL,
    P.NAME AS PRIORITY,
    S.NAME AS STATUS,
    I.NAME AS IMPACT,
    CAT.NAME AS CATEGORY,
    HD_QUEUE.NAME AS QUEUENAME
    FROM HD_TICKET
    JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
    AND C.ID = <CHANGE_ID>
    JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
    JOIN HD_PRIORITY P ON P.ID = HD_PRIORITY_ID
    JOIN HD_STATUS S ON S.ID = HD_STATUS_ID
    JOIN HD_IMPACT I ON I.ID = HD_IMPACT_ID
    JOIN HD_CATEGORY CAT ON CAT.ID = HD_CATEGORY_ID
    LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
    LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
    JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
    WHERE
    (C.DESCRIPTION LIKE '%Changed ticket Owner%')
    and OWNER.USER_NAME is not null
    GROUP BY OWNER.EMAIL
    HAVING 1 = 1

    Enable Email each recipient in query results
    Subject: Whatever
    Column containing email addresses: OWNER_EMAIL
    Message: whatever
    On Ticket Save - Druis 6 years ago
  • Druis, this is not working. When I click "View KACE Ticket Search Results" i see syntax errors. I do not know if these syntax errors are the cause. I have another rule running, which does work, that also receives syntax errors. It's confusing.

    Here is the last run log: 09/08/2017 12:03:57> Starting: 09/08/2017 12:03:57 09/08/2017 12:03:57> Executing Select Query... 09/08/2017 12:03:57> selected 0 rows - mmarchese@cookcountytreasurer.com 6 years ago

Answers (2)

Posted by: chucksteel 6 years ago
Red Belt
0
The system rule for email owner on ticket change should accomplish this. The new owner should be notified that the ticket was changed when it was saved. It won't be a specific, this ticket has been assigned to you message, but it should notify them. 

Comments:
  • The thing is, I want it to be specific. I want my subject line to say: "Ticket xxx has just been assigned to you". Then as added bonus, I would love if the body could read: "Ticket xxx has just been assigned to you by [USERNAME]"
    - Mo - mmarchese@cookcountytreasurer.com 6 years ago
Posted by: chucksteel 6 years ago
Red Belt
0
This select statement will identify ticket changes and includes the necessary fields that you need. I use it to notify the previous owner that a ticket has been removed from their assigned tasks, but it will work for your situation.
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,
U4.EMAIL as PREVIOUS_OWNER_EMAIL,
U5.EMAIL as NEW_OWNER_EMAIL,    U6.EMAIL as CHANGER_EMAIL,U6.FULL_NAME as CHANGER_NAME           
                        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>
JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID>
 and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='OWNER_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 U4 on U4.ID = HD_TICKET_CHANGE_FIELD.BEFORE_VALUE
LEFT JOIN USER U5 on U5.ID = HD_TICKET_CHANGE_FIELD.AFTER_VALUELEFT JOIN USER U6 on U6.ID = HD_TICKET_CHANGE.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 ((  (1  in (select 1 from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)) ))
You would want to specify NEW_OWNER_EMAIL for the column containing email address in your rule. For the message $changer_name will contain the name of the person that made the change.

Comments:
  • Chuck, this is not working. When I click "View KACE Ticket Search Results" i see syntax errors. I do not know if these syntax errors are the cause. I have another rule running, which does work, that also receives syntax errors. It's confusing.

    Here is the last run log: 09/08/2017 12:00:20> Starting: 09/08/2017 12:00:20 09/08/2017 12:00:20> Executing Select Query... 09/08/2017 12:00:20> - mmarchese@cookcountytreasurer.com 6 years ago
    • You can't use the ticket search results as the query is designed to be run on ticket save and uses the <CHANGE_ID> variable. - chucksteel 6 years ago
      • How can I test/troubleshoot this? - mmarchese@cookcountytreasurer.com 6 years ago
      • I have a queue that I use for creating new rules and perform my testing there. For trouble shooting you can turn on the Email results option in the rule and it will send you a table of the results when the rule fires. - chucksteel 6 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