NXQL tutorial (classic)
Overview
The Nexthink Query Language (NXQL) is a language designed to query the in-memory database of the Nexthink Engine via the NXQL API. The language is loosely based on SQL, using similar keywords in its statements, but with a LISP-like syntax.
NXQL is the evolution of the selector language (another pseudo-SQL internally developed language). Finder, Portal and the Lua scripts running within the embedded Lua interpreter of Engine currently use the selector language to query Engine. Being specifically designed for integrations and with speed improvements in mind, NXQL outperforms the selector language in many areas. NXQL lets you write more complex queries and, since you have more control over the object traversal, queries typically execute faster.
This tutorial is meant to guide you through the process of learning NXQL by example. Follow the NXQL tutorial in the suggested order to get the most out of it.
To execute the queries in the tutorial, use the NXQL editor that is available in every Engine with the Integration toolkit module. The rest of the tutorial assumes that you are authenticated in the NXQL editor with admin credentials, so you have the access rights to see all available data (such as the name of computers and users).
First queries
To get a list with the unique identifiers and the names of all available devices, enter the following query:
Note that the query starts with an opening parenthesis and ends with a closing parenthesis. The number of opening and closing parentheses must be balanced for the query to be well-formed. To help you formulate your queries, the system automatically adds missing parentheses at the end when needed. The query starts with the keyword select and it is thereby called a select statement. The select statement includes a list of the fields to be retrieved and a from clause that specifies the table where the fields are found.
Within a query, fields may contain wildcard characters. For instance, to get the names and all the antivirus related fields of devices, type in the following query:
If you mistype the name of a field, the system signals the error and suggests as an alternative either the exact name of the field that you most probably misspelled or, if no field exists whose name is close enough to the input, the complete list of field names that you can use in that context.
To retrieve only a subset of the devices, filter the results by the value of some of the fields. For example, to select the device named NXT-DV10 only, type in the following query:
Inside the from clause, the where clause keeps only those devices whose name is equal to NXT-DV10. The first argument of a where clause is the table to which the filter applies, and the second argument is the expression of the filter itself. A filter is composed of an operation, followed by the name of a field and a typed value. The possible operations are eq, ne, lt, le, gt and ge meaning equal, not equal, less than, less or equal, greater than, and greater or equal, respectively. The type of the value must match the type of the field. Find the names and the types of all the fields in the data model.
Logical and operation
You can define a where clause for more than one filter. In this case, only those objects matching all the filters are selected.
For instance, the following query returns the list of all devices running Windows 7 with no antivirus installed:
Logical or operation
On the other hand, if you want to retrieve objects that either match one set of filters or another, you have to write two where clauses for the same kind of object.
For instance, to retrieve the list of devices running Windows 7 or Windows 8 / 8.1, type the following query:
Remember that this is valid for where clauses on the same kind of object only. When writing more advanced queries that set conditions on objects of different tables, keep in mind that multiple where clauses on different kinds of objects behave as a logical and. Examples will follow below.
At this stage, you are already able to query any field of any object tables defined by Nexthink. You may try with other objects different from device, such as user or binary, to get more familiar with the NXQL.
Using Events
An event is an occurrence in your IT infrastructure that happens at a specific moment in time. All events have a timestamp, therefore events can be ordered by time. Events are at the core of the Nexthink technology, being the basic information units of the in-memory database. Depending on the kind of occurrence that they describe, there are several types of events. Each type of event is linked to a well-defined set of objects. For instance, connection events are linked to user, device, binary, destination, and port objects.
The number of events in the database is usually several orders of magnitude higher than the number of any other kind of object. While an object table like the device table may contain from a few hundred to ten thousand elements, the event table may hold tens of millions of elements. For performance reasons, it is important to keep this in mind when setting the time span of a query involving events.
In your queries, you can use the event table in two ways:
Directly selecting those events that occur during a given time interval. For instance, to retrieve the last 100 connections made by firefox.exe on the last day:
Selecting those objects that are linked to events occurring during a given time interval. For instance, retrieve all devices that used firefox.exe to access the web yesterday:
In addition to events, the with clause can also precede the package keyword when it expresses the relationship between a device and a package object, as explained below.
Logical operation with events
You can refine your query even further. Let us suppose that you are interested in those devices using firefox.exe that accessed mail.google.com yesterday:
Note that the query holds two where clauses that apply to two different kinds of objects: binary and domain. Thus, they behave as a logical and, meaning that the two conditions must be satisfied.
To behave as a logical or, the where clauses must apply to the same kind of object. For example, to expand our query to those devices that used chrome.exe in addition to firefox.exe to access mail.google.com yesterday, write:
On the other hand, to refine our original query even more and return only those devices which used a version of firefox.exe lower than 50, type in:
That is, set several conditions on the where clause of the same kind of object (the binary object, in this case) for the conditions to be combined with a logical and.
Finally, in the rarer cases where you need to combine conditions on different kinds of objects with a logical or, use the union keyword documented below.
Computing aggregates
The selection of objects linked to events can be augmented with aggregates. An aggregate is a named function that computes a count, a sum or an average of a given field for all selected events. For instance, the incoming_traffic aggregate adds up all the values of the field incoming_traffic of all the connection or web_request events selected by a with clause. Specify aggregates in a compute clause inside a with clause.
Since some aggregates require the traversal of events for their computation, you have similar performance concerns when using aggregates as when using events in your queries. It is important to limit the time interval of queries that may otherwise need to traverse many millions of events. Thus, aggregates that are not marked as FP in the data model require a between clause to limit the traversal. The between clause, however, does not put a strict limit on the time interval that you can specify. It is your responsibility to set a reasonable time interval, especially if the query is going to be periodically repeated.
For instance, to compute the incoming traffic per device of all web requests made to mail.google.com during the last 7 days, write the following query:
The list of aggregates for each event table is defined in the NXQL data model.
At this stage, you may wonder how to filter devices based on the value of an aggregate. In our previous example, you may want to select devices that transferred 1GB of data yesterday. This is the purpose of the having clause, which may appear in a from clause within a with clause. Of course, the aggregates filtered by the having clause must be declared first inside the compute clause.
Using categories and custom fields
In NXQL, both categories and custom fields are treated equally. They behave like classic fields, but their name is prefixed by the # character. For instance, to retrieve the list of devices with their Location, given that Location is a category on device, write the following query:
You can also use categories or custom fields as filters:
The names of categories or custom fields containing spaces or quotes must be quoted:
Campaigns custom fields
The results of campaigns are visible in NXQL as custom fields of the object user. The name of custom fields related to campaigns have the following format:
#"campaign:Name of the campaign/Name of the question"
Note the use of the keyword campaign: at the beginning of the name of the custom field. For example, to know the answers of every user to the question Device preference within the campaign Laptop satisfaction, write the query:
The underlying type of an answer to a single answer or opinion scale question is the string type. In turn, the underlying type of an answer to a multiple-answer question is a list of strings. Compare the values of an answer with the eq and ne operators (no other operator is allowed for comparing answer values). For example, to get the name and the actual answer of all the users who did not answer No to the single answer question Device preference, write the query:
Similarly, to select the users who did not answer a specific single answer or opinion scale question yet, compare with the empty string:
In the case of multiple-answer questions, it is possible to query for combinations of answers in the response given by the users. Use the logical and and logical or operations in the where clause described above or specify a list of values to exactly match a particular combination. For example, to get the users who answered both Speed and Size (and possibly something else) to the Positive points question of the campaign Laptop satisfaction, write the query:
Instead, if you want to query for the users that exactly answered Speed and Size and nothing else, specify them as a list:
Alternatively, to get the users that chose one of the values Speed or Size (or both), write the logical-or version of the query:
Finally, to get the users that did not give any answer yet to a multiple answer question, compare with the nil keyword instead of an empty string:
Scores custom fields
Scores are accessible through NXQL as special custom fields of the objects device or user. The name of custom fields related to scores have the following format:
#"score:Name of the score definition/Name of the score"
Note the use of the keyword score at the beginning of the name of the custom field. For example, to get the Boot speed leaf score of all devices, which is inside the Device performance score definition, write the query:
Because scores hold numerical values, the underlying type of any score is the real type. As an example of putting a condition on the value of a score, the following query retrieves all the devices whose Boot speed score is higher than 5.0:
Apart from numerical values, a score may have no value at all. To query for objects with an empty score, compare the value of the score with the nil keyword using the eq or ne operators. For example:
Remote actions custom fields
Get results and other information about the execution of remote actions through NXQL by accessing special custom fields of the object device. The name of custom fields related to remote actions have the following format:
#"action:Name of the remote action/Name of the output or exec info"
Note the use of the keyword action at the beginning of the name of the custom field. For example, to get the Execution status of the remote action Get Event Log on all devices, write the query:
To filter devices with no status for a particular remote action, compare the value of the status to the nil keyword. For example, to get all devices that have an execution status regarding the remote action Get Event Log, type in:
The results of remote actions (that is, their output values) are accessible through NXQL as well. Inside your NXQL queries, type in the name of the output exactly as defined in the remote action. Note that the name of the output may differ from its assigned label, which is displayed in the Finder. For instance, to get the path to the file generated by the remote action Get Event Log (name of the output OutputFile, label Output file), write the query:
Each output value is of the type indicated in the definition of the remote action. To compare with empty values, use nil in the case of numeric outputs and the empty string "" for outputs of the string type.
Using platforms
NXQL supports three platforms Windows, Mac, and Mobile.
When using the NXQL editor, select the platforms to which the query applies by ticking the check boxes at the top right corner of the editor.
When directly querying the API via an HTTP request (for example, from a script or an integration) use the platform parameter described in the introduction.
When selecting multiple platforms, beware that only those tables and fields that are common to all the selected platforms are valid in your query. For instance, the field name of a device is available for all three platforms, but all_antiviruses is available only for devices of the Windows platform. Therefore, a multi-platform query that includes the field all_antiviruses is not valid.
Selecting multiple tables
There are two types of queries in NXQL which let you combine information from multiple tables:
Selecting unique pairs of objects in relation to events of a particular kind.
Selecting events of a particular kind, as well as information from objects linked to those events.
Although they may look similar, both types of queries differ in some aspects that we detail below.
The most common type of query that requires multiple tables is the selection of unique pairs of objects which took part in a series of events. In this type of query, you can select only two object tables, while you specify the event table that makes the link between each pair of objects inside a with clause. In the select clause, specify the name of each object table before its corresponding list of fields, and then repeat the names of the object tables in the from clause. For instance, if you are interested in the names of both the users that executed firefox.exe and the devices on which it was executed, write the following query:
In the second type of query, the main interest lies in the individual events of the selected event table, which you may decorate with information from the objects linked to each event. Thus, to write queries of the second type, specify the name of the event table and the names of each additional object table in the from clause, as well as before each corresponding list of fields of interest in the select clause. For example, the following query returns the last 100 connections of firefox.exe, as well as the names of the devices that originated each connection:
In this second type of query, objects may be repeated in the results if they are linked to multiple events. For instance, in the example above, there may be a device that is linked to more than one of the selected connections. The name of that device will therefore appear repeated for each related connection. That is the opposite of the first type of query, where you get unique pairs of objects which may be linked to many events and you are not interested in the individual events.
Despite the given example, you may have noticed that queries of the second type are not limited to two tables. You must select one event table and one or more object tables instead. For example, to get all the executions of binaries that do not have their threat level set which took place today and display their binary path, along with some info about the binaries, devices, and users involved, write:
As for constraints, both types of multiple table queries require a limit clause to restrict the maximum number of returned entries and they do not allow the computation of aggregates.
Using packages in queries
package is a special keyword in NXQL in the sense that it can function as an object table or as a relationship table. Indeed, a package can refer to an installed package itself, with its attributes such as name, version, company, etc. or to its relation with devices through its installation. That is the reason why you can use packages inside a with clause, which is otherwise reserved for events.
For instance, to list all devices with the package Microsoft Office 365 installed, write the following query (package works as relation):
To get the package version along with the device, write the following query (where package works both as an object and as a relation):
If you simply want to compute the number of packages installed on every device, write the following query (where package works as a relation):
Operations on sets of objects
With NXQL, it is possible to compute two lists of objects of the same type and combine them into a single result with just one query.
For example, to compute the list of devices without the package Microsoft Office:
To execute the query above, the system computes the list of all devices and subtracts from it the list of devices with Microsoft Office, logically creating the list of devices without Microsoft Office.
Three set operators exist:
except (A) (B) Return objects appearing in A but not in B.
union (A) (B) Return all objects appearing in A or in B.
intersect (A) (B) Return only those objects appearing both in A and in B.
Remember that only one object table can be used in the two from clauses linked by a set operator. It is impossible to do a union of devices and users, for instance.
Note as well that these operators work with object tables only and not with event tables.
Updating values of categories and custom fields
To update a dynamic field, i.e. a category, use an update statement. An update statement sets the values of the specified dynamic fields in all the objects selected by a from clause. For instance, to set the location of some devices to Paris, based on their last IP address, write the following query:
Setting category overrides the auto-tagging rules associated with a keyword. If you want to reactivate the auto-tagging rules, write the following query.
Note that the table returned by an update statement contains the identifiers of all modified objects
Using placeholders
To generalize a query that you often execute, use placeholders. A placeholder is a number prefixed by the % character that you put in the place of a value, a custom field name, or a category name inside a query. When the query is executed, each placeholder is replaced by the actual value supplied as a parameter. For example, the following query includes two placeholders:
To execute this query, you should provide the name of a custom field or category for devices and its actual value as parameters. In the NXQL editor, provide the parameter values in the two text boxes for parameter input below the query.
In programmed queries, provide the actual parameters in the HTTP request.
Last updated