/build/static/layout/Breadcrumb_cap_w.png
09/23/2016 1711 views
I originally was using the "Email on Events - Comments" options (which worked), but when I started making custom rules, it did not work as expected.  On my custom rule I checked the "Append comment to service request" and the "Owners Only" box.  I expected the comments to be updated in the service request and because the "Owners Only" box was checked, it would not send out an email to the submitter, but it did.  So apparently the "Email on Events - Comments" does not take the "Owners Only" box into consideration when adding custom rules.  I read on another string that the out of the box "Email on Events Comments" should be turned off and another custom rule should be made for comment but, the SQL was not provided.  Does anyone have custom rule to email the submitter when a comment is added, only when the "Owners Only" box is unchecked?
2 Comments   [ + ] Show comments

Comments

  • Yes, I use such a kind of rule.
    I have a rule that send an email to the submitter only when the 'owner only' box is unchecked and the comment is not from the submitter.
  • I tried using the code that svmay posted but it still does not work. What am I doing wrong???? Someone please help.

All Answers

1
Here is my custom ticket rule for that, hope it can help you:

sql-select:
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 UPDATERNAME,
UNIX_TIMESTAMP(TICKETCHANGE.TIMESTAMP),
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,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID
 and TICKETCHANGE.ID=<CHANGE_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.COMMENT != '' and
TICKETCHANGE.DESCRIPTION not like "Changed ticket Machine from%" and
TICKETCHANGE.DESCRIPTION not like "%Ticket Created%" and
U3.FULL_NAME not like (U2.FULL_NAME) and
TICKETCHANGE.OWNERS_ONLY = 0 and
HD_TICKET.HD_QUEUE_ID = 10 and
HD_STATUS.NAME != 'Closed'

[x] send an email to..

subject:                                                                  column with mail adress:
[TICKET-ID:$id] new comment.SUBMITTER_EMAIL                                     
mail:
Your email text.

Answered 09/26/2016 by: svmay
Red Belt

  • Disregard my previous distress email. The issue was found. Just in case anyone else needs the codes:


    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 UPDATERNAME,
    UNIX_TIMESTAMP(TICKETCHANGE.TIMESTAMP),
    TICKETCHANGE.COMMENT as LAST_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,
    HD_PRIORITY,
    HD_STATUS,
    HD_IMPACT,
    HD_CATEGORY)
    JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID
    and TICKETCHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.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.COMMENT != '' and
    TICKETCHANGE.OWNERS_ONLY = 0 and
    HD_STATUS.NAME != 'Closed'