Call O365 Endpoints Web Service and Return the Latest FQDNs – Part 2

Now we will look into an automated process that allows us to call the Office 365 IP Address and URL Web service and to have the new Endpoints returned in FQDN Format.

The full unformated worldwide list can be found here in JSON format & here in CSV format.

As part of the article, it explains how you can access the changes, essentially the newest updates since the month before. You can see the data here.

The URI looks like this – https://endpoints.office.com/changes/Worldwide/2018102900?singleVersion&clientRequestId=b10c5ed1-bad1-445f-b386-b919946339a7

This can be changed as and when a new version is published by Microsoft, but of course the output via the browser is in JSON format. So let's walk through how I have captured the new FQDNs and emailed them to a shared mailbox. I did this with some help from Paul Andrew as he had detailed a good deal of the process at this blog.

Let's begin …

Firstly, we will need to use a SharePoint list in order to compare old and new versions. It will look like the next image:

Go to SharePoint and click the Lists menu item, then click New, and select List to create a new list.

Call the list Endpoints

Click the Add column button, then add Latest (Single line of text), Endpoints (Multiple lines of text), and Previous (Single line of text).

The Id must now be made visible. To do this, select All Items at the top right and select Edit current view. Check the box next to the Id field and select OK.

Add a seed item to allow the Flow to make it's initial comparison.

Add the title as Worldwide and add the Latest as 0000000000. There is no need to populate the other fields. Now save this and you’re ready to create the flow that references this list.

No we will create the Flow and call it something like O365 Endpoints.

Here is a glimpse of the completed Flow.

We will now use a blank template so choose Create from Blank.

As this Flow will be automated, the trigger we will choose will be a Recurrence trigger, so search for and choose Recurrence.

Configure the schedule to run once a day and set a time when you'd like it to run. I prefer 9am.

Now add Get Items command from the SharePoint category.

You will notice I have accessed a Site page and then chosen the list that was created above.

So, for our next command we want to use HTTP so will choose New Step and search for HTTP.

Configure it as follows:

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

N.B. I have used NoIPV6. If you want IPV6 Endpoints or just want to work with the full list within Flow then be sure to remove NoIPV6& from all URIs.

Now we will Parse the JSON output by using the Parse JSON action and building it's schema from the JSON on the URI above

In the content field, you will find the body of the HTTP action so populate it or use expression

body('HTTP')

Now to build the schema by clicking Use sample payload to generate schema.

Go to https://endpoints.office.com/… listed above in your browser, then ctrl + A, ctrl + C so that you have the JSON copied as below

Now click Use sample payload to generate the schema and paste it in then press Done.

Congratulations, you have just returned a web service using Flow and done something with it's output.

You can see from the below that if you were to save an run the flow the output of Parse JSON would look like this

Now, in order to establish if anything has changed and if there may be new endpoints, we will compare the latest version with all of the SharePoint items. We will do this by using the Apply to Each action. Within the Apply to Each will add a condition, more on that shortly.

@contains(items('Apply_to_each')?['Latest'], body('Parse_JSON')?['latest'])

So, with the above Apply to Each we are going to loop through all SharePoint items and compare the latest version. It will return true if it matches the latest version and the flow will complete as a success. But if it returns false, this indicates Microsoft has released new endpoints and we will no take steps to fetch them and do more with the data.

In the No section of the condition we will now add an other HTTP 2 action as below.

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

and another

https://endpoints.office.com/changes/worldwide/@{items('Apply_to_each')['Latest']}?NoIPV6&clientrequestid=b10c5ed1-bad1-445f-b386-b919946339a7

And now we will parse the response of HTTP 3 in order to get just the changes since the last update, usually 1 month ago.

The content field contains

body('HTTP_3')

Now it's time to build the schema.

Like before, we will go to the URI we are parsing, capture the JSON and click Use sample payload to generate schema.

Paste it in then click Done. The Parse JSON has built the schema for us.

Now we filter the response as in this example, we are looking to access just the new add urls, nothing more.

body('Parse_JSON_2')

@not(equals(item()?['add'], Null))

Now we will populate our HTML table

body('Filter_Array')

Then choose some columns and table data, FQDN being the information required and the others for dressing of sorts.

Now let's use Get my mail profile (V2) from Office 365

Then pre-populate and send an email.

Office 365 endpoints | version @{body('Parse_JSON')['latest']} available

<html>
   <head>
      <style> 
         table {
         border-collapse: collapse;
         width: 100%;
         text-align: center;
         }
         td, th {
         border: 1px solid #ddd;
         padding: 4px;
         }
         th {
         padding-top: 8px;
         padding-bottom: 8px;
         background-color: #7F7D7E;
         color: white;
         font-weight: normal;
         }
      </style>
   </head>
   <body>
      <span style="display:none;">
      Attention! New Endpoints. Version - @{body('Parse_JSON')['latest']}
      </span>
      <span>
      <img src="#" alt="SDS" height="100" width="114">
      <img src="#" alt="text" width="100">
      </span>
      <br/><br/>
      @{body('Create_HTML_table')}
   </body>
</html>

And to tidy things up, let's update the SharePoint list.

body('Parse_JSON')['instance']

body('Parse_JSON')['latest']

body('HTTP_2')

items('Apply_to_Each')['latest']

On to the final step.

items('Apply_to_Each')['Id']

Now if you run your Flow and there are new FQDNs to report they will come to you by the way of email. Below is the output of that email

Testing this is pretty easy. If your endpoints in the SharePoint list are on the latest version and you want a real life simulation of new endpoints being published, just change the latest version value to an older version. The key to this Flow running and calling new endpoints is the comparison to the latest version. If within SharePoint the 10 digit value for the latest version is out of date the Flow will go on to carry out the HTTP requests as described above.

So, should you need to trial this, go to the SharePoint list and change the latest version to an older version you know will produce some results. Like so …

All versions can be located by clicking this link

So there we have it. Admittedly, I would like the output to email just a little more concise and just each FQDN on its each line without the noise around it. I am working on that at present and plan to post a revision if I can pull it off.

If maybe you manage to enhance the output to email, I'd love to see that. Be sure to let myself and the community know.

Alan

Part 3 >>>

Tweet