How to use Query Builder dialog for Query Criteria and Filter rule settings

Published: 10/04/2018

Applies to: Cayosoft Administrator all versions

Summary: Many automation rules are supplied with the Query Criteria and post-query Filter settings to refine the list of objects that will be affected by the rule when it is executed. There is a point-and-click Query Builder dialog provided to simplify configuring both Query Criteria and Filter.

Query Criteria may dramatically improve performance because the criteria are sent to the target system - to AD or Office 365 - with the query. It may result in a significantly reduced amount of data that must be sent over the network and processed by rule. Plus, target systems usually apply indexing, caching, and other optimization functions to provide optimal query performance. That is why the Query Criteria attribute list is limited to what the target system supports query conditions.

Post-query Filters are more flexible and support all the attributes that can be returned from the target system. But Filter conditions are applied by Cayosoft Administration Service only after all the data has been returned from Active Directory or Office 365.

Since Query Criteria and post-query Filters work differently, there are certain considerations to keep in mind when constructing conditions with Criteria Builder.

In this article:

Criteria Builder operators for Query Criteria

'Equal' and 'Not equal' operators

If you need to filter objects that have (not have) a specific attribute value, you need to use the Equal (Not equal) operator.

Examples:

1. Find all users with Office 'New York' you should use this condition:

2. Find all objects that do not have Office 'New York' and are not empty you need to use Not equal:

3. Find all enabled AD users

'Like' and 'Not like' string matching operators

You need to use 'Like' ('Not Like') operators in combination with wildcard (*) expressions to find objects that match (do not match) a specified pattern.

Examples:

1. Find all users who have extensionAttribute1 starting with 'svc', you should use this condition:

2. Find all users who have Department not start with '_internal':

3. Find all users who have Department name containing 'temp', so it does not matter if 'temp' is at the beginning of the string, at the end, or in the middle:

4. Find all users with empty City attributes:

5. Find all users with no empty Office Phone attribute:

6. Find all AD users whose email or SIP or other address does not end with the suffix '@mail.onmicrosoft.com' within their proxyAddresses attribute

'Contains' and 'Not contains' operators

Operators 'Contains' and 'Not contains' are not supported in Query Criteria.

Criteria Builder operators for post-query Filter

'Equal',  'Not equal', 'Like' and 'Not like' operators

The filter also supports all operator types described for Query Criteria ('Equals', 'Not equals', 'Like', 'Not like'), but there is a little difference when you want to find objects with empty and not empty attribute values. To find empty values using Filter you need to compare the attribute with $null.

Examples:

1. Find users with empty City attribute:

2. Find users with no empty Department:

'Contains' and 'Not contains' operators 

To perform a search within an array or multi-valued attribute like ProxyAddress you need to use the containment operators ('Contains' and 'Not contains'). These advanced operators are hidden by default, to enable them in the condition builder, hold the Shift button on the keyboard when clicking on the selector […] button.

Examples:

1. Find all users who have one of the attributes otherTelephone values '111':

2. Find all users with c attribute from the list of possible values: "AF","DZ","AO","AZ":

Advanced filtering with multi-valued attributes

Post-query Filter is more flexible than a Query and if you need to check if the multi-valued attribute is empty or contains more than a specific number of values you can use .Count property.

Examples:

1. Find all users with empty proxyAddress attribute:

2. Find all users that have more than 2 proxyAddresses:

Compare string attributes with numbers

To compare string attributes with numbers, you need to explicitly convert them to an integer value. To do this, you need to add [int32]$_. before the attribute name manually.

The attribute that you are using in the Filter should be added to Returned Properties.

Examples: 

1. Find users with EmployeeID greater than or equal to 10  and less than or equal to 1000.

2. Find users with employeeNumber greater than 100.

Compare string attributes with dates

To compare string attributes with the date, you need to convert it to its DateTime equivalent explicitly.

The attribute that you are using in the Filter should be added to Returned Properties.

Examples:

1. extensionAttribute1 contains the date. Find all users whose extensionAttribute1 was not updated in the past 4 days. {[string]::IsNullOrWhiteSpace($_.extensionAttribute1) -eq $False -and [DateTime]::Parse($_.extensionAttribute1) -ge [DateTime]::Now.AddDays(-4)}

   

2. 2. Find all objects that were created for the last 30 days: {$_.whenCreated -ge (Get-Date).AddDays(-30)}

   

Compare date attributes

If you want to use the exact date for comparison, you can use (Get-Date m/d/yyyy), for example (Get-Date 1/31/2018).
If you want to use current date +/- days from it, you can use (Get-Date).AddDays(<number of days>), for example (Get-Date).AddDays(60) or (Get-Date).AddDays(-30).

The attribute that you are using in the Filter should be added to Returned Properties.

Example:

1. Find all users whose accounts will expire after 1/09/2018.

MS Graph query condition (OData) filters in automation rules and web queries

When the MS Graph query condition is specified, it overrides the Query criteria setting. You can use OData filters supported by MS Graph that will be added to API requests.

Filter by displayName starts with

Example 1: Find Microsoft 365 user accounts which displayName starts with 'Adam':

(startswith(displayName,'Adam'))

Filter by extension attribute

Example 2: Find Microsoft 365 users with extension attribute1 equal to Sales:

onPremisesExtensionAttributes/extensionAttribute1 eq 'Sales'

Filter by custom attribute synced from on-prem Active Directory

Example 3: Find Microsoft 365 users with custom attribute synced from AD equal to 111 (in AD this is employeeNumber and in Azure AD it is named as extension_5f93355d7e794a30a02ff55b6e70c363_employeeNumber):

extension_5f93355d7e794a30a02ff55b6e70c363_employeeNumber eq '11111'

Filter by assigned Microsoft 365 license

Example 5: Find Microsoft 365 users with assigned Microsoft 365 license.
Note: You can find the GUID of the required license in this file: C:\ProgramData\Cayo Software\AdminAssistant\ItemCache\MS365LicenseCache.xml

assignedLicenses/any(u:u/skuId eq <LicenseSkuID>)

Example 6: Find Microsoft 365 users who don't have a Microsoft 365 license assigned.

not assignedLicenses/any(u:u/skuId eq <LicenseSkuID>)

Filter by date

Example 7: Find Microsoft 365 users with a creation date greater or equal 2023-11-01.

CreatedDateTime ge 2023-11-01T12:00:00Z