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>]
[TIMEOUT <seconds>]

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.
timeout: A deadline in whole seconds, as in QUERY. See Time-Limiting SUMMARIZE.

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

Time-Limiting SUMMARIZE (TIMEOUT)

SUMMARIZE accepts the same TIMEOUT clause as QUERY. When the deadline passes, FSQL stops fetching records, terminates any outstanding connector queries, and answers with the statistic calculated over the records that arrived before the deadline. A SUMMARIZE that finishes before its deadline is unaffected.

SUMMARIZE COUNT authentication
WITH authentication.status_id = FAILURE
GROUP BY authentication.user.name
SINCE 7d
TIMEOUT 30

When a TIMEOUT cuts a SUMMARIZE short, FSQL reports it as a warning rather than an error, along with the number of records the statistic covers:

Results are partial because the 30s TIMEOUT was reached. Records summarized: 41822.

A statistic calculated from partial data describes only the records that arrived:

COUNT and SUM are lower bounds. The complete values are at least as large.
MIN, MAX, AVG, and COUNT DISTINCT describe only the records that arrived, and may differ in either direction from the complete values.
With GROUP BY, groups whose records hadn't arrived are missing, and the groups that are present may be incomplete.
If no records arrived before the deadline, the statistic has no value: null, or no groups with GROUP BY.

The deadline covers fetching records, including any subqueries in the filter. It doesn't cover calculating the final statistic from the records that arrived. The other rules for TIMEOUT apply as they do for QUERY.