Ticket Rules Question
I have a question about ticket rules. I have made a few custom fields in my queue and now I want to send an email to the tech every time a ticket is created but I want some of those values from the custom fields to be send in the email.
I have tried to play with it and asked for help from support but they tell me its custom and I would need to pay extra to get help (which I do not agree with but okay)
All I need to know is how to get these fields to populate in the message.
This is my select statement:
SELECT
-- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
C.COMMENT, -- $comment
HD_TICKET.CUSTOM_FIELD_VALUE0 AS BackgroundCheckCleared,
HD_TICKET.CUSTOM_FIELD_VALUE1 AS DateofHire,
HD_TICKET.CUSTOM_FIELD_VALUE2 AS FirstName,
HD_TICKET.CUSTOM_FIELD_VALUE3 AS Building,
HD_TICKET.CUSTOM_FIELD_VALUE4 AS Department,
HD_TICKET.CUSTOM_FIELD_VALUE5 AS Title,
HD_TICKET.CUSTOM_FIELD_VALUE6 AS DoorAccess,
HD_TICKET.CUSTOM_FIELD_VALUE7 AS Picture,
HD_TICKET.CUSTOM_FIELD_VALUE8 AS AdditionalInformation,
HD_TICKET.CUSTOM_FIELD_VALUE9 AS LastName,
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kace.com/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
-- about status
S.NAME AS STATUS, -- $status
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
-- other fields
-- -- example of static distribution list
'email@email.com' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_ID>
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE
C.DESCRIPTION LIKE '%CREATED%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
And this is my message:
Ticket: [TICK:$ticknum]
Badge First Name: $FirstName
Badge Last Name: $LastName
Badge Building: $Building
Badge Department: $Department
Badge Title: $Title
Door Access: $DoorAccess
Date of Hire: $DateofHire
Picture Location: $Picture
Additional information: $AdditionalInformation
It's not working, can anyone explain to me why?
-
Thak you sooo much for this. I never once thought about the lower case being a problem, you both made me very happy!! - tollie 4 years ago
Answers (2)
First I would try creating a basic rule using the wizard, let KACE put in all the lines to the query it needs, then I suggest you add in your fields and copy the format of the wizard select statement. Just as an idea also change the names of the custom fields in the select statement so there is no confusion with the default field titles in the database (eg Title).
Try this with just one or two of your fields and then insert them into the template, with the $variables all in lower case.
if that works, keep adding in select lines and $variables until you have what you need.
If it doesn’t work, let me know what version you are as it may yet still be a bug ;o)
The variables need to be lower case.
Badge First Name: $firstname
Badge Last Name: $lastname
Badge Building: $building
Badge Department: $department
etc.
