K1000 Email on Ticket Creation
I assume this has been dicussed before, but I'm running into issues when trying to create a custom rule to notify via email when any ticket is created. I've used the pre-made SQL code at http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222 but still cannot get it to go.
All I need is the code for sending a message to "firstname.lastname@example.org" when any ticket is created.
Any light you can shed? Thanks in advance!
Community Chosen Answer
Here is another suggestion, we use this to notify internal IT professionals when a new ticket comes in:
HD_TICKET.ID as TICKNUM,
'someEmail@company.com, someOneElse@company' as EMAILCC,
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,
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 UPDATERNAME,
TICKETCHANGE.COMMENT as COMMENT,
TICKETINITIAL.COMMENT as INITIAL_COMMENT,
TICKETCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
HD_CATEGORY.CC_LIST AS CATEGORYCC,
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,
JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID
and TICKETINITIAL.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = TICKETCHANGE.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
TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and
HD_TICKET.HD_QUEUE_ID = <CHANGE THIS TO THE QUEUE ID> and
HD_STATUS.NAME != 'Closed'
We run this on ticket save and you want to send an email for each result
Please note that a new ticket has come into $queue_name Queue!
Here are the details:
Created: $created in $queue_name
Created by: $submitter_fullname at $submitter_email
****END OF MESSAGE*****
For the email column, put in "EMAILCC" without the $
For the subject field, you the $ sign.
Write back if you have issues.