Microsoft Graph Delta Query from Microsoft Flow: Part 2

The Three Part Series

Since you made it to this page I assume that you read and followed the first article. If you didn’t, stop and go do that. Even if you aren’t using the SharePoint parts, you will need to configure your App in Azure to access the Microsoft Graph. Once you do that, come back here.

Next, this is a long, detailed post and there is even a Part 3 that makes it all work. If you get lost in the details, don’t despair and don’t panic! I captured the entire Flow in a single graphic at the end of this page so you can use it as a map if you get lost.

Part 1: The App, Consent, and the Plan

  • Get set up and prepared for creating the Flow Part 2: Authentication and Initial Request (this post)
  • Create the Flow
  • Get Authenticated
  • Get the Bearer Token
  • Make the first Request
  • Process the Request
  • Store the deltaLink Part 3: Delta Query and Deleted Objects
  • Enhance the Flow, perform a Delta request, deal with deleted objects

Create the Initial Auth Flow

  1. In Microsoft Flow choose My Flows and choose New | Create from blank.
  2. For the demo I am going to choose to manually trigger the Flow; in production you may want to choose Recurrence and set up a schedule.
  3. Add the following Steps to initialize the Flow and test it the first time:
    1. Three Initialize variable Actions: renamed Initialize variable - Tenant ID, Initialize variable - Client ID, and Initialize variable - Secret ID.
    2. An HTTP Action renamed HTTP - Get Token.
      Framing our first request
  4. Now configure each variable as follows:
    1. TenantID - String - {your Tenant ID}
    2. ClientID - String - {your App ID}
    3. SecretID - String - {your password}
      Initialize the variables
  5. Configure the HTTP - Get Token action as follows:
    1. Method: POST
    2. URI: https://login.microsoft.com/{tenant id}/oauth2/v2.0/token
    3. Headers: Content-Type | application/x-www-form-urlencoded
    4. Body: client_id={your app id}&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default &client_secret={your password}&grant_type=client_credentials
      HTTP Action
  6. Save and Test your Flow. Correct any errors that ocurred and confirm that the HTTP - Get Token action returned an access-token.
    HTTP Action access token

Grab the Schema

In several places in this exercise, you are going to use a Parse JSON action on the response body of the HTTP action. The Parse JSON action needs to know the schema of the response to perform it’s work. Here is how to save yourself a ton of time.

  1. Select and copy the response body from the HTTP - Get Token action in your test. It should look like this:
    {
    "token_type": "Bearer",
    "expires_in": 3600,
    "ext_expires_in": 3600,
    "access_token": "eyJ0eXAiOiJKV1QiLCJub25jZSI6IkFRQUJBQUFBQUFDRWZleFh4amFtUWIzT2VHUTRHdWd2YlRYZDhBQVlEWHExQjJwbEpTbWpiZVBkYldkTkRHLUVoaEYtNlZ3bFM5MXE5Q3o3Uzg1VmFqNkRmZUJJOG5BdVY3R0I0ZGkIjoiOTY4ZDAwZjctM2FmOS00NzQxLTk5ZDUtZTU1Y2JiODg5MjhlIiwicm9sZXMiOlsiR3JvdXAuUmVhZC5BbGwiLCJVc2VyLlJlYWQuQWxsIl0sInN1YiI6Ijk2OGQwMGY3LTNhZjktQYXH6AvQQlDNyMv15xkV6She9Fwbx00kCgQhDg0Vl-g"
    }
  2. Return to Edit mode for your flow and add a new step Parse JSON action. Rename it to Parse JSON - Access Token.
  3. Set the Content property to the body of the HTTP - Get Token action.
  4. Click Use sample payload to generate schema and paste the response body you copied previously and click Done.
    Paste the response body
  5. You should see the Schema result interpreted from the response body.
    Parse JSON Action
  6. Save your Flow and test it again. You should see that the Parse JSON action correctly parsed the result from the HTTP action.
    Successful test of Parse JSON

Make the First Request

