KACE Product Support

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

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.

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

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

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

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