Add inbound replies to comments based on email subject/short description

[Marcel H_]

I have a requirement for one of our internal teams that I don't think is unique, and there have been quite a few posts I've found that make reference to what I need to accomplish for them, so hoping someone might be able to provide some additional help on this for me.

Basically we've got a mailbox (lets call it info@) that takes incoming email from various senders that the team processes. Recently we started to redirect (not forward) the mailbox to a custom email address in our Prod instance. This is working well and catches pretty much everything that comes into the mailbox (with the exception of a few domains that weren't whitelisted yet).

The problem that we're having though is:

1. All incoming mail is creating records (tickets) on a custom table
2. No notifications are sent back to the original sender from the instance (this may come later)
3. Replies back to the same email thread sometimes update the Additional Comments of the correct record, but other times it will create a new ticket for the same email thread
4. The times that duplicate tickets are created seems to be when someone that was perhaps on CC, or was forwarded the original email and then replies to the thread. I think that something flags it not as a reply, but a new email and it is processed that way
5. The replies aren't in response to a ServiceNow generated email with a watermark or even a ticket number in the subject line. I'm not sure how some replies are being matched and updating tickets while others are creating new tickets for the same email thread
6. The subject will be different for each email thread, so there is no single subject line that we can use to match, it will need to be dynamic

The end goal would be to match all incoming mail to any existing ticket in the custom table by subject/short description, and update Additional Comments (after which a notification is fired to let the assigned group know there is an update). If an existing ticket can not be found with the criteria a new record is created.

I've seen a few examples, but not sure what will really work effectively for this, and if I can get the criteria into a single inbound rule (or if I'll need one each for New, Forward and Reply)

Example from Marketing Request OOB rule:

if (current.getTableName() == "sn_sm_marketing_request") {
	var bodyText = email.body_text;
	if (!bodyText)
		bodyText = email.body_html;
	current.work_notes = "Reply from: " + email.origemail + "\n\n" + email.subject + "\n\n" + bodyText;	
	current.update();
}

From a community post:

var gr = new GlideRecord('incident');

gr.addEncodedQuery('short_description=' + email.subject + '^sys_created_on>=javascript:gs.minutesAgoStart(10)');

gr.query();

// If I find an email with this subject in the last 10 minutes, stop.

if(gr.next()){

     event.state = "stop_processing";

}

[SanjivMeher]

Do you ever have issues where the Caller/Opened by changes when someone other than the original sender replies to the email thread? The team I'm working with has reported this happening, and they just want to make sure once that's set it doesn't change.

No. We never had that issue. Opened By is automatically populated by system. So next time when we update, we only update the additional comments and no other info

Is the "\n\n[code]" in the inc.comments line used to capture html and wrap it in code tags to be displayed in a field?

Yes. Our analyst wanted all the inbound email to be in HTML. Otherwise the email is not readable in Plain Text format. Below blog should be helpful.

https://community.servicenow.com/community?id=community_blog&sys_id=df609af8dbbbdb405ed4a851ca961928

I see what looks like a variable "nrarray" that is being used to populate the watch list. How is that array being pulled so that people can be added to the watch list?

var rarray = email.recipients.toLowerCase().split(","); //email.recipients holds all the recipients both To and CC
var nrarray = '';
var instanceEmail = gs.getProperty('<Our ServiceNow Email ID>');

if (current.watch_list!='' && current.watch_list.indexOf(email.origemail)==-1)
current.watch_list = current.watch_list+','+email.origemail;
else
current.watch_list = email.origemail;

for (var i=0; i<rarray.length; i++) {
if (rarray[i] != instanceEmail && current.watch_list.indexOf(rarray[i])==-1) {//Don't add the ServiceNow email id to CC
if (nrarray)
nrarray=nrarray+','+rarray[i];
else
nrarray = rarray[i];
}
}

[Marcel H_]

Is the var nrarray defined in the same inbound action script? If so, do you mind sharing the full script with me (of course obfuscating any sensitive information).

For the var instanceEmail, is that the address that the Security Request is coming to (e.g. testing+secreq@service-now.com) or the main instance address?

Again, I really do appreciate the information you've shared, should get me going in the right direction much quicker.

[SanjivMeher · Accepted solution]

Create Inbound

//	Note: current.opened_by is already set to the first UserID that matches the From: email address

var rarray = email.recipients.toLowerCase().split(",");
var nrarray = '';
var instanceEmail = gs.getProperty('sn_si.security.email');

for (var i=0; i<rarray.length; i++) {
	if (rarray[i] != instanceEmail) {
		if (nrarray)
			nrarray=nrarray+','+rarray[i];
		else
			nrarray = rarray[i];
	}
}