Now that we are authenticated, we are going to use the access token for every subsequent request. When we get a response from the groups request it is going to return Users and either a nextLink or a deltaLink. So we need variables for these three items.

  1. Edit your Flow and under the Initialize Variable - Secret ID add three more Initialize variable actions.
  2. Name them Initialize Variable - Users, Initialize Variable - NextLink, and Initialize Variable - DeltaLink respectively.
  3. Set the Type for Users to Array.
  4. Set the Type for NextLink and DeltaLink to String.
    Initialize the variables
  5. Add an HTTP action to the bottom of the Flow. Rename it to HTTP - Get Group Members Initial Query.
  6. Configure the action as follows:
  • Method: GET
  • URI: https://graph.microsoft.com/beta/groups/delta/?$filter=id%20eq%20’{group id}’&$select=displayName,description&$expand=members (Where group ID is the unique identifier of the group from Azure AD.)
    Azure AD Group ID
    My URI looks like this:
    https://graph.microsoft.com/beta/groups/delta/?$filter=id%20eq%20'd3dd0158-a239-4417-bd75-150a16bfdc99'&$select=displayName,description&$expand=members
  • Headers: Authorization : Bearer {access token} (Important note: There is a [space] between the word “Bearer” and the access token. Failing to include it caused me many wasted hours!)
    HTTP Action
  1. Save and test your Flow. You should see in the Body of the response the Display Name of the group you queried. Below that you should see group members.
    {
    "@odata.context": "https://graph.microsoft.com/beta/$metadata#groups(displayName,description,members)",
    "@odata.nextLink": "https://graph.microsoft.com/beta/groups/delta/?$skiptoken=rUj7-LdP8JZhP1eR4xwz8RFLB10gzqT4VuUmz2aba8sDbpfE38oeFi6if63K9w1nK9VN1n4yiakdtAI8XJ_8vQUL4rbSs8El740j...",
    "value": [
    {
    "displayName": "Spanning Protected Users",
    "description": "Users protected by Spanning Backup for Office 365",
    "id": "d3dd0158-a239-4417-bd75-150a16bfdc99",
    "members@delta": [
        {
        "@odata.type": "#microsoft.graph.user",
        "id": "13af31a2-18e9-432e-bb21-c6989c85af18"
        },
        {
        "@odata.type": "#microsoft.graph.user",
        "id": "e2e6d7ad-eb03-4466-a05c-74dee6b60b10",
        "@removed": {
            "reason": "deleted"
        }
        },
        {
        "@odata.type": "#microsoft.graph.user",
        "id": "d04f8905-55e3-4be9-af52-5c4abe131989"
        }
    ]
    }
    ]
    }

Pretty cool isn’t it! All we have to do now is process the JSON like we did before and decide what to do with the users. Notice that there are two kinds of users; two are current members of the group and one was removed by deletion. Copy the body of the response and do yourself a favor - reorder the member data so that the deleted user is first. Like this:

{
"@odata.context": "https://graph.microsoft.com/beta/$metadata#groups(displayName,description,members)",
"@odata.nextLink": "https://graph.microsoft.com/beta/groups/delta/?$skiptoken=rUj7-LdP8JZhP1eR4xwz8RFLB10gzqT4VuUmz2aba8sDbpfE38oeFi6if63K9w1nK9VN1n4yiakdtAI8XJ_8vQUL4rbSs8El740j...",
"value": [
{
"displayName": "Spanning Protected Users",
"description": "Users protected by Spanning Backup for Office 365",
"id": "d3dd0158-a239-4417-bd75-150a16bfdc99",
"members@delta": [
    {
    "@odata.type": "#microsoft.graph.user",
    "id": "e2e6d7ad-eb03-4466-a05c-74dee6b60b10",
    "@removed": {
        "reason": "deleted"
    }
    },
    {
    "@odata.type": "#microsoft.graph.user",
    "id": "13af31a2-18e9-432e-bb21-c6989c85af18"
    },
    {
    "@odata.type": "#microsoft.graph.user",
    "id": "d04f8905-55e3-4be9-af52-5c4abe131989"
    }
]
}
]
}

In the next section we’re going to add a Parse JSON action and use this result to prep the schema. The schema tools is pretty lazy, it only looks at the first object and we want to be sure that @removed is part of the schema. Otherwise you are at the mercy of the Flow expression editor; personally I’d prefer a slow walk across hot coals.

Process the Request

Since this is just a demo, and I don’t know what you want to do with your users, I am just going to write them to a SharePoint list. First we have to process the JSON. We’ll use the same technique as before.

  1. Edit your flow and add a Parse JSON action below the HTTP - Get Group Member Initial Query. Rename the action Parse JSON - Group Members.
  2. Set the Content property to the body of the HTTP - Get Group Member Initial Query.
  3. Click the Use sample payload… link and paste in your updated response. Click Done and check that the resulting Schema includes the @@removed object.
    Check the Schema
  4. Save and test your Flow. As before, you should see that the parse action properly handled the Get Groups response.

Process the Group Response

