NXQL language definition (classic)

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:

CODE

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

Example:

CODE

(select (device_uid name)
    (from device))

Selecting plain events

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

CODE

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

Example:

CODE

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

CODE

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

Example:

CODE

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

CODE

(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 that it does not return the start time of the connection. Since these kinds 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.

CODE

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

CODE

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

CODE

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

Example:

CODE

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

CODE

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

CODE

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

Examples:

CODE

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

CODE

(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 a condition on a field value. It has the following format:

CODE

([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, for example, "Softy ""Visual""".
integer: An integer number, for example 10.
real: A floating-point number, for example 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, for example 60 seconds.
millisecond: A natural number representing milliseconds, for example 60000 milliseconds.
microsecond: A natural number representing microseconds, for example 60000000 microseconds.
byte: A natural number representing bytes, for example 1048576 bytes.
ip_address: An IP address, for example 172.16.10.5.
ip_network: An IP network, for example 172.16.0.0/16.
mac_address: A MAC address, for example 48:5b:39:18:70:bb.
mhz: A natural number representing mega hertz, for example 1600 mhz.
sid: A Windows security token, for example S-1-5-21-3623811015-3361044348-30300820-1013.
md5: A MD5 hash code in hexadecimal format, for example d41d8cd98f00b204e9800998ecf8427e.
port: A port type (udp/tcp) followed by a port number, for example tcp/8080.
version: Four integers separated by a '.', for example 5.1.0.34.
datetime: A date and time in ISO 8601 format, for example 2014-06-12T13:54:51.
time: A time in ISO 8601 format, for example 13:54:51.
date: A date in ISO 8601 format, for example 2014-06-12.
day: A natural number representing days, for example 7 days.
percent: A fraction of 1 represented with 2 decimal places, for example 0.75, or 75% when displaying formatted output.
permill: A fraction of 1 represented with 3 decimal places, for example 0.752, or 75.2% when displaying formatted output.

Use the special type pattern to match a string against a star pattern expression. 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:

CODE

(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, for example 7 days.
d: day for example 24 hours.
h: 1 hour.
m: 1 minute.
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.