Custom SQL Help
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' HD_TICKET.ID in (<TICKET_IDS>) WHERE
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
5 years ago
Top Answer
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.
Comments:
-
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 ( )") - pandrews 5 years ago-
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. - chucksteel 5 years ago
-
You were spot on again, thank you so much for the help! - pandrews 5 years ago
