KACE: Email Owner on Ticket Creation
I would like a rule that emails the owner on ticket creation. I've seen similar rules to trigger email events, but I don't know SQL well enough to figure out how to change that code to do what I want.
I am trying to use the article from KACE here: http://www.kace.com/support/resources/kb/article/How-To-Notify-Any-User-or-Group-by-Email-When-a-New-Help-Desk
When I use the SQL statement in the article as is (with changes to our URL and email address) it sends me the query results, but is not sending the assigned owner an email. I have rules set up to auto-assign tickets to owners based on our location field. I have the assign rule run first and then the email owner rule run next.
This is not really relevant, but the sql query that it sends me every time is basically unreadable in columns that are super narrow. How do I change that format?
Answers (1)
I checked the KACE article you referenced and if all you want is for the results to go to the ticket owner, just change the Email Column field in the "Send an email for each result row" part of the ticket rule from NEWTICKETEMAIL to OWNER_EMAIL.
If you look in the Select query, this is the alias that corresponds to OWNER.EMAIL. The OWNER table is itself an alias of the USER table and works by way of the join statement that corresponds the USER.ID to the HD_TICKET.OWNER_ID, i.e.:
LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
As for the SQL query that has narrow columns, I'm assuming that you're referencing the "Send query results to someone" part of the ticket rule. As far as I know, this is mainly for testing the Select query results (rather than using a separate SQL Report or something like the MySQL Query Browser) and not currently intended for formatted usage (SQL Reports are more intended for that and can be scheduled to email results). Also, as far as I know, there's no way to tweak the output format of these results from the GUI (anything's possible with under-the-cover tweaks by Professional Services, of course...).
Hope that helps!
John
Comments:
-
Thanks. I had the wrong variable in the email field and that fixed it. My follow up question is how to know the identifying information for custom fields I have created in order to add them to the email.
BTW, primer on SQL was very helpful. Still a newbie, but getting the hang of it. - lmland 11 years ago -
Teach a man to fish... ^_^
Check out the Email Alert on Waiting on Approval ticket rule in this blog for examples of adding custom fields to a tweaked wizard-generated ticket rule and then referencing them in the email message body:
http://www.itninja.com/blog/view/k1000-service-desk-equipment-request-queue-setup
Just be aware that in the GUI, the custom fields will be one number higher than in the SQL. For example, in the referenced ticket rule I have HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, this actually corresponds to CUSTOM_3 in the GUI (Ticket Layout section).
To have this field appear in the email, I simply use $fullname in the body.
Let me know if you have any questions. ^_^
John - jverbosk 11 years ago -
do you have to assign the field to the variable in the query? e.g FULLNAME = $fullname - lmland 11 years ago
-
Also your link has "been removed by user request". - lmland 11 years ago
-
Yes, you alias the field in the query and then reference it in the body of the email.
Thanks for pointing out that my post was hidden - I wasn't aware of that and have submitted a request to have it "unhidden". For the time being, here's another article that has similar rules documented:
http://www.itninja.com/blog/view/k1000-service-desk-time-tracking-queue-config-custom-ticket-rules-sql-reports
Hope that helps!
John - jverbosk 11 years ago
http://www.kace.com/support/resources/kb/article/How-To-Notify-Any-User-or-Group-by-Email-When-a-New-Help-Desk
The KACE site no longer has that article.
Thanks. - smalls 10 years ago