Email Ticket Owner on New Ticket Creation.
It seems that prior to KACE version 9 there was a bug that emailed a ticket owner when a new ticket was created. I contacted support and they told me it was a bug and KACE was never supposed to work that way. Well we have several users that rarely get to a computer like our maintenance staff that relied on that email to do work. There is no check box to send an email to the ticket owner upon ticket creation and I really think there should be.
I have read some scenarios about doing this via a custom ticket rule but none of that is very clear to me as I am not a SQL person and most of the information I have found is years old. Can anyone outline in more of a step by step fashion how to do this on my ticket queues?
I have read some scenarios about doing this via a custom ticket rule but none of that is very clear to me as I am not a SQL person and most of the information I have found is years old. Can anyone outline in more of a step by step fashion how to do this on my ticket queues?
I would like a rule to email the ticket owner as soon as a new ticket is created. We have several categories that get automatically assigned to a specific owner when it is created by the submitter.
5 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
5 years ago
Here is what we use to email the owner on ticket creation:
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
OWNER.USER_NAME as OWNER_NAME,
UPDATER.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,
OWNER.FULL_NAME as OWNER_FULLNAME,
OWNER.EMAIL as OWNER_EMAIL,
SUBMITTER.USER_NAME as SUBMITTER_NAME,
SUBMITTER.FULL_NAME as SUBMITTER_FULLNAME,
SUBMITTER.EMAIL as SUBMITTER_EMAIL,
UPDATER.EMAIL as UPDATEREMAIL,
UPDATER.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,
SUBMITTER.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 OWNER on OWNER.ID = HD_TICKET.OWNER_ID
left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
left join USER UPDATER on UPDATER.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 = SUBMITTER.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
OWNER.EMAIL != 'helpdesk@domain.tld' and
HD_TICKET.OWNER_ID != 0 and
HD_TICKET.HD_QUEUE_ID = 2
A few notes:
HD_TICKET_CHANGE.DESCRIPTION LIKE '%Created%' - rule only matches on a new ticket
HD_TICKET.OWNER_ID != HD_TICKET.SUBMITTER_ID - We don't bother notifying the owner if they are also the submitter
OWNER.EMAIL != 'helpdesk@domain.tld' - no need to let the helpdesk know that a ticket was created for them, either (this also prevents email loops)
HD_TICKET.OWNER_ID != 0 - don't try to email someone if the ticket isn't assigned to anyone
HD_TICKET.HD_QUEUE_ID = 2 - set this to your queue's ID
The rule is set to run on Save
Place OWNER_EMAIL in the Column containing email addresses
Our email template looks like this:
A new ticket has been created in the $queue_name queue.
Title: $title
Submitter: $submitter_fullname
Submitter Phone: $submitter_work_phone
Submitter Location: $submitter_location
Category: $category_name
Priority: $ticket_priority
Notes (by $updater_name): $comment
View the ticket here:
https://kace.domain.tld/adminui/ticket?ID=$ticknum
You are receiving this email because you are the owner of this ticket.
Comments:
-
Maybe a stupid question, but does all of that code go into the Select SQL box and the Update SQL box is left empty?
I'm trying to find a way to email the owner on ticket creation/save. Kind of insane that it's not included out of the box with this system
Appreciated. - gfacchini 5 years ago-
Yes, the query is the select query. The rule doesn't change the ticket, so there is no need for an update query.
There are system rules for notifications, so there is an option included out of the box. Custom rules provide more flexibility. - chucksteel 5 years ago-
Hi again.
OK interesting. I couldn't get the rule to work for a queue and I'm not real sure why. I'll have to see what error it was throwing back.
Basically we can't get at least 1 queue to email the Owner when the Owner is changed. I thought it worked but I might be thinking back to when it was glitched. - gfacchini 5 years ago
-
Be sure to update the HD_QUEUE_ID = 2 at the end of the statement to match the queue that the rule is running in. Also, the rule I posted will only work for ticket creation (HD_TICKET_CHANGE.DESCRIPTION LIKE '%Created%') and won't run for any other ticket changes. - chucksteel 5 years ago
-
OK I must be missing something because I cannot get the process to email the ticket owner. I created a new custom ticket rule and I pasted the query and only changed the queue ID to match. I changed nothing else.
I was not sure what was meant by "Place OWNER_EMAIL in the Column containing email addresses" so I checked the box "Email Results" and put OWNER_EMAIL into that. Somehow I do not think that is correct. - bwilkerson 5 years ago-
When you check the box for Email each recipient in query results you will get fields to configure the message. One of those fields is Column containing email addresses. The value for that field needs to be OWNER_EMAIL. - chucksteel 5 years ago
-
Thank you Chuck. I cannot believe I missed that. Now I just have to find where to get the field information so I can customize the email template to include more of our help desk fields. I need to know the information that comes after the $. For example if I wanted to include the field named Custom_2 in the email template what would the $ name be?
Forgive my ignorance and I appreciate your expertise. - bwilkerson 5 years ago
-
Thanks for sharing this code. Could you explain what's happening here (specifically what <CHANGE_ID> needs to be)?
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID> - falcon31 5 years ago-
Ticket changes are stored in the HD_TICKET_CHANGE table. The join statement is creating the relationship between the ticket and a change. In this case <CHANGE_ID> is a variable that the appliance will replace when the rule runs (at ticket save) with the ID of the change that is associated with the ticket being saved.
There's no need to alter that statement. - chucksteel 5 years ago-
Working great! Thanks for the explanation. - falcon31 5 years ago
Posted by:
shanemg
3 years ago
Any updates on this ? After adjusting queue and email address.. this SQL query just tests with a "There were syntax errors in your query."
Quest SMA Current Version: 11.0.273
Thanks in advance...
Shane
Comments:
-
You can't test this query like this, you'll always have this error "There were syntax errors in your query" when there is something like "<CHANGE_ID>" in your query. This is not working but this is normal.
If you want to test it, you must send a "real" ticket that will trigger the query. - ngerard 3 years ago
which by the way does not notify the ticket OWNER, it notifies the static email address that you enter into the query. - bow03 5 years ago