Tokens not working in system ticket rules
We are trying to customize the out-of-the-box system ticket rules such as "WaitingOverdue", "OverdueClose", "EmailOnClose", etc. located under the Service Desk --> Configuration --> Ticket Rules section in the Admin logon. We want to be able to use the tokens such as $ticket_title, $ticket_id, $ticket_section that auto-fills the information when the emails goes out. These work fine in the Service Desk Email Notifications section (under Configured Queue Email Settings). For example, this is the template for "Ticket Escalated":
This $ticket_priority priority ticket has been escalated and an escalation email will be sent every $ticket_escalation_minutes minutes.
For complete details, see:
This works fine. The problem is when we try to use tokens in the aforementioned "System Ticket Rules" ("WaitingOverdue", "OverdueClose", "EmailOnClose"). It will not replace the tokens with the actual values.
I talked to KACE support and the tech seemed to think it has something to do with the SQL Select statement. I'm not sure this is the case (I could be wrong). My understanding is the SELECT statement simply queries to see if any tickets trigger the rule.
Could something please enlighten me on this situation? I wonder if the tokens are not expected to be used in the system ticket rules, only for the Service Desk Email Notifications. However this seems like a shortcoming if that's true.
I think that when using these rules they are built as ticket rules, so use Select and update statements, and select fields directly from the database.
The $ values used in the wizards are there are used as variables and as such are not selected as those in a ticket rule.
The only way to actually edit or confirm that the correct fields are being used would be to access the SQL for the default rules. To do that you click the link at the top of an enabled rule to “edit rule using the standard ticket rule editor” Once you’re in their you can see if the select statement includes the fields you need, and if they have an alias i.e.
SELECT HD_TICKET.CUSTOM_FIELD_VALUE0 AS Custom_value0
You can then edit the email template in the rule and use $custom_value0 as a variable
for context, this is the comment that is not working:
This is an automated email to inform you that we believe this ticket $ticket_title has not received any update from you in 3 days. Please reply to this email with an update to this ticket soon so we can help you resolve this issue.
If no update is received in the next 7 days, the ticket will be closed automatically.
So I tried your suggestion and unfortunately it did not work. Here's my code, the code i added is in bold. Let me know if I'm missing something here:
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.TITLE as TICKET_TITLE,
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,
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(HD_TICKET.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 Overdue'))
and DATE_SUB(NOW(), INTERVAL 3 DAY) > DATE(HD_TICKET.MODIFIED)
and HD_TICKET.HD_QUEUE_ID = 1
Here's the comment that should replace $ticket_title with the actual title of the ticket.
This ticket "$ticket_title" has been closed because we believe we have not received any update from you for 3 days. Replying to this email will re-open the ticket and notify the support team member you have been working with.
Does it really matter what the language of the comment is? I think the only thing is that that $ticket_title should be replaced with the actual ticket title. The only thing I see is you put $ticket_title in quotations. I've added it to the SELECT statement with the alias, I'm not sure what else I need to do.