Outbound Email Summary Tool (What Action Triggered an Email Sent by the Instance)

An outbound email case that is often created by customers goes as follows:

“I just received an email from a ServiceNow instance. Can you please tell me why I received it? What action in the instance triggered this email?”

To give the customer the answer, the TSE first asks the customer for the message-id if not already provided by the customer:

Message-ID:

The next step the TSE performs is search the Email [sys_email] table with the following filter:

Type=sent Message ID=

From the Email [sys_email] record, the TSE is able to locate more information regarding the source of the trigger such as:

The Notification used to send the email message The Event that triggered the notification The target record that was updated and tied to this notification

There is now a tool that does all of the above in second. All that is required is provide a message-id, and the Outbound Email Summary Tool will provide a summary of all of the components (target record, event, notification, email record) involved in sending that email message. This should save customers and TSEs time when troubleshooting these types of email issues.

On top of the time savings, the tool also provides additional information not available from the previous manual steps. For example:

What specific update on the target record kicked off the trigger Emails that were triggered from a Flow Action

Here is a sample of the results from running this tool:

To use this script simply

1. copy and paste this script into the Scripts-Background area 2. replace the value of "message_id" with the desired message-Id

Script:

var message_id = " "; // replace this with your message_id

var buf = []; var email = new GlideRecord("sys_email"); email.addQuery("message_id", message_id); email.query(); if(email.next()){ buf.push("\n=============================================================================="); buf.push("\nEmail information:"); buf.push("\n=============================================================================="); buf.push("\nURL: " + email.getLink(true)); buf.push("\nCreated (email record created and queued): " + email.sys_created_on.getDisplayValue()); buf.push("\nUpdated (email sent): " + email.sys_updated_on.getDisplayValue()); buf.push("\nType: " + email.type); buf.push("\nSend To: " + email.recipients); buf.push("\nSubject: " + email.subject);

//was this email generated from an event or record update (event-based or engine-based)? buf.push("\n=============================================================================="); buf.push("\nEmail generated from:") buf.push("\n=============================================================================="); var email_log = new GlideRecord("sys_email_log"); email_log.addQuery("email", email.sys_id); email_log.query(); if( email_log.next() ) { var email_notif = new GlideRecord("sysevent_email_action"); email_notif.addQuery("sys_id", email_log.notification); email_notif.query(); if( email_notif.next()) { buf.push("\nNotification: " + email_notif.name + " (" + email_notif.getLink(true) + ")"); buf.push("\nGeneration type: " + email_notif.generation_type.getDisplayValue()); if("event"==email_notif.generation_type){ buf.push("\nEvent name: " + email_notif.event_name); } } var email_event = new GlideRecord("sysevent"); email_event.addQuery("sys_id", email_log.event); email_event.query(); if( email_event.next() ) { buf.push("\n\nThe notification was triggered by the following event:"); buf.push("\nEvent fired: " + email_event.name + " (" + email_event.getLink(true) + ")"); buf.push("\nCreated: " + email_event.sys_created_on.getDisplayValue() + " (Created by: " + email_event.sys_created_by + ")"); buf.push("\nProcessed: " + email_event.processed.getDisplayValue()); buf.push("\nProcessing duration: " + email_event.processing_duration);

buf.push("\n\nThe event was triggered by the following record update:"); var event_rec = new GlideRecord(""+email_event.table); event_rec.addQuery("sys_id", ""+email_event.instance); event_rec.query(); if( event_rec.next() ) { buf.push("\n" + event_rec.getDisplayValue() + " (" + event_rec.getLink(true) + ")"); }

//if this is an event-type of notificaiton, then we need to know what script could have fired the event, // check these tables: if("event"==email_notif.generation_type) { var business_rules = new GlideRecord("sys_script"); business_rules.addQuery("script", "CONTAINS", ""+email_event.name); business_rules.query(); if( business_rules.getRowCount()>0 ) { buf.push("\n\nPossible business rule(s) that triggered the event:"); while (business_rules.next()){ buf.push("\n.... Business Rule: " + business_rules.name + " (Active=" + business_rules.active +", URL=" + business_rules.getLink(true) + ")"); } } var inactivity_mons = new GlideRecord("sysrule_escalate_am"); inactivity_mons.addQuery("table",""+email_event.table); inactivity_mons.query(); if( inactivity_mons.getRowCount()>0 ) { buf.push("\n\nPossible Inactivity monitor(s) that triggered the event:"); while( inactivity_mons.next() ) { buf.push("\n... Inactivity monitor: " + inactivity_mons.name + "(URL=" + inactivity_mons.getLink(true) + ")"); } }

var script_includes = new GlideRecord("sys_script_include"); script_includes.addQuery("script","CONTAINS",""+email_event.name); script_includes.query(); if( script_includes.getRowCount()>0 ) { buf.push("\n\nPossible Script Include(s) that triggered the event:"); while( script_includes.next() ) { buf.push("\n... Script Include: " + script_includes.name + " (URL=" + script_includes.getLink(true) +")"); } } }

