Get the values from HTML table from an email in a Inblund Action

[Ajay_Chavan]

Hi All, 

I have seen people post queries on retrieve values from HTML table from an email in an Inbound Action. I have tried to work on and get the desired output.

Usually we try to get the Value from plane text where we get the Name/Value pair as below: 

<string>:<string>

Example: 

Category: Software 

Solution:

if (email.body.category != undefined)
{

current.category = email.body.category

}

What if you have a HTML table as below in your email , then the above method will not work as it should be. it would be real pain .

Incident Response Summary:
Priority:	P1
Sensor:	New
Protocol:	TCP
Connected:	Yes
Alert Volume:	167,194
Category:	Software
Source Country:	United States

Lets try to get the value of Category from above html table so please follow below steps:

Get the HTML body of the email 

var htmlcode=email.body_html; 

Now remove all the HTML tags from the HTML body

htmlcode = htmlcode.replace(/<style([\s\S]*?)<\/style>/gi, '');
htmlcode = htmlcode.replace(/<script([\s\S]*?)<\/script>/gi, '');
htmlcode = htmlcode.replace(/<\/div>/ig, '\n');
htmlcode = htmlcode.replace(/<\/li>/ig, '\n');
htmlcode = htmlcode.replace(/<li>/ig, '  *  ');
htmlcode = htmlcode.replace(/<\/ul>/ig, '\n');
htmlcode = htmlcode.replace(/<\/p>/ig, '\n');
htmlcode = htmlcode.replace(/<br\s*[\/]?>/gi, "\n");
htmlcode = htmlcode.replace(/<[^>]+>/ig, '');
htmlcode=htmlcode.replace('  ','');

Now remove all the new line tags from the plain text

htmlcode=htmlcode.replace(/\r?\n|\r/g,'');

Now put the result in log:

gs.log("final email String  "+htmlcode);

you will get the email text as below:

CDTSensor:aresmgtlaeProtocol:TCPConnected:YesAlert Volume:167,194Category:SoftwareSource Country:United StatesHost Lookup:PSINet, Inc., United StatesDNS Lookup:COGENT-ASource Port:4091Destination IP:38.106.6.211Destination Country:United StatesHost Lookup:PSINet, Inc., United States

 You will find something like below in this log:

Category:SoftwareSource Country 

from this we need value of category as Software.

Now get the value of category from a htmlcode string using Regular expressions:

var indesREG = new SNC.Regex('/Category:.*Source Country/si');  // this line will get the value of string between Category: and Source Country
	
var finalvalue = indesREG.match(htmlcode).toString();

Here finalvalue will have our value of Category. 

Now you can go ahead and update the value of category in an Incident Category field as below:

current.category=finalvalue;

That's all guys, Please mark it helpful , subscribe and give your valuable comments for improvement.

See you!

Regards,

Ajay

+91-9769949577

[Nikhil Pandit]

Very helpful... Keep it up..

[Sriniwas]

Hi Ajay,

Thanks for the article, it is very useful.

Just to confirm, is it extract the data between category: and source country or it includes category:

i have tried this and getting category: also in the value.

var indesREG = new SNC.Regex('/Category:.*Source Country/si'); // this line will get the value of string between Category: and Source Country

var finalvalue = indesREG.match(htmlcode).toString();

Thanks,

Sriniwas

[Nathan Okh]

I'm having trouble setting a reference field from the variables found. Can anyone help?

https://community.servicenow.com/community?id=community_question&sys_id=633dc309dba6b850457ae6be1396...

[blakemolmen]

Thanks for the post. This was very handy for getting information from an email that we could not control the formatting so we could not use the name : value system. 

I did notice that when I attempted the code above the regex match portion was returning the whole string including the "start" and "end" producing "Category:SoftwareSource Country" as the finalvalue variable. 

I found by adding a capturing group to the regex this will store that portion in an array separately from the full string.

var indesREG = new SNC.Regex('/Category:(.*)Source Country/si');  // this line will get the value of string between Category: and Source Country
	
var finalvalue = indesREG.match(htmlcode)[1].toString();

the addition of the "()" around the ".*" in the regex will capture just that section in an array in position 1 (0 is the full string)

Category:SoftwareSource Country = indesREG.match(htmlcode)[0].toString();

Software = indesREG.match(htmlcode)[1].toString();

[Sanjay34]

What steps need to follow if I need to get the Source Country in above the question ?

[Wayne Miller]

This is great, BUT what happens if my Table does not include a ":" as the colon is the name:value seperator.

For example, I'd like to extract both values from the below:

Name	Value
Email Address	Email Value

So "name" and "email address" are the variables we need to extract.

[Dean Attewell]

My table did contain : either, so I convert the table column to :  and then remove the rest of the tr/td data

	htmlcode = htmlcode.replace(/<style([\s\S]*?)<\/style>/gi, ''); //removes styling
	htmlcode = htmlcode.replace(/<script([\s\S]*?)<\/script>/gi, ''); //removes scripts
	htmlcode = htmlcode.replace(/<div>/ig, ''); //removes divs
	htmlcode = htmlcode.replace(/<\/div>/ig, ''); //removes divs
	htmlcode = htmlcode.replace(/<\/li>/ig, ''); //removes lists
	htmlcode = htmlcode.replace(/<li>/ig, '');//removes lists
	htmlcode = htmlcode.replace(/<ul>/ig, ''); //removes nordered lists
	htmlcode = htmlcode.replace(/<\/ul>/ig, '');//removes nordered lists
	htmlcode = htmlcode.replace(/<\/p>/ig, ''); //remove paragraphs
	htmlcode = htmlcode.replace(/<p>/ig, ''); //remove paragraphs
	htmlcode = htmlcode.replace(/<br\s*[\/]?>/gi, '');
	htmlcode = htmlcode.replace(/<(?!\/?(td|tr)\b)[^>]+>/ig, '');//removes all except td and tr
	htmlcode = htmlcode.replace(/<(td|tr|\/td|\/tr)[^>]*>/ig, '<$1>'); //removes format in td and tr
	htmlcode = htmlcode.replace(/<(?!td|tr|\/td|\/tr)[^>]+>/ig, '');
	htmlcode = htmlcode.replace(/\r?\n|\r/g,'');
	htmlcode = htmlcode.replace(/&amp;/g,'and');// replace ampersand with 'and'
	htmlcode = htmlcode.replace(/&nbsp;/g,' '); //replaces &nbsp
	htmlcode = htmlcode.replace(/<\/td><td>/ig,':'); //to allow pair to be created
	htmlcode = htmlcode.replace(/<\/td><\/tr>/g,'\n'); //replaces end of table with 
	htmlcode = htmlcode.replace(/<tr><td>/g,''); //replaces start of table row with nothing
	htmlcode = htmlcode.replace(/::/g,''); //replaces :: with nothing