/build/static/layout/Breadcrumb_cap_w.png

KACE: sending new owner an email when ticket is re-assigned

Hello, I have checked with KACE support, read these forums, and searched the Internet but cannot find an answer to my question.

I have a Change Management queue. Tickets are created and immediately assigned to the "CSA" user. The CSA receives an email notification due to the check mark in All Changes under Owner in the Email on Events section of the queue configurations. The CSA goes into KACE updates the ticket. I wrote a rule that automatically assigns the ticket to the "CCB" user once the CSA checks a box on the ticket form. I cannot get KACE to send an email notification to CCB.  I need KACE to email a new Owner when an existing ticket is assigned to them. Like I said, I searched for an answer and KACE KB article 111222 does not help upon my reading. I don't believe Escalations are appropriate here. Any guidance is appreciated.

Thank you for your time and attention.


4 Comments   [ + ] Show comments
  • Have you tried clicking on the check box next to Owner Change for Owner in the Email on Events section? - Timi 6 years ago
  • Yes, I have. That only sends an email to original Owner stating the ticket was updated. - sweetsteve 6 years ago
  • When I ran this, it threw an error in the line [GROUP BY OLIST.EMAIL], so I removed it. Now it works fine. - ondrar 5 years ago
    • ondrar, can you share your Select SQL for this rule? I am trying to use this and I am having no luck getting this rule to work. - totero21 5 years ago
      • I can if you really want, but it's literally the same thing as below, just with this line added to the WHERE statement (because I only want it to run on this one queue):

        and HD_TICKET.HD_QUEUE_ID = 5

        And this line removed (because it gave me an error):

        GROUP BY OLIST.EMAIL

        "Email each recipient in query results" is checked, the Subject and Message are filled out, and "Column containing email addresses" is OWNER_EMAIL.

        Does that help? - ondrar 5 years ago
  • Will this also work any time the owner is changed? I'm looking for an email to be sent out to the owner each time owner is changed and the ticket is saved. Will this accomplish that? - mmarchese@cookcountytreasurer.com 3 years ago
    • Yes, this works any time the owner is changed. - ondrar 3 years ago

Answers (1)

Answer Summary:
Posted by: grayematter 6 years ago
5th Degree Black Belt
1

The following query should work as a ticket rule.  I haven't tested it though.  It is similar to a rule I have in our setup.  The email column is "OWNER_EMAIL" and should pick up the new owner.  You can test with a valid change_id for your particular scenario.


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 OLIST.EMAIL
HAVING 1 = 1


Comments:
  • Thank you very much. How do I test with a "valid change ID?" Do I manually add that (if so, where do I get it) or is that a variable that KACE will enter? - sweetsteve 6 years ago
    • Check the query in a query browser like MySQL Workbench. Connection instructions are available at http://www.kace.com/support/resources/kb/solutiondetail?sol=114992. - grayematter 6 years ago
    • You can get a Change ID from the HD_TICKET_CHANGE table. Find an appropriate ticket id for your scenario and query that table for the change id. - grayematter 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