/bundles/itninjaweb/img/Breadcrumb_cap_w.png

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   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

Answers

1

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

Share