Cloud Decoded

I wrote an article on the same topic a couple of weeks ago that explains how to monitor Office 365 Health Status with PowerShell using the new Graph API. I know, like me, there are a lot of other Power Automate/Flow enthusiasts who would prefer to achieve the same without writing any lines of scripts. So, here is it.

Before you get started, ensure that the account you are using with Power Automate/Flow at least has a “Flow per user” license assigned, since we’ll be using HTTP Action, which is a premium connector now. If you don’t have a Per User license of Power Automate/Flow, you can achieve the same using PowerShell as well by following this article.

Problem

I wrote another article on the same topic couple of years ago, It was based on the API “https://manage.office.com/api/v1.0/$TenantId/ServiceComms/Messages”, which has now been discontinued by Microsoft. I have put an update on that article about this, so that you can save your time by NOT attempting to try n make that work anymore.

Instead Microsoft released Office 365 Service Communications API in Microsoft Graph, which not only provides additional endpoints to work with Service Communications with ever expanding Microsoft Graph ecosystem.

I will re-use some content from my previous article, as most of the steps remain similar and we just need to update some of the API calls to make this work.

I will skip the into part about why need need it, that’s well covered in my previous article, but just to set up some context about what we are trying to achieve here.

Whenever there is any issue identified by Microsoft in any of the office 365 applications, they post the status in the Office 365 Health Center under Service Health and continue posting updates in regular intervals till it’s resolved. That’s the first place to look into if your tenant is facing any issues. Microsoft exposes the messages related to such incidents using a set Office 365 Communication Graph APIs. In this article, we are going to explore how can be use Power Automate/Flow to receive similar email alerts as we did using PowerShell in my previous article.

How to go about it

Now that we have established, that it’s just the solution which is going to be different, I encourage you to take a look at the previous article to understand the problem and context better.

Register App in Azure AD

This section is well explained in the previous article, so I will skip it here. Just follow the steps detailed under the heading “Register App in Azure AD” in my previous article and note down the Tenant ID, Client ID and Client Secret in a notepad.

Solution Outline

Lets first outline what we are trying to achieve here.

• Get the List of Incidents published in the Office 365 Health Center for specific applications since the last run of Power Automate/Flow
• Find out the latest message for each such incident as one Incident may have multiple status messages
• Construct the mail and send to Admins
• Save the last run time to enable Incremental query during next run

And what will it take to achieve the above

• A Custom List in SharePoint to store last run time of the Power Automate/Flow
• Getting an Access Token to call the Office 365 Service Communication Graph API
• A lot of JSON and string manipulations and Filtering Arrays
• Some final tricks to construct the mail output as desired

SharePoint

As mentioned in previous section, we need to maintain the last run time of the Power Automate/Flow to be able to incrementally query the communication API. For that, we’ll use a custom SharePoint list with just one date time column.

Just go ahead and create a list in a SharePoint site and add a date time type column named “LastRunTime”. Enable the column setting to show the time as well. Also, add one item in the list like a date from last week.

This helps in two ways, frist, we don’t need to put a separate logic in Power Automate/Flow to insert a record only first time and second, during tests, you can actually see the incidents published during last week.

Since we don’t want to store any Title in this case, I would just go ahead and make that non-required. This will help when we try to update the last runtime from flow. We’ll just keep on updating the LastRunTime column.

And last optional configuration, change the regional setting of your site in which this list has been created to UTC time zone. Since, SharePoint and Office 365 Heath Center both store datetime as UTC.

If you don’t do so, the solution will still work, but in the list you may see a different time, based on the regional setting of the site.

Power Automate/Flow

Now that our Azure AD App and SharePoint list are ready, we can jump into the core business.

Variables

Let’s start with the basics. Add a trigger as Recurrence, since we’ll schedule it to trigger every 30 mins and we need some variables to store values which we need later in other actions. The variable names are self-explanatory and all of these are of string type.

Next, we still need some more variables, but of different types now.

• A string type variable named “NewLine” with value as two new line characters. You just press enter button twice in the value field. You would see the height of the text field will change. I will explain later why it’s required.
• An array type variable named “MessageTexts” with no initial value. This will be required later to format our mail properly.
• And another string type variable named “FinalMailContent” which will hold our final formatted text to be sent over email.

Get and Set Last Run Time from/in SharePoint

Remember the SharePoint list we created in one of the previous steps? It’s time to get the time stored in that list and update it with the current time, so that next time, our Power Automate/Flow will fetch only the incidents happened after it’s last run.

