Logging Query Language Specification

Processing model

The Logging query language processing is based on the data flow model. Each query should reference one or more logs and expected to produce a table data set as a result. The query language provides several operators for searching, filtering, and aggregating structured and unstructured logs.

Log Streams

To begin your search, you must first define the set of logs you want to search over. You can choose to search over specific log objects, log groups, or compartments. You can mix and match as many logs as you need. Search scope is defined using the following pattern:
search "compartmentOcid/logGroupNameOrOcid/logNameOrOcid"

The query language fetches log entries from the scope you provided, and constructs a log stream that you can filter, aggregate, and visualize.

Fields

All fields in log streams are case sensitive. Although actual logs have indexed fields in lower-case only, it is possible to create new fields in the query with mixed case:
search "..."
   | select event as EventName

Datatypes

The following key data types are supported by the query language. For details about representation of the values of corresponding types refer to Literals.

  • Strings
  • Numbers (integer, float-point)
  • Arrays
  • Booleans
  • Timestamps
  • Intervals

Statements

A query consists of one or more statements. The last statement has to produce data. Following statement types are supported:

  • setting parameters
  • stream expressions

Stream expressions

All expressions which produce a stream are stream expressions. Stream expressions can be formed using the following operators:

  • Streaming operators
  • Aggregating operators

Pipe (|)

Pipe applies an operator on the left side to a stream expression on the right side. The pipe expression is a stream expression.

The operator on the right side of a pipe must consume only one stream (e.g., aggregate operation, filter, etc.).

Left side becomes "current stream" for the right side expression making all fields in the current stream available by short names:
search "application"
  | where level = 'ERROR'
 
>>
{"timestamp": "2019-01-03T00:04:01", "level":"ERROR", "host":"host1", "message":"download failed...", "impact":2}
{"timestamp": "2019-01-03T00:06:39", "level":"ERROR", "host":"host2", "message":"reached 90% file size limit... ", "impact":4}
{"timestamp": "2019-01-03T00:06:59", "level":"ERROR", "host":"host2", "message":"reached 95% file size limit... ", "impact":5}

Operators

The following are supported when performing advanced queries:
  • Stream operators
  • Scalar operators
  • Aggregations

Stream operators

A stream operator creates or modifies a log stream by filtering out or changing log entries.

search
Constructs a log stream from actual log objects. See Log Streams for details.
search "loggroup1/logname1" "loggroup2/logname2" "compartmentOCID/loggroup3/logname3"
where
Filters the current log stream using a Boolean expression.
search "application"
  | where level = 'ERROR'
>>
{"timestamp": "2019-01-03T00:04:01", "level":"ERROR", "host":"host1", "message":"download failed...", "impact":2}
{"timestamp": "2019-01-03T00:06:39", "level":"ERROR", "host":"host2", "message":"reached 90% file size limit... ", "impact":4}
{"timestamp": "2019-01-03T00:06:59", "level":"ERROR", "host":"host2", "message":"reached 95% file size limit... ", "impact":5}
The expression can be a complex Boolean expression:
search "application"
   | where ( level = 'ERROR' or level = 'INFO' )
      and host='host1' and "opc-request-id" ~ '.*AAAA.*'
>>
{"timestamp": "2019-01-03T00:04:01", "level":"ERROR", "host":"host1", "message":"download failed...", "impact":2}
{"timestamp": "2019-01-03T00:04:05", "level":"INFO", "host":" host1", "message":"host list updated..."}
where is optional:
search "application"
   | level = 'ERROR'
You can perform a full text search by specifying a filter on the entire content of the log. A search on logContent returns any log line where a value matches your string. This functionality supports wildcards:
search "application"
   | where logContent = 'ERROR' -- returns log lines with a value matching "ERROR"
search "application"
   | where logContent = '*ERROR*' -- returns log lines with a value containing "ERROR"
top
Fetches only a specified number of rows from the current log stream, based on some expression. A number of rows must be a constant positive integer, and a sorting expression must be provided.
search "application"
  | top 3 by impact
>>
{"timestamp": "2019-01-03T00:06:59", "level":"ERROR", "host":"host2", "message":"reached 95% file size limit... ", "impact":5}
{"timestamp": "2019-01-03T00:06:39", "level":"ERROR", "host":"host2", "message":"reached 90% file size limit... ", "impact":4}
{"timestamp": "2019-01-03T00:04:01", "level":"ERROR", "host":"host1", "message":"download failed...", "impact":2}
sort
Sorts the current log stream by the specified columns, in either ascending (default) or descending order. The operator uses the "DESC" and "ASC" keywords to specify the type of the order.
search "application"
  | sort by impact desc
