Segregate reports based on Quarter of month

[AssassinArno]

Hello All,

We are running VA Scans for all our Servers in 5 different data centres every Quarter and need to create flow with below requirement : 

1. Initial Scan will run on First month of every quarter (ex. January, April, July, October)

2. Vulnerabilities rescans will run on third month of every quarter (ex. March, June, September, December)

My flow requirement is : 

All initial scan report (ran in first & second month of ever quarter) should go in "Initial Folder" and all fixed scan report (ran in every third month of quarter) should go in "Fixed Scan" folders for all 5 locations. Example -

Q2APM/location1/InitialScan/reports.html

Q2APM/location1/FixedScan/reports.html

Q2APM/location2/InitialScan/reports.html

Q2APM/location2/FixedScan/reports.html

Q3JAS/location1/InitialScan/reports.html

Q3JAS/location1/FixedScan/reports.html

Q3JAS/location2/InitialScan/reports.html

Q3JAS/location2/FixedScan/reports.html

Q4OND/location1/InitialScan/reports.html

Q4OND/location1/FixedScan/reports.html

Q4OND/location2/InitialScan/reports.html

Q4OND/location2/FixedScan/reports.html

Q2APM means Quarter 2 (April, May, June)

Q3JAS means Quarter 3 (July, August, September)

Q4OND means Quarter 4 (October, November, December)

I am able to create a flow to send reports based on location example - location reports should go to location one folder in required Quarter folder (Q2APM/location1/FixedScan/reports.html). 

I need some assistance on segregate the reports on month wise as unable to decide on the logic for initial segregation. Can someone help on this?  

Note : We receive all the reports via mail and need to save the same on SharePoint folder.

[AssassinArno]

Hi,

@eliotcole, @Pstork1, @Expiscornovus please have a glace and suggest.

[Pstork1]

Use a FormatDateTime() to extract the month from UTCNow() when the flow runs.

formatDateTime(UTCNow(),'MMMM')

Use a Switch based on that abbreviation to set a variable with the location where you want it to be stored.

[AssassinArno]

Thanks @Pstork1 for your response.

I was able to figure out about below after going through some Power automate help blogs  : 

formatDateTime(UTCNow(),'MMMM')

But still have some doubts on "Use a Switch based on that abbreviation to set a variable with the location where you want it to be stored". 

An sample screenshot for above statement would be very helpful.

[Pstork1 · Accepted solution]

Something like this where there will be 12 cases, one for each month name.

[AssassinArno]

Hey @Pstork1,

Thanks for your suggestions.

Although flow is currently running only for first entry. Can you suggest how to use "Apply to each" condition here? Below is a screen snap of my flow : 

Please suggest at which step i need to use "Apply to each" condition?

Below is the error message : 

[Pstork1]

The use of Last() in Compose 2 will give you the last word in the Subject line of the email.  In what way does the email contain multiple reports or entries?  If its just that there are multiple reports attached, then you want to do apply to each on attachments, not on the Subject line.  If you get a separate email for each report then you don't need an apply to each.

[AssassinArno]

Hi @Pstork1,

I am using the Last() in Compose 2 to filter out the server hostname based on location as we have datacentres at 5 locations. 

Once a VA scan completes we receive a mail with the report as a body (no attachment). I am converting mail body as .html and saving it as a report.

My only ask is where to put "Apply to each" so that it runs the same flow for all VA scans mails not just only the first scan.

Currently the html conversion is also not happening properly as some image are getting blocked which is preventing to save the proper report as html file. (I have a similar flow which is working for my backup reports so i tried same logic here).

[Pstork1]

Are there multiple VA scans in a single email?  Currently you are trying to do the Loop on the subject line, but there is only one subject line in the email.  If each report is a separate email then you don't need an apply to each at all.  If there are multiple reports in a single email then you need to process the body of the email to separate them out and run the apply to each on that array.

[AssassinArno]

Hey @Pstork1,

Each mail is having a separate VA scan as it is filtering the reports based on Server Hostname (which i am extracting via Composite2 function). The only issue is that if i am not putting any "Apply to each" function only first VA scan mail is being generated so thought to apply some kind of loop.. 

But your point seems good if all mails have separate VA reports apply to each is not required. Not sure on why it is not working then... 

[Pstork1]

The Trigger you are using will start a flow for each email.  So if each email contains one scan then each run of the flow will produce one report.  You'll have to check across multiple runs to see all the reports.

[AssassinArno]

Hey @Pstork1,

Thanks for your response. 

As suggested i have tested the scenario and i am getting the reports properly on mail but the only concern is the are not getting saved in proper .html format. 

Below are some screen shots : 

The reports should save as below in .html format : 

Output file screensnaps : 

I want this report to save as a normal report which you can see in first 2 screen shots. Unfortunately i am unable to share the sample html report.

Please suggest if there is something which i can check and resolve this issue.

Thanks.. 

[Pstork1]

I'm not following what your issue is?  Are the reports .html attachments or are they embedded in the email itself?  If they are embedded in the email you'll need to scrap and process the body of the email to extract the report and reconstruct it as HTML.

[AssassinArno]

Hey @Pstork1,

Sorry my bad.. i am just trying to automate a flow wherein i will run some VA scan for which i will receive the reports via mail which are embedded in the mail. Please refer below screenshot : 

And i want to save these reports as HTML files.

I am processing the body of the mail via a composite function. Please refer below 

Can you provide some suggestions (sample flow screenshots) on how to extract the report and reconstruct it as HTML?

I tried lot of options but nothing seems to help on this issue. The output html file is coming like below 

[Pstork1]

There are a couple things that will have to happen.

1) Since the email is in HTML format you should be able to grab most of the body and just add it to a string with the appropriate HTML head and body tags. 

2) You'll also need to find any image tags that are used and get the images they reference and embed the Base64 contents of the images in the HTML

3) Once that's all done you can save the string as an .html file.

If you have a sample report that doesn't have confidential info in it you can send that to me and I'll show you a sample flow to process it.

[AssassinArno]

Hey @Pstork1,

Yes i have a sample report which i tried sharing over here but it was not allowed. Let me know how i can share the sample report and will share that.

Thanks..

[Pstork1]

I sent you a private message with my personal email.  You can forward an email with the sample to me there.

[AssassinArno]

Hey @Pstork1,

I have forwarded the sample reports on pstork@dontpapanic.com mail ID. 

[AssassinArno]

Hey @Pstork1, 

Any luck with the reports?

[Pstork1]

Not yet.  It looks like the embedded report isnt just HTML.  It looks like its a combination of HTML and XML.  So pulling it out and embedding it in an email is going to take a good bit of work and analysis.