File Attachment URL's in KACE K1000 tickets
We were using 5.2 for a long time, but recently upgraded the k1000 to the most current version (at the time of this questions, v5.4.70402)
when we were on the old version we had a custom rule on the ticketing system to add url's to the tickets for the file attachments so we could view the screenshots, excel files etc from a click in the email to the technician. This seems to be broken now in 5.4.
the select query we had was this:
select C.ID FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID> and DESCRIPTION LIKE '%Added Attachment%' WHERE 1=1
The update query we had was this:
UPDATE HD_TICKET_CHANGE C JOIN (select C.ID, GROUP_CONCAT( CONCAT('Added Attachment http://k1000.comapnyhidden.org/packages/hd_attachments/',C.ID,'/',REPLACE(FILE_NAME,' ','%20')) SEPARATOR '\n') GROUPURLFIX,GROUP_CONCAT( CONCAT('Added Attachment \'',FILE_NAME,'\'') SEPARATOR '\n') GROUPURLOLD FROM HD_TICKET_CHANGE C JOIN HD_ATTACHMENT A ON A.HD_TICKET_CHANGE_ID=C.ID WHERE C.ID=<TICKET_IDS> and DESCRIPTION LIKE '%Added attachment%' GROUP BY C.ID) REPVALUES ON REPVALUES.ID=C.ID and DESCRIPTION LIKE '%Added attachment%' SET DESCRIPTION=CONCAT( SUBSTRING(DESCRIPTION FROM 1 FOR LOCATE(REPVALUES.GROUPURLOLD,DESCRIPTION)-1), REPVALUES.GROUPURLFIX, SUBSTRING(DESCRIPTION FROM (LOCATE(REPVALUES.GROUPURLOLD,DESCRIPTION)+LENGTH(REPVALUES.GROUPURLOLD)) FOR LENGTH(DESCRIPTION)) )
When I try to manually run the query, I get this error
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 '= and DESCRIPTION LIKE '%Added Attachment%' WHERE 1=1 and HD_TICKE' at line 3] in EXECUTE("SELECT COUNT(*) FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID= and DESCRIPTION LIKE '%Added Attachment%' WHERE 1=1 and HD_TICKET.HD_QUEUE_ID = '1' LIMIT 1")
I'm not any sort of sql person so I don’t know what this means. Any help would be appreciated. Thanks!