/build/static/layout/Breadcrumb_cap_w.png

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":


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:

    $ticket_url 


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.


Thanks,


0 Comments   [ + ] Show comments

Answers (6)

Posted by: Hobbsy 3 years ago
Red Belt
1

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

Posted by: CSVNinja 3 years ago
Senior White Belt
0

That is very helpful, thanks.  If I understand correctly, I need to add something to my SELECT statement like this:

HD_TICKET.TITLE as TICKET_TITLE,


I will give it a try and report back.

Posted by: CSVNinja 3 years ago
Senior White Belt
0

for context, this is the comment that is not working:

Hello,


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.


Regards,

Support Team

Posted by: CSVNinja 3 years ago
Senior White Belt
0

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:


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,

                STATE, 

                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,

                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(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

Posted by: CSVNinja 3 years ago
Senior White Belt
0

Here's the comment that should replace $ticket_title with the actual title of the ticket.

Hello,


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.


Regards,

Support Team

Posted by: CSVNinja 3 years ago
Senior White Belt
0

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.


Thanks

 
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