Kace Customize Email Using Custom Fields

Question

We are setting up email layout for open tickets and for closed tickets.  We have custom fileds that we use under ticket layout called Custom_2 and Custom_1.  How do I reference them in tickets.  I believe it should be something similiar to $ticket_custom_1  But any help would be greatly apprecaited.  Also is there a list of variables because only a few default show on the right of the screen.  
 
 Such as $userui_url, $helpdesk_name, $helpdesk_email, $ticket_escalation_minutes, $ticket_priority, $ticket_number, $ticket_title, $ticket_url, $ticket_history, $ticket_submitter_name, $ticket_submitter_email, $ticket_owner_name, $ticket_owner_email, $change_desc 
 
 Is there a bigger list of commands on the web somewhere or am I not looking through the manual in the correct location?

Answer

I don't think you can do that with the built in rules (I haven't seen it anyway).
 
 You could definitely do it with a custom helpdesk rule.
 
 You would be using the send an email for each result row.
 
 Here's the help.
 This rule assumes that the Select Query will return a column that will contain an email address, and that you want to send a mail to that address. Basically, for each resulting row from the 'Select Query' statement, the text in the 'Email Body' will be sent to the recipients from the field 'Email Column'. 
 
 Variables will be replaced in the subject and body of the email. Strings like $title and $due_date will be replaced by the values in the columns names TITLE and DUE_DATE respectively. Lowercase with a dollar sign on the front. Any column returned by the select statement can be replaced in that way. 
 
 The SQL generated by the Ticket Rule Wizard will supply OWNER_EMAIL and SUBMITTER_EMAIL as well as CC_LIST as possible values.

Answer

Thanks

Answer

I don't think you can do that with the built in rules (I haven't seen it anyway). What David means is that you cannot do that with the built-in email on events templates. And that is very true. The only fields you can use in those templates are listed in the R.H.S. help blurb. In 5.4 we are adding many more variables to that including custom fields.

If you want those fields you will have to turn off the email on events and rewrite those events as ticket rules and write the query in the way that David illuminates. Rewriting them as rules is non-trivial.

But I was bored this morning :D so here is a rewrite for all of the "change" fields. Here is how to use it:
Note: this only replaces the "change" emails which are driven by the template "Email Ticket Creation Acknowledgement"

Do not change any of your email on events checkboxes. This rule relies on those checkboxes to know who to email
In contrast to #1 the email text for the template "Email Ticket Creation Acknowledgement" is no longer relevant, but if you like the content you can reuse it ...keep reading
Create a new blank rule
Title it "Replacing Email Ticket Creation Acknowledgement"
Change frequency to "on ticket save"
Change select query to select query below. If needed change the hostname (currently it is "k1000")
Check the box for "send an email for each result row"
--> set subject the same as what you had in the template
--> set email column as NOTIFY_USER
--->set Email Body to same as what you had in the template
Change update query to update query below
NEVER HIT RUN NOW ON THIS RULE (although that should fail if you do)
save it

Features:

replaces 80% of the checkboxes (all the change driven ones).
will give the proper url to admin portal users and user portal users
leverages your existing checkboxes so you can change them whenever you want
uses column names that match the email template variables so you can copy/ paste the email you used before
you can add additional values in by modifying the select list
will not notify anyone that has an invalid email address including people on cc list
owners only comments and owners owners field changes will go to owners only
ticket history is only the history that users are allowed to see.
change emails will go out immediately with no delay (normally they are on a 3 minute interval)

select query:

