# 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: ``` (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. ``` (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 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. ``` (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 a 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, 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: ``` (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.