Inbound Actions: Handling Email Conversations

conmic · ‎03-02-2016

Introduction

So we ran into an issue when implementing ServiceNow with one of our Service Desks. We wanted to automatically create a ticket in the system when a new email is received, however often the customer is adding our support email address into a conversation with multiple people. Of course this causes the system to create a new ticket for each reply, as the system does not recognize it as part of an existing ticket.

We have already a policy in place that only authorized users are allowed to communicate with us, however this is not entirely preventing this issue. Also telling the users to stop building conversation is not an option, as they would just ignore it and educating the customer is not really professional.

So a technical solution is the only way to go and I think I cracked it.
I build an inbound action that does the following steps:

Triggers on every email of type 'new'.
Checks if the subject contains a recognized reply prefix (system property: glide.email.reply_subject_prefix)
If it does, it searches sys_email by the subject for received and processed emails of the past 60 days that do have a target record.
For each found target record (in the tables incident, change_request, problem) it checks if such is still active and updates the comments accordingly. It also adds the sender to the watch list, if (s)he is not already in there.
Note 1: it may update multiple records and there is a possibility that it also updates none-related records if the initial subject of the email was the same. One way to limit this is to decrease the email search from 60 days to a lower number. You might need to adapt this to your needs.
Note 2: it will not update the "Target" field of the email that triggered this inbound action. It will still be of type 'new' with an empty "Target" field.
If at least one record was successfully updated, it will stop processing any further inbound actions.
Note: The order of your inbound actions is important. I suggest that this inbound action is trigger before any 'Create' (e.g.: Create an Incident) inbound actions.
If no record was updated, it will not stop processing any further inbound actions.

The Inbound Action

Name: Handle Conversations

