How to properly filter for specific enabled services via the Graph API/SDK - Blog (2024)

Time for another public service announcement: you might be doing filtering wrong! To be more precise, the filters you might be using in order to get a list of users with specific service enabled, Exchange Online Plan 2 for example, might not be giving you the correct results. So in this article, we will review some common mistakes and provide you with a filter query that will return the correct set of results.

First, let me be clear that I’m not pointing fingers at anyone. Filtering in the Graph API leaves a lot to be desired, and is not as user-friendly as it should be. Case in point: lambda operators and their limited usability with other operators. Or the need to use advanced queries. If anything, we can yet again blame Microsoft for repeatedly ignoring customer feedback and providing us with a solution not even developers are eager to use. To top it off, examples provided in the official documentation articles or by support representatives on the various communities can be just plain wrong. So let’s address some common misconception.

To achieve the goal of filtering the set of user objects based on a given “license” (the correct term is service plan), one first needs to know which properties to leverage. Generally speaking the licensing information for the user is contained within the assignedLicenses property, which is unfortunately a no go from filtering perspective. Not only we cannot filter on the disabledPlans values, but even if we were able to do so, we would have to account for gazillion of licenses that feature any of the Exchange Online service plans.

A better approach would be to leverage the assignedPlans property instead, which gives us the set of all individual service plans assigned to the user object, regardless of the service plan’s state. Herein lies the issue that causes inconsistencies with the filter results, but more on that later. For now, here is an example on how the assignedPlans property looks like:

 { "assignedDateTime": "2023-03-08T00:57:58Z", "capabilityStatus": "Enabled", "service": "exchange", "servicePlanId": "efb87545-963c-4e0d-99df-69c6916d9eb0" }

The example above features the identifier for the Exchange Online Plan 2 service plan, namely efb87545-963c-4e0d-99df-69c6916d9eb0. By looking at the value of the corresponding capabilityStatus property, we can tell whether the user in question has a valid Exchange Online “license”. Common mistake #1 is observed ad this point – often times examples use a filter that only takes into consideration the servicePlanId value, without checking the actual status. In other words, a filter based on just the servicePlanId property is wrong:

#Filter based on the presence of the servicePlanId for Exchange Online Plan 2. Does NOT check the status.Get-MgUser -Filter "assignedPlans/any(c:c/servicePlanId eq efb87545-963c-4e0d-99df-69c6916d9eb0)" -ConsistencyLevel eventual -CountVariable count

Earlier, we alluded to one of the challenges with Graph API filters, namely the limited set of properties we can filter against and the need to leverage advanced queries for many scenario. This is one such example, where the filter query must meet the requirements for “advanced”, i.e. the query needs the consistencyLevel header value of eventual and the $count operator (represented by –CountVariable in the Graph SDK for PowerShell). Which in fact is common mistake #2!

While the filter above does work, it will return a larger set of results, which includes any users that will have the Exchange Online Plan 2 service plan disabled! In order to get the proper set of results, our filter needs to also account for the value of the capabilityStatus property. At this point, common mistake #3 is observed, which revolves around the proper use of filters against arrays. The wrong method, and what you commonly see used in examples online, goes like this:

#Filter that uses both servicePlanId and capabilityStatus, but has faulty logicGet-MgUser -Filter "assignedPlans/any(c:c/servicePlanId eq efb87545-963c-4e0d-99df-69c6916d9eb0) and assignedPlans/any(c:c/capabilityStatus eq 'Enabled')" -ConsistencyLevel eventual -CountVariable count

This filter will also work and return results. If you closely inspect the output however, you will notice that it will still include users for which the Exchange Online Plan 2 service plan is not in Enabled state. The reason for this is that the logic used is incorrect. As we are effectively filtering array elements here, based on their property values, we need a filter that checks both properties on each individual array entry. In contrast, the filter above fetches all users with at least one of the assigned service plans matching the id of Exchange Online Plan 2 (this part is OK) combined with the condition that at least one of the service plans assigned to the user is in enabled state (i.e. has capabilityStatus value of ‘Enabled’). In other words, the filter does not guarantee that the desired plan is enabled, but that is present, and at least one of the other service plans is enabled.