if (email.recipients.toLowerCase().indexOf(gs.getProperty('sn_si.security.email'))>-1)
	{
	var email_sub = email.subject.split(': ')[1];
	
	var inc = new GlideRecord('sn_si_request');
	//inc.addActiveQuery();
	//inc.addQuery('short_description',email_sub);
	inc.addEncodedQuery('active=true^short_description='+email_sub+'^ORshort_description='+email.subject);
	inc.query();
	
	if (inc.next())
		{
		var bodyText = email.body_text;
		if (!bodyText)
			bodyText = email.body_html;
		inc.comments = "Replied by email:\n\nReceived from: " + email.origemail + "\n\n" + email.subject + "\n\n[code]" + email.body_html+'[/code]';
		if (nrarray!='')
			{
			if (inc.watch_list!='')
				inc.watch_list = inc.watch_list+','+nrarray;
			else
				inc.watch_list = nrarray;
		}
		inc.update();
			
		if (email.uid != undefined){
			//find match for an email with a matching UID
			var em = new GlideRecord('sys_email');
			em.query('uid', email.uid);
			em.orderByDesc('sys_created_on');
			em.query();
			if(em.next()) {
				em.instance = inc.sys_id;
				em.update();
			}
		}
	}
	else
		{
		var bodyText = email.body_text;
		if (!bodyText)
			bodyText = email.body_html;
		
		current.contact_type = 'email';
		current.short_description = email.subject;
		current.description = bodyText;
		current.comments = "Security Request created by email:\n\nReceived from: " + email.origemail + "\n\n" + email.subject + "\n\n[code]" + email.body_html+'[/code]';
		if (nrarray!='')
			{
			if (current.watch_list!='')
				current.watch_list = current.watch_list+','+nrarray;
			else
				current.watch_list = nrarray;
		}
		
		if (email.importance != undefined) {
			if (email.importance.toLowerCase() == "high")
				current.priority = 1;
		}
		
		var newId = current.insert();
	}
}

Update Inbound

if (email.recipients.toLowerCase().indexOf(gs.getProperty('sn_si.security.email'))>-1)
	{
	if (current.watch_list!='' && current.watch_list.indexOf(email.origemail)==-1)
		current.watch_list = current.watch_list+','+email.origemail;
	else
		current.watch_list = email.origemail;
	
	//this.clearComments(current.sys_id);
	
	if (current.getTableName() == "sn_si_request") {
		var bodyText = email.body_text;
		if (!bodyText)
			bodyText = email.body_html;
		current.comments = "Reply from: " + email.origemail + "\n\n" + email.subject + "\n\n[code]" + email.body_html+'[/code]';
		
		var rarray = email.recipients.toLowerCase().split(",");
		var nrarray =  '';
		var instanceEmail = gs.getProperty('sn_si.security.email');
		
		for (var i=0; i<rarray.length; i++) {
			if (rarray[i] != instanceEmail && current.watch_list.indexOf(rarray[i])==-1) {
				if (nrarray)
					nrarray=nrarray+','+rarray[i];
				else
					nrarray = rarray[i];
			}
		}
		
		
		
		if (nrarray!='')
			{
			if (current.watch_list!='')
				current.watch_list = current.watch_list+','+nrarray;
			else
				current.watch_list = nrarray;
		}
		current.update();
	}
	
	
}

We are not using servicenow email accounts. We have created our own mailboxes and using it.

For ex servicenow@company.com

And we have several modules. One of the modules is security. For security, emails are sent to security@company.com. Any email coming to security@company.com is redirected to servicenow@company.com.

We are storing the security@company.com in a property. Using gs.getProperty('<Our ServiceNow Email ID>') we get the security email address and exclude it from getting added to the watchlist.

[Marcel H_]

Thanks again! With your example I've been able to get nearly to where I need to be.

Do you know offhand a good way to ensure that the same user isn't added to the Watch List multiple times? Sometimes the group that is asking for this functionality has email threads that are more like conversations, and in my testing I've found each time that I've replied the other email addresses are added to the Watch List again.

Since we won't know for sure who might reply, I'm not sure if there is a good way to tell the system to exclude anything that's already in the Watch List dynamically.

[SanjivMeher]

Below code should already be taking care of it 'current.watch_list.indexOf(rarray[i])==-1'

		var rarray = email.recipients.toLowerCase().split(",");
		var nrarray =  '';
		var instanceEmail = gs.getProperty('sn_si.security.email');
		
		for (var i=0; i<rarray.length; i++) {
			if (rarray[i] != instanceEmail && current.watch_list.indexOf(rarray[i])==-1) {
				if (nrarray)
					nrarray=nrarray+','+rarray[i];
				else
					nrarray = rarray[i];
			}
		}