Parsing Multiple (Varied Amount) of Lines from Email into Excel Table

[MDE94]

I have posted on many occasions around half a year ago, on this and similar forum pages, yet ultimately failed in obtaining a defined solution. I have decided to tackle this again in hope I can achieve my goal. 

To give a breakdown of my problem I will first post the threat which brought me closest to an answer: 

Solved: Parse multiple lines of email with same data struc... - Power Platform Community (microsoft....

There are many other tutorials I have viewed on Youtube, but most of them come in the form of emails that have been restricted for the owners need. for example a form in which a user fills out and the email is delivered which set values such as:

Name: XXXXX

Age: XX

Location: XXXXX

Name: XXXXX

Age: XX 

Location: XXXXX

The difficulty I have, is that I'm trying to parse values from an email in which the response cannot be manipulated (Office 365 Endpoint Notifications). One day I may receive an email containing details of one vulnerability, the following week I may contain an email which has details of 5 vulnerabilities. When I use 'HTML to text' the values are delivered like so:

VULNERABILITIES DETAILS

Vulnerability Name

CVE-2092-9000

Severity

High

CVSS

9

Exposed devices

102

Affected products

Microsoft Teams

Vulnerability Name

CVE-3000-2112

Severity

Medium

CVSS

7.9

Exposed devices

99

Affected products

Zoom Meetings

Vulnerability Name

CVE-1721-3033

Severity

Medium

CVSS

7.5

Exposed devices

2

Affected products

Windows 2000 Server  

\"Facebook\"

[\"https://images.ecomm.microsoft.com/cdn/mediahandler/azure-emails-templates/production/shared/images/..."]

There are some links etc prior to the 'VULNERABILITY DETAILS' heading I have left out, but to aid your solution, I know that I only want the values after 'Vulnerability Details' and before '\"Facebook\" where a Facebook logo is added. I need to grab each of the values below 'Vulnerability Name', 'Severity, 'CVSS', 'Exposed Devices' and finally 'Affected Products'. Each value is located one line space below each heading, and again the number of values may vary from one email to another.

So my end goal, is to have a flow which parses each value of the email and adds that value into a corresponding table within excel - I would like each group of values to be added into a row, then a new row to be added for the next group of values until finally we reach the \"Facebook\" wording which indicates there are no more values to be grabbed. I believe this would be easier if it was laid out such as:

Vulnerability Name:  CVE-1721-3033

Severity: High

CVSS: 7.5 

But again this cannot be altered. I require a flow to loop through and store each set of values into excel per row, until all the contents I require have been captured and documented. Like before, I'm not expecting anyone to come up with a solution to this, but I will offer an abundance of feedback and support to clarify and bridge confusion amongst my post. Any existing articles or topics answering this would also be appreciated if stated, but I can assure I'll have likely viewed most of them. 

To give a starting point, I can confirm the wording 'VULNERABILITY DETAILS' always starts at line 47 of each email, though the wording \"Facebook\" line ends at will vary depending on the amount of vulnerability info received prior. I also know something similar to a split/skip function could be applied to miss a blank line of text between each heading and the value I need 2 lines below - but other than that I only have a basic understanding. The Excel sheet being used has each of the headings per column, once each row is filled a new row should be added to log the next set of values, and so on.

Any support of guidance is appreciated, this is one of the things I can't let lie as I know it is possible once someone with an abundance of knowledge in this field can shed any light. Thank you in advance, feel free to message me any questions relating to this post or the email being received. 

[grantjenkins]

@MDE94 Will you always have the same number of lines per Vulnerability? I mean will it only contain the following, or will there be a variable list of properties in addition to this?

• Vulnerability Name
• Severity
• CVSS
• Exposed devices
• Affected products

[MDE94]

@grantjenkins many thanks for your response, I will be going to bed soon but will reply to any further questions first thing tomorrow. But yes, each of those attributes will always be included in every email, will never be blank or missing 1 of the 5 etc. So one thing guaranteed is that 'VULNERABILITY DETAILS' opens at line 47, meaning 'Vulnerability Name' will begin at line 49 of each email after accounting the blank space in between. The email body could be split from everything prior to 'Vulnerability Details' as it isn't required, and after \"Facebook\". 

Some form of array could be applied to each sub set of vulnerability details as like you ask, all of those 5 headings are going to be included in each email (the email is not received at all unless one set of those 5 sub-headings are included). 

[grantjenkins · Accepted solution]

@MDE94 Hopefully this will get what you're looking for.

See full flow below. I'll go into each of the actions.

Compose contains your data that you get after you've used HTML to text. I didn't have the raw data so had to start from here.

Filter array uses a fairly long expression to give us an array of items. The expressions used are below. The first expression uses slice to retrieve the data we want including the VULNERABLILITIES DETAILS. We then split on new line then remove the first line using skip. 

//From (input)
skip(split(slice(outputs('Compose'), indexOf(outputs('Compose'), 'VULNERABILITIES DETAILS'), indexOf(outputs('Compose'), '\"Facebook\"')), decodeUriComponent('%0A')), 1)

//Condition
trim(item())

This would give us the following output.

Initialize variable creates an array variable called data.

Apply to each chunks the data into groups of 10 rows which is the number of rows per item in our array. The expression used is:

chunk(body('Filter_array'), 10)

Append to array variable appends an object using the data from the current item to our data array. The expressions used are:

trim(items('Apply_to_each')[1])
trim(items('Apply_to_each')[3])
trim(items('Apply_to_each')[5])
trim(items('Apply_to_each')[7])
trim(items('Apply_to_each')[9])

The full object data is below:

{
    "Vulnerability Name": @{trim(items('Apply_to_each')[1])},
    "Severity": @{trim(items('Apply_to_each')[3])},
    "CVSS": @{trim(items('Apply_to_each')[5])},
    "Exposed devices": @{trim(items('Apply_to_each')[7])},
    "Affected products": @{trim(items('Apply_to_each')[9])}
}

Output is a Compose that just shows the actual data stored in our data array.

The output in this instance would be:

[
  {
    "Vulnerability Name": "CVE-2092-9000",
    "Severity": "High",
    "CVSS": "9",
    "Exposed devices": "102",
    "Affected products": "Microsoft Teams"
  },
  {
    "Vulnerability Name": "CVE-3000-2112",
    "Severity": "Medium",
    "CVSS": "7.9",
    "Exposed devices": "99",
    "Affected products": "Zoom Meetings"
  },
  {
    "Vulnerability Name": "CVE-1721-3033",
    "Severity": "Medium",
    "CVSS": "7.5",
    "Exposed devices": "2",
    "Affected products": "Windows 2000 Server"
  }
]

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[MDE94]

You have no idea how much your time and effort has been appreciated here, I cannot thank you enough - I received many messages offering what you have provided me here under a commercial basis only. Regardless on if you can answer this next question, I will accept as a solution as I now have enough to work on. The only thing missing which I should have been clearer on, is adding each of the chunked valuables into an excel row, per group output of data. 

I will share an image to show the Excel table's headings: 

From this, the actual 'vulnerability name', 'severity' etc can be ignored - as the headings within Excel indicate each per row. for example only the: 

CVE-1721-3033

Medium

7.5

2

Windows 2000 Server

 are needed, and a new row added for the next lot of this information. ignore the heading for 'recommended actions' in the excel table as that's something I manually do myself, the main goal was to store each of the vulnerability details in a log that can be accessed at any time for review.

Again, don't worry if you cannot provide any guidance on how storing each set if info can be logged per heading, I assume I can create a new compose where '"vulnerability name:" is split between that word and ' , ' so only the wording between those parameters are captured, in that instance it would be just 

"CVE-2092-9000"

 which would then be stored under the column 'Vulnerability Name' within Excel. 

Again please do not worry if you don't have a defined solution to get around this, you have been more than helpful and gone above and beyond what I expected anyone to achieve on this forum I thank you dearly. To say any further feedback or guidance on this final step is generous would be a major understatement - I will await before accepting as a solution in case. Many thanks again @grantjenkins  

[MDE94]

@grantjenkins following on from my previous post, I attempted to simply add each of the 

'trim(items('Apply_to_each')[9])' elements into each excel row, though this returned an error:

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Add_a_row_into_a_table' at line '1 and column '3632' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Add_a_row_into_a_table' to be referenced by 'repeatItems' or 'items' functions.'.'.

I guess if those values could be grabbed as an output on their own (individually) I may be able to log accordingly 

[grantjenkins]

Glad to help 🙂

I've restructured the original flow, so it doesn't build up an array of objects, but instead just adds them to Excel.

See full flow below. I'll go into each of the actions.

Compose, Filter array, and the Apply to each are the same as the original flow.

I've removed the variable data as we aren't storing the data in an array this time. So, within the Apply to each we iterate over each of the items and add them to Excel using Add a row into a table. The expressions used are the same as before.

//Vulnerability Name
trim(items('Apply_to_each')[1])

//Severity
trim(items('Apply_to_each')[3])

//CVSS
trim(items('Apply_to_each')[5])

//Exposed Devices
trim(items('Apply_to_each')[7])

//Affected Products
trim(items('Apply_to_each')[9])

Hopefully, that gets what you're after.

[MDE94]

@grantjenkins you sir, are a saint this is just the ticket! I've accepted your previous solution as the answer as promised. Thank you ever so much, you have no idea how long it has taken for someone to provide a definitive answer to this across three websites in total. 10/10 enjoy your weekend 🙂