Now that we have the user info, it is up to you to decide what you want to do with each user. In our case we’ll just write them to a SharePoint list. The main thing is that you need to submit another request using the nextLink that was returned in the response until you get a deltaLink.

  1. Let’s start by storing the necessary info from our Get into our variables. Add three Set variable actions and name them:
    • Set variable - NextLink
    • Set variable - DeltaLink
    • Set variable - Users Array.
  2. Set the NextLink variable Value property to the Parse JSON - NextLink property @odata.nextLink.
  3. Set the Users Array variable Value property to the Parse JSON - NextLink property to the Expression First(body('Parse_JSON_-_Group_Members')?['value'])['members@delta']. This expression will grab the array of Users and load them into the variable so we can loop over them.
  4. We’ll use the Delta Link variable later, so for now just put my dog’s name in there so we can save the Flow.
    Three variables
  5. Save and test your flow. You should see that the variables are populated. In this case here is the Users variable.
    The Users Variable

Process the Users

OK, this is where teaching and documenting Flow gets a bit tricky. We need to loop through the Users and then request more until we don’t get a nextLink but get a deltaLink instead. In Flow that means Do-Until we have no users, Apply to Each user, If the user is not deleted, and then Do Something with the user - in our case we’ll record the user in SharePoint. (If you want to see all this visually, take a look at the Visio diagram in the first post.) Here we go:

  1. At the bottom of your Flow add a Do until step.
  2. Inside the Do until add an Apply to each step.
  3. Inside the Apply to each step add a Condition step.
  4. Inside the Yes side of the Condition add a Create Item action.
  5. Rename all of the steps as follows:
    • “Do until - Users Array is Empty”
    • “Apply to each - User in Array”
    • “Condition - User Not Removed”
    • “Create item - Group Member”
  6. Set the Do until condition to @empty(variables('Users')) in Advanced mode.
  7. In the Apply to each add the Users variable.
  8. In the Condition - User Not Removed in Advanced mode set the condition to @equals(items('Apply_to_each_-_User_in_Array')?['@removed']?['reason'], null).
  9. In the Create item - Group Member choose your SharePoint Site and FlowGroups list you created in the first post. Set the Title property to the Current item of the Apply to each - User in Array.
    Adding User looping

Whew! Almost there! One last condition, if we don’t have a nextLink then we have a deltaLink. So after the first batch of users has been processed, check the nextLink and then go get more users or exit the loop.

  1. Under (outside) the Apply to each - User in Array but inside the Do until - Users Array is Empty add a Condition action.
  2. Name the Condition Condition - NextLink is Empty
  3. Set the condition in Advanced mode to @empty(variables('NextLink'))
  4. On the Yes side of the condition add a Set variable action.
  5. Rename the Set variable to Set variable - Clear Users Array
  6. Change the Name to Users and the Value to []. This will empty the Users arrray variable.
  7. On the No side of the Condition - NextLink is Empty add an HTTP action named HTTP - Get Next Data
  8. Configure HTTP - Get Next Data as follows:
    • Method: GET
    • URI: NextLink variable
    • Headers: Authorization | Bearer {access_token}
      Looping and processing nextLink
  9. Add a Set variable action named Set variable - NextLink Loop, Set variable - DeltaLink Loop, and Set variable - Next Users
  10. Set the Values of each variable to the expression null. This will ensure that our loop only runs one time for now. We need the result so that we can parse the schema later.
    Adding User looping
  11. Save and Test your Flow.

JSON Parse Trick

Once the Flow runs, check your work. You should see that the Apply to each - User in Array ran for every user, even the deleted ones. What we need right now is the result of the HTTP - Get Next Data. Open the inspector and copy the Body to a text editor.

The deltaLink
Notice that the format is the same as our original response, it is just missing the nextLink. That’s because there is no more data, so we need to save the deltaLink for the next time we make a request.

{
  "@odata.context": "https://graph.microsoft.com/beta/$metadata#groups",
  "@odata.deltaLink": "https://graph.microsoft.com/beta/groups/delta/?$deltatoken=rUj7-LdP8JZhP1eR4xwz8RFLB10gzqT4VuUmz2aba8tcB5NxzFGmUQgI3AlFDJJc4d9ZzgtsbBAsOodPpYW4d-mq1jGwJJxgeIx7w06Sfj1lZ3DjfrZ5LK84qT-c_5ghdwmt96DHVOrgfscSeG84E0nQQ-C6jv92NVyGxc_Cs5CDcl1dp8zs5om9S4YBSCvsVmkJMSafNeKhm9lm46cYlKiOiQV64SGxX83z.TOZqP3XGc2gay36Ha9uDL84JwCVk_IbWFL5A6J0n-Ns",
  "value": []
}

