NXQL Tutorial

Contents

NXQL Tutorial

Overview

The Nexthink Query Language (NXQL) is a language designed to query the in-memory database of the Nexthink Engine via the Web API V2. 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). The Finder, the Portal and the Lua scripts running within the embedded Lua interpreter of the Engine currently use the selector language to query the 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:

(select (device_uid name) (from device))

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.

(select            - select statement
   (id name)       - list of fields
   (from device))  - queried table

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:

(select (name *antivirus*) (from device))

If you mistype the name of a field, the system signals the error and suggest as alternative either the exact name of the field that you most probably mispelled 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:

(select (name)
   (from device
   (where device
       (eq name (string "NXT-DV10")))))

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 that 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:

(select (name os_version_and_architecture number_of_antiviruses)
   (from device
   (where device
       (eq os_version_and_architecture (pattern "Windows 7*"))
       (eq number_of_antiviruses (enum 0)))))

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:

(select (name os_version_and_architecture number_of_antiviruses)
   (from device
   (where device
           (eq os_version_and_architecture (pattern "Windows 7*")))
   (where device
           (eq os_version_and_architecture (pattern "Windows 8*")))))

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 infrastruture that happens at a defined moment in time. All events have a timestamp, therefore events can be ordered by time. Events are at the core of 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 hundreds 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 connection made by firefox.exe the last day:

(select (start_time end_time incoming_traffic outgoing_traffic)
   (from connection
       (where binary (eq executable_name (pattern firefox.exe)))
       (between midnight-1d midnight))
   (limit 100)
   (order_by start_time desc))

  • 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:

(select (id name)
   (from device
       (with connection
           (where binary (eq executable_name (pattern firefox.exe)))
           (between midnight-1d midnight))))

While the former query is similar to queries made so far, the latter introduces the with clause. This clause specifies the type of events to traverse in order to build the list of selected objects. Of course, only those events that are linked to the object of interest can be used for the traversal.

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:

(select (id name)
   (from device
       (with web_request
           (where binary (eq executable_name (pattern firefox.exe)))
           (where domain (eq name (string mail.google.com)))
        (between midnight-1d midnight))))

Note that the query holds two where clauses which 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 for accessing mail.google.com yesterday, write:

(select (id name)
  (from device
      (with web_request
          (where binary (eq executable_name (pattern firefox.exe)))
          (where binary (eq executable_name (pattern chrome.exe)))
          (where domain (eq name (string mail.google.com)))
       (between midnight-1d midnight))))

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:

(select (id name)
  (from device
      (with web_request
          (where binary (eq executable_name (pattern firefox.exe))
                        (lt version (pattern 50)))
          (where domain (eq name (string mail.google.com)))
       (between midnight-1d midnight))))

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 which 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:

(select (id name)
   (from device
       (with web_request
       (where domain
           (eq name (string mail.google.com)))
       (compute incoming_traffic)
       (between midnight-7d midnight))))

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

