I've created a custom ticket rule that checks to see if the newest comment on a ticket starts with "USERNOTE:". If it does, it sends the contents of the comment to the Submitter of the ticket. I have created the same exact rule but also set it up to send to the CC List of a ticket if "USERNOTE:" is used.

My problem I am facing is that only the first entry on the CC list is receiving the an email even if there are multiple people on the CC List.

I think this is happening because in Custom Ticket Rules, one email is sent per result row of the Select Query. Is there another way to accomplish this task?

I've included the SQL code of the ticket rule here:

 

 select 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_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
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,
HD_TICKET.CC_LIST AS CCLISTEMAIL, /*<<<change your email here */
STATE,
HTC.COMMENT as NEWCOMMENT,
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
LEFT JOIN ( select max(HD_TICKET_CHANGE.ID) MAXID, HD_TICKET_ID from HD_TICKET_CHANGE/*added>>*/,
HD_TICKET WHERE HD_TICKET.ID=HD_TICKET_CHANGE.HD_TICKET_ID
and USER_ID=SUBMITTER_ID/* <<added*/ GROUP BY HD_TICKET_ID) HTC_LATEST
ON ( HD_TICKET.ID=HTC_LATEST.HD_TICKET_ID )
LEFT JOIN HD_TICKET_CHANGE HTC ON HTC.ID = HTC_LATEST.MAXID
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.MODIFIED < DATE_ADD(NOW(), INTERVAL 1 MINUTE) /* change was made in the last 60 seconds */
and HTC.COMMENT like 'USERNOTE:%'
and HD_TICKET.HD_QUEUE_ID = 4

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

This is one possible answer, try using this as your select for email:

We have run into a similar problem.  Try this:

 

SELECT 

concat('<someEmailAddress>,<someOneElse>',', ', SUBMITTER_EMAIL) as EMAILCC,

additional sql code......

 

Then in your email column add in EMAILCC without a $

 

 

 
Answered 06/24/2013 by: Jbr32
Tenth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share