/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: chucksteel 11 years ago
Red Belt
1

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
Posted by: Timi 11 years ago
7th Degree Black Belt
0

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