Notice that there is no nextLink. Remember that we need to evaluate if there is a nextLink or a deltaLink. Also, remember that the JSON syntax parser is lazy. So let’s do a little work and help that parser. Copy the deltaLink from up above and paste it right under the @odata.context bit in the previous response. It should look like this. (Heck, you could just copy mine, it doesn’t matter.)

{
"@odata.context": "https://graph.microsoft.com/beta/$metadata#groups(displayName,description,members)",
"@odata.deltaLink": "https://graph.microsoft.com/beta/groups/delta/?$deltatoken=rUj7-LdP8JZhP1eR4xwz8RFLB10gzqT4VuUmz2aba8tcB5NxzFGmUQgI3AlFDJJc4d9ZzgtsbBAsOodPpYW4d-mq1jGwJJxgeIx7w06Sfj1lZ3DjfrZ5LK84qT-c_5ghdwmt96DHVOrgfscS",
"@odata.nextLink": "https://graph.microsoft.com/beta/groups/delta/?$skiptoken=rUj7-LdP8JZhP1eR4xwz8RFLB10gzqT4VuUmz2aba8sDbpfE38oeFi6if63K9w1nK9VN1n4yiakdtAI8XJ_8vQUL4rbSs8El740j...",
"value": [
    {
    "displayName": "Spanning Protected Users",
    "description": "Users protected by Spanning Backup for Office 365",
    "id": "d3dd0158-a239-4417-bd75-150a16bfdc99",
    "members@delta": [
        {
        "@odata.type": "#microsoft.graph.user",
        "id": "e2e6d7ad-eb03-4466-a05c-74dee6b60b10",
        "@removed": {
            "reason": "deleted"
        }
        },
        {
        "@odata.type": "#microsoft.graph.user",
        "id": "13af31a2-18e9-432e-bb21-c6989c85af18"
        },
        {
        "@odata.type": "#microsoft.graph.user",
        "id": "d04f8905-55e3-4be9-af52-5c4abe131989"
        }
    ]
    }
]
}

Now copy all of that and go back and Edit your Flow.

  1. Under HTTP - Get Next Data add a Parse JSON action. Rename it to Parse JSON - Next Data.
  2. Set the Content property to the body of the HTTP - Get Next Data action.
  3. Click Use sample payload… and paste in the edited payload from above including both the deltaLink and the nextLink.
  4. Click Done.
    Parse the JSON response
  5. Now you can configure the Variables the right way. Set the NextLink to the Parse JSON - Next Data output @odata.nextLink.
  6. Set the Set the DeltaLink to the Parse JSON - Next Data output @odata.deltaLink
  7. Set the Users array to the expression First(body('Parse_JSON_-_Next_Data')?['value'])?['members@delta']
    Parsing the data and setting the variables
  8. Save and test your Flow. You should see that user items are getting added to the FlowGroups list on your SharePoint site.
    Success, items in the list

Whew! What now? Well, now that we’ve managed to get through one full cycle, we have to store the deltaLink. Let’s just throw it into our FlowGroupConfiguration list on your SharePoint site.

  1. Edit your Flow.
  2. At the very bottom add a step of Update item. Rename it to Update item - Save Delta Link Configuration.
  3. Configure the action as follows:
    • Set the Site Address URL to your site.
    • Set the List Name to your configuration list.
    • Set the ID to 1. (If you are reading the first item, like I am.)
    • Set the Title to DeltaLink.
    • Set the Value to the variable DeltaLink.
      Saving the DeltaLink value.
  4. Save and test your Flow. You should now see the Delta Link in your Configuration list when the Flow completes.
    Delta Link saved for future use.

Where Are We?

Wow, ok, I admit, that was a lot of work and all we have to show for it is one pass through the Flow. If you got lost along the way don’t panic! I captured the entire flow in a single graphic so you can use it as a map if you get lost.

In the next post in the series we are going to augment our Flow so that it check for the Delta Link and if it doesn’t find it, it’ll run the initializtion. If it does find the Delta Link, it’ll run the Delta Query and only get the items that have changed.

Hop over to part three: Part 3: Delta Query and Deleted Objects

The Video

The Whole Flow

The whole Flow
|| Azure || Office 365 || Microsoft Flow

comments powered by Disqus

Let's Get In Touch!


Ready to start your next project with us? That’s great! Give us a call or send us an email and we will get back to you as soon as possible!

+1.512.539.0322