/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Sending an e-mail after manually creating a ticket.

05/02/2016 548 views
I want to send an e-mail to the customer when we manually enter a ticket.  I'm on version 6.4 - can you help.   SQL is not something I do well. 
1 Comment   [ + ] Show comment

Comments

  • I get a Syntax error. I posted it here.


Community Chosen Answer

1

Here is the select statement we use for new tickets:

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,
U2.LOCATION 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
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 '%Ticket Created%' and
HD_TICKET.OWNER_ID != HD_TICKET.SUBMITTER_ID 

When creating your rule use SUBMITTER_EMAIL as the column containing the email address for the Email each recipient the query results settings.


Answered 05/03/2016 by: chucksteel
Red Belt

All Answers

0
Thanks that is Awesome, but I'm very lost I don't see a Submitter_email What am I missing. 
Answered 05/03/2016 by: anaccarato
Orange Belt

  • It's one of the columns being selected:
    U2.EMAIL as SUBMITTER_EMAIL,
    • Can I customize the e-mail that is sent
      • Yes, when creating the rule check the box for Email each recipient in query results. For Column containing email addresses: use SUBMITTER_EMAIL. Fill in the subject and message boxes appropriately. Any of the columns selected can be used as variables in the email. For example, the ticket title is $title, the ticket ID is $id, and the user's full name is $submitter_fullname.
This content is currently hidden from public view.
Reason: Removed by member request For more information, visit our FAQ's.

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