Graph API adds support for $count and $search query parameters

This year’s Build conference seems to be run under the “back to basics” moto, and we’ve seen some monumental improvements being announced, such as CTRL+F support for Teams (!) and similarly, a basic search query support for the Graph API. Sarcasm aside, we’re finally seeing (the beginning of) some meaningful improvements in that space, so let’s cover the new operators.

To recap the situation until now: searching and filtering capabilities in the Graph leave a lot to be desired. Surely, they’ve received some improvements over the years, and some of the most annoying issues are due to poor implementation in clients (*cough* case-sensitivity for parameter and property names *cough*), but we were still stuck with tons of limitations. Now, there’s light in the end of the tunnel, but still a long road ahead. Without further ado, meet the $search query parameter.

A query containing the $search parameter will act like a filter and in turn is combined with any filter query in a logical AND configuration. What’s new is the fact that $search query can match more than just the beginning of a string (as in the startwith parameter), but sadly only two properties currently support this: displayName and description. For all others, $search query will act exactly like $startswith one, in other words kinda useless. Let’s take a look at some examples:

$uri = 'https://graph.microsoft.com/beta/users?$count=true&$search="displayName:US"'
$Gr = Invoke-WebRequest -Headers $AuthHeader -Uri $uri
($gr.Content | ConvertFrom-Json).Value | select displayName,UserPrincipalName

displayName userPrincipalName
----------- -----------------
SfBMailUser SfBMailUser@michev.info
Vasil US    vasilUS@michev.info

In this example, we’ve used the $search query parameter to return just objects whose displayName property contains the value “US”. The results include one object where the match is a substring of the whole value and another one, where it matched a ‘word’ at the end of the property value. Both of these wouldn’t be possible until now.

Before we move to other examples, a few words about the syntax. The $search query parameter requires you to specify the property against you are performing the search, and as mentioned above the only two properties currently supported are displayName and description. You need to separate the property and value to look for via colon (:). Multiple values can be provided, in either logical AND or logical OR configuration, with parenthesis used to specify precedence. And, the funky part, you can also use a tokenized string, delimited either by space, casing, special characters or numbers. The search will then be performed against all the individual “tokens”, in a logical AND configuration. Here’s an example:

$uri = 'https://graph.microsoft.com/beta/servicePrincipals?$count=true&$search="displayName:MicrosoftTeams"'
$Gr = Invoke-WebRequest -Headers $AuthHeader -Uri $uri
($gr.Content | ConvertFrom-Json).Value | select displayName

displayName
-----------
Microsoft Teams Task Service
Microsoft Teams
Microsoft Teams Mailhook
MicrosoftTeamsCortanaSkills
Microsoft Teams RetentionHook Service
App Studio for Microsoft Teams

In the above, we are performing the query against the /servicePrincipals endpoint, to illustrate the fact that all directory object types are supported. We used the string value of “MicrosoftTeams”, which when tokenized is split to “Microsoft” and “Teams”. Matches against the individual tokens are not case-sensitive, supposedly, thus the query will search for every service principal that contains both the tokens in its display name. I’ve trimmed the output a bit, but you can still see several types of matches.

Now, according to the official documentation, matches should be case insensitive, meaning “Teams” should match “teams”, “TEams” and so on. In reality, things are a bit different, as tokenization seems to also be applied against the string we are examining for matches. Thus, if we add say the “Hook” string to our search query, we now have three different substrings against which matches will occur (as “MicrosoftTeamsHook” is tokenized to “Microsoft”, “Teams” and “Hook”). Given no case sensitivity, one would expect that the “hook” bit will match both “Mainhook” and “RetentionHook” in the above. The actual result however is different:

$uri = 'https://graph.microsoft.com/beta/servicePrincipals?$count=true&$search="displayName:MicrosoftTeams" AND "displayName:hook"'
$Gr = Invoke-WebRequest -Headers $AuthHeader -Uri $uri
($gr.Content | ConvertFrom-Json).Value | select displayName

displayName
-----------
Microsoft Teams RetentionHook Service

Turns out, only the entry containing “RetentionHook” is returned, most likely because only it matches the “hook” part (after being tokenized to “Retention” and “hook”). The “Mainhook” entry on the other hand is not tokenized and thus fails to match “hook”. Or at least I think so, but the below proves me wrong:

$uri = 'https://graph.microsoft.com/beta/users?$search="displayName:new"'
$Gr = Invoke-WebRequest -Headers $AuthHeader -Uri $uri
($gr.Content | ConvertFrom-Json).Value | select displayName

