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:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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

Answers

Answer this question or Comment on this question for clarity