K1000 Round Robin based on Category

I am working on finishing up configuration of Service Desk for our company, and I have been combing IT Ninja.  There are a few technicians that will be in this group, but what I would like to do is have the round robin go if specific categories are picked.  Most categories go to a specific person, but others are to share for everyone.  If you are willing to share if you have the SQL query, I would greatly appreciate it.  I have just started picking up SQL again, and I see that the Wizard is extremely limited.

0 Comments   [ + ] Show comments

Answers (3)

Answer Summary:
Posted by: airwolf 5 years ago
Red Belt
What you are asking for is quite a complex query. We do offer fee-based professional services to write custom queries like this. If you are interested, feel free to contact Dell KACE support and we can get you a quote.
Posted by: zgillette 5 years ago
Second Degree Green Belt
Thanks airwolf.  I thought this may be the case.  I was wondering if anyone else had done this type of querying before.  I may come back to Dell if I can't figure out how to do it.
Posted by: zgillette 5 years ago
Second Degree Green Belt

Top Answer

After using the wizard and throwing some SQL in there, we figured out how to do a coin flip round robin.  It works best with 2 people.  It will not give to the next person every other ticket, but it does even out pretty well.  These are the queries.

**Select SQL**

select HD_TICKET.*,
                        HD_STATUS.NAME AS STATUS_NAME,
                        HD_STATUS.ORDINAL as STATUS_ORDINAL,
                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
                        if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
                        case upper(STATE)
                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
                        U1.FULL_NAME as OWNER_FULLNAME,
                        U1.EMAIL as OWNER_EMAIL,
                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
                        U2.FULL_NAME as SUBMITTER_FULLNAME,
                        U2.EMAIL as SUBMITTER_EMAIL,
                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
                        Q.NAME as QUEUE_NAME
                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
                        where HD_PRIORITY.ID = HD_PRIORITY_ID
                        and HD_STATUS.ID = HD_STATUS_ID
                        and HD_IMPACT.ID = HD_IMPACT_ID
                        and HD_CATEGORY.ID = HD_CATEGORY_ID
                        and (((  HD_CATEGORY.NAME like '%support::PC Issue%')) and HD_TICKET.HD_QUEUE_ID = 1 )


This is just for a categories that are like support::PC Issue.  You can add a lot more by putting OR between them.

As for the update query, here is what we used.  You can put whichever user names in there that you would want, otherwise the rest can stay the same.

Update Query:
update HD_TICKET, USER as T5
  where T5.USER_NAME = (select U2.USER_NAME from USER as U2 where USER_NAME in ('user1','user2') order by rand() limit 1)
        (HD_TICKET.ID in (<TICKET_IDS>))

I hope that this helps someone out.  It sure has helped for our needs.

The highlighted fields are ones that you can change to fit your needs.  It would all depend on what your categories, queue ID's and Users are.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login


This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