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

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

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".

 

 

Answered 03/20/2013 by: chucksteel
Red Belt

  • 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
    • It's not HD_STATUS.STATE, it's HD_STATUS.NAME. State is either opened, closed or stalled.
      • 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.
    • Just noticed that you're joining HD_STATUS twice, maybe that's causing a problem.
      • Whoops, sorry thought I responded to this yesterday. I saw it and removed it, still getting the same error.
  • 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.
    • 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
      • Does it work in MySQL WorkBench but not when running as the rule?
    • It fails in workbench when I try the custom name/state (WAITING ON IT). All the preloaded ones (NEW, OPEN, CLOSED) work fine.
      • Have you done a browse of the HD_STATUS table to make sure the name isn't messed up somehow?
    • 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!
      • Glad to help. It's easy to miss something like that.
Please log in to comment

Answers

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. 

Answered 03/19/2013 by: Timi
Seventh Degree Black Belt

  • 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.
  • 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.
Please log in to comment
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