We have some tickets that are created from systems for alerts. When the email is sent in a ticket is created and then a notification is sent back with Ticket Info. When a comment, resolution or ticket is closed it sends an email back with updates. The issue is that we do not have these alerts sent from actual users, but just generic email address assigned to specific applications. I can see the in the beginning part of the ticket Rule to select the Submitter User Name (its the email address) but then what would i put in the modified SQL to NOT send an email on any event to these specific "users" on Ticket Save? I do want normal communication to be sent out to other real user's though so I do not want to turn off the built in notifications. 

0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

1

Depending on what other statements are in the where clause you should be able to include 

and HD_TICKET.SUBMITTER_ID != <id of generic user>

Even if this is a generic user there should be a userid for them in the KACE database since the KBOX will create an ID for them when the email is submitted. If your rule is joining to the user table based on submitter ID then you can search on the email address instead.

Answered 02/21/2014 by: chucksteel
Red Belt

  • I am able to get the Select Query to pull the username that I need, I just don't know what to add to not send any email notifications to this user for any communications. Would I need to put something in the Update Query to stop emails being sent? Sorry, I am less then a beginner when it comes to SQL but am trying to learn.
    • The key isn't to add them to the select portion of the statement but the where clause. If you post one of your rules I can show you exactly where to place the additional line.
      • Thanks!

        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((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
        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 (( (1 in (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.USER_NAME = 'parity@xxx.com')) ) and HD_TICKET.HD_QUEUE_ID = 9 )
      • So it looks like U2 is the submitter (the join statement LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID is where this comes from). Based on that you would need to exclude any tickets where U2.EMAIL = automated@xxx.com or whatever the email address these tickets are coming from. I would replace this:
        and (( (1 in (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.USER_NAME = 'parity@xxx.com')) ) and HD_TICKET.HD_QUEUE_ID = 9 )

        with this:
        and U2.USER_NAME = 'parity@xxx.com'
        and HD_TICKET.HD_QUEUE_ID = 9
        and U2.EMAIL != 'automated@xxx.com'

        Note that I'm not sure why this rule only applies to tickets where the user_name is parity@xxx.com but that is what is listed in the rule.
      • Thanks for your Help! I have made the changes and will be able to see if it is working Monday morning.
      • Chuck, new tickets from this parity@xxx.com (I am using xxx to hide my company email address) are still trying to send emails to this address. Maybe my SQL below is not correct. I guess the simple question would be, What would the SQL be to stop Service Desk emails to be sent to specific users/email address? My SQL below was the start of my adding a new ticket rule with the "Submitter User Name" as the starting point in a new Rule and then just pasted the SQL below. I hope this makes more sense.
      • I might have misunderstood earlier. After:
        and HD_CATEGORY.ID = HD_CATEGORY_ID

        You should just have:
        and HD_TICKET.HD_QUEUE_ID = 9
        and U2.EMAIL != 'parity@xxx.com'

        Also, to be clear, this should be on the rule that sends emails from the helpdesk to other users. You need to add the and U2.EMAIL != 'parity@xxx.com' statement to any rules that send email from the helpdesk in order to have it not send anything to that email address.
    • Thanks, how would I modify the Canned Email's that would come from Email On Events "Customize Emails"
      • You can only change the content. On the queue configuration page under Email on Events click Customize Emails.
Please log in to comment
Answer this question or Comment on this question for clarity

Share