/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


How to notify any user or group by email when a new ticket is created [Specific to Queue]

03/04/2016 717 views
Hi, 
I had created a ticket rule using the instruction on the link below and it was working fine till I created an additional Queue. I think I need to specify the queue ID somewhere in the SQL query statement. Does anyone know how to do this?

Thank you in advance.

Jim

https://support.software.dell.com/k1000-systems-management-appliance/kb/111222
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
In the where portion of your SQL select statement add this line:
,AND HD_TICKET.HD_QUEUE_ID = 1

This would go after the description like statement and before the group by clause.

Answered 03/07/2016 by: chucksteel
Red Belt

  • Thanks Chuck. Works great.
  • I am trying to do the same thing but it is not working on my K1 server. Could it be that I am on a newer version now? I am on Version: 7.0.121306. Here is my error:

    There were syntax errors in your query.
    mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = ' at line 41] in EXECUTE("SELECT -- ticket fields HD_TICKET.ID, -- $id HD_TICKET.ID AS TICKNUM, -- $ticknum HD_TICKET.TITLE, -- $title DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified -- change fields C.COMMENT, -- $comment C.DESCRIPTION, -- $description GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n', H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://k1000.corp.ftch.com/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n') ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history -- about the updater UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional -- about the owner OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user -- about the submitter SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email -- about priority P.NAME AS PRIORITY, -- $priority -- about status S.NAME AS STATUS, -- $status -- about impact I.NAME AS IMPACT, -- $impact -- about category CAT.NAME AS CATEGORY, -- $category -- other fields -- -- example of static distribution list 'helpdesk@####.com' AS NEWTICKETEMAIL -- $newticketemail FROM HD_TICKET /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID= /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID WHERE ,AND HD_TICKET.HD_QUEUE_ID = 1 C.DESCRIPTION LIKE '%CREATED%' /* this is necessary when using group by functions */ GROUP BY HD_TICKET.ID HAVING 1=1 and HD_TICKET.HD_QUEUE_ID = '1' ")
    • You have some extra stuff in it. The end of the statement should look like this:
      WHERE HD_TICKET.HD_QUEUE_ID = 1 C.DESCRIPTION LIKE '%CREATED%'
      GROUP BY HD_TICKET.ID

      I removed the comment and some extra bits that aren't necessary.
      • Sorry but i am a bit new to working with SQL queries. Here is what my Select SQL query looks like. Any help/direction would be great.

        SELECT
        -- ticket fields
        HD_TICKET.ID, -- $id
        HD_TICKET.ID AS TICKNUM, -- $ticknum
        HD_TICKET.TITLE, -- $title
        DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
        DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
        -- change fields
        C.COMMENT, -- $comment
        C.DESCRIPTION, -- $description
        GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
        H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://k1000.####.com/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
        ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
        -- about the updater
        UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
        UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
        UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
        IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
        -- about the owner
        OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
        OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
        OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
        IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
        -- about the submitter
        SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
        SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
        SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
        -- about priority
        P.NAME AS PRIORITY, -- $priority
        -- about status
        S.NAME AS STATUS, -- $status
        -- about impact
        I.NAME AS IMPACT, -- $impact
        -- about category
        CAT.NAME AS CATEGORY, -- $category
        -- other fields
        -- -- example of static distribution list
        'helpdesk@####.com' AS NEWTICKETEMAIL -- $newticketemail
        FROM HD_TICKET
        /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
        AND C.ID=<CHANGE_ID>
        /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
        /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
        /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
        /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
        /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
        /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
        /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
        /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
        WHERE
        ,AND HD_TICKET.HD_QUEUE_ID = 1
        C.DESCRIPTION LIKE '%CREATED%'
        /* this is necessary when using group by functions */
        GROUP BY HD_TICKET.ID
        HAVING 1=1
      • Replace this:
        WHERE
        ,AND HD_TICKET.HD_QUEUE_ID = 1
        C.DESCRIPTION LIKE '%CREATED%'
        /* this is necessary when using group by functions */
        GROUP BY HD_TICKET.ID
        HAVING 1=1

        With this:
        WHERE HD_TICKET.HD_QUEUE_ID = 1 C.DESCRIPTION LIKE '%CREATED%'
        GROUP BY HD_TICKET.ID

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