Good morning.

I have been getting emails from new tickets using the SQL statement published on the kace support site. It 'seems' to work but I click 'View Ticket Search Results' I receive an error which I am not sure I understand enough to resolve on my own. 

Here is the error and if anyone has some suggestions, I am very interested... 

mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_I' at line 41]

The SQL statement is below

Thanks in advance,
--james

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://kace/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 
 'some-email@somebiz.nada' 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
 C.DESCRIPTION LIKE 'TICKET CREATED%'
 /* this is necessary when using group by functions */
 GROUP BY HD_TICKET.ID HAVING 1=1
2 Comments   [ + ] Show Comments

Comments

  • I found that there is a warning on KB https://support.software.dell.com/kb/SOL111222
    which states that if you run the 'View Ticket Search Results', you will throw that error
  • I copied the SQL statement for the version 6.0 or newer and it doesn't email a ticket at all
Please log in to comment

Answers

0
I don't know how fussy the SQL parser is in your situation but I can't help noticing that all the comments (I presume they're comments...) have a space before the closing "*/" except for the one you mention.
Answered 05/05/2015 by: VBScab
Red Belt

Please log in to comment
0
This rule uses the <CHANGE_ID> variable that is only available when a rule runs. KACE substitutes the ID of the change at runtime allowing the rule to access the most recent change on the ticket. In your rule it is used on this line:
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_ID>
The <CHANGE_ID> doesn't appear in your post above (I'm hoping it appears here) because it looks like an HTML tag. Double check that it is there in the KACE rule, though.

This variable is the reason that you can't use the View Ticket Search Results link to test the rule because it only gets populated when a ticket is saved.
Answered 05/06/2015 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share