Auto-assign tickets in a round robin
Acedashdog's post http://itninja.com/question/internal-error-2343.61 got me thinking about a rule we did lately...
Suppose you wanted to auto-assign tickets in a round-robin fashion. Here is how we will define the next owner who gets a ticket:
We can use this to create a rule. In the select query for the rule we want to get a newly created ticket.
Then we want to update it to have an owner by round-robin assignment. This is where we incorporate our query from above
TIP: you may want another rule that would prevent anyone except the manager from manually changing the owner
Suppose you wanted to auto-assign tickets in a round-robin fashion. Here is how we will define the next owner who gets a ticket:
- Owner is not marked out of the office by label called "Out Of Office" or by entering the word "out" in the custom1 user field
- Since tickets are assigned at creation now, we look at the owners most recent tickets based on ticket creation time
- Only care about their last ticket with respect to today. i.e first ticket of the day is completely random to all eligible owners
- We don't care how they got their last ticket -- auto assigned or created one and assigned it to themselves (although you may want to watch out for this queue-jumping behaviour)
- Owners all belong to a label that is designated as owners for the queue
- ties are broken randomly
- If an user can be an owner in multiple queues then we only consider his latest ticket in this queue. If you desire the other way then take out the reference to HD_QUEUE_ID in the T2 sub 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 1We can use this to create a rule. In the select query for the rule we want to get a newly created ticket.
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 */
Then we want to update it to have an owner by round-robin assignment. This is where we incorporate our query from above
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>TIP: you may want another rule that would prevent anyone except the manager from manually changing the owner
1 Comment
[ + ] Show comment
-
How do I put this into Kace? I see these scripts everywhere but nothing on where or how to actually use them. Thanks. - j.hough_FNP 11 years ago
Answers (1)
Please log in to answer
Posted by:
jmarotto
12 years ago
Thanks for this GillySpy, it's been helpful.
I have need to assign a ticket based on users in a specific label while at the same time excluding any who may be OOO users, where multiple users in multiple labels can be ticket owners in the same queue. Initial ticket assignment goes to Tier 1 techs but Tier 2 techs are able to be ticket owners as well and, at times I need to route to one of the other labels based on Category selected.
I could take your OOO piece from here to 'exclude' the other users as well as the Out Of Office users from the group of user IDs to select from but, I think it would be easier / more efficient to 'include' a smaller number in a specific label. As in - included 7 users in 1 label to select from or excluded 38 users in 17 labels leaving only 7 to select from.
Any assistance would be greatly appreciated - thank you
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
