NXQL language definition

While the NXQL tutorial guides you through your first steps with NXQL, this document gives a more formal definition of the query capabilities of NXQL.

Selecting plain objects

To select objects from an object table, use this form of the select statement:

(select ([field]...)
    (from [object]      
        (where [object] [filter])...))

Example:

(select (device_uid name)
    (from device))

Selecting plain events

To select events from an event table, use this form of the select statement:

(select ([field]...)
    (from [event]       
        (where [event] [filter]...)... 
        (between datetime datetime))
    (order_by start_time [asc|desc]) // optional
    (limit number))

Example:

(select (start_time incoming_traffic outgoing_traffic)
    (from connection
        (where connection (ne status (enum established))   
                          (ne status (enum closed)))
        (where user (eq name (string "siesme@AONNETWORK"))) 
        (between now-7d now))
    (order_by start_time asc)
    (limit 100))

This query returns the start time and the incoming and outgoing traffic of the last 100 connections whose status is not equal to established or closed. That is, those connection with a status equal to rejected, no host or no service.

Selecting events with decoration

To select events and their linked objects from a given event table, use the following form of the select statement. Note that there is no limit on the number of object tables that you can specify, as long as the object table is really linked to the events. For instance, it would not make much sense to query about printers related to execution events, since printers are not linked to executions.

(select (([object|event] [field]...)...)
    (from ([event] [object]...)     
        (where [object|event] [filter]...)... 
        (between datetime datetime))
    (order_by start_time [asc|desc]) // optional
    (limit number))

Example:

(select ((connection (start_time)) (user (name)))
    (from (connection user)
        (where connection (ne status (enum established))   
                          (ne status (enum closed)))
        (between now-7d now))
    (order_by start_time desc)
    (limit 100))

The query returns the start time as well as the name of the user who initiated the last 100 connections whose status is not equal to established or closed, that is, with a status equal to rejected, no host or no service.

Another example:

(select ((user (name)) (device (name)))
    (from (connection user device)
        (where connection (ne status (enum established))   
                          (ne status (enum closed)))
        (between now-7d now))
    (order_by start_time desc)
    (limit 100))

This last query is identical to the previous one, except for that it does not return the start time of the connection. Since these kind of queries return one tuple per event, you may see a tuple with the same user name and device name appearing more than once in the results. These tuples are not really duplicated results, they actually belong to different connections although you may not see the difference due to the selected fields.

Selecting objects with activity

To select objects linked to an activity (event), use the following select statement. The difference with the previous family of queries is that in the former you get one result tuple per event, while in this latter you get one result tuple per object.

