Advanced Search Operators

Advanced searches include aggregation, processing, and formating operators that can help you to refine and modify your search results.  

You can add advanced search operations to your query after filtering. Search operations work sequentially on a set of events, and the result from one operation is piped into the next one. Some operations work on an event at search time (parsing fields), others require a partial set (limit) or the full set (sort) of events to produce a result. You can view the results of an advanced search in the Aggregate tab.

Aggregation Operators

avg

Calculates an average of values in a specified source field. 

Syntax

* | avg(<FIELD>) (as<ALIAS>)
* | avg(<FIELD>) (as<ALIAS>) by <FIELD> (,<FIELD>)

Examples

Example 1: The following example searches through numbers in the 0-9 range in a four-digit sequence pattern, and calculates the average for the numbers found, if any. The average number will be extracted and added to the log event as a field called “number”.

* | parse / ([0-9]{4}) / as number | num(number) as number2 | avg(number2)

count

Counts the number of events returned by a filtering query, and optionally groups the events by one or more fields. You can also count events that contain specific fields.

Syntax

Count the number of events in the specified time range:

* | count

Count the number of events optionally grouped by one or more fields:

* | count (as <ALIAS>) by <FIELD> (, <FIELD>)

Count the number of events only if the event contains the field specified in the argument to count():

* | count(<FIELD>) (as <ALIAS>) by <FIELD> (, <FIELD>)

Examples

Example 1: Count the number of events for each unique resource and show the first 15 results.

* | count by _resource.name | sort by _count desc | limit 15

Example 2: You can count the number of unique values for a field by passing the results of one count operation into another count. The following example counts the number of unique resource names sending logs during the specified time range.

* | count by _resource.name | count

max

Extracts the highest value for a set of values for a specified source field. Returns one field if no “by <FIELD>” is specified. Otherwise, an average for each of the specified fields is returned.

Syntax

* | max(<FIELD>)

Examples

Example: 1 The following example searches for numbers in the 0-9 range in a four-digit sequence pattern. If found, the highest number is extracted and added to the log event as a field called “number”.

* | parse / ([0-9]{4}) / as number | num(number) as number2 | max(number2)

min

Extracts the lowest value for a set of values for a specified source field. Returns one field if no “by <FIELD>” is specified. Otherwise, an average for each of the specified fields is returned.

Syntax

* | min(<FIELD>)

Examples

Example: 1 The following example searches for numbers in the 0-9 range in a four-digit sequence pattern. If found, the lowest number is extracted and added to the log event as a field called “number” .

* | parse / ([0-9]{4}) / as number | num(number) as number2 | min(number2)

sum

Calculates the sum of the values of specified fields, and optionally groups the calculations by one or more fields.

Syntax

Counts the number of events in the specified time range:

* | sum(<FIELD>) (as <ALIAS>) by <FIELD> (, <FIELD>)

Examples

Example 1: Calculate the sum of the size field for each unique resource name, and sort the results.

* | sum(_size) by _resource.name | sort by _sum desc

Processing Operators

parse

Extracts the contents of a specified source field into a new field based on a string pattern match. The new field is added as a field:value pair to the log metadata.

Syntax

* | parse <SRC-FIELD> <PATTERN> as <FIELD1> (, <FIELDN>)

• If the source field, <SRC-FIELD>, is not specified, the message field is used by default.
• <PATTERN> is a string with wildcards or a Regex.
• If parsing multiple fields, the number of field names must match the pattern.

Examples

Example 1: The following example uses a wildcard to search words containing “unix” in the message field and extracts the parts before “unix”. If found, the extracted words are added to the log event as a field called “unix”.

* | parse "* unix" as unix

Example 2: The following example uses a Regex search. The query looks for the phrase “Failed to pull image” in the message field, and anything that is not a space, tab, or newline. If found, the image name will be parsed out and added to the log event as a field called “image”.

"Failed to pull image" | parse /Failed to pull image "(\S+)"/ as image

You can then search for a specific image using the query image=<imagename>.

where

Limits the result of an operation by a specified condition. Can be used on log processing, or on aggregation. When used in processing it limits the results of the logs. When used on aggregation it limits the aggregated result. If a source field is not specified, the operation defaults to the message field.

Syntax

Extracts a specified value from the Source field. The following example limits the logs and not the aggregation. You must use an aggregation function to limit the aggregation. 

...where <FIELD>="value"

Examples

Example 1: The following example searches for the exact phrase “unix” in the event messages. If found, the “unix” part will be extracted and added to the log event as a field called “unix”.

* | parse /(unix)/ as unix | where unix="unix"

concat

Concatenates multiple fields or expressions together in the log query.

Syntax

Concats field 1 and field 2 together

concat (<FIELD1>, <FIELD2>)

Examples

• Concatenates abc and xyz fields together
  • concat(abc, xyz)
• Concatenates abc and xyz fields together adding hyphen in between them
  • concat(abc, “-“, xyz)
• Concatenates expressions result (10) to abc field’s value
  • concat(abc, 5+5)

Formatting Operators

as

Displays the column name as the alias value.

Syntax

... as <ALIAS>

Examples

Example 1: Calculate the sum of the size field for each unique resource name, and sort the results.

* | sum(_size) as log_volume by _resource.name | sort by log_volume desc

limit

Limits the result set to a fixed number of results. The limit operator can only be used after an aggregation operation.

Syntax

* | limit <number>

Examples

Example 1: Count the number of events for each unique resource and show the first 15 results.

* | count by _resource.name | sort by _count desc | limit 15

sort

Sorts the result by a set of fields, ascending or descending.  The sort operator can only be used after an aggregation operation. By default, results are sorted in descending order by timestamp and ID.

Syntax

* | sort by <FIELD> desc

Examples

Example 1: When sorting by an aggregation operator, include a leading underscore on the operator.

* | count by _resource.name | sort by _count desc

Example 2: When sorting by an alias, no underscore is needed.

* | sum(_size) as log_volume by _resource.name | sort by log_volume desc

More Examples

Various examples of how to use advanced search operators.

Example 1:  Displays the list of resource group names with a count of logs and the sum of log size, sorted by count of logs, limited to the top 25 resource group names.

* | count(_size), sum(_size) by _resource.group.name | sort by _count desc | limit 25

Example 2: Parses the image name from the message and stores the name in image field (image=imagename). Then displays the unique image names and sorts by imagename with the most occurrences in the specified time range.

“Failed to pull image” | parse /Failed to pull image "(\S+)"/ as image | count by image | sort by _count desc | limit 15

Example 3: Displays a timeslice aggregation grouping search results into 5s buckets counting the number of hits within each bucket. 

* | bucket(span=5s) | count by _bucket | sort by _bucket desc

Example 4: Displays the aggregated log usage volume by resource name, sorted by descending.

* | sum(_size) by _resource.name | sort by _sum desc

Example 5: Generates a report showing how many logs a specific resource is generating. This query shows the top 25 resources with a time range set to 30 days. If you want a specific resource or group, replace * with _resource.name=hostname. To generate a report with the results, select the Download Aggregate CSV icon.

* | count(_size), sum(_size) by _resource.name | num(_sum/1000000000) as GB | num(_sum/_count) as avg_size | sort by GB desc | limit 25