Service Desk Ticket Email Tokens - One for Category?
We just started using the Service Desk Portion of our K1000, and I'm trying to customize the ticketing emails... I don't know any SQL but expect to pick some up as I work more with this. Here's what I did - I renamed the Category field to Building because we have 12 buildings that the techs travel to, and the owner is assigned based upon location of the request. Our actual categories don't need to be all that involved (with subcategories, etc), so I just created a custom field for those. What I'd like to do is include that name of the building in a new ticket email but from what I've seen on ITNinja and in the KMA v.9 manual there is no automatic $Category token that I can just plug into the email. Please correct me if I'm wrong, because that would be awesome. But if it's more involved in that, how do I plug in the data from the Category (aka Building) field into a New Ticket Email?
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
First, I would recommend that you use the appliance's Location functionality instead of the Category field. There may be instances in the future that you want to perform reporting or another task that will become more difficult if you aren't using the in-built functionality. That's your decision, of course, but thought I would share some advice based on past experience. For instance, when we went to compare numbers of tickets related to number of computers in a building it was very difficult because we were tracking the locations for tickets and computers in different ways.
If you need to include fields in the email notification then you have to use a Custom Ticket Rule instead of the system rules. Our appliance is configured to set the user's location based on their Active Directory information, so each user has a location defined already. If your tickets are based on the submitter's location, then that makes including the submitter's location in the email easy. Here is the select statement for the rule we have setup for our main Helpdesk:
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
U3.FULL_NAME as UPDATER_NAME,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
HD_TICKET_CHANGE.COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
SUBMITTER_LOCATION.NAME AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
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_CHANGE.USER_ID
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
left JOIN ASSET SUBMITTER_LOCATION on SUBMITTER_LOCATION.ID = U2.LOCATION_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_CHANGE.DESCRIPTION LIKE '%Created%' and
HD_TICKET.OWNER_ID != HD_TICKET.SUBMITTER_ID and
U1.EMAIL != 'helpdesk@dickinson.edu' and
HD_TICKET.OWNER_ID != 0 and
HD_TICKET.HD_QUEUE_ID = 2
Each of the selected columns becomes available as a variable for the email. The selected columns are the comma separated names in the first part of the statement, it starts with SELECT and ends with FROM. For instance,
U1.EMAIL as OWNER_EMAIL
generates a variable named $owner_email,
HD_CATEGORY.NAME AS CATEGORY_NAME
generates a variable named $category_name and
SUBMITTER_LOCATION.NAME AS SUBMITTER_LOCATION
generates a variable named $submitter_location.
When creating a custom rule there is an option for Column Containing Email address, to send an email to the ticket owner, use OWNER_EMAIL.
I hope that helps.
Comments:
-
Thank you, it does. I had to table this for a couple of weeks and just now get back to it. I was finally able to create my first custom rule for a new ticket email that didn't break with syntax errors. I still have a lot to work on obviously, but how do I list multiple Columns Containing Email Addresses? I want this email to go to the submitter, owner, and category CC's... but if I add any 2 of those variables separated by commas, semicolons, or "and", the email goes to no one.
Thanks for the thoughts on location... we do not have users' location set in AD because too many users change location too often (and HR doesn't always update us), that it's too much to keep up with. I'll cross that bridge when/if we get to it. - nicolebeth 6 years ago-
You will need to use the concat function, or better, concat_ws:
https://mariadb.com/kb/en/library/concat_ws/
CONCAT_WS(',',OWNER_EMAIL, SUBMITTER_EMAIL, NEWTICKETEMAIL) AS RECIPIENTS - chucksteel 6 years ago