It’s a set of three actions, which I have put in a separate scope for ease of maintenance.

• Get List Item (SharePoint)
  • Provide the Site URL in which the list has been created
  • Select the List from the drop down
  • Provide the list item ID. This is the OOB ID field value from your list. Since our list contains only one value, it will be “1” by default, unless you delete any item. You can add ID field in the view of your SP List to see the exact value.
• Set LastRunTime Variable
  • Select LastRunTime variable
  • Select the field value “LastRunTime” from under Dynamic Content of “Get Last Runtime from SP List” source
• Update List Item (SharePoint): Now that we have stored last run time value from the SP List in a variable, we can update the list with current date.
  • Provide the Site URL in which the list has been created
  • Select the List from the drop down
  • Provide the same list item ID
  • Title, we made non mandatory, so you can skip, but I provided some value
  • LastRunTime: You need to provide utcNow(). But for testing purposes I have provided an expression addDays(utcNow(),-7) which gives a date of 7 days before today, so that it returns results during testing

Get Access Token

Office 365 Communication API needs OAuth2 Authentication token. So, lets use our Tenant ID, Client ID and Client Secret noted during Azure AD App configuration.

If you have worked with SharePoint REST Services using .Net/PowerShell, you know that we need to get a bearer token first before we can call any APIs. This is received by passing the Tenant ID, Client ID and Client Secret that we registered earlier in a specific format to a specific endpoint.

We already have Client ID and Client Secret. But in cases where the client secret contains any special characters we need to URL Encode it. I just use https://www.urlencoder.org/ to get the encoded client secret or just use encodeURIComponent expression in Power Automate/Flow. If you don’t do this, you may get an error like “Invalid Client Secret” when the step is executed. So, if your generated client secret came up like BhW/rsym7yD6we8XOGB91DvtqK/NowARtJ4KH/YZ+wo= the value that you should be using as client secret in this step would be  BhW%2Frsym7yD6we8XOGB91DvtqK%2FNowARtJ4KH%2FYZ%2Bwo%3D

Now that we have all the inputs lets go ahead and fill the values in the Flow Action. Search and add an HTTP action in the Power Automate/Flow and configure it with following values

• Method: POST
• Uri: https://login.microsoftonline.com/<tenant ID>/oauth2/token?api-version=1.0
• Headers: Content-Type as Key and application/x-www-form-urlencoded as Value
• Body:  client_id=<ClientID>&scope=https://graph.microsoft.com/.default&client_secret=<Encoded Client Secret> grant_type=client_credentials&resource=https://graph.microsoft.com

To extract the Access token from the output, add another step by selecting “Data Operations – Compose” from under Actions. It will add another Action and will ask for Input.

Type “@outputs(‘Get_Bearer_Token’).body.access_token” in the input box, including the double quotes.

Here Get_Bearer_Token is the name of the previous action with spaces replaced with underscore (_) character. If you have named your previous action something else, use that name here. Also, always Type this, don’t copy-paste from here otherwise, you might get http 400, bad request error.

At this stage, we have extracted the access token which can be passed to the next action which will make Office 365 Communication Services API call.

Call Office 365 Communication Services Graph API

Add another Action after Compose and select HTTP like the previous step of Get Bearer Token. It will add another HTTP action and we need to prepare for the values to be passed to it. 

• Method: GET
• Uri: https://graph.microsoft.com/v1.0/admin/serviceAnnouncement/issues
• Headers:
  • Content-Type as Key and application/json as Value
  • Authorization as Key and Bearer<space><select output from previous step>
• Body: <Leave Empty>

Once this API call succeeds, it will give us the list of all issues.

Now, to be able to work with the output of the API call, we need to parse the output in a JSON format removing unnecessary properties. It will also generate the list of those properties and show up under the dynamic content in Power Automate/Flow to be reused easily in next steps.

To do so, just add an Action – Parse JSON. In the newly added action under Content, select Body from the Dynamic content of “Get Office 365 Incidents” and paste the following schema. I have removed many properties already from here, you can remove some more based on your requirements.

