Filter tickets based on time
Is there a way to filter tickets based on time?
ie. ticket is created time>8am & time <12pm assing to user jdoe
I tried this through the wizard and it does not seem to work. mabye because i dont know the syntax to use for time?
The select SQL came out like this:
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(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_TICKET.CREATED > '8am') AND HD_TICKET.CREATED < '1pm') and HD_TICKET.HD_QUEUE_ID = 1 )
and the update SQL came out:
update HD_TICKET, USER as T5
set HD_TICKET.OWNER_ID = T5.ID
where T5.USER_NAME = 'jdoe' and
(HD_TICKET.ID in (<TICKET_IDS>))IM suck at SQL scripting.. but it seems like there is a lot of syntax to do this simple task?
Any pointers? I want to auto assign based on time.
1 Comment
[ + ] Show comment
Answers (3)
Please log in to answer
Posted by:
chucksteel
10 years ago
The wizard doesn't handle creating time queries correctly. To find tickets created between 8am and 1pm replace:
(( HD_TICKET.CREATED > '8am') AND HD_TICKET.CREATED < '1pm')
with:
TIME(CREATED) BETWEEN "08:00:00" and "13:00:00"
Posted by:
Wildwolfay
10 years ago
Good logic, bad syntax is all. I've been playing with the CURTIME() field and think I found a simple solution to your problem.
select CURTIME()
from HD_TICKET
where CURTIME() > '11:30:00'
Essentially what you need to do is add the CURTIME() column to your query by adding it within your select statement, then, within the where filter, you can add the CURTIME() > '11:11:11' or whatever time you want it to do. Make sure you use the HH:MM:SS even if it's 08:00:00.
give that some testing and that should work for you.
select CURTIME()
from HD_TICKET
where CURTIME() > '11:30:00'
Essentially what you need to do is add the CURTIME() column to your query by adding it within your select statement, then, within the where filter, you can add the CURTIME() > '11:11:11' or whatever time you want it to do. Make sure you use the HH:MM:SS even if it's 08:00:00.
give that some testing and that should work for you.
Posted by:
ecszone
10 years ago
Ok, so i thought i had it per my "comment" under the main question. But i seem to found a problem.
reposting my syntax. its running anytime we save it, not only on submit? is there a way to make this only happen when the ticket is initially created?
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(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_IDwhere 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_TICKET.ID like '%1') OR HD_TICKET.ID like '%3') OR HD_TICKET.ID like '%5') OR HD_TICKET.ID like '%7') OR HD_TICKET.ID like '%9') and HD_TICKET.HD_QUEUE_ID = 1 )and (CURTIME() BETWEEN '08:00:00' AND '17:00:00')
update HD_TICKET, USER as T5 set HD_TICKET.OWNER_ID = T5.ID where T5.USER_NAME = 'jdoe' and (HD_TICKET.ID in (<TICKET_IDS>))
Thoughts?
Comments:
-
In order to have the rule only run on ticket creation you need to add a join to the HD_TICKET_CHANGE table and then only act on specific changes. The join you need is the following:
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
This goes in the same part of the select query as the other join statement before the where clause. Next you need to add a statement to the where clause to only match ticket creation:
and HD_TICKET_CHANGE.DESCRIPTION LIKE '%Ticket Created%'
You can add this to the end of the statement.
Also you kind of combined my answer with Wildwolfay's. Your rule will only match tickets if the current time is between 8am and 5pm which probably works since the ticket creation time should be the same as when the ticket is created but you should keep in mind that it is acting on the time the rule runs, not strictly on the time the ticket was created. - chucksteel 10 years ago-
oh wow. ok ill have to see if i can figure this out. Ill try and lore our SQL guy back here again to assist. you know if Kace had a "use round robin" check-box like many other service desk solutions, things would be much easier. - ecszone 10 years ago
-
A few folks have posted here with different round robin rules. If that's what you're looking for there have been some solutions proposed. - chucksteel 10 years ago
Thanks again. - ecszone 10 years ago