Send email to an individual when a ticket is assigned to a group
We have a Purchasing Group that all of our tickets get assigned to that multiple use. One individual wants to have an email sent to them every time a ticket is assigned to that Purchasing Group. I have created a rule that if a ticket is assigned to the group that this person gets put into the cc list and is sent an email. It works great on ticket save for individual tickets but when it is a work flow that auto assigns to the group they are not getting an email.
Any suggestions on how to make this work for both individual tickets and work flow processes.?
Thanks
2 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
Hobbsy
6 years ago
Top Answer
I would change your approach to sending this email, assuming that you want a single email being sent to the user, first why not hardcode the email address into a custom field, simply use a custom field with the email address as the default value and set the field to hidden. You will then be able to add the custom field to the select query and make it available as a variable for the email part.
Also to prevent the email being constantly sent use another custom field as a switch field, set the default value to '1' and set it to '2' as part of the ticket rule. Again setup the field make it visible to test the rule and then hide when the rule works.
Setup the ticket rule using the wizard, set the criteria you need i.e.
If the category is 'purchasing'
the status is 'logged'
the switch field is '1'
(maybe even is the log date is within the last hour)
Set the update criteria to set the switch field from '1' to '2'
Now configure your rule, add in the hardcoded email address to the select statement and configure the email settings in the rule and test the rule.
You should see the switch field change in the ticket and you can set the rule to run every 15 mins.
Finally hide the switch field and the ticket rule will keep on working
select HD_TICKET.*,
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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
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(HD_TICKET.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 (( ( exists (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.FULL_NAME = 'Purchasing Group')) ) and HD_TICKET.HD_QUEUE_ID = 1 )
Email each recipient in query results
Subject:
$ticknum
Column containing email addresses:
(individual persons email address)
Update Query
update HD_TICKET
set HD_TICKET.CC_LIST = 'individuals email address'
where
(HD_TICKET.ID in (<TICKET_IDS>))
This was working but isn't now so maybe with the updates and changes I should be doing this differently? Tried a couple of other ways I found on ITNINJA but that still didn't work. The rule and the emails all are kicked off on incident save - scarpent 6 years ago