/build/static/layout/Breadcrumb_cap_w.png

K1000 Service Desk: Escalation Rule returning 0 results

I have changed a number of my ticket rules to be based in the Timestamp column in the HD_TICKET_CHANGE table. I'm working on a post to explain why. However, I was checking that the rules were working and found that one rule was returning 0 results even though I know a Ticket meets the requirements. This particular rule selects all tickets that have been in status "New" for 10 days and sends an email to ticket owners that their ticket has not been changed in 10 days.

Here's the query:

SELECT
      -- ticket fields
      HD_TICKET.ID, -- $id
      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
      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
    
      -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox.wacoisd.org/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
    
    
      -- about the updater
      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
    
      -- about the owner
      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
    
      -- about the submitter
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
      -- about priority
      P.NAME AS PRIORITY, -- $priority
    
      -- about status
      S.NAME AS STATUS,   -- $status
    
      -- about impact
      I.NAME AS IMPACT,   -- $impact
    
      -- about category
      CAT.NAME AS CATEGORY, -- $category
    
      -- other fields
      HD_TICKET.CUSTOM_FIELD_VALUE0 AS LOCATION, -- $location
      HD_TICKET.CUSTOM_FIELD_VALUE1 AS ROOM, -- $room
      HD_TICKET.CUSTOM_FIELD_VALUE2 AS WISD_NUM, -- $wisd_num
      HD_TICKET.CUSTOM_FIELD_VALUE7 AS EQUIPMENT, -- $equipment
      HD_TICKET.CUSTOM_FIELD_VALUE5 AS DESCRIPTION, -- $description
      HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email

      -- -- example of static distribution list
      'helpdesk@wacoisd.org' AS NEWTICKETEMAIL -- $newticketemail
    
    
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
                            /*AND C.ID=<CHANGE_ID>*/
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
     WHERE
(S.NAME = 'New' AND DATEDIFF(NOW(), LAST_CHANGE.TIMESTAMP) > 10 AND DATEDIFF(NOW(), LAST_CHANGE.TIMESTAMP) < 11)
    
      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1

I have a ticket #670 that is greater than 10 days and less than 11, but the query still returns 0 results. I queried HD_TICKET_CHANGE and see the timestamp for Ticket ID 670 that is correct. Any ideas why I am still getting 0 results?


0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 11 years ago
Red Belt
0

Since DATEDIFF returns a result as days then there cannot be any tickets with a value greater than 10 and less than 11, only values like 10.1, 10.2, etc. would fall between 10 and 11. If you want to find tickets that are ten days old then you just need to look for DATEDIFF(NOW(), LAST_CHANGE.TIMESTAMP) = 10.



Comments:
  • Ok, that fixed the problem. I was trying to avoid the problem of missing tickets because they rolled over to 11 days before the rule runs. Is there a way to make sure that =10 catches all tickets between 10 and 11 days. - lmland 11 years ago
    • Between 10 and 11 days would be greater than 9 and less than 12. I would probably have the rule run once per day to make sure you always catch tickets that are 10 days old, however. - chucksteel 11 years ago
      • So, if I use = 10 I will miss some? Is that what you're saying? If I use > 9 and < 12 I won't? - lmland 11 years ago
    • How often are you running the rule? If you are running it every day then it should catch the ticket on the tenth day. - chucksteel 11 years ago
      • Yeah it runs daily. - lmland 11 years ago
      • If you're running it daily then it shouldn't miss any tickets. - chucksteel 11 years ago
      • Then I will leave it as = 10. - lmland 11 years ago
  • Here's another one giving me fits. Same exact query except the where statement is changed to find ticket in "New" or "Follow-up" for 30 days. I know I have a ticket that matches, but get 0 results.

    WHERE
    (S.NAME = 'New' AND DATEDIFF(NOW(), LAST_CHANGE.TIMESTAMP) = 30)
    OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), LAST_CHANGE.TIMESTAMP) = 30) - lmland 11 years ago
    • Not sure about this one. A similar query works for me. If you look at the ticket in workbench what do you see? - chucksteel 11 years ago
  • It returns 0 results. So, I see nothing. I changed it to >29 and < 31 and got 24 results. - lmland 11 years ago
    • Sometimes my status names are a little different then I think they are and that messes up my query. Have you verified that the tickets returned for greater than 29 and less than 31 have the status that you're looking for? - chucksteel 11 years ago
      • Yes, the status names are correct. I'm using the same exact query for multiple rules and just changing the number of days it's looking for. - lmland 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

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