K1000: Custom Overdue Ticket Rule Not Working
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?
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)
Comments:
-
It's in the WaitingOverdue rule that comes with Kace. - lmland 11 years ago
-
I tried changing it to DATEDIFF(NOW(), HD_TICKET.MODIFIED) > 7 but still get 0 results. - lmland 11 years ago
-
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? - lmland 11 years ago-
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 - chucksteel 11 years ago
-
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. - lmland 11 years ago
-
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 < NOW() - INTERVAL 4 HOUR - chucksteel 11 years ago-
NOTE: The < was turned into an ASCII "<" 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. - lmland 11 years ago
-
Gotcha! I see that now. - lmland 11 years ago
-
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? - lmland 11 years ago-
So you're doing a query on HD_TICKET_CHANGE where TICKET_ID = number and only getting one row? - chucksteel 11 years ago
-
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. - lmland 11 years ago
-
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. - chucksteel 11 years ago
-
ok I get 6 results for that. I didn't realize workbench was limiting results. - lmland 11 years ago
-
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? - lmland 11 years ago
-
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) > DATE(HD_TICKET.MODIFIED) line to
LAST_CHANGE.TIMESTAMP < NOW() - INTERVAL 7 DAY
That should change it to use the latest change instead of the modified field. - chucksteel 11 years ago-
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! - lmland 11 years ago
