KACE Product Support

Someone asked me about this recently so I'll post an experiment I did a while ago. Please use cautiously. If someone uses it let us know how it went.

In the current helpdesk (5.1) it is only possible to use tokens on certain fields (e.g. @owner but not @resolution). As written this rule should work in 5.0 and 5.1 (but not 4.x or lower)

Tokens are covered in the documentation so I'll leave the details to that, but you can use ticket rules to parse your emails instead. There are some enhancements in the works to make this work better at some point in the future, but for today...
The rule needs to be an on ticket save rule so that it recognizes the email as it comes in.

The select query would be something like this -- note the inline comments that explain the query.
Select DISTINCT C.ID FROM HD_TICKET
JOIN HD_QUEUE Q ON Q.ID=HD_TICKET.HD_QUEUE_ID
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
JOIN HD_CATEGORY CAT ON CAT.ID=HD_TICKET.HD_CATEGORY_ID
JOIN HD_STATUS S ON S.ID=HD_TICKET.HD_STATUS_ID
JOIN HD_PRIORITY P ON P.ID=HD_TICKET.HD_PRIORITY_ID
JOIN HD_IMPACT I ON I.ID=HD_TICKET.HD_IMPACT_ID
LEFT JOIN HD_TICKET_CHANGE_FIELD F ON
F.HD_TICKET_CHANGE_ID=C.ID and FIELD_CHANGED ='COMMENT'
LEFT JOIN USER UO ON UO.ID=HD_TICKET.OWNER_ID
LEFT JOIN USER US ON US.ID=HD_TICKET.SUBMITTER_ID
LEFT JOIN USER_LABEL_JT OL ON UO.ID=OL.USER_ID /*get labels for owner */
LEFT JOIN HD_QUEUE_OWNER_LABEL_JT QL ON QL.LABEL_ID=OL.LABEL_ID /*labels that own this queue */
WHERE
COMMENT LIKE '%@custom_token%' /*change this line */
and VIA_EMAIL <> ' /*entry came by email */



The update is a bit trickier so make sure to update it properly:
update HD_TICKET as T JOIN
HD_TICKET_CHANGE C ON C.HD_TICKET_ID=T.ID and C.ID=<TICKET_IDS>
SET
T.PARENT_ID=CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
COMMENT,'@custom_token=',-1),'\n',1) AS UNSIGNED), /* change this line*/
DESCRIPTION=TRIM( LEADING '\n' FROM
REPLACE(
CONCAT(DESCRIPTION, '\nCustomFieldName Changed to "', /*change this line */
SUBSTRING_INDEX(SUBSTRING_INDEX(COMMENT,'@custom_token=',-1), '\n',1),'".\n'),'\n\n','\n') /*change this line*/
),
COMMENT=REPLACE(COMMENT,CONCAT('@custom_token=', /*change this line */
SUBSTRING_INDEX(SUBSTRING_INDEX(COMMENT,'@custom_token=',-1),'\n',1),'\n'),' /*change this line*/
)