I created some custom ticket rules based on the out of the box WaitingOverdue and OverdueClose rules. One of them does the same thing for the status "Waiting on PO". All I did was change which status and customised the email it sends out to use a custom email field I'm using for now. The first rule just sends a reminder email after 7 days and looks like this:

select HD_TICKET.*,
                HD_STATUS.NAME AS STATUS_NAME,
                HD_STATUS.ORDINAL as STATUS_ORDINAL,
                HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
                HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
                HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
                HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email
                STATE,
                if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
                if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
                if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
                if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
                if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
                if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
                if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
                if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
                case upper(STATE)
                when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
                when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
                U1.FULL_NAME as OWNER_FULLNAME,
                U1.EMAIL as OWNER_EMAIL,
                if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
                if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
                U2.FULL_NAME as SUBMITTER_FULLNAME,
                U2.EMAIL as SUBMITTER_EMAIL,
                if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
                if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
                if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
                Q.NAME as QUEUE_NAME                       
                from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
                LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
                LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
                LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
                where HD_PRIORITY.ID = HD_PRIORITY_ID
                and HD_STATUS.ID = HD_STATUS_ID
                and HD_IMPACT.ID = HD_IMPACT_ID
                and HD_CATEGORY.ID = HD_CATEGORY_ID and ((HD_STATUS.NAME = 'Waiting On PO'))
                       and DATE_SUB(NOW(), INTERVAL 7 DAY) > DATE(HD_TICKET.MODIFIED)
                       and HD_TICKET.HD_QUEUE_ID = 1

I tried simplifying the SQL, because there seems like a lot of extra stuff in there. Regardless, the query runs fine, but returns 0 rows when i know there is a ticket that has been in Waiting on PO for almost a month. Any ideas?

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Did you add this line or was it part of the rule you copied from:

DATE_SUB(NOW(), INTERVAL 7 DAY) > DATE(HD_TICKET.MODIFIED)

 

Answered 04/04/2013 by: chucksteel
Red Belt

  • It's in the WaitingOverdue rule that comes with Kace.
  • I tried changing it to DATEDIFF(NOW(), HD_TICKET.MODIFIED) > 7 but still get 0 results.
  • Ok, I figured out that the default Modified field is getting updated. Therefore the interval is never increasing from when the status was changed. Do you know what modifies that field? I have a custom rule for a custom modified field (for an SLA timer) that is updating every 15 minutes, but it shouldn't affect the default modified field... UNLESS the default modified field has a rule to update whenever any change is made which would be happening every 15 minutes.

    Can I base the interval on a different field?
    • I have based it on HD_TICKET_CHANGE table. Your rule is making a change, however, so it probably generates an entry in that table, too. In order to find the last change, add the following join statement:

      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)

      You can then search for the last change with a statement like this:
      and LAST_CHANGE.TIMESTAMP < NOW() - INTERVAL 4 HOUR
  • I don't see the field LAST_CHANGE in the table HD_TICKET or HD_TICKET_CHANGE. What is the whole query you are using? I am adding it to the above query and it returns an error. Probably has to do with naming conventions.
    • LAST_CHANGE is the alias I'm giving the HD_TICKET_CHANGE when I join it. Here's a query that finds tickets that have been updated in the past 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 &lt; NOW() - INTERVAL 4 HOUR
      • NOTE: The < was turned into an ASCII "&lt;" in your above query.

        When I run this query I get 1000 rows returned and the timestamps go all the way back to January. So it is not getting only changes made within last 4 hours.
  • Gotcha! I see that now.
  • Not to throw another wrench into things, BUT...

    I'm using a particular ticket to check this rule which I can see in the HelpDesk has been in a status for a certain period and had changes made including status change and comments added. In the table HD_TICKET_CHANGE it shows only one change for when that ticket was created.

    Any ideas why that table would not be accurately getting change information for tickets?
    • So you're doing a query on HD_TICKET_CHANGE where TICKET_ID = number and only getting one row?
  • No, using MySQL Workbench to look at the whole table, sorting it by ID number and scrolling. I could try querying it the way you are suggesting, but my guess is i would get same result.
    • Since Workbench only returns 1000 results there's no guarantee that you'll get all of the results for that ticket. It's best to query for the ticket specifically.
      • ok I get 6 results for that. I didn't realize workbench was limiting results.
  • So I could edit the above rule to be based on the Timestamp field in HD_TICKET_CHANGE instead of the default modified field? How would I get it to check versus only the latest timestamp for each ticket?
    • So to incorporate this into your rule you would need to add the join statement and then change the
      DATE_SUB(NOW(), INTERVAL 7 DAY) &gt; DATE(HD_TICKET.MODIFIED) line to

      LAST_CHANGE.TIMESTAMP &lt; NOW() - INTERVAL 7 DAY

      That should change it to use the latest change instead of the modified field.
      • That worked! Woohoo! I ran the rules and even got a call from someone that received the email because we were waiting on them and they didn't know it! Success!
Please log in to comment
Answer this question or Comment on this question for clarity

Share