K1000 Service Desk: Assign by Location Rule
Ok I created a rule to auto-assign a ticket when the status is "Parts Available" and Location matches the locations each technician is in charge of. For some reason it is preventing anyone from changing ownership even if the status is "New" or something besides "Parts Available". In other words it is forcing the owner based on the location.
Here's the SQL Query:
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,
STATE,
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_STATUS.NAME = 'Parts Available') AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'G W Carver') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Cedar Ridge') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Hillcrest PDS') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Indian Springs') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Alternative') OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Credit Recovery') and HD_TICKET.HD_QUEUE_ID = 1 )
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
jverbosk
11 years ago
OK, so here's the "acting part" of the Select query, which is only selecting tickets if the STATUS.NAME column is set to "Parts Available", the GUI custom 1 field is set to a specific location value and the queue ID is set to 1:
and (
((((((( HD_STATUS.NAME = 'Parts Available')
AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'G W Carver')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Cedar Ridge')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Hillcrest PDS')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Indian Springs')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Alternative')
OR HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Credit Recovery')
and HD_TICKET.HD_QUEUE_ID = 1 )
This will select the matching tickets every time this criteria is met, but won't change anything by itself. This is what the Update query does, which raises the question - what is the corresponding Update query?
Other things it would be helpful to know:
Do you have this set to run on a schedule or on ticket save?
If you run this Select query in MySQL Query Browser, are multiple tickets selected?
John
Comments:
-
Set to run on ticket save. When i run the above query I get multiple tickets.
Update query:
update HD_TICKET, USER as T5
set HD_TICKET.OWNER_ID = T5.ID
where T5.USER_NAME = 'ajimenez' and
(HD_TICKET.ID in (<TICKET_IDS>)) - lmland 11 years ago -
I think the Select statement needs to be adjusted a bit to select for both fields in each "OR" statement. Try this adjustment at the end in place of what I pasted above:
and (
(HD_STATUS.NAME = 'Parts Available'
AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'G W Carver')
OR (HD_STATUS.NAME = 'Parts Available'
AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Cedar Ridge')
OR (HD_STATUS.NAME = 'Parts Available'
AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Hillcrest PDS')
OR (HD_STATUS.NAME = 'Parts Available'
AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Indian Springs')
OR (HD_STATUS.NAME = 'Parts Available'
AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Alternative')
OR (HD_STATUS.NAME = 'Parts Available'
AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Credit Recovery')
and HD_TICKET.HD_QUEUE_ID = 1 )
John - jverbosk 11 years ago -
Ahhhh... SQL must group by and statements or something. I will try it. - lmland 11 years ago
-
That worked! Thanks. - lmland 11 years ago
