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>]
[ORDER BY <attribute> [ASC|DESC] [, ...]]
[SINCE <start time>]
[UNTIL <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.
• result ordering: Optional ordering of grouped or aggregated results using ORDER BY.
• timeframe: The same SINCE ... UNTIL ... 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.

ORDER BY is supported for SUMMARIZE results. This is commonly used with GROUP BY to rank highest/lowest groups. If ORDER BY is not specified, grouped result ordering is not guaranteed.

Operations

COUNT

Count events matching the filter criteria.

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

SUMMARIZE COUNT #iam.*

Example result:

{"count": 4271}

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

Example result:

{"count_distinct": 87}

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 'example.com'
GROUP BY %email
SINCE 1mo

Example results:

[
  {"%email": "[email protected]", "count": 142},
  {"%email": "[email protected]", "count": 89},
  {"%email": "[email protected]", "count": 37}
]

You can order grouped results explicitly:

SUMMARIZE COUNT authentication
WITH %email CONTAINS 'example.com'
GROUP BY %email
ORDER BY count DESC