We have a standard ticket rule that sends an alert to a distribution group when a ticket arrives.  What I would like to do is modify that rule so it only sends alerts between 6:30 PM and 11 PM.  The alerts will only be for our on call team.

How can we modify the following to account for time/date created

 

select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME, 
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'department.IT.Tier1traige@clearesult.com' as NEWTICKETEMAIL, /*<<<change your email here */
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
COMMENT
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
 and HD_TICKET_CHANGE.ID=<CHANGE_ID>
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_CHANGE.USER_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_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'

3 Comments   [ + ] Show Comments

Comments

  • You need to extend the where clause to specify the condition where time created is within the desires range. Shouldn't be that difficult.
  • it was very straight forward and I got it working. To add some complexity is there a way to account for having notifications for all hours on the weekend. I know I have to be able to identify the day but not sure it's worth pursuing.
  • think I figured out. thanks for the help.
Please log in to comment

Answers

0

Add the following statement to the end of the query:

and TIME(CREATED) between "18:30:00" and "23:00:00"

Answered 02/25/2014 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share