/build/static/layout/Breadcrumb_cap_w.png

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 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
  • Glad I am not the only one who used this "bug" to my advantage. I noticed it recently and it seems if the ticket is created without an owner (or owner is left as the default owner of the queue) it will still email when it is manually assigned to the new owner. I have some users setup to NOT get ALL ticket notifications and ONLY the ones they are the owner of. Also, this affects emails to Owners from an auto-assignment based on the category (Category Owners). Only way we have been able to get by is to constantly check your ticket queue. Not an ideal solution since we do not have our SMA open to the Internet and emails can go to phones. Can't check the queue from your phone this way. - GreeNova 5 years ago
  • I would like this functionality. Apart from the other problems I am having just getting emails to trigger we have several vendor Q's (third parties) that when we log a ticket and put it into their Q it would send them an email (so that it would generate a ticket in their system) - kiwiblue 5 years ago
  • I am also struggling with this. I need to have an email send when a ticket is created I can't believe this isn't built in to the system. The product has been pretty worthless from a service desk standpoint since we do not receive emails when tickets are created. - cmeisinger@lakeerieelectric.com 5 years ago
  • I'm trying to figure out the exact same thing. I have many Owners that are automatically assigned a particular Category when the ticket is created. They don't camp on the ticketing system and rely on their email when they've been assigned a ticket. It's very frustrating when they have to go into the system to see if anything is there or have another person go in and manually assign them the ticket so they get an email. I even tried the CC List in the customization but that doesn't work either. - sabrina.geoffroy 5 years ago
  • "I contacted support and they told me it was a bug and KACE was never supposed to work that way." This is ridiculous! We have used Kace for 10 years. A functional service desk will send a notification to the ticket owner when a ticket is create by DEFAULT! I should not have to build the query in the "answer"
    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
    • The OWNER_EMAIL column contains the email address of the owner. The static email address listed in the query excludes tickets where the owner is the helpdesk. - chucksteel 5 years ago

Answers (2)

Posted by: chucksteel 5 years ago
Red Belt
2

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 4 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 4 years ago
      • Working great! Thanks for the explanation. - falcon31 4 years ago
Posted by: shanemg 2 years ago
White Belt
0

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 2 years ago
    • Can you review this ? https://www.itninja.com/question/email-ticket-owner-on-new-ticket-creation-1 - shanemg 2 years ago
    • Can you review this ? https://www.itninja.com/question/email-ticket-owner-on-new-ticket-creation-1 - shanemg 2 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