Powershell - Useful Azure AD queries using the AzureAD Module

Article
06/30/2017

It’s been a while since I have posted and wanted to share some queries I’m using for Azure AD to collect information. As an Active Directory Admin, I have spent a lot of time with the active directory PowerShell module and I’ve been finding the Microsoft Online and AzureAD PowerShell module’s to be at times frustrating in comparison. One of the more frustrating things that I have found with the new AzureAD module is the syntax around filtering and the lack of examples. Hopefully I can clear things up a little or at least give a few useful examples.

Here is why this can be frustrating.

At the time of writing this the azuread module version is 2.0.0.115

Normal PowerShell operators and syntax are different because you need to be familiar with using filters Expressions in OData. However it does not appear that all expressions are available.

“userprincipalname –eq <‘bob@contoso.com’>”

-vs-

“userprincipalname eq ‘bob@contoso.com’”

This doesn’t seem to be a big change but something that seems to be lacking is the ability to use a wildcard on any attribute.

“userprincipalname –like <“*@contoso.com>”

I would expect this to work instead but it does not:

“substringof(userprincipalname, ‘contoso.com’)”

Here is a couple of tries for queries that don’t work.

The only one I was able to get work is the following with various attributes and not sure if it provides much value though I would really like to have the substringof capability.

 get-azureaduser -Filter "startswith(displayName,'Chad')"

The cmdlets seem to only accept a few of the expressions outlined in the ODATA article, the only ones that do are the ones kind of highlighted here.

Here is some samples of queries I have put together:

Get a list of disabled users.

 $_default_log = $env:userprofile + '\Documents\azuread_disabled_accounts.csv'
 get-azureaduser -all $true -filter 'accountEnabled eq false' | select DisplayName,`
     UserPrincipalName,Mail,Department,UserType,CreationType,RefreshTokensValidFromDateTime,AccountEnabled,`
     @{name='Licensed';expression={if($_.AssignedLicenses){$TRUE}else{$False}}},`
     @{name='Plan';expression={if($_.AssignedPlans){$TRUE}else{$False}}},ObjectId | export-csv $_default_log -NoTypeInformation

Get a list of Guest Users

 $_default_log = $env:userprofile + '\Documents\azuread_guest_accounts.csv'
 Get-AzureADUser -Filter "userType eq 'Guest'" -All $true | select DisplayName,`
     UserPrincipalName,Mail,Department,UserType,CreationType,RefreshTokensValidFromDateTime,AccountEnabled,`
     @{name='Licensed';expression={if($_.AssignedLicenses){$TRUE}else{$False}}},`
     @{name='Plan';expression={if($_.AssignedPlans){$TRUE}else{$False}}},ObjectId | export-csv $_default_log -NoTypeInformation

Get a list of Role Members.

 $_usersinroles = @()
 $_default_log = $env:userprofile + '\Documents\azuread_role_members.csv'
 Get-AzureADDirectoryRole | foreach {
     $_objectid = $_.ObjectId; $rolename = $_.Displayname
     $_usersinroles += Get-AzureADDirectoryRoleMember -ObjectId $_objectid | select  `
     @{name='RoleName';expression={$rolename}},displayname,UserPrincipalName,UserType,LastDirSyncTime,DirSyncEnabled,mail,accountenabled
     }
     $_usersinroles | export-csv $_default_log –NoTypeInformation

Get a list of in cloud only accounts

 $_default_log = $env:userprofile + '\Documents\azuread_incloudonly_accounts.csv'
 Get-AzureADUser -filter "userType eq 'Member'"-all $True | where {$_.userprincipalname -like "*onmicrosoft.com"} | select DisplayName, `
     UserPrincipalName,Mail,Department,UserType,CreationType,RefreshTokensValidFromDateTime,AccountEnabled,`
     @{name='Licensed';expression={if($_.AssignedLicenses){$TRUE}else{$False}}},`
     @{name='Plan';expression={if($_.AssignedPlans){$TRUE}else{$False}}},ObjectId | export-csv $_default_log -NoTypeInformation

And last store all users in an Array and use the Where-Object

This last query isn't the greatest way of doing this. If the plan is to perform multiple queries or filtering on different attributes it will probably be easier to query every user and property and store them in an array. Once the array is populated pipe the array to a where-object to be able to perform any kind of filter needed. Keep in mind in large environments it will take some time to populate or repopulate the array.

 $_all_azure_ad_users = Get-AzureADUser -all $true
 $_all_azure_ad_users | where {$_.userprincipalname -like "*onmicrosoft.com"} | select UserPrincipalName

This is really all I have for now. I will try my best to add more to this as I continue to figure out what works and doesn’t work. Hopefully in the near future the management pack will get updated again with additional capabilities.

Kind of usefulish resources

Supported queries, filters, and paging options | Graph API concepts

Comments

Anonymous
July 02, 2017
Thanks for these..
Anonymous
December 27, 2017
This is really helpful, thanks. I'm finding the lack of documentation on the AzureAD modules to be frustrating, like what are the outputs/properties from the "Get-AzureADUser"?
- Anonymous
  December 14, 2018
  I know this is a while after, but still worth a response for later readers.With PowerShell, any object can be piped to | Get-Member - to view the properties and methods available on it.
Anonymous
December 22, 2018
In your example of searchstringof, you have your parameters reversed. According to Odata Version 3, the expressions should be “substringof(‘contoso.com’,userprincipalname)”. This appears to be the only string command with 'backward' paramters. Reference: https://www.odata.org/documentation/odata-version-3-0/odata-version-3-0-core-protocol/#queryingcollections
Anonymous
March 07, 2019
if you want to match a UPN domain you can use the following command Get-AzureADUser | where {$_.userprincipalname -like "*yourdomain.com"}

Share via