Scripting

Hi,

I found a thread on here about assigning tickets in a round-robin fashion via a script.  I can't remember the link for it but this is the script....

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


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>

Now, first off let me state I have NO scripting experience on the Kace box and very little SQL experience so I need some detailed instructions.  I have no idea what to do with this script.  There were no instructions at all on it.  I asked Kace's support site and they told me that I needed to create a new Ticket Rule, so I did.  I put the first part of the script in the "select query" box, and assumed that from "UPDATE HD_TICKET" went in the "update query" box.  However it doesn't work and I have no idea why.  Can anyone help me get this working ?  We would really like to implement this.

If I click on "View search results" under the 'select query' box this is what I get...

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 'LIMIT 1' at line 18] in EXECUTE("SELECT COUNT(*) FROM (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 # */) _ADODB_ALIAS_ LIMIT 1 LIMIT 1")

Thanks!