Assistance needed for extracting results number from email and updating Excel

[Mrabuck85]

Hello everyone,

I'm seeking some guidance on a workflow I'm working on. Here are the steps I'm currently following:

Step 1: Email arrives.

Step 2: Convert HTML to text.

Step 3: Initialize a variable with the following settings:

Name: Results Number

Type: Integer

Step 4: Use the 'Compose' action with the following inputs and outputs:

Inputs:

substring(body('Parse_email')?['body'], lastIndexOf(body('Parse_email')?['body'], 'Results: ') + 9, sub(indexOf(body('Parse_email')?['body'], '\n', lastIndexOf(body('Parse_email')?['body'], 'Results: ')), lastIndexOf(body('Parse_email')?['body'], 'Results: ') + 9))

Outputs:

substring(body('Parse_email')?['body'], lastIndexOf(body('Parse_email')?['body'], 'Results: ') + 9, sub(indexOf(body('Parse_email')?['body'], '\n', lastIndexOf(body('Parse_email')?['body'], 'Results: ')), lastIndexOf(body('Parse_email')?['body'], 'Results: ') + 9))

Step 5: Add a row to a table.

I'm facing an issue where it's adding '0' to the table. Here's the email content that I'm trying to extract the 'Results' number from:

[CAUTION] Company Banner

Your scheduled search "CS Query: Files Written to USB" completed and returned results.

Report name: CS Query: Files Written to USB

Description: Using this to baseline for now. Results include. Source host, user account, file and filepath. Right now the query is filtered to avoid .jpeg, bmp, and .cab files

Data source: Event Search

Schedule: Every 1d

Status: Complete

Time window begin: Oct. 26, 2023 01:40:00 UTC

Time window end: Oct. 27, 2023 01:40:00 UTC

Results: 6630

Execution duration: 09:20

API download URI: /reports/entities/report-executions-download/v1?ids=

View Results in Event Search View

Download Report

Can someone help me review these steps and see if there's an issue with my configuration? Any assistance or suggestions would be greatly appreciated.

Thank you!

[ManishSolanki]

Hi @Mrabuck85 

If the email format is fixed and all parameters or columns are in same order, then you could try the below expression to extract the 'Results' value:

trim(first(split(last(split(outputs('Parse_email')?['body'],'Results:')),'Execution duration:')))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Mrabuck85]

Hello,

I added the second compose but still resulting in 0

[ManishSolanki]

Hi @Mrabuck85 

Could you pls share the output of Parse_email action? The output can be obtained from the previous flow run instance.

Thanks

[Mrabuck85]

From: falcon@crowdstrike.com<falcon@crowdstrike.com>

Sent: Thursday, October 26, 2023 9:50 PM

To:

Subject: 6630 results found from scheduled search: CS Query: Files Written to

USB | 01:40 - 01:40 UTC

[CAUTION]

Image removed by sender. CrowdStrike Logo [cid:~WRD0001.jpg]

YOUR SCHEDULED SEARCH "CS QUERY: FILES WRITTEN TO USB"

[https://falcon.crowdstrike.com/scheduled-search//summary]

COMPLETED AND RETURNED RESULTS.

Report name: CS Query: Files Written to USB

Description: Using this to baseline for now. Results include. Source host, user

account, file and filepath. Right now the query is filtered to avoid .jpeg, bmp,

and .cab files

Data source: Event Search

Schedule: Every 1d

Status: Complete

Time window begin: Oct. 26, 2023 01:40:00 UTC

Time window end: Oct. 27, 2023 01:40:00 UTC

Results: 6630

Execution duration: 09:20

API download URI:

/reports/entities/report-executions-download/v1?ids=

View results in Event Search

[https://falcon.crowdstrike.com/scheduled-search/search-log//results]

Download report

• [https://falcon.crowdstrike.com/api2/files/entities/file-content/v1?id=6ecf71ba-]

Copyright © 2023 CrowdStrike, Inc. All rights reserved.

[ManishSolanki]

Thanks for sharing the information.

The expression which I have shared earlier is working for me:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Mrabuck85]

I start at 

Email arrives 

Convert to html

Initialize variable

Compose

Compose 2

Add row to table

Still is not working for me 

[ManishSolanki]

Hi @Mrabuck85 

Could you pls share the screenshot(s) of both compose actions? Also paste the text of expressions used in those action. 

Thanks

[Mrabuck85]

Compose 1

substring(body('Parse_email')?['body'], lastIndexOf(body('Parse_email')?['body'], 'Results: ') + 9, sub(indexOf(body('Parse_email')?['body'], '\n', lastIndexOf(body('Parse_email')?['body'], 'Results: ')), lastIndexOf(body('Parse_email')?['body'], 'Results: ') + 9))

Compose 2

trim(first(split(last(split(outputs('Parse_email')?['body'],'Results:')),'Execution duration:')))

[ManishSolanki]

Hi @Mrabuck85 

Pls enter the expression in the expression window as highlighted in the below screenshot, it will not be evaluated if entered directly in the compose textbox:

trim(first(split(last(split(outputs('Parse_email')?['body'],'Results:')),'Execution duration:')))

I hope that this will resolve the issue.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Mrabuck85 · Accepted solution]

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The action(s) 'Parse_email' referenced by 'inputs' in action 'Compose_2' are not defined in the template.'.'.

[ManishSolanki · Accepted solution]

As there is no action with name "Parse email" but accepts input from "Html to text" action so pls replace the expression in compose 2 action with the below:

trim(first(split(last(split(outputs('Html_to_text')?['body'],'Results:')),'Execution duration:')))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Mrabuck85]

Thank-you so much!!

[AlexEncodian]

@Mrabuck85 

For speedy flow builds, a handy tool for email / other parsing is Encodian’s Utility - Extract Text between Values action. 

In this case, just extract between “Results:” and a line break.

 

Of the 150+ Encodian actions, those prefixed with “Utility” only consume 0.05 Encodian action credits.