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

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, 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
Please log in to comment

Answers

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. 
Answered 09/08/2017 by: chucksteel
Red Belt

  • 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
Please log in to comment
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.
Answered 09/08/2017 by: chucksteel
Red Belt

  • 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>
    • 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.
      • How can I test/troubleshoot this?
      • 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.
Please log in to comment
Answer this question or Comment on this question for clarity