Without further ado, here is the correct way to filter for all user objects that have the Exchange Online plan 2 service plan enabled. Note that the two conditions we are looking for, the servicePlanId and the capabilityStatus values, are both in the same “group”, thus only array elements matching both the conditions will be returned. A minor syntax difference, but still an important one! Here are the examples for both “raw” Graph API request and the Graph PowerShell for SDK:

#Needs ConsistencyLevel="eventual" header!GET https://graph.microsoft.com/v1.0/users?$filter=assignedPlans/any(c:c/servicePlanId eq efb87545-963c-4e0d-99df-69c6916d9eb0 and c/capabilityStatus eq 'Enabled')&$count=true

#The correct filter, ensuring both servicePlanId and CountVariable values match!Get-MgUser -Filter "assignedPlans/any(c:c/servicePlanId eq efb87545-963c-4e0d-99df-69c6916d9eb0 and c/capabilityStatus eq 'Enabled')" -All -ConsistencyLevel eventual -CountVariable count

To illustrate the difference between the three filter examples, let’s compare the results each of them returns:

So in my tenant, out of 21 total users with the Exchange Online Plan 2 service plan assigned, only 15 actually have it Enabled state, which is quite the difference. Results will of course vary based on the tenant size and licensing practices used.

Now, all the examples we explored thus far only look for the Exchange Online Plan 2 service plan. Of course, other plans do exist and to get the full picture, we would need to also include them. Here is a list of the corresponding servicePlanId values:

#Don't forget to add consistencyLevel header!GET https://graph.microsoft.com/v1.0/users?$filter=assignedPlans/any(c:c/servicePlanId eq efb87545-963c-4e0d-99df-69c6916d9eb0 and c/capabilityStatus eq 'Enabled') or assignedPlans/any(c:c/servicePlanId eq 9aaf7827-d63c-4b61-89c3-182f06f82e5c and c/capabilityStatus eq 'Enabled') or assignedPlans/any(c:c/servicePlanId eq 4a82b400-a79f-41a4-b4e2-e94f5787b113 and c/capabilityStatus eq 'Enabled')&$count=true

Get-MgUser -Filter "assignedPlans/any(c:c/servicePlanId eq efb87545-963c-4e0d-99df-69c6916d9eb0 and c/capabilityStatus eq 'Enabled') or assignedPlans/any(c:c/servicePlanId eq 9aaf7827-d63c-4b61-89c3-182f06f82e5c and c/capabilityStatus eq 'Enabled') or assignedPlans/any(c:c/servicePlanId eq 4a82b400-a79f-41a4-b4e2-e94f5787b113 and c/capabilityStatus eq 'Enabled')" -CountVariable count -ConsistencyLevel eventual

Those are quite lengthy examples, but unfortunately there is no way to shorten the query as the Graph API does not allow us to combine lambda operators with operators such as in, even though (some of) the documentation claims this is a supported scenario.Interestingly, it does work for a single entry… but that doesn’t really help us. If anyone can figure this out, please let me know 🙂

#in operator works for single entries onlyGet-MgUser -Filter "assignedPlans/any(c:c/servicePlanId in (efb87545-963c-4e0d-99df-69c6916d9eb0) and c/capabilityStatus eq 'Enabled')" -ConsistencyLevel eventual -CountVariable count

The last adjustment we can make is to account for other possible values of the capabilityStatus property. As noted in the documentation, it can take several values, only one of which technically corresponds to the scenario where a mailbox is not provisioned for the user. Granted, you probably want to be aware of scenarios where the license is expiring, so in most real-life scenarios filtering based on the Enabled value should be sufficient. For the sake of completeness though, we might also want to include the other scenarios.To do that, we can either list all “acceptable” values or filter out the Deleted status.

Unfortunately, we again run into a brick wall due to the limited filtering capabilities of the Graph. As mentioned above, the in operator does not look to be supported within lambda, and similarly, the ne one does not work either. At the same time we cannot use the NOT clause either, as it again fails to work within lambda’s and prefixing it in front of the entire condition will result in faulty logic (i.e. will return users for which at least one non-Exchange service plan is present). Even the “dumb” method of combining (four) individual filters (one for each value) will likely cause problems due to the length of the query. So unfortunately, this scenario is best addressed by client-side filters.

So yeah, let me know if we can further improve on the examples above to better address scenarios where we want to check the status of multiple service plans!