select C.ID,

 HD_TICKET.ID TICKET_NUMBER,

 NOTIFIERS.EMAIL NOTIFY_USER,

 P.NAME TICKET_PRIORITY,

 HD_TICKET.TITLE TICKET_TITLE,

 case when PV.VALUE<>'HIDE' then CONCAT('http://',KBOX.HOST,'/adminui/ticket.php?ID=',HD_TICKET.ID)

 else CONCAT('http://',KBOX.HOST,'/adminui/ticket.php?ID=',HD_TICKET.ID) end TICKET_URL,

 

 IFNULL(GROUP_CONCAT(DISTINCT CONCAT('----- Change by ', UPDATER.EMAIL,if(H.VIA_EMAIL=',',' (via email)'),' at ',cast(H.TIMESTAMP as char),' -----\n',

       H.DESCRIPTION,'\n',H.COMMENT)

       ORDER BY H.ID DESC SEPARATOR '\n'),'no user entries') TICKET_HISTORY,

 P.ESCALATION_MINUTES TICKET_ESCALATION_MINUTES,

 

 IFNULL(SUBMITTER.FULL_NAME,'unassigned') TICKET_SUBMITTER_NAME,

 IFNULL(OWNER.FULL_NAME,'unassigend') TICKET_OWNER_NAME,

 IFNULL(SUBMITTER.EMAIL,'unassigned') TICKET_SUBMITTER_EMAIL,

 IFNULL(OWNER.EMAIL,'unassigend') TICKET_OWNER_EMAIL,

 

 case when NOTIFIERS.ID=OWNER.ID then CONCAT(UPDATER.EMAIL,if(C.VIA_EMAIL=',',' (via email)'),' at ',cast(C.TIMESTAMP as char),'-----\n',

 C.DESCRIPTION,'\n',C.OWNERS_ONLY_DESCRIPTION,'\n',C.COMMENT)

 else CONCAT(UPDATER.EMAIL,if(C.VIA_EMAIL=',',' (via email)'),' at ',cast(C.TIMESTAMP as char),'-----\n',

 C.DESCRIPTION,'\n',C.COMMENT) end CHANGE_DESC     /* future goal: modify the owners to be based on owner label */

 

 from HD_TICKET 

 JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>

 left JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID=HD_TICKET.ID and H.OWNERS_ONLY=0

 JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID

 JOIN USER NOTIFIERS ON

   /* notifiers is in the list */

    (FIND_IN_SET(NOTIFIERS.ID,C.NOTIFY_USERS)>0 and

     CONCAT(NOTIFIERS.EMAIL,',')

       RLIKE  '^([[:alnum:][.period.][.hyphen.][.underscore.]]+@([[:alnum:][.hyphen.][.underscore.]]+[.period.])+([[:alnum:][.hyphen.]]){2,4}[[.comma.]]+[[:space:]]*)+$' )

   OR

   /* email is in list and it's a valid address */

    (FIND_IN_SET(NOTIFIERS.EMAIL,C.NOTIFY_USERS)>0 and

    CONCAT(NOTIFIERS.EMAIL,',')

       RLIKE  '^([[:alnum:][.period.][.hyphen.][.underscore.]]+@([[:alnum:][.hyphen.][.underscore.]]+[.period.])+([[:alnum:][.hyphen.]]){2,4}[[.comma.]]+[[:space:]]*)+$' )

 JOIN USER_ROLE_PERMISSION_VALUE PV ON PV.ROLE_ID=NOTIFIERS.ROLE_ID and PV.PERMISSION_ID=43

 JOIN (select 'k1000'   -- < change this value to your server name

   HOST) KBOX ON 1=1

 LEFT JOIN USER OWNER ON OWNER.ID=OWNER_ID

 LEFT JOIN USER SUBMITTER  ON SUBMITTER.ID=SUBMITTER_ID and C.OWNERS_ONLY=0

 LEFT JOIN USER UPDATER ON C.USER_ID=UPDATER.ID

 

 GROUP BY HD_TICKET.ID, NOTIFIERS.ID

 HAVING 1=1

update query:

UPDATE HD_TICKET_CHANGE C

 SET MAILED=1, 

 MAILED_TIMESTAMP=NOW()

 WHERE 

 C.ID IN (<TICKET_IDS>)

Answer

I know this is an old post, but did you ever find what you wanted? My suggestion would be to try the fields listed in the email on event page. There is one for the custom fields there that we use: ("x" being the custom field number), but I haven't tried them in the ticket itself.

$ticket_custom_X_label, $ticket_custom_X_value

Kace Customize Email Using Custom Fields

Answers (4)

Posted by:

View more:

Related Questions

Related Links

Related Posts

KACE Product Support

Kace Customize Email Using Custom Fields

Answers (4)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share