Manipulating Outbound Email in the ‘sys_email’ Table

///Manipulating Outbound Email in the ‘sys_email’ Table

Manipulating Outbound Email in the ‘sys_email’ Table

H

ere’s a cool tip that was just sent to me by my friend Jim Coyne. We collaborated to solve a problem that he had in his environment and this post shows the result he came up with. This post shows how you can manipulate records in the email log (sys_email table) when you have a need to change the contents or recipients of an email record. Manipulating the outbound email logs isn’t something that should be relied upon heavily and I would consider it basically a last resort but it can prove very helpful in solving the right type of problem.

The problem in this example was that there were emails being sent from Jim’s Service-now system that contained sensitive information. It was necessary to send this information as part of an integration with a 3rd party system but they didn’t want to retain that information in Service-now to be viewed in logs and in the activity history of task records.



The solution that we talked about was to set up a business rule on the ‘sys_email’ table to intercept these emails immediately after they were sent and parse out the sensitive information. This way, the email could be sent to the 3rd party system, but there would be no record of that sensitive information retained in Service-now. Jim then went and did the hard work and here is the business rule he ended up with…

‘sys_email’ Business Rule
The script makes sure we are looking at the proper type of email log record that we want to erase some of the contents for. The result is that any of these emails now show up in task records (indicating that they’ve been sent) but they contain no sensitive information.

Name: Remove Sensitive Email Log Info.
Table: Email (sys_email)
When: Before
Insert/Update: True
Condition: current.type.changesTo(‘sent’)
Script:

var index = current.body.indexOf('Service Request Form');
if (current.recipients == 'abc@xyz.com' && index != -1 ){
   current.body = 'Log information sent to 3rd party system';
   current.body_text = 'Log information sent to 3rd party system';
}
By | 2018-07-09T15:00:08+00:00 August 11th, 2010|Categories: Email Notifications|Tags: , , |8 Comments

About the Author:

Mark has worked in the IT industry since 2002 and with ServiceNow since 2007. He is the founder and creator of SN | Guru and the co-founder of Crossfuze, one of the worlds leading ServiceNow consulting partners. Prior to co-founding Crossfuze, he worked for ServiceNow as a Senior Architect on the Professional Services team. He has personally led dozens of successful implementations encompassing every part of the ServiceNow platform. He is also responsible for designing and developing groundbreaking ServiceNow solutions and best practices in the form of various applications, turnkey solutions, and integrations during his tenure at ServiceNow, Crossfuze and, of course, SN | Guru. These solutions are used today by ServiceNow administrators and consultants alike in hundreds of ServiceNow instances around the world!

8 Comments

  1. valor November 15, 2010 at 7:26 am - Reply

    I’ve used this technique for 2 reasons:

    1. prevent duplicate emails from going out to users (when you otherwise can’t figure out why)

    2. to attach a file to an outgoing message.

    Here’s the code I used for #1:

    Name: Prevent duplicate emails

    When: before / insert

    Condition: current.mailbox == ‘outbox’

    Script:

    // match Subject AND body?
     
    var bodyCheck = false;
     
     
     
    if (getSimilar(bodyCheck)){
     
       current.mailbox = "ignored";
     
       current.type = "send-ignored";
     
    }
     
     
     
     
     
    function getSimilar(body){
     
       var bodyText = current.body_text.toString();
     
       var ref = bodyText.toLowerCase().indexOf('ref:msg');
     
     
     
       var eml = new GlideRecord('sys_email');
     
       eml.addQuery('sys_id', '!=', current.sys_id);
     
       eml.addQuery('target_table', current.target_table);
     
       eml.addQuery('instance', current.instance);
     
       eml.addQuery('subject', current.subject);
     
       eml.addQuery('recipients', current.recipients);
     
       eml.addEncodedQuery('sys_created_onRELATIVEGT@minute@ago@1');
     
       if (body && body === true && ref > 0){
     
          bodyText = bodyText.substring(0, ref);
     
          eml.addQuery('body_text', 'STARTSWITH', bodyText);
     
       }
     
       eml.query();
     
       return eml.hasNext();
     
    }
  2. Wesley Van Sickle April 8, 2011 at 4:15 pm - Reply

    We currently right our analyst cell number to the activity log. We would like that to be be hidden/masked from end users/analysts. We are concerned that folks will call these numbers outside of ticket escalation

    We would like to determine if replacing current message in activity log with informative text instead would be feasible request.

    Today the Activity Log message reads:

    Sent: xxxxxxxxxx@vtext.com

    From:Service Desk Sent: 2011-04-08 08:50:31

    To: xxxxxxxxxx@vtext.com

    Subject:

    Body:

    Incident INC459498 assigned to you. Contact: Caller at xxxxxxxxxx.

    Suggest changing header message to read: On call has been paged for immediate assistance.

    We would like it to read –

    Sent: On call has been paged for immediate assistance.

    From:Service Desk Sent: 2011-04-08 08:50:31

    To: xxxxxxxxxx@vtext.com

    Subject:

    Body:

    Incident INC459498 assigned to you. Contact: Caller at xxxxxxxxxx.

    • Mark Stanger April 11, 2011 at 7:32 am - Reply

      This is possible. The easiest way is just to change the subject in the email notification. If, for some reason, you need to send the email with the contact information (as shown in your first example) but then take the information out so that it isn’t in the activity log, you could use a business rule as explained here to parse the information from the subject line when the email log entry changes to a sent state. I don’t have a pre-built script for that, but it should be very simple to figure out.

  3. Wesley Van Sickle April 13, 2011 at 9:32 am - Reply

    Mark, what email notification are you referring to? In our initial Incident Assigned to Me notification this is the subject “${number} assigned to you (${assigned_to}): ${short_description}”

    • Mark Stanger April 13, 2011 at 9:53 am - Reply

      I’m not referring to any specific notification…you’ll have to determine what notification is being used in your environment and modify it accordingly. I’m not sure what that would be in your environment.

  4. Philippe SIEK July 12, 2011 at 5:42 am - Reply

    Hi,

    I would like to know if it’s possible to modify email’s header in .
    I’ve tryed email.setHeader(“header name”, “value”), but without success.
    Any ideas ?

    Regards,

    • Mark Stanger July 12, 2011 at 5:49 am - Reply

      I’ve never had success setting headers in email notifications…even with a business rule as described here. The most common use for manipulating headers (setting the importance flag) has been addressed though. See this forum post for more details.
      http://community.service-now.com/node/1000684

  5. Philippe SIEK July 12, 2011 at 5:59 am - Reply

    Hi,

    Thanks for your reply.
    Yes, I’ve seen this post while I was looking for a solution.
    My problem is that notifications from workflows do not handle accentuated caracters correctly, they are replaced by “?” in the email body.
    It seems that the encoding / content type is different between email from standard notifications and workflow (“content-type” = “text/html” for workflow and “mixed/mutliparts” for standards ones).

    Once again thanks for you reply (and for this blog also)

    Regards,

    Philippe

Leave A Comment