Skip to main content

Querying Quick Start

SparkLogs defines the SQL-like Lightning Query Language (LQL) to query your data.

In LQL, you define filter expressions that match events you want to find (similar to the WHERE clause in SQL).

Examples​

Query for any log message that contains the word failed:​

failed

Query for any log message that has at least one IP address, and the IPs all do not match the 192.168.0/24 subnet or the 10/8 subnet:​

x.ips! x.ips not in (192.168.0.*, 10.*)
tip

Notice the implied AND between the two expressions (x.ips! AND x.ips ...). You can query array fields by referring to the whole array field x.ips or you can filter over a specific entry in the array, for example x.ips[2]=192.168.0.100.

Query for any log message that has any timestamp that is between 7am and 6:30pm (MDT) on May 3, 2024:​

x.ts between 2024-05-03 07:00 MDT and May 3, 2024 18:30 MDT

Query for any log message that contains timed out or where it contains SLOW QUERY AND the custom field x.querySecs was more than 2 AND where the event does not contain known_complex_query:​

`timed out` OR ("SLOW QUERY" && x.querySecs >= 2.0 && NOT 'known_complex_query')

Query for Apache server log events requesting any URL that begins with document and ends in /create and the HTTP response code was not 2XX or 3XX:​

x.http.request.uri: document*"/create" x.http.response.status not in (2??, 3??)
tip

Note that the /create had to be quoted because an unquoted word cannot begin with a /. Also note that * was not inside the quotes because it represents a pattern operator and would have been treated as an actual text value if it was quoted. This example demonstrates that you can form a single simple pattern by mixing quoted terms and unquoted patterns.

Use a regexp to query for any log message that contains a UUID (e.g., 3c05fbd5-1006-49be-87af-2f28c5c800d5):​

message: /[0-9A-F]{8}(-[0-9A-F]{4}){3}-[0-9A-F]{12}/

Basic Construction​

Each filter expression is either a standalone simple pattern without a field name or operator, or is a complex expression that has a field name, followed by an operator, followed by either a simple pattern or a regular expression (only supported for certain operators) or a value list.

Multiple filter expressions can be combined using AND (&&) or OR (||) or negated with NOT. The precedence of these combinations can be clarified by grouping expressions in parentheses.

Patterns and Regular Expressions​

Simple patterns are a mix of:

  • Pattern operators: * (wildcard: match any number of any character) and ? (match any single character)
  • Unquoted terms: Words with any characters except whitespace or characters reserved for operators. Some characters like - and / can be used in the middle of an unquoted term but cannot begin the term. For example: top/sub-folder/picture.png
  • Quoted terms: You can quote an expression that has whitespace or other reserved characters using ", ', or `. In the quoted string you can escape the quote character itself using \\. For example: 'Alice\'s skill'

Regular expressions are denoted with /.../. A regular expression can only be used in conjunction with certain operators (:, !:, =, and !=). The full power of the re2 regexp engine is supported.

Value Operators​

Simple operators have a fieldname on their left and either a simple pattern, regular expression, or value list on their right.

The simple operators are:

  • : - check if a field contains a simple pattern or regexp; e.g., message: top/sub-folder/picture.png
  • !: - check if a field does NOT contain a simple pattern or regexp; e.g., pattern !: initializing
  • = - check if a field exactly matches a simple pattern or regexp
  • != or <> - check if a field does NOT exactly match a simple pattern or regexp
  • >=, >, <, <= - check if a field has the given relation to a literal value (patterns and regexp not allowed)
note

For non-string fields, the : and !: operators are equivalent to = and !=.

Complex operators require a field on their left, but may have zero to many values on their right:

  • <field>! - match events that have any non-NULL value for the given field; e.g., x.http.response.status!
  • <field> between <literal> and <literal> - match events where the given field has a value between the two literals (inclusive); e.g., billable_bytes between 512 and 2048 or severity between warn and fatal
  • <field> in <value list> - match events where the given field exactly matches at least one of the given patterns or regexp in the value list
  • <field> not in <value list> - match events where the given field does NOT exactly match ANY of the given patterns or regexp in the value list

