Ticket Rule to create email alert if ticket status is still "new" after 2 hours
I'm trying to create a ticket rule that will send an email to alert a manager if, during an after-hours time frame, a ticket stays in the "New - Not Started" status for more than 2 hours so an on-call technician can be notified. I've been trying to create a ticket rule through the wizard, but the filters within the wizard do not seem to work in terms of filtering based on ticket age. I'm not familiar enough with SQL to be able to write out the ticket rule in SQL without the wizard. I've been old posts(6-7 years old) on here where people were able to do something similar with SQL, but the SQL looks different than what is being created by my K1000 box today so I don't think it will work anymore.
Is anyone able to help me figure out what the SQL needs to be to create this rule, or if it is even possible?
You will need to setup the SQL to say
if the status is still “new not started” and
the time now is more than two hours after the logged time and
the time now is greater than 6pm or less than 6am and
a switch field (to prevent multiple emails is equal to 1 then
set the switch field to 2 and send the email.
Does that logic sound about right?