Understanding the query syntax

NOTE: We’ve upgraded the reporting system to offer better clarity and flexibility. If a report doesn’t load or shows an error, it’s likely due to these recent enhancements.
To resolve this, open the report, click Edit, and add the missing fields. You will also find more detailed property options available to help you build even more insightful and customized reports.

The query language included with KACE Cloud allows you to modify the report contents and extract applicable pieces of information from your selected data set, by directly communicating with the database.

The Query field on the report detail page helps you to filter the data that you want to appear in the report. For more information about report editing features, see Create and edit reports.

A query that you can specify in this field is very similar to the WHERE clause in a standard SQL statement, in that it is used to extract only those records that match a certain condition. You can use a variety of elements in every query, such as Values (literals), Comparison operators, Logical operators, Mathematical operators, and Functions.

Start by specifying the data property that you want to evaluate in the filter, followed by a conditional operator, such as the equal sign '='. Next, provide the value that you want to match.

As you type, a list of auto-complete suggestions appear based on the context of your query. This can help you to quickly find and select applicable elements. This field is not case-sensitive. The following syntax contains a set of common elements that can be used to generate reports. Additional elements are also available.

Typical syntax

([entity_set.]<property_name> <comparison_operator> <value>) [AND|OR|NOT (<condition_2>)... AND|OR|NOT (<condition_n>])

Where:

  • entity_set : The entity set different than the one associated with the report. Use it only when referencing a property that belongs to a different entity set. Use a period '.' as a separator between the entity set name and the actual property. For example, if you associated the Devices entity set with the report, and want to display only the devices that run a specific operating system, use the following syntax for your query: OperatingSystem.Name = '<os_name>'. For example: OperatingSystem.Name = 'Android'. For complete information about the available entity sets, see Reporting entity sets.
  • property_name: The name of the property whose values you want to evaluate. You can use any properties associated with the selected entity set, or any related properties from other entity sets.
  • comparison_operator: An operator that compares values, such as '='. See Comparison operators for a full list.
  • value: The value that you want to compare the property value with. For example, in a report that references the Devices entity set, you can use the following statement to only display enrolled devices: EnrollmentStatus = 'ENROLLED'. See Values (literals).

    For complex statements involving numbers, datetime, and duration values, you can use common Mathematical operators such as '+' or '-', and Functions. See these sections for complete details.

  • condition_n: Your expression can have multiple conditions, as needed. Combine them together using logical operators, such as AND or OR. See Logical operators.

Examples

Basic syntax:​

[entity].property <operator> value​

([condition]) AND|OR ([condition])​

Start by specifying the name of the property you want to query followed by a comparison operator, and the desired property value. You can use the properties associated with the selected entity set, their child properties, or any properties associated with a different entity set. If you choose to query the properties that belong to a different entity set, you must specify the name of that entity set, followed by a period'.'. For complete information about the available entity sets, see Reporting entity sets.

For example, if you select the Devices entity set, and want to list only the devices that are currently enrolled into KACE Cloud, use the following expression: EnrollmentStatus='ENROLLED'

To check if the user name contains a specific string: User.Name CONTAINS ‘George’​

Understanding entity sets

An entity set is a specific view of the data:

  • It is designed to address a particular scenario or data profile​.
  • It always starts with a base entity, such as Device​.
  • Child entities are referenced from the based entity using the '.' syntax​.
  • A child entity can be another entity.​
  • The base entity is implicitly referenced.

For complete information about the available entity sets, see Reporting entity sets.​

Child entities

The query language allows you to use child entites of the specified entity set. For example, the entity set used in these examples, Devices, contains a number of child entities, such as User, Operating System, and many others. Each child entity contains one or more properties that you can evaluate in your query expression.

For example, to list the devices with a particular OS, such as iOS, use the Name property of the child OperatingSystem entity: OperatingSystem.Name: OperatingSystem.Name='iOS'

Similarly, to list only devices of a selected manufacturer, such as Apple or Google, specify the related child entity using the following expression: Manufacturer.Name='Apple'

Entities can be chained together to reference related data: Apple.DEP.ProfileName​

For complete information about the available entity sets, see Reporting entity sets.

Operators

The following types of operators are supported:

  • Comparison operators: =, !=, <, <=, >, >=​
  • Array/list operators: IN, NOT IN​
  • Wildcard operators: CONTAINS, NOT CONTAINS, STARTS WITH, ENDS WITH​

Comparison operators allow you to specify complete or partial text strings that you want to look for in your selected property. You can use the equal sign '=' to specify the entire string, or look for properties that contain, start with, or end with a particular text using the CONTAINS, STARTS WITH, and ENDS WITH comparison operators. Here are some examples associated with the Devices entity set:

To show all devices whose name contains the word Pixel: Name CONTAINS 'Pixel'

To list all devices without an asset tag: AssetTag = ''

To show all devices whose name starts or ends with a specific string:

Name STARTS WITH 'Discovered'

Name ENDS WITH 'x86'

Greater than '>' or less than '>' symbols can be used to compare the selected property value with a desired number. To show all devices that have at least one app installed, use the COUNT function together with the greater than operator: COUNT(Apps) > 0

You can use arrays to list items with specific property values. Operators such as IN and NOT IN operators allow you to indicate if you want to include in the report only those object whose property values match or do not match one of the values specified in the array.

For example, to list only those devices that are either enrolled or discovered, use the following expression: EnrollmentStatus IN ['Enrolled', 'Discovered']

For more information, see Comparison operators.

Using date/time and duration values

Quite often in your report you need to list only those items that were changed or updated within a specific time period. Dates can be referenced using relative or absolute syntax​.

To specify a relative date range, for instance, to display only the devices that were updated over the last seven days, use the following expression: UpdatedAt > NOW() - 7days

The same syntax applies to other entity sets, such as Apps. You can use the above statement to show the apps that were updated during a desired time period.

To list only the devices updated after a specific date, you can use the absolute <yyyy>-<mm>-<dd> syntax to specify the date. For example: UpdatedAt > 2021-06-01

To list the devices after a specific point in time, simply use the letter 'T' as a separator between the date and time, to specify the fully qualified time value (<yyyy>-<mm>-<dd>T<hh>:<mm>:<ss>.<ms>): UpdatedAt > 2020-12-17T19:30:49.72

Here are some duration examples that you can use to generate reports:​

  • 1mo, 1month, 2months​
  • 1y, 1yr, 1year, 2years​
  • 3months4days

For more information, see Duration syntax and Duration examples.

Functions

A full range of functions is available for use in query expressions. For example, NOW() can retrieve the current date and time, while COUNT (<entity_set>) obtains the total number of items in the specified entity set.

For more information, see Functions.