Forum Discussion
PaulAndrew
Microsoft
Aug 30, 2018Use Microsoft Flow to receive an email for changes to Office 365 IP Addresses and URLs
[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
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
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.
Figure 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.
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
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.
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
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.
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
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.
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
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.
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.
Step 9 – Add the comparison condition
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:
- 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
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:
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:
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.
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.
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
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
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']
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”.
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
Summary
You could extend this Flow with approvals as needed and forward the changes to your team who manages network perimeter updates.
- 1derWomanCopper Contributor
PaulAndrewSince Microsoft has made HTTP a Premium connector, unless you have a P1 or P2 Power Automate plan, this guide won't work. For those not wanting to spend $15 USD/mo/user, I ended up using a workaround that calls the endpoint version URL API and saves the output to a file in my OneDrive for Business. Based on steps I found here: https://henrik-llb.medium.com/power-automate-hack-http-request-to-rest-apis-without-premium-license-da784a5de448
Once I had the JSON file in my OneDrive, I used 2 conditions after parsing the JSON: Instance = Worldwide AND Latest > Latest from my SharePoint list. If both were true, I then just sent myself an email with the changes link so I could update our PAC file manually.
- DGrigCopper Contributor
Thank you for brilliant description
Btw. I have a question in regards of notification.
For example I created a flow for user wher he/she can read a list of different documents based in sharepoint. I want to receive notification that user A/Y read this documents. How can I integrate such form in that flow and is that possbile with standard features provided by office365?
Or should I write some scripts which will search user activity and will send me detailed report?
I just want to receive a notification that user A successfully review document B
With best Regards
- jcorbachoCopper Contributor
Hi
is someone getting a timeout error querying the URL with the HTTP connector?
https://endpoints.office.com/version/Worldwide?ClientRequestId=b10c5ed1-bad1-445f-b386-b919946339a7
It worked fine until the last published updated.
thanks
- PaulAndrew
Microsoft
Thanks for the report. We'll investigate.
Regards, Paul
- William WarmCopper Contributor
excellent article, some web interface are different now, and it need a premium flow plan, unfortunately, we don't have one 😞
- bower_007Copper Contributor
Not sure when the contents of the JSON files changed, but what I see today does not match the images in this article. This causes the data points to not line up with what's been set in SharePoint for the flow process to work. Is there an update to this article or will it be updated to reflect the changes to flow and the JSON data? Thank you.
- PaulAndrew
Microsoft
bower_007 There's no changes to the JSON output. I'm working on an update to the Flow now and do not see any changes needed to read the JSON so you may need to keep debugging.
Paul
- PaulAndrew
Microsoft
For anyone following this article, I've rewritten it and posted the udpated here: https://github.com/pandrew1/Office365-IPURL-Samples/tree/master/FlowNotifications
Paul
- Peter HeckCopper Contributor
Hello Paul,
we want to use this in our company in Germany to keep track of the IP changes. I've setup the flow and struggle with step 10 (HTTP2) - the flow hangs here. It's not reaching the next step (tested it with inserting a Mail notification between the two HTTP2 / HTTP3 steps, but the mail is not send.
If I replace the URL with just some other URL (like https://www.google.com), it continues until the end. I even copied the content of the Microsoft URL into a .json file and put it on one of our webserver and used this address - then everything works like it should.
I tested this URL ( https://endpoints.office.com/endpoints/worldwide?clientrequestid=a10c5ed1-aad1-445f-b386-b912226339a8) from HTTP2 to query the IP details before in the browser to make sure it's working (which is the case). I even changed the ClientReuest ID to something else, but without any change. When I go into the URL field in HTTP2, mark the URL and copy it to the browser, it works well!
Looks like the Flow engine has a problem to reach this URL...?
BTW - Tested it in two different tenants - same in both tenants!
Any idea what is wrong here?Cheers Peter- ThomasTBCopper Contributor
Peter Heck have the same here.
I can add something before the HTTP 2 step this is working, but then he stops.
Did you got some hints?
Regards
Thomas
- Sankarasubramanian ParameswaranIron Contributor
- K_balakumarCopper ContributorIn create Item and Delete Item, i am not getting the Latest and ID from Apply to Each, can you help me please
- Joseph HalpyBrass Contributor
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
- PaulAndrew
Microsoft
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
Regards,
Paul
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"
}- PaulAndrew
Microsoft
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
I'll try that Paul. Thanks
- Deleted
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....
- PaulAndrew
Microsoft
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