Powershell - Useful Azure AD queries using the AzureAD Module

  • Article

 

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

image

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.

image

 

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

image

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

image

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

image

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

image

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

image

 

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