KB20180410-1 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 Filter 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 starts with 'svc', you should use this condition:

2. Find all users who have Department not starts 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 attribute:

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

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

'Contains' and 'Not contains' operators

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

Filter users by their Office 365 plans

Examples:

1. Find users with Office 365 E3 plan assigned (its ID = ENTERPRISEPACK) assigned:

{$_.Licenses.AccountSkuId -match "ENTERPRISEPACK"}

2.Find users who have two plans assigned simultaneously:

{$_.Licenses.AccountSkuId -match "O365_BUSINESS_PREMIUM" -and $_.Licenses.AccountSkuId -match "STANDARDPACK"}

3. Find users with at least one of two assigned plans:

{$_.Licenses.AccountSkuId -match "O365_BUSINESS_PREMIUM" -or  $_.Licenses.AccountSkuId -match "STANDARDPACK"}

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 not 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 Shift button on the keyboard when clicking on the selector […] button.

Examples:

1. Find all users who have one of the attribute 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 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 attribute with numbers

In order to compare string attributes with numbers, you need to explicitly convert it 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 10  and less than or equal 1000.

2. Find users with employeeNumber greater then 100.

Compare string attributes with dates

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

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

Examples:

1. extensionAttribute1 contains 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.