Setup a rule in KACE Service Desk
I am trying to setup a rule that looks at any change ticket with the following criteria:
ticket type of "Scheduled Change"
Ticket Status != "Change Completed"
Comment Contains "Approved"
once found update the
Ticket Status = "Approved"
Change Ticket owner = Ticket Submitter
Here is my SQL update statement:
update HD_TICKET, HD_STATUS as T5
set OWNER_ID = SUBMITTER_ID WHERE TICKET_ID in (<TICKET_IDS>), HD_TICKET.HD_STATUS_ID = T5.ID,
HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
where T5.NAME = 'Approved' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
(HD_TICKET.ID in (<TICKET_IDS>))
Here is the error I receive:
02/17/2017 16:39:55> Starting: 02/17/2017 16:39:55 02/17/2017 16:39:55> Executing Select Query... 02/17/2017 16:39:55> selected 1 rows 02/17/2017 16:39:55> Adding ticket comments... 02/17/2017 16:39:56> updated 1 tickets 02/17/2017 16:39:56> Executing Update Query... 02/17/2017 16:39:56> 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 ' HD_TICKET.HD_STATUS_ID = T5.ID, HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened' at line 2] in EXECUTE("update HD_TICKET, HD_STATUS as T5 set OWNER_ID = SUBMITTER_ID WHERE TICKET_ID in (47450), HD_TICKET.HD_STATUS_ID = T5.ID, HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), HD_TICKET.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING), HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT) where T5.NAME = 'Approved' and HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and (HD_TICKET.ID in (47450))") 02/17/2017 16:39:56> Ending: 02/17/2017 16:39:56
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
You have an extra where statement here:
set OWNER_ID = SUBMITTER_ID WHERE TICKET_ID in (<TICKET_IDS>),
The update statement should only have one where statement and it is at the end.
The part to set the owner should just be:
set OWNER_ID = SUBMITTER_ID
