KACE Product Support

This update statement seems to work. I used a label called Round_Robin. This could be changed to your liking. 

 

 

Select Statement

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%' 
  and ( HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL)

Update Statement

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
  JOIN USER_LABEL_JT ON UL.USER_ID = O.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 = 'Round_Robin') RR ON O.ID=RR.USER_ID /* Change Round_Robin to your Label Name*/
where
  O.BUDGET_CODE<> 'out'
and RR.USER_ID != ''
  and T.OWNER_ID=0
  and T.ID=<TICKET_IDS>
ORDER BY MAXC ASC, RAND()
LIMIT 1 )  BATTERUP)
WHERE HD_TICKET.ID =<TICKET_IDS>

That's a good one. You should be able to use this as your new update query. I just chose round robin as the label. Change if necessary to match your environment.

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
JOIN USER_LABEL_JT ULJ ON ULJ.USER_ID = O.ID
JOIN LABEL L ON (L.ID = ULJ.LABEL_ID AND L.NAME = 'ROUND-ROBIN') -- add round robin label here
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=
ORDER BY MAXC ASC, RAND()
LIMIT 1 ) BATTERUP)
WHERE HD_TICKET.ID =

This is something I'm trying to implement into our Service Desk. You bring up a very good point. All our infrastructure guys are a part of the same label. Would it be a good idea to make different labels for certain IT personal and, just add the label to the selected queue? Just an idea but I think the rule would run against any users tied to the queue am I right?

I must give credit for this to our Dell Services guy - the God like Kevin Wright. We have the same situation in that we needed a way to care for Out of Office techs, who shouldn't get tickets when they're out and Managers who needed visability into the queue but shouldn't get tickets assigned.

We use two different labels (one for each of the above named groups) to sort this out.

Select =
select HD_TICKET.*
from HD_TICKET
where HD_TICKET.OWNER_ID = 0
and HD_TICKET.HD_QUEUE_ID= 1

Update =
update HD_TICKET
set HD_TICKET.OWNER_ID = (select USER_ID from (select T.ID, O.ID USER_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 from HD_TICKET WHERE CREATED > CURDATE() GROUP BY OWNER_ID) T2 /* create table of all owners' oldest tickets created today */
ON O.ID=T2.OWNER_ID


where O.ID
not in (select ULJT.USER_ID
from USER_LABEL_JT ULJT
join LABEL L on L.ID=ULJT.LABEL_ID
where L.NAME like 'U-Out of office') /* exclude members of this label from selection */

and O.ID
not in (select ULJT.USER_ID
from USER_LABEL_JT ULJT
join LABEL L on L.ID=ULJT.LABEL_ID
where L.NAME like 'U-Queue Manager') /* exclude members of this label from selection */

ORDER BY MAXC ASC, RAND()
) BATTERUP2

where

USER_ID
in (select ULJT.USER_ID
from USER_LABEL_JT ULJT
join LABEL L on L.ID=ULJT.LABEL_ID
where (L.NAME like 'U-T1 Service Desk' /* or '%T1 Service Desk%' */) ) /* narrow selection further to members of these labels for routing to work units in the same queue */
limit 1)

where
(HD_TICKET.ID in ())