Use Microsoft Flow to receive an email for changes to Office 365 IP Addresses and URLs

Microsoft

[Edit 4/16/2019: I've rewritten this article, please take a look here: https://github.com/pandrew1/Office365-IPURL-Samples/tree/master/FlowNotifications]

 

If you manage an enterprise network that uses Office 365 you are probably familiar with Office 365 IP Addresses and URLs you probably also know that there are updates to these about once a month.

 

This article demonstrates how you can use Microsoft Flow to alert you with an email whenever there are changes to the Office 365 IP Addresses or URLs. The flow will fetch the latest version of Office 365 network endpoints once an hour and it will compare that with the previous one that you have seen. The previous version that you have seen is stored in a SharePoint Online list on your Office 365 tenant. You will need to have an Office 365 tenant and the ability to create a SharePoint list that you have read/write access to.

 

The Flow created in this article is not supported by Microsoft and you should follow your own development review processes before relying on it in a production environment. If you make improvements to this Flow, we would like to hear from you. Let us know in the comments below.

 

Step 1 – Create a SharePoint list for the project

 

1.png

Figure 1 - An example empty SharePoint Online site

 

Figure 1 shows an example empty SharePoint Online site. Click the Lists menu item in the left navigation menu, then click “New”, and select “List” to create a new list.

 

2.pngFigure 2 - Create a new list

 

We will call the new list Endpoints so type that in the Name field.

 

3.png

Figure 3 - Create list form

 

4.png

Figure 4 - New list created for the local data

 

Add text columns by clicking the “Add column” button for Latest (Single line of text), Endpoints (Multiple lines of text), and Previous (Single line of text).

 

5.png

Figure 5 - New columns

 

You will also want to make the ID column visible. Do this select All Items at the top right and select Edit view. Check the box next to the ID field and select OK.

 

6.png

Figure 6 - Making the ID column visible in the view

 

Add a seed item for the Flow to read. Add the “title” as “Worldwide” and add the “Latest” as “0000000000”. Other fields are not required. Save this and you’re now ready to create the flow that references this list.

 

Keep a Web Browser tab open with this list. You will need the URL for it later.

 

7.pngFigure 7 - Creating a seed item for the list

Step 2 – Sign up for Microsoft Flow

Flow requires sign-up. I’ve only used free elements in Flow for this. You can read about the sign-up process and the free and paid plans at https://docs.microsoft.com/en-us/flow/sign-up-sign-in

Once you’ve signed up you can go to flow at https://flow.microsoft.com

 

Step 3 – Create a flow

At the flow home page, select My Flows from the top menu. It takes you to https://flow.microsoft.com/manage/flows. On the My Flows page you can select Create from blank to create your flow.

8.pngFigure 8 - Create from blank command

Step 4 – Add the trigger

A trigger starts your flow executing. We’re going to check the version of the Office 365 network endpoints once an hour, so our trigger is going to be a schedule.

 

9.png

Figure 9 - Search triggers command

 

Click on the search command and you should see the schedule trigger. If you don’t see it, you can type “Schedule” into the search box.

 

Configure the schedule trigger to run once an hour. Then click the New step command and choose Add an action.

 

10.png

Figure 10 - Configured schedule trigger

Step 5 – Add the first command and configure it

The first command is to get the last version of the network endpoints data that we saw from your SharePoint Online list.

 

11.png

Figure 11 - Searching for SharePoint - Get items

 

This command needs configuration. Enter the site address where the SharePoint list that you created earlier is located. You will need to authenticate to your Office 365 subscription for that. You may be able to select your SharePoint site from the drop-down list once you are authenticated. Then add the name of the list. Figure 12 shows the SharePoint list that I am using which you will not have access to.

 

12.png

Figure 12 - Configuration for SharePoint Online list

Step 6 – Add the command to check Office 365 network endpoint data version

Click the New step button under your Get items action and select Add an Action again. Type HTTP and select HTTP – HTTP from the list of action search results as shown in Figure 13.

 

13.png

Figure 13 - Search results for HTTP action

 

Configuration of this action is really simple. Select the GET method and enter the Uri for the endpoints version web method as shown in Figure 14. You can use any GUID as the ClientRequestId or copy the one in the figure. Test the Uri in a web browser before you put it into the Flow. In the web browser you should see a result that has the same format as in Figure 15. Keep this web browser window open as we will refer to it later.

 

14.png

Figure 14 - Configured HTTP action

 

15.png

Figure 15 - Example web service call in Web Browser

Step 7 – Add a command to apply schema to the JSON output

This next command is required to identify the output of the web service request as JSON by providing the data structure that is used.

 

Click Add action and enter “Parse JSON” in the search field. You should see the action in the search results.

 

16.png

Figure 16 - Parse JSON action

 

The Parse JSON action requires two fields to be entered. The Content field will be the first dynamic content field that you enter. To do this you click in the Content field and the Dynamic content window will appear. You just have to click Body in the HTTP section of this and you will see Body appear in the Content field. There’s more to these fields as we’ll see later, but this one is that easy.

 

17.png

Figure 17 - After selecting the HTTP Body content item

 

Next you will enter the schema. You can enter the schema directly, but it’s easier to have Flow generate it. Click “Use sample payload to generate schema” and you’ll see a popup. Go back to the browser window you had open in Figure 15.

 

18.png

Figure 18 - Right click on selected web service output

 

Copy the output and paste it into the Flow “Enter or paste a sample JSON payload” window. You should see the schema generated as in Figure 19.

 

19.png

Figure 19 - Configured Parse JSON action

Step 8 – Compare the current version to the SharePoint list item(s)

The SharePoint list may return multiple items so we need to figure which one we are working with. To simplify this, we will just compare the latest version with all of the SharePoint items. This is done by adding the “Apply to each” action.

 

20.png

Figure 20 - adding the apply to each action

 

The Apply to each action requires configuration and Dynamic content makes this pretty easy. Only one item shows up and it’s the right one. Click the “value, List of Items” content in the Dynamic content pane to select it for the Apply to each configuration.


21.png

Figure 21 - Configured Apply to each action

 

The “Apply to each” works like a loop. We’re going to add a condition inside of it next and that condition will get executed for every result. There will typically only be one item in the SharePoint list.

Step 9 – Add the comparison condition

Click Add a condition within the Apply to each action. You should see Figure 22

 

22.png

Figure 22 - Adding a new condition to the Apply to each

 

To configure the condition, we have to setup the three fields. Click within the left field and the Dynamic content pane will appear with lots of items. Scroll down to the section titled “Get items” and find the SharePoint item column called Latest. If this doesn’t appear then you may not have added it to the SharePoint list configuration so go back and add that.

 

23.png

Figure 23 - Choosing the Latest column from your SharePoint list

 

Next choose “contains” in the second field as the condition. And then click in the third field to bring up the Dynamic content window again. This time we will select “latest” from the Parse JSON section.

 

24.pngFigure 24 - The third field in the condition

 

You’ve now created a condition that compares the version of the endpoint data that you have in your SharePoint Online list with the version of the endpoint data that you just got from the web service. If these are the same, then the Flow can exit. This is the Green path labelled “If yes”. If these are not the same, then we have more work and we will add some more actions to the Red path labelled “If no”.

Figure 25 shows what it will look like when you’re complete. Walking through these one by one:

  • Get the latest endpoints data
  • Get the changes since last time you got an update
  • Create an email and attach the two outputs of the latest endpoints data and the changes
  • Add a new item to the SharePoint Online list to represent the new latest version
  • Delete the old latest item from the SharePoint list. You can omit this and keep all the old versions if you choose

 

25.png

Figure 25 - Completed "If no" path in the condition

Step 10 – Add the two HTTP GET actions

Click Add an action within the Red path that is labelled “If no”. Type HTTP and select the HTTP – HTTP action. Configure this action as GET, and with the Uri for getting the latest endpoints data. Again, you can test the Uri in a web browser. This is the Uri:

 

https://endpoints.office.com/endpoints/worldwide?clientrequestid=b10c5ed1-bad1-445f-b386-b919946339a...

 

Click Add an action underneath the HTTP 2 action that you just added and add another one in the same way. Configure this one as GET also. The Uri for this one is a little more complicated.

Here’s a Uri you can test in a web browser which has the latest version listed as 0000000000:

 

https://endpoints.office.com/changes/worldwide/0000000000?clientrequestid=b10c5ed1-bad1-445f-b386-b9...

 

We’re going to use that, but the previous version will come from your SharePoint Online list instead of being 0000000000.

 

Add this text to the Uri field:

 

https://endpoints.office.com/changes/worldwide/   

 

With the field still selected and the cursor at the end of that text make sure that the Dynamic content window it is showing. If it isn’t then you will need to click on “Add dynamic content”.

 

Scroll to the “Get items” section and click “see more” to show all the columns from the SharePoint Online list and select “Latest”. We are going to use the latest attribute to provide a parameter to the changes web service so that we see all of the changes since that version. This is what you want to review and apply to your network perimeter devices since the last update you took. Once you click Latest, you should see the action configured as in Figure 26.

 

26.png

Figure 26 - The parameter for the changes web service

 

To complete the Uri, we need to add the clientrequestid parameter. You can just click in the field after the Latest tag and type or paste the extra text in.

 

?clientrequestid=b10c5ed1-bad1-445f-b386-b919946339a8

 

Once you’ve done that you should have two GET actions that look like this with parameters. If you get the “Enter a valid uri” error you need to fix this. I fixed this by selecting the URL and using CTRL-X and CTRL-V. You can also test the Uri in a web browser.

 

27.png

Figure 27 - Two configured HTTP GET actions for fetching endpoint data

Step 11 – Sending an email or getting a review and approval on the updates

We’re going to add a “Send an email” action to the flow next. You could alternatively use an approval request action here and have the changes reviewed before being sent out if that’s required in your process for these updates.

 

Click “Add an action” underneath the HTTP 3 action. Search for “Send an email” and add it to the flow. This action is in the “Office 365 Outlook” connector and will likely be the first search result.

Fill in the To, Subject, and Body fields to be whatever you want. You can use fields from the Dynamic content window in these also.

 

Click advanced options so that we can add the attachments. Also, you can set a From address which either needs to be an account that you have Send as permissions on, or a Distribution List that you are the owner of.

 

Click “Add new item” to add a second attachment and configure both as shown in Figure 28. You can see that some of the text is typed into the field and some is from the Dynamic content generator similar to the HTTP 3 GET action.

 

The four Dynamic content items in order are:

  • Get items -> Latest
  • HTTP 3 -> Body
  • Parse JSON -> latest
  • HTTP 2 -> Body


28.png

Figure 28 - Configured Send an email action

 

The email will have two attachments with contextual filenames where the first contains the data from the web service listing the most recent changes and the second contains all the network endpoints data.

 

To help debug these fields you can hover over the Dynamic content fields and see the code behind them. This shows which flow activity the content came from and the field on that flow activity. This text is also shown in the JSON downloadable version of the flow.

Step 12 – Cleaning up the SharePoint Online list

In this step we will add the new latest seen version to the SharePoint list and delete the old item.

We will add an action called “Create item” which is in the SharePoint connector. Search for it by typing “Create item” in the search box. Enter your SharePoint Online site address and select your list name. Once the list is selected you should see the columns appear from your list and we will populate several of these from Dynamic content. If the column names don’t appear, check that they are included in the list on your SharePoint Online site.

  • Title: select Dynamic content Parse JSON -> Instance
  • Latest: select Dynamic content Parse JSON -> latest
  • Endpoints: select Dynamic content HTTP 2 -> Body
  • Previous: select Dynamic content Apply to each -> Latest

 

29.png

 

 

Figure 29 - Configured Create item action

 

Add another action and search for “Delete item” in the SharePoint connector. Enter the same SharePoint Online list site address and list name as before. The Id can be found in the Dynamic content window in the “Apply to each” section.

 

For some reason it was missing when I did this. You don’t want to choose the Id column from the Create item action because that would delete the item you just created. If it’s not in the Apply to each section you can add it manually by opening the Dynamic content window, and selecting the Expression tab. Next enter this as the expression text.

 

Items('Apply_to_each')['Id']

 

30.png

 Figure 30 - Configured Delete item action

Step 13 – Save and Test

You can now save and test the Flow directly on the editing window. After you click Test you will see this question. Select “I’ll perform the trigger action”.31.png

Figure 31 - Testing the flow


32.png

Figure 32 - Running the flow

 

After the flow has run, you can review the execution steps. Figure 33 shows a run where no new version was detected.

 

Figure 34 shows a Flow run where a new version was detected, and the email was sent. To pretend that a new version has been detected you can simply edit the list item on SharePoint Online and set the previous version to something old such as 2017010100. Then run the flow and it will think there’s a new version on the web service so that you can test the “If no” path.

 

33.png

Figure 33 - Execution steps for the Flow

34.png

Figure 34 - Successful Flow run where a new version was detected

Summary

You could extend this Flow with approvals as needed and forward the changes to your team who manages network perimeter updates.

39 Replies

Within Step 8 you are showing 'Add a Condition'. I don't seem to have this option within Flow, only 'Add an Action'.

 

Any suggestions?

When you 'Add an Action' and the first, default option would be to make the Action type of 'Condition'.

 

 

On the last step, I get an error asking for a valid integer if I use:

    Items('Apply_to_each')['Id']

 

There is the option to choose the ID from the dynamic content of the 'Get items' container... would this work?  the underlying value is:

     @items('Apply_to_each')?['ID']

Great work Paul.

 

I've been looking for a decent solution for this for some time. #Kudos

 

I am now going to see if I can get the JSON output filtered in some way, looking for just the "add" FQDN, so just ignoring IPv4, IPv6 and any "remove" entries from the latest (HTTP 3) Uri.

 

An output like the following by email, in an HTML table would suit my org.

 

FQDNPorts
aadcdn.msauth.netTCP 80,443
aadcdn.msftauth.netTCP 80,443
ccscdn.msauth.netTCP 80,443
ccscdn.msftauth.netTCP 80,443
roaming.officeapps.live.comTCP 80,443
 

If anyone has any advice on converting the JSON from "HTTP 3" to an HTML table (like the above) in an email I'd be grateful. 

@Karen Terenzoni it looks like Flow is evolving. I'll do an update to what we have published soon.

-- Paul

@AlanPs1 Thanks for the feedback. We use PowerShell to create the tables at http://aka.ms/o365ip from the JSON web services output. You can run PowerShell in Flow using an Azure Function. It's a little tricky to get working and there's likely other ways to create those tables too.

-- Paul

Thanks for the info Paul.

 

I'll take a look at running PowerShell as an Azure Function. I've pressed on a little with the JSON to filter out "NoIPV6" from the GET to https://endpoints.office.com/changes/worldwide. So let's see what I can get working.

 

I'll let you know how I get on and be keen to see any new evolution from you or another. 

Hi

 

Thank you for sharing this flow. However, I'm struggling with the consistency of the flow when it reaches "Apply to each". As it always hangs at this point but I have got it to run a couple of times successfully. When i have got it to run i ended up fiddling with HTTP sections and changing the ClientRequestId but even this is not always consistent.

 

Any suggestions why this is hanging?

 

Also could i "https://endpoints.office.com/endpoints/Worldwide?NoIPv6=$NoIpv6&ClientRequestIdClientRequestId=b10c5..." to remove the IPV6 in the get request?

 

Kind Regards

Thomas

 

Looks to be an issue with the email action and could be related to the ongoing incident EX165763 - Can't access email. I removed the email action and the flow has been successful each time.

The noipv6 seems to be working too.

@Thomas Beach. I don't know what your email issue is, but I noticed an error in the URL you posted. You have the ClientRequestId parameter name listed twice.

 

Regards,

Paul

This is really nice, but I have to ask: can't Microsoft just mail out updates to a subscriber list like everyone else?

I'm really trying not to be sassy, but I just took about a thousand rows from a web page and filtered it down to 192 IPv4 addresses that we might care about, and couldn't help noticing that (1) there's a lot of duplication; (2) no shortcuts come out of that, because the networks range in size from /12 to /32 and (3) there's little correlation between a product and the IP networks its servers inhabit. Might I suggest that MS take all the servers for one product and group them in three or four /18s, take all the servers for the next and do the same? That way my Office365 ACL could be a dozen lines rather than 192. And here comes someone telling me how I can write a routine in Yet Another Scripting Language to extract information that the vendor could just email to me. Thanks, but....

@Deleted thank you for the feedback. We are not able to subscribe you to an email list directly for when these change. I think you might prefer the RSS notification feed that we have. It doesn't require any setup, you can add it to Outlook and the updates will look like emails and can be forwarded to other people like emails. You can use this URL to produce an RSS feed for the Office 365 worldwide commercial. https://endpoints.office.com/version/worldwide?clientRequestId=b10c5ed1-bad1-445f-b386-b919946339a7&allVersions=true&format=RSS

 

We are working to reduce the number of IP Address ranges, but this project will take a little time.

 

Regards,

Paul

Hi Paul. Once again thank you for your excellent post.

Here is a series I have made with 8 parts that creates an Office 365 Admin Alerts Mailbox. It calls various APIs and RSS feeds, captures the required data and sends it by email.

 

Create Office 365 Alerts Shared Mailbox from APIs

 

I have credited you in section 2 (Call O365 Endpoints Web Service and Return the Latest FQDNs) as your content was very helpful to me and got me off and running.

 

Best Wishes,

Alan

I'm getting this error on Create Item

 

{
  "status": 400,
  "message": "Invalid text value.\n\nA text field contains invalid data. Please check the value and try again.\r\nclientRequestId: 26c15b2f-5fca-40b5-bb72-02d203ce97e7\r\nserviceRequestId: 2c2dab9e-60ee-7000-993a-d17c4698a71b"
}

I am having trouble getting the flow setup correctly, i think the instructions are a bit outdated.  Can anyone provide me the new RSS Feed URL so I can keep track of the IP changes?  Thank you

Hi @Joseph Halpy,

Yes, I need to take an update pass over the instructions. Here's an example RSS output for Office 365 worldwide commercial:

 

https://endpoints.office.com/version/worldwide?clientRequestId=b10c5ed1-bad1-445f-b386-b919946339a7&...

 

More detail about using the RSS option is at http://aka.ms/ipurlws

 

Regards,

Paul

Hi @Mark Joseph Eser Eser Barbadillo, to debug this you need to look at the flow to see exactly what it's trying to save into the SharePoint Online list item, and then go and try to add the same content yourself in your SharePoint Online list. The issue should become apparent once you've done that.


Regards,

Paul