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.

Answer Summary:
Cancel
3 Comments   [ + ] Show Comments

Comments

  • Have you tried clicking on the check box next to Owner Change for Owner in the Email on Events section?
  • Yes, I have. That only sends an email to original Owner stating the ticket was updated.
  • When I ran this, it threw an error in the line [GROUP BY OLIST.EMAIL], so I removed it. Now it works fine.
    • 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.
      • 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?
Please log in to comment

Answers

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

Answered 07/29/2014 by: grayematter
Fourth Degree Black Belt

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