/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 7 years ago
Red Belt
1
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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