>>
{"timestamp": "2019-01-03T00:06:59", "level":"ERROR", "host":"host2", "message":"reached 95% file size limit... ", "impact":5}
{"timestamp": "2019-01-03T00:06:39", "level":"ERROR", "host":"host2", "message":"reached 90% file size limit... ", "impact":4}
{"timestamp": "2019-01-03T00:04:01", "level":"ERROR", "host":"host1", "message":"download failed...", "impact":2}
{"timestamp": "2019-01-03T00:05:33", "level":"WARNING", "host":"host2", "message":"reached 70% file size limit... ", "impact":1}
{"timestamp": "2019-01-03T00:04:05", "level":"INFO", "host":" host1", "message":"host list updated..."}
{"timestamp": "2019-01-03T00:06:59", "level":"INFO", "host":" host2", "message":"fs clean up started..."}
More than one column can be used to specify the order:
search "application"
  | sort by host, impact desc
>>
{"timestamp": "2019-01-03T00:06:59", "level":"ERROR", "host":"host2", "message":"reached 95% file size limit... ", "impact":5}
{"timestamp": "2019-01-03T00:06:39", "level":"ERROR", "host":"host2", "message":"reached 90% file size limit... ", "impact":4}
{"timestamp": "2019-01-03T00:05:33", "level":"WARNING", "host":"host2", "message":"reached 70% file size limit... ", "impact":1}
{"timestamp": "2019-01-03T00:06:59", "level":"INFO", "host":" host2", "message":"fs clean up started..."}
{"timestamp": "2019-01-03T00:04:01", "level":"ERROR", "host":"host1", "message":"download failed...", "impact":2}
{"timestamp": "2019-01-03T00:04:05", "level":"INFO", "host":" host1", "message":"host list updated..."}
dedup
Process the current log stream by filtering out all duplicates by specified columns. If more than one column is specified, all columns have to be delimited by commas.
search "application"
  | dedup host
>>
{"timestamp": "2019-01-03T00:04:01", "level":"ERROR", "host":"host1", "message":"download failed...", "impact":2}
{"timestamp": "2019-01-03T00:05:33", "level":"WARNING", "host":"host2", "message":"reached 70% file size limit... ", "impact":1}
search "application"
  | dedup host, impact
{"timestamp": "2019-01-03T00:04:01", "level":"ERROR", "host":"host1", "message":"download failed...", "impact":2}
{"timestamp": "2019-01-03T00:05:33", "level":"WARNING", "host":"host2", "message":"reached 70% file size limit... ", "impact":1}
{"timestamp": "2019-01-03T00:06:39", "level":"ERROR", "host":"host2", "message":"reached 90% file size limit... ", "impact":4}
{"timestamp": "2019-01-03T00:06:59", "level":"ERROR", "host":"host2", "message":"reached 95% file size limit... ", "impact":5}
select
Applies a series of named scalar expressions to the current log stream. See summarize for an aggregation version of select.
search "application"
  | select level, host, impact+10 as impact, timestamp
>>
{"level":"ERROR", "host":"host1", "impact": 12, "timestamp": "2019-01-03T00:04:01"}
{"level":"INFO", "host":" host1", "timestamp": "2019-01-03T00:04:05"}
{"level":"WARNING", "host":"host2", "impact": 11, "timestamp": "2019-01-03T00:05:33"}
{"level":"ERROR", "host":"host2", "impact": 14, "timestamp": "2019-01-03T00:06:39"}
{"level":"ERROR", "host":"host2", "impact": 15, "timestamp": "2019-01-03T00:06:59"}
{"level":"INFO", "host":" host2", "timestamp": "2019-01-03T00:06:59"}
extend
Extends the current log stream with a computed column.
search "application"
  | extend concat(host, 'us.oracle.com') as fqn
>>
{"timestamp": "2019-01-03T00:04:01", "level":"ERROR", "host":"host1", "message":"download failed...", "impact":2, "fqn": "host1.us.oracle.com"}
{"timestamp": "2019-01-03T00:04:05", "level":"INFO", "host":" host1", "message":"host list updated...", "fqn": "host1.us.oracle.com"}
{"timestamp": "2019-01-03T00:05:33", "level":"WARNING", "host":"host2", "message":"reached 70% file size limit... ", "impact":1, "fqn": "host2.us.oracle.com"}
{"timestamp": "2019-01-03T00:06:39", "level":"ERROR", "host":"host2", "message":"reached 90% file size limit... ", "impact":4, "fqn": "host2.us.oracle.com"}
{"timestamp": "2019-01-03T00:06:59", "level":"ERROR", "host":"host2", "message":"reached 95% file size limit... ", "impact":5, "fqn": "host2.us.oracle.com"}
{"timestamp": "2019-01-03T00:06:59", "level":"INFO", "host":" host2", "message":"fs clean up started...", "fqn": "host2.us.oracle.com"}
rex
Expands a current log stream with values extracted from a raw message or text column, by the provided regular expressions:
search "application"
 | rex(raw, /reached (?<percentage>[0-9]+)%/)
 | where !isnull(percentage)
 | select host, percentage
>>
{"host": 'host2', percentage: '70'}
{"host": 'host2', percentage: '90'}
{"host": 'host2', percentage: '95'}
explode
Expands a row into multiple entries in the current stream, based on a provided array:
search "application"
 | select host
 | dedup by host
 | explode([1,2,3]) as num