(select ([field]...)
    (from [object] 
        (with [event]   
            (where [object|event] [filter]...)...
            (compute [aggregate]...)      // optional 
            (between datetime datetime))
        (having [filter on aggregate]...) // optional 
    (order_by [field] [asc|desc])         // optional
    (limit number))                       // optional

Example:

(select (name)
    (from device 
        (with execution
            (where binary (eq threat_level (enum high))) 
            (where binary (eq threat_level (enum intermediate)))
            (compute number_of_binaries)
            (between midnight-1d midnight)))
   (limit 100)
   (order_by name desc))

This query returns those devices which executed a binary whose threat level is intermediate or high yesterday. In addition, for each device, the query computes the number of distinct binaries matching the condition.

Selecting two objects

To select unique pairs of objects linked to a given type of events, use the following select statement. Note that you can select no more than two object tables and that you cannot use any logic operator.

(select (([object] [field]...)...)
    (from ([object] [object])       
        (with [event]   
            (where [object|event] [filter]...)...
            (between datetime datetime))
    (limit number))

Example:

(select ((package name) (device name))
    (from (package device)
        (with package
            (where package (eq name (pattern "*Office*")))))
    (limit 100))

This query returns the unique pairs of devices and packages, where the name of the package contains the term Office.

Updating objects

The update statement modifies categories or custom fields of an object table:

(update (set [field] ([type] [value]))...
    (from [object]
            (where [object] [filter]...)...))

To reset the value of a category or custom field, use the following update statement:

(update (set [field] nil)...
    (from [object]
            (where [object] [filter]...)...))

Examples:

(update (set #Location (enum Paris))
    (from device
        (where device (eq name (pattern "PA*")))))

This query updates the Location category of every device whose name begins with PA to Paris.

(update (set #Location nil)
    (from device
        (where device (eq name (pattern "PA*")))))

This query resets the Location category to nil. If an auto-tagging rule for the Location of devices is in force, the system will reset the value to the keyword of the matching auto-tagging rule.

Filter

A filter is condition on a field value. It has the following format:

([comparer] [field] ([type] [value]))
([comparer] [field] nil)

Where [comparer] may have one of the following values:

• eq: equal. If the type of the field is an array of [type], eq is true if at least one element of the array is equal to the value.

• ne: not equal. If the type of the field is an array of [type], ne is true if no element of the array is equal to the value.

• lt: less than.

• le: less or equal.

• gt: greater than.

• ge: greater or equal.

Where [type] may have one of the following values:

• boolean: A true or false value. Use keywords true and false, yes and no, or 1 and 0 as boolean literals.

• string: A string, If the string contains a space or a double-quote, it must be double-quoted and the quote duplicated, e.g "Softy ""Visual""".

• integer: An integer number, e.g. 10.

• real: A floating-point number, e.g. 12.56.

• enum: A list of distinct values. As in the case of strings, if the value contains a space or a double-quote, it must be double-quoted.

• second: A natural number representing seconds, e.g. 60 second (= 1 minute).

• millisecond: A natural number representing milliseconds, e.g. 60000 millisecond (= 1 minute).

• microsecond: A natural number representing microseconds, e.g. 60000000 microsecond (= 1 minute).

• byte: A natural number representing bytes, e.g. 1048576 byte (= 1MB).

• ip_address: An IP address, e.g. 172.16.10.5.

• ip_network: An IP network, e.g. 172.16.0.0/16.

• mac_address: A MAC address, e.g. 48:5b:39:18:70:bb.

• mhz: A natural number representing mega hertz, e.g. 1600 mhz (= 1.6 GHz).

• sid: A Windows security token, e.g. S-1-5-21-3623811015-3361044348-30300820-1013.

• md5: A MD5 hash code in hexadecimal format, e.g. d41d8cd98f00b204e9800998ecf8427e.

• port: A port type (udp/tcp) followed by a port number, e.g. tcp/8080.

• version: Four integers separated by a '.', e.g. 5.1.0.34.

• datetime: A date and time in ISO 8601 format, e.g. 2014-06-12T13:54:51.

• time: A time in ISO 8601 format, e.g. 13:54:51.

• date: A date in ISO 8601 format, e.g. 2014-06-12.

• day: A natural number representing days, e.g. 7 days (= 1 week).

• percent: A fraction of 1 represented with 2 decimal places, e.g. 0.75, or 75% when displaying formatted output.

• permill: A fraction of 1 represented with 3 decimal places, e.g. 0.752, or 75.2% when displaying formatted output (note that formatted permill values are displayed as a percentage).

Use the special type pattern to match a string against a star pattern expresssion. Note that only the eq and ne operators are available for the type pattern, for example:

(eq name (pattern "NY*"))

Filters belonging to the same where clause are composed with a logic AND. For instance, the following where clause selects only devices whose name begins with NY and whose manufacturer is Dell:

(where device (eq name (pattern "NY*"))
(eq device_manufacturer (string "Dell"))

Between

Date and time in a between clause is composed of a date time in ISO 8601 format or one of the following keywords:

• now: query time.

• midnight: last midnight.

• sunday: last Sunday at 00:00:00.

• monday: last Monday at 00:00:00.

• tuesday: last Tuesday at 00:00:00.

• wednesday: last Wednesday at 00:00:00.

• thursday: last Thursday at 00:00:00.

• friday: last Friday at 00:00:00.

• saturday: last Saturday at 00:00:00.

Optionally followed by a positive or negative integer and one of the following units:

• w: week i.e. 7 days.

• d: day i.e. 24 hours.

• h: 1 hours.

• m: 1 minutes.

• s: 1 second.

Examples:

• (between midnight now): today.

• (between midnight-1d midnight): yesterday.

• (between monday monday+24h): last monday.

• (between 2014-7-16@14:00:00 2014-7-16@15:00:00): on 2014-7-16 between 2 and 3 PM.