displayName
-----------
newMEU

$uri = 'https://graph.microsoft.com/beta/users'
$Gr = Invoke-WebRequest -Headers $AuthHeader -Uri $uri
($gr.Content | ConvertFrom-Json).Value | select displayName | ? {$_ -match "new"}

displayName
-----------
newMEU
sharednew
WCnew

So a simple query looking for matches for the “new” token. The first entry, “newMEU” being broken down to “new” and “MEU” matches OK. The second one we can accept as not being tokenized, so not matching. The third one though should be tokenized to “WC” and “new” and should match?! I think at this point I’m back to proclaiming filtering in Graph is crap…

Anyway, before I end on this note, I shouldn’t forget to mention that in order for any of the above examples to work, you need to add an additional header to your requests. Namely, the “ConsistencyLevel” with value set to “eventual”, as detailed in this blog article.

To end on a positive note, the $count parameter seems to do its job right and gives you a quick way to get a total number of entries returned by a given query. Granted, you can do this yourself, but it’s valuable addition for scenarios where you want to just get the object count, without actually fetching any data. For example, the below query will get me the count of all users within my tenant:

$uri = 'https://graph.microsoft.com/beta/users/$count'
$Gr = Invoke-WebRequest -Headers $AuthHeader -Uri $uri
$gr.Content

Be careful where you put the parameter though, for example the following will instead return the full set of objects, without giving the count:

$uri = 'https://graph.microsoft.com/beta/users?$count'
$Gr = Invoke-WebRequest -Headers $AuthHeader -Uri $uri
$gr.Content

and this one will give you both the count and list of objects:

$uri = 'https://graph.microsoft.com/beta/users?$count=true'
$Gr = Invoke-WebRequest -Headers $AuthHeader -Uri $uri
($gr.Content | ConvertFrom-Json).'@odata.count'

Some other improvements are also available, like being able to use both $filter and $orderby in the same query. Refer to the aforementioned blog article or the official documentation for details on those.

Posted in Azure AD, Graph API, Office 365 | 1 Comment

Reporting on Conditional Access policies in Microsoft 365

The Conditional Access endpoints have been available for a while in the Graph API, and while still in beta, they can be used to get a list of your CA policies or manage them. In this short article, we will explore how to build a report of any CA policies configured in the tenant and provide you with a proof-of-concept script.

First things first, as usual with Graph API calls, you need to obtain a token. You can use either the delegate permission or application permission model for that, and all you need is to include the Policy.Read.All scope. Once you have obtained a token, getting a list of policies is as easy as querying the /identity/conditionalAccess/policies/ endpoint (under /beta for now):

$CAs = Invoke-WebRequest -Headers $AuthHeader1 -Uri "https://graph.microsoft.com/beta/identity/conditionalAccess/policies/" 

Some additional transformation of the output might be needed to get a meaningful report, featuring the different conditions and controls. Things like the name of the policy and its status are a non-brainer, but when it comes to the full list of conditions and actions supported, the output can be a bit ugly. The format I’ve chosen adds some readability and looks something like the below:

Now, this is after all a proof-of-concept, so some obvious quality of life improvements have been skipped. The output will feature tons of GUIDs, which you can replace with the actual app name, user UPN, group name, role name and so on. This is fairly straightforward when using the Graph API, as long as you have the necessary permissions to call the corresponding endpoints. So we can make a small addition to the script, which “converts” GUIDs to more readable identifiers, but it turn requires you to run it with an application that has Directory.Read.All permissions as well. The report is much easier to read now:

In the above, I have rearranged/hidden some columns for illustrative purposes. Even with those small tweaks, some GUIDs will remain, such as those corresponding to application IDs, or named locations. You can handle them in the same manner – run a query against the corresponding Graph endpoint, and as long as you have the necessary permissions, replace the result with a more readable value. I left this as an exercise for the reader 🙂

Note that the createdDateTime and modifiedDateTime properties are currently returned blank, as a known issue. Those values will be populated for newly created or modified policies though. The output will also fail to return any “baseline” policies, which is also a known issue/expected behavior.

Anyway, you can find the sample script over at my GitHub PowerShell repo. For additional details, you can always consult the official documentation.

Posted in Azure AD, Graph API, Office 365, PowerShell | Leave a comment

Microsoft addresses some of the Power platform governance issues

