KACE Product Support

We would like to use round robin for our helpdesk to auto assign ticket to technician.  I found the code in IT ninja and it keep getting error. 

This is what I did:

I created a customer ticket rules called "round robin" and run it "on ticket save". I try the code in MySQL workbeach, the select query section seems working, it display a user in returned.  The update query is the one I have problem.

Select query:

select T.ID, O.USER_NAME, MAXC,IF(MAXC IS NULL, 1, 2) BATTERUP
from
  HD_TICKET T
  JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID
  JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID
  JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID
  JOIN USER O ON O.ID=UL.USER_ID
  LEFT JOIN
      (select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from HD_TICKET WHERE  CREATED > CURDATE() GROUP BY OWNER_ID, HD_QUEUE_ID)T2
              ON    O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID
    LEFT JOIN (select USER_ID from USER_LABEL_JT UL2 JOIN LABEL OUTLABEL
              ON OUTLABEL.ID=UL2.LABEL_ID  and OUTLABEL.NAME='Out of office') OOO ON O.ID=OOO.USER_ID
where
  O.CUSTOM_1<> 'out'
  and OOO.USER_ID IS NULL
  and T.OWNER_ID=0
  and T.ID=123  /* example new ticket # */
ORDER BY MAXC ASC, RAND()
LIMIT 1

Update query:

select HD_TICKET.ID from
  HD_TICKET
  JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
WHERE
  C.DESCRIPTION LIKE 'Ticket Created%'  /* new ticket */
  and ( HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL) /* no owner yet */



UPDATE HD_TICKET
set HD_TICKET.OWNER_ID = (select BATTERUP.ID from  (select O.ID
from
  HD_TICKET T
  JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID
  JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID
  JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID
  JOIN USER O ON O.ID=UL.USER_ID
  LEFT JOIN
      (select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from HD_TICKET WHERE  CREATED > CURDATE() GROUP BY OWNER_ID, HD_QUEUE_ID) T2
              ON    O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID
    LEFT JOIN (select USER_ID from USER_LABEL_JT UL2 JOIN LABEL OUTLABEL
              ON OUTLABEL.ID=UL2.LABEL_ID and OUTLABEL.NAME='Out of office') OOO ON O.ID=OOO.USER_ID
where
  O.CUSTOM_1<> 'out'
  and OOO.USER_ID IS NULL
  and T.OWNER_ID=0
  and T.ID=<TICKET_IDS>
ORDER BY MAXC ASC, RAND()
LIMIT 1 )  BATTERUP)
WHERE HD_TICKET.ID =<TICKET_IDS>

Thanks for your help.

Andy