I was reading the article http://www.itninja.com/question/auto-assign-tickets-in-a-round-robin which is about setting up your Service Desk to use Round Robin.

This pretty much will work for me but I was wondering how to add one more option. Would it be possible that this rule only works for people that I put in a "Round Robin" Label? We would not like everyone to be in the Round Robin group, just our first tier support.
I tested with this some but could not seem to get it to work the way I wanted it to.
Answer Summary:
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= ORDER BY MAXC ASC, RAND() LIMIT 1 ) BATTERUP) WHERE HD_TICKET.ID =
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

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>

Answered 06/19/2012 by: young020
Black Belt

Please log in to comment
1
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 =
Answered 03/27/2012 by: dchristian
Red Belt

  • Hmm... Looks like its getting cut off...

    The last line should have the "greater than" TICKET_IDS "LESS THAN"
  • It still does not quite work properly, I am using

    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
    JOIN LABEL L ON (L.ID = UL.LABEL_ID AND L.NAME = 'Round_Robin')
    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 =

    I created a user label called Round_Robin. There are 3 people in the group for testing. It will not hand the tickets off. It sees the ticket come in but will not assign it.

    If I remove JOIN LABEL L ON (L.ID = UL.LABEL_ID AND L.NAME = 'Round_Robin') the tasks works fine but it will pick anyone that has ticket privileges .
Please log in to comment
0
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?
Answered 03/27/2012 by: volcy
Orange Belt

  • Yes, the select statement builds at the Queue level. We have a couple of different teams (ServiceDesk & Telecom) sharing this queue, each with their own label. I've also built rules that look at the Queue AND the Category selection with the Category added to the Select statement above.
Please log in to comment
0
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 ())
Answered 03/27/2012 by: jmarotto
Second Degree Green Belt

  • I'm not sure why my code isn't workin;
    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 = 'Test Label') -- 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_2 'Out and About'
    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 = "greater than" TICKET_IDS "LESS THAN"

    I have a test label for my queue and I made the Out and About field in Custom Field 2...I'm very new to SQL so I'm not sure what to look for exactly.
Please log in to comment
Answer this question or Comment on this question for clarity