Aug 30 2018 04:46 PM - edited Apr 16 2019 08:07 AM
Aug 30 2018 04:46 PM - edited Apr 16 2019 08:07 AM
[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.
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.
Figure 2 - Create a new list
We will call the new list Endpoints so type that in the Name field.
Figure 3 - Create list form
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).
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.
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.
Figure 7 - Creating a seed item for the list
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
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.
Figure 8 - Create from blank command
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.
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.
Figure 10 - Configured schedule trigger
The first command is to get the last version of the network endpoints data that we saw from your SharePoint Online list.
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.
Figure 12 - Configuration for SharePoint Online list
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.
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.
Figure 14 - Configured HTTP action
Figure 15 - Example web service call in Web Browser
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.
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.
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.
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.
Figure 19 - Configured Parse JSON action
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.
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.
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.
Click Add a condition within the Apply to each action. You should see Figure 22
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.
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.
Figure 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:
Figure 25 - Completed "If no" path in the condition
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:
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:
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:
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.
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.
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.
Figure 27 - Two configured HTTP GET actions for fetching endpoint data
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:
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.
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.
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.
Figure 30 - Configured Delete item action
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”.
Figure 31 - Testing the flow
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.
Figure 33 - Execution steps for the Flow
Figure 34 - Successful Flow run where a new version was detected
You could extend this Flow with approvals as needed and forward the changes to your team who manages network perimeter updates.
Nov 01 2018 03:07 AM
Within Step 8 you are showing 'Add a Condition'. I don't seem to have this option within Flow, only 'Add an Action'.
Nov 01 2018 09:05 AM
When you 'Add an Action' and the first, default option would be to make the Action type of 'Condition'.
Nov 06 2018 09:21 AM - edited Nov 06 2018 09:23 AM
On the last step, I get an error asking for a valid integer if I use:
There is the option to choose the ID from the dynamic content of the 'Get items' container... would this work? the underlying value is:
Nov 07 2018 03:26 PM
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.
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.
Nov 07 2018 03:33 PM
@Karen Terenzoni it looks like Flow is evolving. I'll do an update to what we have published soon.
Nov 07 2018 03:35 PM
Nov 07 2018 03:42 PM
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.
Nov 27 2018 07:01 AM
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?
Nov 27 2018 07:56 AM
Nov 27 2018 09:28 AM
@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.
Nov 27 2018 04:00 PM
Nov 27 2018 04:00 PM
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....
Nov 28 2018 11:00 AM
Nov 28 2018 11:00 AM
@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.
Dec 08 2018 07:07 AM
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.
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.
Dec 11 2018 08:27 PM
I'm getting this error on Create Item
"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"
Dec 12 2018 05:32 AM
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
Dec 12 2018 09:12 AM
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:
More detail about using the RSS option is at http://aka.ms/ipurlws
Dec 12 2018 09:39 AM
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.