//if this is an engine-based notification, then we need to know what record update caused the notificaiton engine to fire if("engine"==email_notif.generation_type) { var sys_his_set = new GlideRecord("sys_history_set"); sys_his_set.addQuery("id", email_event.instance+""); sys_his_set.addQuery("table", email_event.table+""); sys_his_set.query(); if( sys_his_set.next() ) { if( sys_his_set.updates==0 ) { buf.push("\nCreated on " + sys_his_set.sys_created_on.getDisplayValue() + " by " + sys_his_set.sys_created_by ); } else { //find the update that caused this notification var sys_his_line_email_sent = new GlideRecord("sys_history_line"); sys_his_line_email_sent.addQuery("set", sys_his_set.sys_id); sys_his_line_email_sent.addQuery("type", "email"); sys_his_line_email_sent.addQuery("update_time", email_event.processed); sys_his_line_email_sent.query(); if( sys_his_line_email_sent.next() ) { //buf.push("\nDEBUG_CS: located sys_history_line for email sent based on update" + sys_his_line_email_sent.update ); var sys_his_line_updated_fields = new GlideRecord("sys_history_line"); sys_his_line_updated_fields.addQuery("update", sys_his_line_email_sent.update); sys_his_line_updated_fields.query(); var counter = 0; while(sys_his_line_updated_fields.next()){ if( counter++ == 0 ) { buf.push("\n... update on " + sys_his_line_updated_fields.update_time.getDisplayValue() + " by " + sys_his_line_updated_fields.user.getDisplayValue()); } if("email"!=sys_his_line_updated_fields.field) { buf.push("\n... ... " + sys_his_line_updated_fields.label + ": " + sys_his_line_updated_fields.getValue("new")); } } } } } else { //if sys_history_set not found, try sys_audit //note: i prefer sys_history_set because there is a direct connection to update, with sys_audit we have to guess based on timestamps

//first, need to substract event created date by 1 second (will use later) var event_sys_created_on_minus_a_sec = new GlideDateTime(email_event.sys_created_on); event_sys_created_on_minus_a_sec.subtract(1000); //in ms. var sys_audit_recs = new GlideRecord("sys_audit"); sys_audit_recs.addQuery("tablename", ""+email_event.table); sys_audit_recs.addQuery("documentkey", ""+email_event.instance); sys_audit_recs.addQuery("sys_created_by", ""+email_event.sys_created_by); var created_on_query = sys_audit_recs.addQuery("sys_created_on", ""+email_event.sys_created_on); created_on_query.addOrCondition("sys_created_on", ""+event_sys_created_on_minus_a_sec); sys_audit_recs.query(); var counter = 0; while( sys_audit_recs.next() ) { if( counter++ == 0 ) { buf.push("\n... update on " + sys_audit_recs.sys_created_on.getDisplayValue() + " by " + sys_audit_recs.sys_created_by.getDisplayValue()); } buf.push("\n... ... " + sys_audit_recs.fieldname + ": " + sys_audit_recs.newvalue); } } } } } else { //notification was not triggered by event or record update (event-based or engine-based). Therefore, it must be Flow Designer var index_of_flowdesigner_header = email.headers.indexOf("X-ServiceNow-Source: FlowDesigner-"); if( index_of_flowdesigner_header>0 ) { var flowdesigner_sysid = email.headers.substring(index_of_flowdesigner_header+34,index_of_flowdesigner_header+66); var flowdesigner_context = new GlideRecord("sys_flow_context"); flowdesigner_context.addQuery("sys_id", flowdesigner_sysid); flowdesigner_context.query(); if( flowdesigner_context.next() ) { buf.push("\nFlow name: " + flowdesigner_context.name + " (" + flowdesigner_context.getLink(true) + ")"); buf.push("\nState: " + flowdesigner_context.state); buf.push("\nCreated by: " + flowdesigner_context.sys_created_by); buf.push("\nCreate on: " + flowdesigner_context.sys_created_on); } } }

} else { buf.push("\nNo Email record [sys_email] found with the given message id: " + MESSAGE_ID); }

gs.print(buf.join(""))

Note that this is tool is for troubleshooting purposes and it is not a part of the ServiceNow platform or any ServiceNow-supported applications. Therefore, ServiceNow support will not be able to assist on any questions or help regarding this tool.

Cesar Sandoval

Technical Support Architect