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

Comments

  • Thanks for your feedback. it helped me solve the problem. I actually changed it to look at ticket number, but your tips below still helped me. So now if the ticket ends with 0,2,4,6,8 one person gets it. if opposite the other gets it. THen i made a rule for each user "jdoe vacation" if this is enabled it has a higher priority value and forwards all tickets on creation to the other guy. WOrks pretty slick.
    Thanks again.
Please log in to comment

Community Chosen Answer

2
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"

Answered 07/28/2014 by: chucksteel
Red Belt

Please log in to comment

Answers

1
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.


Answered 07/25/2014 by: Wildwolfay
Red Belt

Please log in to comment
0
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?

Answered 07/28/2014 by: ecszone
White Belt

  • 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.
    • 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.
      • 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.
Please log in to comment
Answer this question or Comment on this question for clarity