Target table: task (it's not important, but not setting it will create error logs).

Type: New

Condition: (empty)

Script:

//has this 'new' email a reply prefix? If yes, it's a reply
var isReply = this.checkIfReplyBySubjectPrefix(email.subject);
if(isReply){
         //search for the subject in sys_email and gather the details of past sys_emails (UIDs, target records, etc...)
         var thread = this.getThreadBySubject(email.subject,60);
         if(!thread.Subject.nil()){
             var stopProcessing = false;
                   //if the EmailCount is 0 then don't take any action. Another inbound action may be triggered.
                   if(thread.EmailCount > 0){
                             //handle multiple target records
                             for(var x = 0 ; x < thread.EmailTargetCount ; x++){
                                       //write the target sys_id and table of the current loop-cycle into variables
                                       var sysId = thread.EmailTargets[x].sys_id;
                                       var table = thread.EmailTargets[x].table_name;
                                       //get the target record of the current loop-cycle and execute the according actions
                                       //the function returns 'true' if any record was updated
                                       var didUpdate = this.getRecordAndTakeAction(sysId,table);
                                       //once at least one target record has been updated, we want to stop any other inbound actions from triggering
                                       //if no target record has been updated, another inbound action may be triggered
                                       if(didUpdate)
                                                 stopProcessing = true;
                             }
                   }
                   //if the Email Count is -1, we also stop processing and thus stop triggering any other inbound actions
                   else if(thread.EmailCount == -1){
                         stopProcessing = true;
                   }
                   //stop any other inbound actions from triggering
                   if(stopProcessing)
                         event.state="stop_processing";
         }
}
function getThreadBySubject(Subject,DaysAgo){
         //get the Topic by removing any possible reply prefixes from the Subject
         var topic = Subject;
         topic = this.removeSubjectReplyPrefix(Subject);
         //build the object that is returned by this function
         var res = {};
         res.Subject = Subject;
         res.Topic = topic;
         res.EmailCount = 0;
         res.EmailUIDs = [];
         res.EmailTargetCount = 0;
         res.EmailTargets = [];
         //stop searching for emails under some exceptions.
        //We can return the Email Count as -1 if we want to stop any other inbound actions from triggering
        var stopSearch = false;
        //the topic is empty
        if(topic.nil()){
             stopSearch = true;
             res.EmailCount = -1;
        }
        //the topic only contains one word or less
        var topicWordCount = topic.trim().split(' ').length;
        if(topicWordCount < 2){
             stopSearch = true;
             res.EmailCount = -1;
        }
        //the topic contains an excluded wording/phrasing
        //see the function "excludeException_TopicWording()" to add excluded wordings
        var excludedTopicWording = this.excludeException_TopicWording(topic);
        if(excludedTopicWording){
             stopSearch = true;
             res.EmailCount = -1;
        }
        //stop search by returning the 'empty' result object
        if(stopSearch)
             return res;
         //search for processed sys_emails that contain the subject
         //search specifically for sys_emails where the target is not empty
         var grMAIL = new GlideRecord('sys_email');
         grMAIL.addQuery('mailbox','received');
         grMAIL.addQuery('state','processed');
         grMAIL.addQuery('subject','CONTAINS',topic);
         grMAIL.addNotNullQuery('instance');
         grMAIL.addQuery('sys_created_on','>',gs.daysAgoStart(DaysAgo));
         grMAIL.query();
         while(grMAIL.next()){
                   //get the result details of the current loop-cycle into temp variables and force them to string
                   var intEmailCount = res.EmailCount; var strEmailCount = intEmailCount.toString();
                   var arrEmailUIDs = res.EmailUIDs; var strEmailUIDs = arrEmailUIDs.toString();
                   var unkGrUID = grMAIL.uid; var strGrUID = unkGrUID.toString();
                   var unkGrTargetID = grMAIL.instance; var strGrTargetID = unkGrTargetID.toString();
                   var unkGrTargetTable = grMAIL.target_table; var strGrTargetTable = unkGrTargetTable.toString();
                   //Each UID is another email that needs to be returned. Avoid duplicate UID counts and actions
                   if(res.EmailCount == 0 || strEmailUIDs.indexOf(strGrUID) == -1){
                             //Count the emails, different actions might be taken if there is more than 1
                             res.EmailCount++;
                             res.EmailUIDs.push(strGrUID);
                             //Email Targets need to be an object as we need to know sys_id as corresponding table
                             var target = {};
                             target.sys_id = strGrTargetID;
                             target.table_name = strGrTargetTable;
                             //We only care for differing targets in our list
                             var similarTarget = false;
                             for(var i in res.EmailTargets){
                                       var temp = res.EmailTargets[i].sys_id;
                                       if(temp.indexOf(target.sys_id) > -1)
                                                 similarTarget = true;
                             }
                             if(!similarTarget){
                                       res.EmailTargetCount++;
                                       res.EmailTargets.push(target);
                             }
                   }
         }
         //return the result object
         return res;
}
function excludeException_TopicWording(Topic){
   var result = false;
   var arrExclusions = [];
   /*ADD EXCLUDED WORDING HERE*/
   //Copy and paste: arrExclusions.push("")
   arrExclusions.push("Urgent");
   arrExclusions.push("Top Urgent");
   arrExclusions.push("High Priority");
   arrExclusions.push("Important");
   arrExclusions.push("Very Important");
   arrExclusions.push("Access Request");
   arrExclusions.push("Access Rights");
   //cycle through the excluded wordings
   for(var x in arrExclusions){
       var lowerExclude = arrExclusions[x].trim().toLowerCase();
       var lowerTopic = Topic.trim().toLowerCase();
       if(lowerExclude.indexOf(lowerTopic) > -1){
           //if an exclusion is found, return true
           result = true;
       }
   }
   return result;
}
function getRecordAndTakeAction(SysId,Table){
         //return 'true' if any record is updated
         var result = false;
         //get the record
         var gr = new GlideRecord(Table);
         if(gr.get(SysId)){
                   //each table has it's own fields, requirements and actions
                   //only process the ones handle by emails
                   if(Table == 'incident' || Table == 'change_request' || Table == 'problem'){
                             //only update an active task
                             if(gr.active == true){
                                       //update the record
                                       gr = this._updateTaskRecord(gr);
                                       gr.update();
                                       result = true;
                                       //copy possible attachments from the sys_email to the record
                                       this.copyEmailAttachmentsToRecord(sys_email.sys_id,Table,SysId);
                             }
                   }
         }
         return result;
}
function _updateTaskRecord(gr){
         //add comments
         var replyText = this.removeRepliedToMessage(email.body_text);
         gr.comments = "reply from: " + email.origemail + "\n\n" + replyText.replace(/\n\n/g,'\n');
         //add the sender of the email to the Watch List
         var unkWatchList = gr.watch_list; var strWatchList = unkWatchList.toString();
         if(strWatchList.indexOf(email.from_sys_id) == -1){
                   var newWL = email.from_sys_id;
                   if(!strWatchList.nil())
                             newWL += "," + strWatchList;
                   gr.watch_list = newWL;
         }
         //return the GlideRecord containing the updates
         return gr;
}
/*Check if the email is a Reply by identifying the prefix of the subject of an email.
The prefix is identified by running it against the system property of reply subject prefixes*/
function checkIfReplyBySubjectPrefix(Subject){
         var sysPrefixes = gs.getProperty('glide.email.reply_subject_prefix');
         var replyPrefixes = sysPrefixes.split(',');
         var isReply = false;
         for(var x=0 ; x < replyPrefixes.length ; x++){
                   var replyPrefix = ""+replyPrefixes[x];
                   if(Subject.startsWith(replyPrefix)){
                             isReply = true;
                   }
         }
         return isReply;
}
/*Remove the reply prefix from the subject of an email*/
function removeSubjectReplyPrefix(EmailSubject){
         var prefixes = gs.getProperty('glide.email.reply_subject_prefix');
         var replyPrefixes = prefixes.split(',');
         var returnSubject = EmailSubject;
         for(var x=0 ; x < replyPrefixes.length ; x++){
                   var replyPrefix = ""+replyPrefixes[x];
                   if(EmailSubject.startsWith(replyPrefix)){
                             returnSubject = EmailSubject.replace(replyPrefix,"").trim();
                   }
         }
         return returnSubject;
}
/*Discard all the text behind the Separators. We use the reply-separators system property to run against*/
function removeRepliedToMessage(Text){
         var sysReplySeparators = gs.getProperty('glide.pop3.reply_separators');
         var replySeparators = sysReplySeparators.split(',');
         var result = Text;
         for(var x in replySeparators){
                   var separator = replySeparators[x]; separator = separator.replace(/\\n\\n/g,"\n\n");
                   if(Text.indexOf(separator) > -1)
                             result = Text.substring(0,Text.indexOf(separator));
         }
         return result;
}
/*Copy all attachments from a sys_email record to another record on another table*/
function copyEmailAttachmentsToRecord(SysEmailSysId,TargetTable,TargetSysId){
         var result = [];
         var gr = new GlideRecord('sys_attachment');
         gr.addQuery('table_name','sys_email');
         gr.addQuery('table_sys_id',SysEmailSysId);
         gr.query();
         while(gr.next()){
                   var grr = new GlideRecord('sys_attachment');
                   grr.table_name = TargetTable;
                   grr.table_sys_id = TargetSysId;
                   grr.file_name = gr.file_name;
                   grr.compressed = gr.compressed;
                   grr.content_type = gr.content_type;
                   grr.encryption_context = gr.encryption_context;
                   grr.size_bytes = gr.size_bytes;
                   grr.size_compressed = gr.size_compressed;
                   grr.sys_domain = gr.sys_domain;
                   var attSysId = grr.insert();
                   result.push(attSysId);
         }
         return result;
}

conmic · ‎03-04-2016

I updated the script to be able to handle attachments.

It will now copy possible attachments on the sys_email to all the records it updates.

daniellethomson · ‎03-09-2016

Hi Michael,

This is great stuff, although I was unable to get the script to work in our environment. Do you have any tips/missing piece suggestions that I may be missing to get this working?

Thank you!

Danielle

conmic · ‎03-09-2016

Hello Danielle,

I am currently working on an updated version, because I discovered an issue with certain subjects. For exmaple if it only consists of one word, it will update all the open records that contain such word.

The script that you find here should be working, however in my environment I use another more extended version to meet our requirements. So it might be that when stripping those parts, I removed also something important. you might want to add debug logging. I will try to add such in the updated version that I'll publish soon.

conmic · ‎03-09-2016

I have added another update as I ran into an issue if a subject only consists of one word, it will update all the open records that contain such word. I have added an "ignore email" rule if the subject only contains 1 word. I added this to the function "getThreadBySubject()".

Also I added a new function that is also called by the one above, called "excludeException_TopicWording(Topic)". It allows you to exclude subjects with a certain phrasings, example "Very Urgent".

To make this possible, the starting logic changes:

//has this 'new' email a reply prefix? If yes, it's a reply

var isReply = this.checkIfReplyBySubjectPrefix(email.subject);

if(isReply){

       //search for the subject in sys_email and gather the details of past sys_emails (UIDs, target records, etc...)

       var thread = this.getThreadBySubject(email.subject,60);

       if(!thread.Subject.nil()){

           var stopProcessing = false;

                   //if the EmailCount is 0 then don't take any action. Another inbound action may be triggered.

                   if(thread.EmailCount > 0){

                           //handle multiple target records

                           for(var x = 0 ; x < thread.EmailTargetCount ; x++){

                                       //write the target sys_id and table of the current loop-cycle into variables

                                       var sysId = thread.EmailTargets[x].sys_id;

                                       var table = thread.EmailTargets[x].table_name;

                                       //get the target record of the current loop-cycle and execute the according actions

                                       //the function returns 'true' if any record was updated

                                       var didUpdate = this.getRecordAndTakeAction(sysId,table);

                                       //once at least one target record has been updated, we want to stop any other inbound actions from triggering

                                       //if no target record has been updated, another inbound action may be triggered

                                       if(didUpdate)

                                               stopProcessing = true;

                           }

                   }

                   //if the Email Count is -1, we also stop processing and thus stop triggering any other inbound actions

                   else if(thread.EmailCount == -1){

                         stopProcessing = true;

                   }

                   //stop any other inbound actions from triggering

                   if(stopProcessing)

                         event.state="stop_processing";

       }

}