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!

1 Comment   [ + ] Show Comment

Comments

  • Anyone have any suggestions or help? Thanks!!
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share