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

Many automation rules in Cayosoft Administrator include Query Criteria and Post-Query Filter settings to refine the list of objects affected during rule execution. The Query Builder dialog provides a point-and-click interface to simplify configuring these settings.

IMPORTANT: To edit a query filter, use the filter wizard and edit the existing values in the corresponding fields to update the resulting conditional expression. Editing an expression manually is not supported.

Query criteria

Query Criteria are highly efficient because they filter data at the source, either Active Directory (AD) or Microsoft 365, before the data is transmitted over the network. By limiting the scope of the query, Query Criteria can:

• Enhance performance by reducing the volume of data transferred and processed.

• Leverage system optimizations, such as indexing and caching, for faster query execution.

  IMPORTANT: The attributes available for Query Criteria are limited to those supported by the target system. This restriction ensures compatibility and optimal performance during query execution.

Post-query filters

Post-Query Filters, applied by the Cayosoft Administrator Service, offer greater flexibility:

• They can use any attributes returned from the target system, not just those supported for querying.

• However, these filters are applied after all data is retrieved from AD or Microsoft 365.

This approach can be useful for more complex filtering scenarios but may result in higher resource usage due to the additional data retrieval and processing.

Key considerations

When constructing conditions with the Query Builder, it’s essential to understand the differences between Query Criteria and Post-Query Filters:

• Use Query Criteria to optimize performance by reducing the initial data set returned by the target system.

• Use Post-Query Filters for advanced filtering needs when specific attributes are unavailable in Query Criteria.

• Balance the use of both to achieve the desired result while minimizing performance overhead.

Criteria Builder operators for Query Criteria

NOTE: Any valid attribute name can be specified manually if it is not listed in Query Criteria or Filter.

Both Query Criteria and Filter are case insensitive.

'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.

NOTE: Placeholder for a single character ('?') is not supported. '? = matches exactly one character.

Examples:

1. Find all users who have extensionAttribute1 starts 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

NOTE: To filter by a specific attribute, you should also add this attribute to the list of Returned Properties in the automation rule. Otherwise, objects in the result set would not contain this attribute, and filter conditions would treat the value as not set or $null.

'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.

NOTE: Wildcards (*) are not supported for 'Contains' and 'Not contains' operators.

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 the .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:

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. 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 the Returned Properties.

Example:

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.

NOTE: Some operators that are used in Odata filters require advanced query parameters to work correctly. If you use OData Filter, set MS Graph advanced queries to Enabled.

In Dynamic Groups and Restricted Groups MS Graph advanced queries setting is set to Enabled by default.

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 4: 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 5: 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 6: Find Microsoft 365 users with a creation date greater or equal 2023-11-01.

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