PowerShell Invalid filter clause-CodePudding

I'm trying to execute and get the result using the below command:

$usertype = 'member'
Get-MgUser -All:$true -Filter 'UserType -eq "$usertype" -and OnPremisesSyncEnabled -eq "$true" and usagelocation -like "*"'| 
                Select-Object UserPrincipalName, ID, DisplayName, CreatedDateTime, SignInActivity, OnPremisesSyncEnabled, Usagelocation

But it always failed with the error below:

Get-MgUser : Invalid filter clause
At line:1 char:1
  Get-MgUser -All:$true -Filter 'UserType -eq "$usertype" -and OnPremis ...
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      CategoryInfo          : InvalidOperation: ({ ConsistencyLe...ndProperty =  }:<>f__AnonymousType59`9) [Get-MgUser_List], RestException`1
      FullyQualifiedErrorId : BadRequest,Microsoft.Graph.PowerShell.Cmdlets.GetMgUser_List

CodePudding user response：

The Filter is send to the graphAPI - you can't use the operators (e.g. -eq) as on PowerShell, the syntax is different see:

https://learn.microsoft.com/en-us/graph/filter-query-parameter

Also there are limitations which attributes you can use in queries, which combination of attributes and also which operators for which attributes.

https://learn.microsoft.com/en-us/graph/aad-advanced-queries?tabs=http

In principle if you define a filter you have to enclose the whole filter in double quotes and the strings within the filter in single quotes, so your filter would have to look like this:

"UserType eq '$usertype' and OnPremisesSyncEnabled eq true and usagelocation like '*'"

But this will also not work, because wildcards are not supported and also there is no operator 'like'. Also note if a string contains a single quote you have to double it to escape. URL encoding is done by the cmdlet but not escaping....

Check the documentation: https://learn.microsoft.com/en-us/graph/api/resources/users?view=graph-rest-1.0

Also note that you often have to use the beta API to do common stuff, e.g. to query the lastSignInDate for a user. You can switch to the beta API:

select-mgprofile -name beta

But back to your filter - you can only do:

get-mguser -all -Filter "usertype eq '$usertype' and OnPremisesSyncEnabled eq true" | ?{$_.UsageLocation}

Currently you can't do UsageLocation ne 'null' because you will get: Unsupported property filter clause operator 'NotEqualsMatch'. So you have to filter at shell level.

Sooner or later you will have to learn about consistencyLevel and countVariable:

https://devblogs.microsoft.com/microsoft365dev/build-advanced-queries-with-count-filter-search-and-orderby/

Also note if you loop over some thousand objects or you query the auditlog you will probably face task cancelled/timeout exceptions. Those are related to the throttling "feature" of the graphAPI. There is a hard limit of 2.000 requests per second. If so take a look at json batching -> invoke-mggraphRequest...

So hope this helps, sadly the graphAPI is far away from the performance and useability which you probably know from the good old AD.