Flow - Email to SP List but with text/strings of varying lengths

[Anonymous]

Dear Community, 

Please help me with this, It's all about to extract data from emails and save it in a SP List. My problem is that data have variable length and really dont know how to deal with variable length. 

Details: 

• I have a server collecting alarms from some devices, with some hardware conditions the server send emails with a defined structure, ex: "Host : 192.168.100.100 - Enc primary",  "Alarm Description : Fan right front failure", "Time :", "Date :", etc 
• Once those alarms arrives in my email I want to extract and save it in a SP List 
• I receibe alarms from different devices, each device have its own "preset alarm" with different length describing a situation 

At the moment I made a Flow following an example that I found (please see bellow), It actually works, but in this case I need to define the exact length of an alarm in order to extract the data that I want. So, if I define in my flow a length of 15 characters, my flow will work with alarms/data of that length, if any other alarm/data comes with a different length (in fact that's how it is) the flow will extract incorrect data or data from the next line (in the email) that should go in another column of my SP List.

This is my current Flow, I defined the length of "Alarm description :" (to not include in my SP list) and the length of the data that I really need to extract and save in sharepont:

This is how my server reports me alarms via email of some hardware with problems, It´s allways the same structure: 

and this is my SP List:

Thank you in advance for any help you can give me, I've been doing this for some time and I really need help

Sebastian 

[ekarim2020 · Accepted solution]

Hi @Anonymous ,

Here is one possible approach, but it depends on how your source data is formatted. My demo flow only works where

1. each line ends in a "newline" character
2. the values after the colon character are separated as "<space>:<space>". For example "Host<space>:<space>" 

This is the sample data I used:

Host : 192.168.7.219
Device : 192.168.7.219
Slot : 0
Date : 2022-07-22
Time : 16:08:51
Alarm Description : Fan right front failure 
Severity Description : Major 
Duration : 00:00:01

I use those spaces to separate label text from the data without which the flow won't work.

 

The expressions used are:

1. split(variables('varBodyText'),variables('varNL'))
2. trim(first(split(item(),' : ')))
3. trim(last(split(item(),' : ')))

Expression (1) will produce an array of values, separated at the newline character:

[
    "Host : 192.168.7.219",
    "Device : 192.168.7.219",
    "Slot : 0",
    "Date : 2022-07-22",
    "Time : 16:08:51",
    "Alarm Description : Fan right front failure ",
    "Severity Description : Major ",
    "Duration : 00:00:01"
]

Each item is then split into its label (expression 2) (e.g. Host, Device, Slot etc) and data value (expression 3). The Select action produces the following data set:

 

The Compoise action then transforms the data set into a JSON object that you can use later in the SharePoint Create Item action. The compose action uses some string manipulation:

json(slice(replace(string(body('Select')),'},{',','),1,-1))

 

To see how this works, see: Solved: Re: Format into proper JSON structure - Power Platform Community (microsoft.com)

Then finally you can reference each property using expressions:

These are the expressions used in the Compose action above:

outputs('Compose_Data')?['Host']
outputs('Compose_Data')?['Device']
outputs('Compose_Data')?['Slot']
outputs('Compose_Data')?['Date']
outputs('Compose_Data')?['Time']
outputs('Compose_Data')?['Alarm Description']
outputs('Compose_Data')?['Severity Description']
outputs('Compose_Data')?['Duration']

This is the output of the compose action:

 

The Flow will only work if the data is formatted as in the sample data above, but hopefully there is enough here to help you create a working flow.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[Anonymous]

Hi @ekarim2020 

Sorry my delayed answer, I was very busy last week. 

Your answer was espectacular, my flow it's now working man, really thanks for this!!!!

If you allow me, I have two additional questions: 

1. I realise that my server reports me data in this way "Host<space>:<space><space>", but is working anyways. Should I change this in split and trim functions? 
2. Sometimes, when multiples events happens at the same time, my server reports me all the data in a single email. When this happens the flow doesn´t work. Could you guide me on this point to do some tests? 

This is how an email with multiple alarms arrives, between each group of data (host - notes) there are two blanc lines:

Thanks again!!! 

Sebastian