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 
Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • What order do your rules run? It might be that the rule which adds the user to the cc list is happening after the email rules are processed.
  • I am going to post the rule maybe there is a better way now of doing this:
    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
Please log in to comment

Answer Chosen by the Author

0
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
Answered 10/19/2017 by: Hobbsy
Red Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share