Creating emails when a ticket hasn't been worked on in a few hours
Hey all,
Been testing around with the code below. The goal was to check for the status first (Waiting on IT), and if that's right, check for the time since that status was changed. If THAT time difference was 4 hours or more, it would shoot out the email.
What it's doing now is just checking the ticket's initial time open being greater than 4 hours, then testing all the rest. That part is working fine, I just need a bit of assistance with the time-since-last-status-change part. Thanks in advance!
SELECT
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
-- SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
-- SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
S.NAME AS STATUS, -- $status
P.NAME AS PRIORITY, -- $priority
CAT.NAME AS CATEGORY, -- $category
E.EMAIL AS OWNER_EMAIL -- $owner email
FROM HD_TICKET
/*status**********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* priority ******/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* category ******/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* submitter *****/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/*owner email ****/ LEFT JOIN USER E on E.ID = HD_TICKET.OWNER_ID
WHERE TIMESTAMPDIFF(MINUTE,HD_TICKET.CREATED, NOW()) > 240 and S.NAME ='waiting on it' AND HOUR(NOW()) > 7 AND HOUR(NOW()) < 18
ORDER BY TICKNUM
That last line currently says CREATED becuse that at least works. I just need the command (I think?) for status change and waiting 4 hours after that. The other time info just says it won't run before 7 am or after 6pm
Answers (2)
Here's a query that will find tickets that haven't been modified (according to the HD_TICKET_CHANGE table) in four hours:
SELECT T.ID, T.TITLE, T.CREATED, LAST_CHANGE.TIMESTAMP, LAST_CHANGE.DESCRIPTION, T.TIME_CLOSED FROM ORG1.HD_TICKET T JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = T.ID and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID) left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID WHERE HD_STATUS.STATE = 'opened' and LAST_CHANGE.TIMESTAMP < NOW() - INTERVAL 4 HOUR
I'm selecting different fields since I just threw this together in MySQL WorkBench to get it working, but this should get you started. You'll also need to add your check for HD_STATUS.NAME = "waiting on it".
Comments:
-
Thakns for that! I was testing it in my own workbench, and I get things to display for CLOSED just fine, but for some reason nothing displays for the Waiting on IT status, despite there being plenty of things to display. Care to take a look? Sorry for not knowing more, I've been thrown into SQL lately. As mentioned, it runs without errors, but displays nothing (same if i swap the < > signs, but both signs work fine for CLOSED)
SELECT
T.ID AS TICKNUM, -- $ticknum
T.TITLE, -- $title
DATE_FORMAT(T.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
S.NAME AS STATUS, -- $status
P.NAME AS PRIORITY, -- $priority
E.EMAIL AS OWNER_EMAIL,
LAST_CHANGE.TIMESTAMP,
LAST_CHANGE.DESCRIPTION,
T.TIME_CLOSED
FROM HD_TICKET T
JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
LEFT JOIN USER E on E.ID = T.OWNER_ID
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = T.ID
and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID)
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE
HD_STATUS.STATE = 'Waiting on IT'
and LAST_CHANGE.TIMESTAMP < NOW() - INTERVAL 4 HOUR
ORDER BY TICKNUM - Mark_B 11 years ago-
It's not HD_STATUS.STATE, it's HD_STATUS.NAME. State is either opened, closed or stalled. - chucksteel 11 years ago
-
Sorry, meant to post that before. I tried name, which works fine for OPEN, CLOSED, NEW, etc. But for my custom ones, it's giving me an error:
'yada yada yada' can not be represented as java.sql.Timestamp
Judging by the full error in between it looks like it pulls info from the first ticket it finds, then fails. - Mark_B 11 years ago
-
Just noticed that you're joining HD_STATUS twice, maybe that's causing a problem. - chucksteel 11 years ago
-
Whoops, sorry thought I responded to this yesterday. I saw it and removed it, still getting the same error. - Mark_B 11 years ago
-
Can you post your query again? I think there have been a couple of changes and I want another fresh look at what you have in the rule. - chucksteel 11 years ago
-
Sure, thanks. This code works fine for CLOSED but gives an error when trying to do WAITING ON IT, as mentioned I think it's having an error when it reaches the second ticket. Much appreciated
SELECT
T.ID AS TICKNUM, -- $ticknum
T.TITLE, -- $title
DATE_FORMAT(T.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
P.NAME AS PRIORITY, -- $priority
E.EMAIL AS OWNER_EMAIL,
LAST_CHANGE.TIMESTAMP,
LAST_CHANGE.DESCRIPTION,
T.TIME_CLOSED
FROM HD_TICKET T
JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
LEFT JOIN USER E on E.ID = T.OWNER_ID
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = T.ID and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID)
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE
HD_STATUS.NAME = 'CLOSED'
and LAST_CHANGE.TIMESTAMP < NOW() - INTERVAL 4 HOUR
ORDER BY TICKNUM - Mark_B 11 years ago-
Does it work in MySQL WorkBench but not when running as the rule? - chucksteel 11 years ago
-
It fails in workbench when I try the custom name/state (WAITING ON IT). All the preloaded ones (NEW, OPEN, CLOSED) work fine. - Mark_B 11 years ago
-
Have you done a browse of the HD_STATUS table to make sure the name isn't messed up somehow? - chucksteel 11 years ago
-
I feel the fool. After checking the config's again, I saw that my custom status was set to STALLED, not OPENED. Because of that, it was failing. Now it works fine.
Thank you very, very much for all of your assistance with this! - Mark_B 11 years ago-
Glad to help. It's easy to miss something like that. - chucksteel 11 years ago
Unless I am missing something would using escalation accomplish the same thing? For the status waiting on IT you could put the escalation time in as 4 hours.
Comments:
-
It also depends on what Priority the ticket is in. If the ticket is in an opened state and the priority is say Low. if you didn't set the escalation for low to be 4 hours, then no email goes out. You probably want to use the modified date, not the created. - nshah 11 years ago
-
I've never actually gotten those escalations to work. I thik it has to do with the status I want, seeing as how we never leave it as "New" or "Opened", it's always "Waiting on IT" or "Waiting on User", or closed, so the escalation defaults never kick in. - Mark_B 11 years ago