>>
{"host": "host1", "num": "1"}
{"host": "host1", "num": "2"}
{"host": "host1", "num": "3"}
{"host": "host2", "num": "1"}
{"host": "host2", "num": "2"}
{"host": "host2", "num": "3"}

Scalar Operators

Scalar operators applicable to individual values:
Arithmetic operations:
: - <expr>
: <expr1> + <expr2>
: <expr1> * <expr2>
: <expr1> - <expr2>
: <expr1> / <expr2>
Boolean operations:
: <expr1> OR <expr2>
: <expr1> AND <expr2>
: NOT <expr1>
Functions/UDFs:
: <func>(<arg1>[, ...])
: CAST(<expr> AS <type>)
String operations:
: <expr> ~ /regex/   # regex matching 
: <expr> = <string>  # substring matching

Aggregate Operators

count
Calculates a number of rows in the current log stream:
search "application"
  | count
>>
{"count": 6}
summarize

Groups the current log stream by the specified columns and time interval, and aggregates using named expressions. If grouping columns are not specified, summarize will aggregate over the whole stream.

search "application"
  | summarize avg(impact) as impact by level interval 1m
>>
{"timestamp": "2019-01-03T00:04:00", "level":"ERROR", "impact": 2}
{"timestamp": "2019-01-03T00:04:00", "level":"INFO", "impact": 0}
{"timestamp": "2019-01-03T00:05:00", "level":"WARNING", "impact":1}
{"timestamp": "2019-01-03T00:06:00", "level":"ERROR", "impact":4.5}
{"timestamp": "2019-01-03T00:06:00", "level":"INFO", "impact": 0}
interval is optional:
search "application"
  | summarize max(impact)
>>
{"impact": 5}

Special Columns

raw
raw is a special column which represents the text of the whole original message.
search "application"
  | select *, command=regex('<([^>]*)>', raw)
  | where command = 'rm'
ts

A timestamp of the log's entry.

index_ts

Ingestion timestamp, in contrast to ts.

Comments

Both single line and multi-line comments are supported, for example:
search "application"
  | count -- this is a single line comment
/* this is a
   multi-line
   comment
*/

Identifiers

Identifiers are the names of all available all entities in the query. An identifier can reference a field in the current log stream, or a parameter defined in the beginning of the query. Identifiers have the following format:
name: \$?[a-zA-Z_][a-zA-Z_0-9]*

For example: level, app_severity, $level.

The quoted form allows special symbols in the names (except double quotes):
name: "[^"]+"
For example: "opc-request-id", "-level".

All parameter references start with a dollar sign ($), for example: $level.

Literals

Type Examples
string 'hello', 'world\'!'
wildcard pattern "acc-*"
integer -1, 0, +200
float 1.2, 0.0001, 1.2e10
array [1,2,3,4], []
interval 3h, 2m
nullable null

Functions

Scalar Functions
Name Description
has(field1) Checks whether field1 exists in the row or not (different from isnull().
isnull(expr1)
concat(expr1, ...)
coalesce(expr1, expr2) Returns expr2 if expr1 is null, otherwise expr1.
join(delim1, val1, val2, ...) Joins several values using a delimiter: join(',','a',b') => 'a,b'.
split(delim1, str1) Splits a string into a sub-string by delimiter: split(',', 'a,b') => ['a', 'b'].
array_append(val1, ...) Creates an array from values passed in. Values can be arrays or scalar values.
array_count(arr1) Returns a number of elements in the array.
time_format(time, format) Generates a string from time based on the provided format.
if(pred, val1, val2) If pred is true, returns val1, otherwise returns val2.
Aggregate functions
Name Description
sum(expr1)
avg(expr1)
min(expr1)
max(expr1)
values(expr1) Accumulates values into an array.
count() Counts a number of rows.
count(expr) Counts a number of non-null expr values.
count(distinct(expr)) Counts a number of distinct expr values.
percentile(expr, percentile1[, percentile2...] Nearest rank percentile, creates multiple columns for each percentile n passed.

System parameters

All parameters with the prefex "query." are reserved. The following parameters are supported:

Name Type Example Description
query.from String with datetime in ISO 8601 format. '2007-04-05T14:30' Specifies starting time of the query window.
query.lookupfolder String with path. 'lookup-bucket/dynamic' Must be an Object Storage bucket with lookup CSV files.
query.to String with datetime in ISO 8601. '2007-04-05T14:30+05:00' Specifies end time of query window.

Filtering Example

search "auditd_syscall"
| type='SYSCALL' OR type='PATH' OR type='PROCTITLE' OR type='EXECVE'

Explicit Time Window Example

set query.from=-30m;
set query.to=now;
 
search audit_authentication

Aggregation Example

search StormEvents
  | isnotempty('EndLocation')
  | summarize count() as event_count by EndLocation
  | top 10 by event_count