/build/static/layout/Breadcrumb_cap_w.png
10/22/2018 127 views
I have a custom rule that transfers a ticket from KACE to JIRA via email when the status is changed. The email it sends contains a link to the ticket, the user that submitted it originally, and all the notes that have been added to the KACE ticket thus far. 

The downside is, it will continue to email every time the KACE ticket is saved which creates multiple JIRA tickets as well. I figured we could leverage one of the CUSTOM_FIELD_VALUES but I can't find a way to select them without getting an error. 

How do I select CUSTOM_FIELD_VALUE0 so that I can update it to prevent multiple emails. The rule worked fine (more or less) until I added the highlighted section, and now I get an 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 '.COMMENT, -- $comment C.DESCRIPTION"

**********************SELECT QUERY**********************
    SELECT
      -- ticket fields
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      HD_TICKET.CUSTOM_FIELD_VALUE0,
      -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',H.COMMENT,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
      -- about the submitter
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      -- about status
      S.NAME AS STATUS,   -- $status
      -- -- example of static distribution list
      'JIRA@DOMAIN.COM' AS NEWTICKETEMAIL -- $newticketemail
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.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
     C.DESCRIPTION LIKE '%XFER to JIRA%'
    AND
     HD_TICKET.CUSTOM_FIELD_VALUE0 = '1'
      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1

**********************EMAIL MESSAGE**********************
Subject: $title 
Message: 
http://helpdesk/adminui/ticket.php?ID=$ticknum
Reporter: $submitter_fname
$history

**********************UPDATE QUERY**********************
UPDATE HD_TICKET
   SET HD_TICKET.CUSTOM_FIELD_VALUE0 = '0'
WHERE
   HD_TICKET.ID in (<TICKET_IDS>)
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
MySQL comments are preceded by two dashes then a space.
 HD_TICKET.CUSTOM_FIELD_VALUE0, --$toggle
should be:
HD_TICKET.CUSTOM_FIELD_VALUE0, -- $toggle

Also, the other lines have the $whatever to help show what the variable name will be in the email template. As you aren't assigning an alias to the column, there's no need to add the comment. If you were going to refer to it, then the line would have to include an alias, like this:
HD_TICKET.CUSTOM_FIELD_VALUE0 as TOGGLE -- $toggle

Your update statement might be broken, but since you didn't put it in a code section, it's hard to know if the browser is rendering the ticket IDs variable. The last line should be:
HD_TICKET.ID = <TICKET_IDS>
You also have more opening parenthesis than closing, so double check that.





Answered 10/23/2018 by: chucksteel
Red Belt

  • Thank you for your time looking at this! I added code brackets to the original post to make it easier to read.

    You were correct on the comment spacing, and the select statement and email complete without error, but the update statement still throws an error like it's not passing the TICKET_IDS correctly.

    Executing Update Query... 10/23/2018 11:21:19> 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 ')' at line 4] in EXECUTE("UPDATE HD_TICKET SET HD_TICKET.CUSTOM_FIELD_VALUE0 = '' WHERE HD_TICKET.ID in ( )")
    • The select query needs to return a column named ID in order to populate the <TICKET_IDS> variable. Your query has the ID column aliased as TICKNUM, so that isn't happening. Either remove the alias on the column, or select HD_TICKET.ID again without an alias.
      • You were spot on again, thank you so much for the help!