{
    "type": "object",
    "properties": {
        "statusCode": {
            "type": "integer"
        },
        "body": {
            "type": "object",
            "properties": {
                "@@odata.context": {
                    "type": "string"
                },
                "value": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "startDateTime": {
                                "type": "string"
                            },
                            "endDateTime": {
                                "type": "string"
                            },
                            "lastModifiedDateTime": {
                                "type": "string"
                            },
                            "title": {
                                "type": "string"
                            },
                            "id": {
                                "type": "string"
                            },
                            "impactDescription": {
                                "type": "string"
                            },
                            "classification": {
                                "type": "string"
                            },
                            "origin": {
                                "type": "string"
                            },
                            "status": {
                                "type": "string"
                            },
                            "service": {
                                "type": "string"
                            },
                            "feature": {
                                "type": "string"
                            },
                            "featureGroup": {
                                "type": "string"
                            },
                            "isResolved": {
                                "type": "boolean"
                            },
                            "highImpact": {},
                            "details": {
                                "type": "array"
                            },
                            "posts": {
                                "type": "array",
                                "items": {
                                    "type": "object",
                                    "properties": {
                                        "createdDateTime": {
                                            "type": "string"
                                        },
                                        "postType": {
                                            "type": "string"
                                        },
                                        "description": {
                                            "type": "object",
                                            "properties": {
                                                "contentType": {
                                                    "type": "string"
                                                },
                                                "content": {
                                                    "type": "string"
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        },
                        "required": [
                            "lastModifiedDateTime",
                            "title",
                            "posts"
                        ]
                    }
                },
                "@@odata.nextLink": {
                    "type": "string"
                }
            }
        }
    }
}

Filter the output

Now comes the important part. Remember the note about not being able to filter the results from the API using $filter keyword? So, we take this alternate approach. This is not as optimal as being able to use $filter with the API URI itself, but it works 🙂

To do so, let’s add an action “Filter Array” in our solution and provide it the input by typing in body(‘Extract_Values_from_Office_365_Incident_API’)?[‘value’] in the Expression and clicking on Update button.

This is important to add the input like this, otherwise you may receive an error like “The value must be of type array” or “The from property value in the query action inputs is of type Null”

@and(
or(
equals(item()?['service'],'SharePoint Online'),
equals(item()?['service'],'Exchange Online')
),
equals(item()?['classification'], 'incident'),
greaterOrEquals(item()?['lastModifiedDateTime'], variables('LastRuntime'))
)

Take a relook at the query format above to understand the structure. You will have to make changes here in case you want to add any other application in the filter. Valid values for applications are like Exchange Online, Microsoft 365 suite, SharePoint Online, etc) For full list, you can call this API in Graph Explorer – https://graph.microsoft.com/v1.0/admin/serviceAnnouncement/healthOverviews and look at Service field

Parse Filtered Incident

Let’s parse the filters outcome again using Parse JSON action. Select the Content as body of “Filter Recent Office 365 Incidents” from under dynamic content and add the following under schema.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "startDateTime": {
                "type": "string"
            },
            "endDateTime": {
                "type": "string"
            },
            "lastModifiedDateTime": {
                "type": "string"
            },
            "title": {
                "type": "string"
            },
            "id": {
                "type": "string"
            },
            "impactDescription": {
                "type": "string"
            },
            "classification": {
                "type": "string"
            },
            "origin": {
                "type": "string"
            },
            "status": {
                "type": "string"
            },
            "service": {
                "type": "string"
            },
            "feature": {
                "type": "string"
            },
            "featureGroup": {
                "type": "string"
            },
            "isResolved": {
                "type": "boolean"
            },
            "highImpact": {},
            "details": {
                "type": "array"
            },
            "posts": {
                "type": "array",
                "items": {
                    "type": "object",
                    "properties": {
                        "createdDateTime": {
                            "type": "string"
                        },
                        "postType": {
                            "type": "string"
                        },
                        "description": {
                            "type": "object",
                            "properties": {
                                "contentType": {
                                    "type": "string"
                                },
                                "content": {
                                    "type": "string"
                                }
                            }
                        }
                    }
                }
            }
        },
        "required": [
            "lastModifiedDateTime",
            "title",
            "posts"
        ]
    }
}

At this stage, we have filtered incidents based on our query which we can send over email. But remember, one such API call gives us all the Incidents occurred during that duration.

Loop and Find Latest Messages

So, we need to loop through each Incident and then send separate mail for each one. Also, each incident may contain multiple status messages published, so we need to find out the latest message to send over email. You guessed correctly, let’s go ahead and add a for each loop action in our solution.