While the Power platform continues to grow, both in features and popularity, it’s not all as peachy as the Microsoft marketing folks would have you believe. Many governance issues, such as the inability to block users from using say Flow (now Power Automate), or limit them in terms of which particular connectors can be leveraged have been repeatedly brought and discussed. Not to mention the ill-fated attempt to shove yet another self-service “feature” down our throats.

Now, Microsoft is finally starting to address some of the issues mentioned above, namely the lack of control we had when it comes to deciding which connectors can and should be used within the organization. Up until recently, we were only able to designate a connector as either “business data only” or “no business data allowed”, and impose some control in situations where more than one connectors was used and data crossed the “business” boundary. Might sound good in theory, but in practice didn’t address many important scenarios, such as the one detailed in this article, as those policies only affected flows with more than one connector.

With the advent of the new, unified Power Platform Admin Center, the Data policies functionality has been expanded to include another “group” for the classification of connectors, namely “blocked”. Putting a connector in the Blocked group effectively prevents users from using it in flows and Power apps, addressing the bulk of scenarios that people were worried about. And, you can select Blocked as the default group, in which case any newly added connector will need to be approved for use first. Note that this does not affect any existing connectors, so you will have to reclassify those manually.

The controls that enable this functionality are only available in the new Power Platform admin center and not in the individual Flow or PowerApps ones. So if you want to start using them, head to this direct link to access the portal: https://admin.powerplatform.microsoft.com/. Then, on the left nav pane, hit the Data policies (preview) item then use the New policy button (or edit an existing policy). This will bring up the familiar wizard interface you might have seen in other parts of Office 365, which will guide you through the process. On the first page, enter a name for the policy and then move to the Connectors page. Here, you will get a list of all the connectors available across your environments. In my case, this amounts to 343. On this page you will also find the button to Set default policy, which brings the dialog shown above.

To move a given connector to the Blocked group (or to the Business one for that matter), hit the button on top or select the corresponding entry from the three vertical dots menu. Here’s the bad news – while you can select the top column checkbox to select them all, you cannot use this method to bulk move all connectors. The reason being, Microsoft has decided to prevent you from blocking certain first-party connectors. Those include things like Bing Maps (?), the SharePoint, OneDrive and OneDrive for Business connectors, the Office 365 Outlook and Outlook.com connectors, and a dozen others. And what’s more surprising – even some third-party connectors (“Cloud PKI management”) cannot be blocked?! Such connectors will be designated with “No” in the Blockable column.

This in turn means that if you want to properly secure your environments(s), you are in for a lengthy process of selecting connectors and moving them to the blocked group. And even then, some connectors will still remain unblockable, and accessible to end users. This will also apply to any newly released connector that is classified as “unblockable”. So overall, Microsoft gets “A” for effort, “C” for execution.

Anyway,  once you have decided which connectors to block or move to another group, hit the corresponding button on top and then proceed to the next page, Scope. Here you will define to which environments the current policy applies. Adjust your selection as necessary, then proceed to the next page where you will review the configuration and if everything checks out, create the policy.

Policies go into effect almost immediately, but a deliberate decision has been made to not hide connectors you have designated as blocked from the UI. Users can still select and use those in their flows/apps, however when they hit the Save button, a tooltip will be shown on top, resembling the following:

Your flow was updated, but it is currently suspended since it uses a combination of connectors that conflict with the company data loss prevention policies or billing restrictions.

In effect, the Flow is now in Suspended state and any sort of Run functionality will be disabled. There doesn’t seem to be a single place from which you can get a list of all such flows within your organization, the only thing that comes close is the “resources” page in the old Flow admin center, which doesn’t show any information on why the flow was disabled. Hopefully improvements are coming in this area as well.

So that’s it, in a nutshell. The new Block controls are nice to have, but should be expanded to cover every available connector, or even better, should be configurable down to the individual action/trigger. Some better inventory functionality should be made available as well. Still, the new controls are a step in the right direction, and I hope Microsoft will expand on these in the future.

Posted in Office 365 | 3 Comments

Mailbox folder permission cmdlets now return a PermissionSecurityPrincipal object

Albeit a minor change, this might affect some of your scripts, so you should be aware of it. Previously, the Get-MailboxFolderPermission cmdlet returned a ReducedRecipient object for the User property, and you could fetch a huge number of properties from it. Now, this has been changed, the User property returns a PermissionSecurityPrincipal object instead. Here’s an example:

C:\> Get-MailboxFolderPermission sharednew:\calendar -User vasil

FolderName           User                 AccessRights
----------           ----                 ------------
Calendar             Vasil Michev         {Editor}

