How to send an email to an arbitrary email address via a ticket rule
It's actualy pretty frustrating that you can't just type in an email address to send to in a rule. e.g: Why on earth would anyone ever want to get an email-based help system to send an email to an arbirary email address? But I digress. I read this post (below) on how to send an email via a ticket rule, but the last post on this thread is not complete.
http://www.itninja.com/question/ticket-rule-sent-mail-to-0-of-1
All I'm trying to do is get the KBOX to send an email to a person or group of people when a new ticket is received. To start with I created a rule that looked for all tickets with STATUS = "New" and set it to run when the ticket is saved.
The post above says you have to hardcode the email address you want to send to in the select statement like this:
select ID, 'support@kace.com' SUPPORT from HD_TICKET WHERE 1=1
That's all find and good, but not being a SQL expert, how do I do that when I already have a select statement (du)? Where do I put this?
Much Thanks in advance.
Answers (1)
I've got email notification ticket rules in my HR, Equipment Request and Time Tracking queues, all of which are documented completely (with tips) in these blogs:
http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned
http://www.itninja.com/blog/view/k1000-service-desk-equipment-request-queue-config-custom-ticket-rules
Also, if you need some pointers in getting started with SQL, try this one:
Here's one of the "send an email" ticket rules from my ER queue, as an example:
Title:
Email Alert on Waiting on Approval
************************************
Sends an email to IT Director when ticket is first created and saved. He clicks on the applicable link (includes a PO# if it's a purchase), hits Send and the ticket gets approved/rejected (and the PO# field gets populated) accordingly.
************************************
Order:
11
Notes:
Sends an email to IT Director when an Equipment Request ticket's approval has not been specified.
Frequency:
on Ticket Save
Select Query:
select 'it.director@company.com' as APPROVER, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT,
HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE1 as EQUIPMENT, HD_TICKET.CUSTOM_FIELD_VALUE5 as SOFTWARE,
HD_TICKET.CUSTOM_FIELD_VALUE6 as REASON, HD_TICKET.CUSTOM_FIELD_VALUE7 as RCOMMENTS, HD_TICKET.CUSTOM_FIELD_VALUE8 as VENDOR,
HD_TICKET.CUSTOM_FIELD_VALUE9 as VLINK, HD_TICKET.CUSTOM_FIELD_VALUE10 as PRICE, HD_TICKET.CUSTOM_FIELD_VALUE11 as MAKE,
HD_TICKET.CUSTOM_FIELD_VALUE12 as MODEL, HD_TICKET.CUSTOM_FIELD_VALUE13 as PONUMBER, HD_TICKET.CUSTOM_FIELD_VALUE14 as QUANTITY,
HD_TICKET.DUE_DATE as DUE_DATE, HD_TICKET.TITLE as Issue, HD_CATEGORY.NAME as CATEGORY,
HD_TICKET.*, 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,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
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.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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.APPROVAL = '' and HD_TICKET.HD_QUEUE_ID = 3 )
__________________________________________
X Send an email for each result row
Subject:
[TICK:$id] EQUIPMENT PURCHASE/REQUEST: $status_name
Email Column:
APPROVER
Email Body:
A ticket in the Equipment Request queue needs your approval, please review.
Ticket ID: $id
Created: $created
Issue: $issue
Category: $category
Status: $status_name
Requesting User: $fullname
Department: $department
Location: $location
Equipment Type: $equipment
Software Type: $software
Quantity: $quantity
Reason: $reason
Reason Comments: $rcomments
Vendor: $vendor
Vendor Link: $vlink
Price (-S&H): $price
Make: $make
Model: $model
PO#: $ponumber
Due Date: $due_date
___________________________________________________________________
To APPROVE this request, please click here and enter PO# (if applicable):
<mailto:it.er@company.com?subject=[TICK:$id]&body=@approval%20=%20Approved%0d%0a%0d%0a@CUSTOM_14%20=%20>
To REJECT this request, please click here:
<mailto:it.er@company.com?subject=[TICK:$id]&body=@approval%20=%20Rejected>
___________________________________________________________________
Thanks,
Company IT
Hope that helps!
John
Comments:
-
Thanks. The simple line in the SQL statement 'it.director@company.com' as APPROVER did the trick. I've already looked at several of the blog pages where people have documented things and learned a few things there, so thanks for the additional links also. - bandries 12 years ago