Value Lists​

Simple operators and the in and not in operators can be used with value lists. A value list is a comma-delimited list of simple patterns or regexp enclosed in parentheses.

When used with "positive" operators (:, =, >=, >, <, <=, between, in), then it will check if the operator matches any pattern in the value list. e.g., message: (ERROR, WARN) will match if the message field contains either ERROR or WARN.

When used with "negative" operators (!:, !=, not in), then it will check if the operator does NOT match all patterns in the value list. e.g., message!:(initializing, shutting down) will match if the message field does NOT contain initializing and does NOT contain shutting down.

Operators Over Array Fields​

You can use an array field in conjunction with any value operator. If you do not specify an index for an array field (e.g., x.ips), then it will check if any value in the array matches the given condition for a "positive" operator. If an unindexed array field is used in conjunction with a "negative" operator, then it will check if all values do NOT match the condition for the given operator.

For example, x.ips=192.168.0.100 will match if any IP address in the x.ips array exactly matches 192.168.0.100. On the other hand, x.ips != (192.168.0.*, 10.*) will match if all IP addresses in the x.ips array do NOT match either of the two patterns.

Field Names and Typing​

For standard field names and their types, see AutoExtract docs.

In general, field names follow JSON naming conventions. Some simple examples are x.var or x.http.response.status.

Different components in a field name are separated by ., and if a component of a field name contains whitespace or even a period, then you should enclose the component name with double quotes. You can escape a double quote in the field name itself by preceding it with a \. For example: x."custom field"."some \"quoted\" field".final_value

Optionally, you can append an explicit type suffix to the end of a field name by using # followed by the type suffix. The type suffix is a letter indicating the type, and then [] if the field represents an array of values. Valid letter type indicators are:

  • s - string
  • n - numeric
  • i - force a numeric field to be treated as a 64-bit integer
  • t - timestamp
  • b - boolean (true or false)

For example, x.my_array#i[] indicates that the x.my_array field should be treated as an array of 64-bit integers.

If you do not specify an explicit type by using a type suffix, then during the query process the type of the given field will be resolved automatically. If multiple types are found for the same field name, it will randomly choose one of the found types.

note

All numeric values are considered to have the numeric type if resolved through implicit type lookup. If you want to treat a field as an integer value rather than as a numeric (floating point) value, then manually specify the #i type suffix.

LQL queries are type-aware and type-safe. If you specify a field with an explicit type that is different from the actual type of a field with that name found in a given event, it will not consider this to be the same field and the expression will not match.

Expression Operators​

LQL expressions can be combined using unary and binary expression operators.

Unary operators:

  • NOT <expr> or -<expr> - match events where the given LQL expression does NOT match

Binary operators:

  • <expr> AND <expr> or <expr> && <expr> - match events where both LQL expressions match
  • <expr> OR <expr> or <expr> || <expr> - match events where either LQL expressions match

Parentheses can be used to control operator precedence. Otherwise, the order of operator precedence is: NOT, AND, OR (NOT expressions are bound first before AND expressions, etc.).

Case Sensitivity​

You can toggle the option whether or not an LQL query should be case sensitive (by default it is case insensitive). There are a few fields that ignore this flag and always have a certain behavior:

  • The pattern_hash field is always case sensitive
  • Any ID field (e.g., agent, organization) is always case insensitive

any Field​

The special meta any field can be used to search all standard string fields and all custom fields at the same time. This can be useful if you specified many custom fields as part of the JSON payload during ingestion and did not rely on AutoExtract to extract custom fields from the message text. In this case, the value of custom fields may not be contained in the event message, and so it can be convenient to search all fields with one operator.

The any field can only be used with the : (contains) value operator. It is a more expensive operation, so use the meta any field only when required.

As a special case, if you use a regexp pattern with the any field, then the regular expression will match over the JSON encoding of all custom fields (in addition to the values of standard string fields).