Custom Email Notifications in Kace not showing correct data
Kace and Mysql newbie here, im trying to get custom fields to auto populate in email responses through custom ticket rules. Can anyone help me find out what I am missing? in the email, just the variables are passed through.
SQL Query
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_VALUE5 as ACCESS_SUMMARY,
HD_TICKET.CUSTOM_FIELD_VALUE0 as DEPARTMENT,
STATE,
if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
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,
U3.EMAIL as APPROVER_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_TICKET.CUSTOM_FIELD_VALUE0 = 'Accounting') AND HD_STATUS.NAME = 'Waiting for Approval Manager') and HD_TICKET.HD_QUEUE_ID = 10 )
(I have a( HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Accounting') as a test but it will eventually just go to waiting for approval status tickets)
Email Subject [TICK:$id] IT Access Request: $status_name
Column for email APPROVER_EMAIL
Body
A ticket in the Access Change Queue needs your approval, please review
Ticket ID: $id
Created: $created
Issue: $title
Status: $status_name
Requesting User: $owner_fullname
Department: $DEPARTMENT
Summary: $ACCESS_SUMMARY
To Approve this request click on the link below:
mailto:accesskace@company.com?subject=[TICK:$id]&body=@approval%20=%20Approved>
To Deny this request click on the link below:
<mailto:accesskace@company.com?subject=[TICK:$id]&body=@approval%20=%20Rejected>
The $DEPARTMENT and $ACCESS_SUMMARY fields are the ones not populating correctly.
Thanks!
Brighamy
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
brighamy
8 years ago
I found my error, I cant have the variable in caps.
Department: $DEPARTMENT
Summary: $ACCESS_SUMMARY
changed to +
Department: $department
Summary: $access_summary
That seemed to do the trick for me! Hopefully this saves some time for someone else that is just learning.
-Brighamy