C:\> (Get-MailboxFolderPermission sharednew:\calendar -User vasil).User

UserType RecipientPrincipal                                                                                                         DisplayName
-------- ------------------                                                                                                         -----------
Internal /o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=bad62dd352f14380881fb5335891a21d-vasil Vasil Michev

C:\> (Get-MailboxFolderPermission sharednew:\calendar -User vasil).User | gm

TypeName: Deserialized.Microsoft.Exchange.Management.StoreTasks.MailboxFolderUserId

The list of properties returned by the PermissionSecurityPrincipal/RecipientPrincipal object is quite smaller compared to what we had previously (16 properties vs 400+), but it sufficient to provide you the important details about the recipient to which the permissions have been added, so that’s OK. Here’s an example on how it looks like for my own user:

Now, I mentioned above that this change might affect some of your scripts. Namely, if you were using the User property to display information about the recipient, you might notice that it is now being displayed “empty” in some cases. For example, if we look into a calendar that has been “published” or shared to external user, or we look into the folder permissions of any Office 365 Group:

The data is still there, it’s just not properly displayed when using the default output formatting. But you might run into a similar issue in your own scripts, so you need to update them accordingly. For example, some of my own scripts previously used the User.ADRecipient property, which no longer exists. Instead, you can use User.RecipientPrincipal now.

And since this change applies only to Exchange Online, the scripts might work funny against on-premises Exchange servers now. But they were never designed to run against on-premises installs anyway. You can find the updated scripts here and here.

Posted in Uncategorized | Leave a comment

Reporting on user’s last logged in date in Office 365

After a long, long wait, Microsoft is finally addressing one of the most common requests from Office 365/Microsoft 365/Azure AD admins – the ability to easily check when was the last time a given user logged in to the service. Up until now, this was only possible by crawling the Azure AD sign-in logs or the Unified audit log in the Security and Compliance Center, which was doable, but unnecessary complicated task. Now, it’s as easy as just looking at the properties of the user object:

As the name of the property (and it members) suggests, we are effectively looking at the last entry for said user in the sign-in logs, and you can easily confirm this by opening the Azure AD blade -> Sign-ins and filtering it:

So how do we go about generating a report of the last login date for all our users? The latest versions of the AzureAD/AzureADPreview modules do not expose the property, and neither does the good old MSOnline module, so we need to get it by querying the Graph API directly. In particular, the following URI will give us a list of all the users, their UPN and the signInActivity value, out of which we can extract the Last login date:

https://graph.microsoft.com/beta/users?$select=displayName,userPrincipalName,signInActivity

I’ve put together a small proof-of-concept script on this, that you can get on GitHub. As usual, few remarks are in order. The script uses the “client app” flow to obtain an access token, meaning it assumes that you already have an application registered in Azure AD, by using the application permissions model, and have granted the necessary permissions on it (User.Read.All, Directory.Read.All, Auditlogs.Read.All). The client secret for said app is stored as a secure string and passed along.

The token is obtained by making a POST request against the v2.0 /token endpoint, and there’s little to no error handling included. If you plan on using the sample in production, you might want to address this, or replace it with your own “get/renew token” routine, use the ADAL/MSAL binaries or whatever.

Once a token is successfully obtained, a single request is made to the Graph API to the endpoint mentioned above, and the result is stored in the $result variable, then transformed and displayed in the console window. The default sorting is used, meaning entries will be ordered by the UPN value. In case you have a large number of users, it’s of course best to export the result in a CSV file, which you can do by uncommenting the last line. Here’s a sample of the output:

There are some empty entries, which correspond to users that haven’t been logged in recently, if at all. Since we have some entries that are over 30 days old, this indicates Azure AD is now keeping data beyond the range available in the sign-ins blade or by querying the auditLogs/signIns endpoint directly. This is most likely just a single entry representing the last login date, but you never know.

Now, since this is obviously still in beta, things might change in the future. One thing I’d like to see addressed is the method for getting the Last login date for a single user. Currently, if you try the “direct” approach by using the /users/UPN endpoint, the signInActivity property will not be exposed. If you modify the query to specifically include a select statement for the signInActivity property, the output is glitchy and returns details for multiple users. Instead, you should use a filter as the one below:

https://graph.microsoft.com/beta/users?$filter=startswith(displayName,'vasil')&$select=displayName,signInActivity

A bit counter-intuitive, but at least it works. You can see some additional examples in the documentation.

Posted in Uncategorized | 4 Comments