Analytics Functions

FSQL can be used to calculate summary statistics with the SUMMARIZE command.

For those accustomed to SPL, STATS is an alias to SUMMARIZE.

SUMMARIZE Syntax

SUMMARIZE <operation>
[<fields to summarize>]
[WITH <filter conditions>]
[GROUP BY <fields to group by>]
[AFTER <start time>]
[BEFORE <end time>]
[FROM <connectors>]

operation: The desired summary statistic, one of COUNT, COUNT DISTINCT, MIN, MAX, AVG, or SUM.
fields to summarize: An event or attribute selection to operate on, if applicable, described just like in the QUERY command.
filter conditions: A filter specification, just as is used in QUERY commands.
fields to group by: A list of scalar attributes to group by.
timeframe: The same BEFORE ... AFTER ... timeframe specification as is used in QUERY commands.
connectors: The same FROM specification as is used in search queries.

NOTE: the selection, filter, and grouping sections can be limited to the INTERSECTION of events across each section.

Operations

COUNT

Count events matching the filter criteria.

SUMMARIZE COUNT [<event-list> ] ...

SUMMARIZE COUNT #iam.*

Note: only events will be counted, no matter which attributes are selected for the COUNT operation.

COUNT DISTINCT

Count distinct values across the selected attributes matching the filter criteria.

SUMMARIZE COUNT DISTINCT [<attribute-list>] ...

SUMMARIZE COUNT DISTINCT authentication.src_endpoint.ip

Note: all attributes must be primitive attributes. Counting objects is not supported at this time.

MIN

Identify the minimum value matching criteria.

SUMMARIZE MIN [<attribute-list>] ...

SUMMARIZE MIN @cvss.base_score

Note: this function only operates on numeric fields. Enumerations are not supported at this time.

MAX

Identify the maximum value matching criteria.

SUMMARIZE MAX [<attribute-list>] ...

SUMMARIZE MAX @cvss.base_score

Note: this function only operates on numeric fields. Enumerations are not supported at this time.

AVG

Identify the average (mean) value for fields matching criteria.

SUMMARIZE AVG [<attribute-list>] ...

SUMMARIZE AVG @cvss.base_score

Note: this function only operates on numeric fields. Enumerations are not supported at this time.

SUM

Calculate a total value for fields matching given criteria.

SUMMARIZE SUM [<attribute list>] ...

SUMMARIZE SUM #network.count

Note: this function only operates on numeric fields.

Grouping

Results can be grouped by the values of fields in records matching the filter conditions, similar to grouping in SQL.

>>> SUMMARIZE COUNT authentication with %email contains 'query.ai' group by %email SINCE 1mo
[email protected]: 35
[email protected]: 22
[email protected]: 15
[email protected]: 28
[email protected]: 11