I have an e-mail rule that basically sends an e-mail to our helpdesk if a ticket has been in a status of "new, not started" for over 2 hours. The rule is scheduled to run hourly but with this approach we are getting a lot of e-mail every night as every hour we will get the same e0-mail for every ticket over two hours old with the status.

I would like to be able to just run this during business hours and not run after hours or on holidays.... Does anyone have any suggestions as to what the best approach is for this?

I am very SQL challenged so I am sure this is going to take some good explaining before I can get it working but if someone can point me in the right direction, I am sure I can get it going with some guidance.


Here is a copy of my current rule:

select HD_TICKET.*,


                                        'helpdesk@mycompany.com' as SUPPORT_EMAIL,


                        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_STATUS.NAME like '%New%') AND HD_PRIORITY.NAME != '')


                        and HD_TICKET.CREATED < NOW() - INTERVAL 2 HOUR


                                                and HD_TICKET.HD_QUEUE_ID = 5 )



Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Excellent Chucksteel, I added this to the rule and it works great (at least the hours of operation, I'll know about the DAYOFWEEK this weekend)

    Thanks
Please log in to comment

Answer Chosen by the Author

2
You should be able to add the following line to the end of the query:
and HOUR(NOW()) BETWEEN 8 and 17 

This will limit the results to only the hours between 8am and 5pm (24 hour clock).

To limit by day of week:
and DAYOFWEEK(NOW()) BETWEEN 2 and 6

Holidays are a bit more difficult and you would need to code those dates in statically. 
Answered 06/15/2015 by: chucksteel
Red Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share