(select (id name)
   (from device
       (with web_request
           (where domain
               (eq name
                   (string mail.google.com)))
           (compute incoming_traffic)
           (between midnight-7d midnight))
   (having
       (gt incoming_traffic
           (byte 1073741824))))

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:

(select (id name #Location) (from device))

You can also use categories or custom fields as filters:

(select (id name)
   (from device
       (where device
           (eq #Location (enum Paris)))))


The names of categories or custom fields containing spaces or quotes must be quoted:

(select (id name)
   (from device
       (where device
           (eq #"My Location" (enum Paris)))))

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:

(select (name #"campaign:Laptop satisfaction/Device preference")
   (from user))

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:

(select (name #"campaign:Laptop satisfaction/Device preference")
   (from user
       (where user (ne #"campaign:Laptop satisfaction/Device preference"
                   (string "No")))))

Similarly, to select the users who did not answer a specific single answer or opinion scale question yet, compare with the empty string:

(select (name #"campaign:Laptop satisfaction/Device preference")
   (from user
       (where user (eq #"campaign:Laptop satisfaction/Device preference"
                   (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:

(select (name #"campaign:Laptop satisfaction/Positive points")
   (from user
       (where user (eq #"campaign:Laptop satisfaction/Positive points"
                   (string "Speed"))
                   (eq #"campaign:Laptop satisfaction/Positive points"
                   (string "Size")))))

Instead, if you want to query for the users that exactly answered Speed and Size and nothing else, specify them as a list:

(select (name #"campaign:Laptop satisfaction/Positive points")
   (from user
       (where user (eq #"campaign:Laptop satisfaction/Positive points"
                   (list ("Speed" "Size"))))))

Alternatively, to get the users that chose one of the values Speed or Size (or both), write the logical-or version of the query:

(select (name #"campaign:Laptop satisfaction/Positive points")
   (from user
       (where user (eq #"campaign:Laptop satisfaction/Positive points"
                   (string "Speed")))
       (where user (eq #"campaign:Laptop satisfaction/Positive points"
                   (string "Size")))))

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:

(select (name #"campaign:Laptop satisfaction/Positive points")
   (from user
       (where user (eq #"campaign:Laptop satisfaction/Positive points" nil))))

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:

(select (name #"score:Device performance/Boot speed")
   (from device))

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:

(select (name #"score:Device performance/Boot speed")
   (from device
       (where device (gt #"score:Device performance/Boot speed"
                     (real 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:

(select (name #"score:Device performance/Boot speed")
   (from device
       (where device (eq #"score:Device performance/Boot speed"
                      nil))))

Using platforms

NXQL supports the three platforms included from Nexthink V5.3: 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 (e.g. 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:

(select ((device name) (user name))
   (from (device user)
       (with execution
           (where binary
               (eq executable_name (pattern firefox.exe)))))
   (limit 100))

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:

(select ((device (name))
         (connection (start_time end_time incoming_traffic outgoing_traffic)))
   (from (device connection)
       (where binary (eq executable_name (pattern firefox.exe))))
   (limit 100)
   (order_by start_time desc))

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 which 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:

(select 
    (
        (execution binary_path)
        (binary (executable_name version)) 
        (device (name last_ip_address))
        (user (name))
    )
    (from (execution binary device user) 
        (where binary (eq threat_level (enum "-")))
        (between midnight now)
    ) 
    (limit 100))

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 to events.

For instance, to list all devices with the package Microsoft Office 365 installed, write the following query (package works as relation):

(select (name)
   (from device
       (with package
           (where package (eq name (pattern "Microsoft Office 365 ProPlus*")))))

To get the package version along with the device, write the following query (where package works both as object and as relation):

(select ((device (name)) (package (version name publisher)))
   (from (device package)
       (with package
            (where package
               (eq name (pattern "Microsoft Office 365 ProPlus*"))
               (eq type (enum program)))))
   (limit 10000))

If you simply want to compute the number of packages installed on every device, write the following query (where package works as relation):

(select (name)
   (from device
       (with package
           (compute number_of_packages)))

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:

(select (name)
   (except
       (from device)   - list of all devices
       (from device    - list of device with Office
           (with package
               (where package (eq name (pattern *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, creating logically the list of devices without Microsoft Office.

Three set operators exists:

  • 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 an 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:

(update (set #Location (enum Paris))
   (from device
       (where device
           (eq last_ip_address (ip_network 172.16.12.0/16)))))

Setting category overrides the auto-tagging rules associated with an keyword. If you want to reactivate the auto-tagging rules, write the following query.

(update (set #Location nil)
   (from device
       (where device
           (eq last_ip_address (ip_network 172.16.12.0/16)))))

Note that the table returned by an update statement contains the identifiers of all modified objects

Using placeholders

To generalize a query that you execute often, use placeholders. A placeholder is a number prefixed by the % character that you put in the place of a value, or 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 parameter. For example, the following query includes two placeholders:

(select (id name)
   (from device
       (with web_request
           (where device (eq #%1 (enum %2)))
           (between midnight-1d midnight))))

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.