• Loop through the list of incidents
• Find messages for each incident (now it’s under Posts)
• Find latest message for each incident (now it’s under posts – description )
• Extract the message (now it’s under posts – description – content)
• Send the extracted message over email

Let’s put a loop on all the filtered Incidents and put the output body of the previous step “Parse Incident Outcome”.

Next, add a Set Variable action and select FinalMailContent variable which we had initialize at the start and put a new line character (by pressing enter button on you machine) as value. I will explain later why this is required.

Now, we need to find out the latest message of the current incident. Messages appear as an array inside the Incident. To do so, we’ll first find out the length of the message (i.e. count)

Drop a Compose Action from under Data Operations and provide the input as in the screenshot above – length(items(‘Loop_through_Each_Filtered_Incident’)?[‘posts’]) by adding this under Expression and clicking OK.

This step will provide us a count of how many messages that particular incident has. Now to extract the latest message drop another Compose Action from under Data Operations and provide this as an input items(‘Loop_through_Each_Filtered_Incident’)?[‘posts’][sub(outputs(‘Get_Count_of_Messages_for_Individual_Incidents’),1)].description, again by adding this under Expression and clicking OK.

What essentially we are doing here, is finding out the item at the end of the Array.

Just add another Parse JSON action to extract the message. Provide Outputs of the step “Find Latest Message” under Content and the below as schema

{
    "type": "object",
    "properties": {
        "content": {
            "type": "string"
        }
    }
}

If everything goes as planned, the outcome of this step would be something like this

"content": "Title: Users may have been unable to access the Exchange Online service via multiple connection methods\n\nUser Impact: Users may have been unable to access the Exchange Online service via multiple connection methods.\n\nMore info: Users may have been unable to access the Exchange Online service utilizing Messaging API or Representational State Transfer (REST) connection methods.\n\nFinal status: We've determined that a transient network issue was causing impact. Our automated recovery system repaired this problem, and we confirmed that service was restored after monitoring the environment.\n\nScope of impact: Impact was specific to users located in North America.\n\nStart time: Monday, January 3, 2022, at 6:50 AM UTC\n\nEnd time: Monday, January 3, 2022, at 7:20 AM UTC\n\nThis is the final update for the event."

As you can see, this is a single message containing all required information with two new line characters (“\n\n”) at end of each section like Message Text, User Impact etc.

So, you might be inclined (as I was) to just add a Send Mail V2 action and send the message over email. However, even after multiple tries, I could not make the Send Mail V2 action to respect these new line characters and mails would arrive like this only.

So, I had to improvise. Remember the MessageText sArray variable that we declared at the start? Let’s make use of it.

What we are going to do

• Split the messages into different items in array whenever “\n\n” is encountered
• Add those items in the MessageTexts Array
• Loop through the Array and construct the whole message again, putting some html code like <br> in between.
• Send the final constructed html message over email

Let’s get started then.

Add a set variable action and select “MessageText” variable. In the value, provide the following formula split(body(‘Extract_Latest_Message’)?[‘MessageText’],variables(‘NewLine’))

Where the variables(‘NewLine’) contains the two new line characters which we declared at the beginning.

Now that we have the Message as different items in the MessageTexts array, let’s put a for each loop and iterate on it.

I also dropped in another Compose action for each of use to get the current item. And then add an “Append to string Variable” action and select “FinalMailContent” under name and put <br>output of previous step</br> under value.

This will ensure that a html line break in inserted where the new line characters were there earlier. So, this loop on one message prepares one complete message to be sent over email with proper line breaks. You can ofcourse add any other html tags to highlight any specific lines if you want.

Now, you can co-relate the action “Clear FinalMailContent Variable Old Values”. We need to clear the values from this variable when the outer loop starts for a different incident.

And now we have the final mail which can be sent over.

Just add your favorite Send mail V2 action and provide the mail IDs. Under subject, you can select WorkloadDisplayName : Title – Status (Id). To do so, you can just add the

@{items('Loop_through_Each_Filtered_Incident')?['service']} : @{items('Loop_through_Each_Filtered_Incident')['Title']} - @{items('Loop_through_Each_Filtered_Incident')?['Status']} 
 (@{items('Loop_through_Each_Filtered_Incident')?['Id']}) 

And select the FinalMailContent variable in the body. If you want you can change the Importance to “High” from under advanced options.

And finally we are done. Just schedule it to run for every 30 mins and enjoy the notifications like this.

Hope this helps.

Enjoy,
Anupam