Aggregation commands

fieldsSummary

The fieldsSummary command calculates the cardinality of field values that the specified fields have.

Parameters

Name	Type	Mandatory	Default	Description
`list<expression>`	list of identifiers	yes	N/A	A list of fields for which the cardinality is to be calculated.
extrapolateSamples	boolean	no	false	Flag indicating if the cardinality shall be multiplied with a possible sampling rate.
topValues	positive long	no	20	The number of top N values to be returned.

Syntax

fieldsSummary list<field> [, extrapolateSamples] [, topValues]
Example 1

In this example, the query fetches logs with a sampling ratio of 10000 and calculates the cardinality of values for the dt.entity.host field, providing the 10 top values encountered when extrapolating the value count by the sampling ratio.
```
1fetch logs, samplingRatio: 100000
2| fieldsSummary dt.entity.host, topValues: 10, extrapolateSamples: true
```
Query result

field rawCount count values
dt.entity.host
36
3,600,000
1 record
Example 2

In this example, the query fetches logs with the default sampling ratio for the environment and calculates the cardinality of values for the dt.entity.host field, providing 100 top values encountered. The calculated count is not multiplied by the sampling ratio, therefore it provides the real number of values encountered.
```
1fetch logs
2| fieldsSummary dt.entity.host, topValues: 100
```
Query result

field rawCount count values
dt.entity.host
3,976,697
3,976,697
2 records

field	rawCount	count	values
dt.entity.host	36	3,600,000	1 record

field	rawCount	count	values
`dt.entity.host`	`3,976,697`	`3,976,697`	`2 records`

makeTimeseries

Creates timeseries from the data in the stream. The makeTimeseries command provides a convenient way to chart raw non-metric data (such as events or logs) over time.

Syntax

makeTimeseries [by: { [expression, …] }] [, interval] [, bins] [, from] [, to] [, timeframe] [, time ,] [, aggregation ] [, default], [, by ]

Parameters

Name	Type	Mandatory	Default	Constraints	Description
aggregation	aggregation function	yes		Input values in the aggregation function need to be numeric.	The aggregation function that should be used to create the series.
default	numeric	no	null		The default value that should be used to fill gaps/empty buckets.
time	expression	no	timestamp	If the current data does not contain a field `timestamp` the parameter needs to be specified, referencing the time field.	The expression that provides the timestamp for a record, which will be assigned to a series bucket.
from	timestamp, duration	no	null	The actual series timeframe will be adjusted by the series alignment.	The start timestamp of the series.
to	timestamp, duration	no	null	The actual series timeframe will be adjusted by the series alignment.	The end timestamp of the series.
timeframe	timeframe	no	query timeframe	The actual series timeframe will be adjusted by the series alignment.	The timeframe of the series.
bins	positive integer	no	120	Must not be specified if interval is specified.	The number of buckets that should be created for the series.
interval	positive duration	no	Automatically calculated based on the number of bins.	Must not be specified if bins is specified.	The length of a bucket in the series.
by	list of expressions	no			The expressions the series should be split by.

Example 1

This basic example illustrates how the error logs for a given host can be observed over time.

1fetch logs
2| filter dt.entity.host=="HOST-1234567890" and loglevel=="ERROR"
3| makeTimeseries count()

Query result

timeframe start	timeframe end	interval	count()
26/07/2023, 10:38	26/07/2023, 12:41	1 min	65,125,109,64,62,36,65,83,36,81,74,39,77,50,74,76,59,39,86,119,65,70,69,39,70,71,44,68,59,50,79,62,49,67,64,41,74,63,45,81,57,52,74,64,34,76,69,40,72,71,42,69,75,43,76,74,28,65,72,33,64,73,41,78,64,44,81,69,35,63,60,48,77,66,40,82,112,45,67,75,34,74,76,30,79,38,105,69,69,42,61,77,43,65,63,38,73,66,49,66,68

Example 2

The following advanced example takes sell transactions recorded as bizevents and charts the total number of transactions, the number of high-volume transactions based on the conditional countIf() function and the maximum price by accountId. The interval parameter is used to define a fixed chart resolution of 1 day.
```
1fetch bizevents
2| filter event.type == "com.easytrade.sell-assets"
3| makeTimeseries count(), high_volume = countIf(amount>=100 and amount <=10000 ), max(price), by:accountId, interval:1d
```

Aggregation functions

Six aggregation functions are available to use with the makeTimeseries command. These functions are: min, max, sum, avg, count and countIf.

Syntax

These functions have the following syntax:
- sum(expression [, default] [, rate])
- avg(expression [, default] [, rate])
- min(expression [, default] [, rate])
- max(expression [, default] [, rate])
- count([default] [, rate])
- countIf(expression [, default] [, rate])

Parameters

Name	Type	Mandatory	Default	Description
expression	expression	yes		The expression the series should be created for.
default	number	no	null	The default value that should be used to fill gaps/empty bins.
rate	duration	no		The duration that shall be used to adjust the bin values using the following formula: (binValue / interval) * rate.

Example 1

1fetch bizevents | makeTimeseries avg(amount), by: session.id

Example 2

1fetch bizevents | makeTimeseries min(amount, default: -1), by: session.id

Example 3

1fetch logs | makeTimeseries count(), by: status

summarize

Groups together records that have the same values for a given field and aggregates them.

Syntax

| summarize [SummarizeParameters] [[field =] aggregation [, ...]] [,by:{[field=]GroupExpression[, ...]}]

Example 1

The below is an example of a simple aggregation.

1fetch events, from:now()-1m
2| summarize event_count = count()

Example 2

The below is an example of an advanced aggregation.

1fetch logs, from:now()-1m
2| summarize severe = countIf(loglevel=="SEVERE"),
3          errors = countIf(loglevel=="ERROR"),
4          warnings = countIf(loglevel=="WARNING"),
5          by:dt.entity.host

Example 3

1fetch logs, from:now()-10m
2| filterOut loglevel == "NONE"
3| summarize count(),
4          by:{
5               loglevel,
6               dt.entity.host
7             }

Using aliases in the summarize command

You can set alias names to attributes within the summarize command, according to the pattern presented in the below example.

Example 4
```
1fetch logs
2| summarize count=count(), by:{level=loglevel}
3limit 3
```
In this query, the count() attribute is given the count alias name, while the loglevel attribute is given the level alias name.

Query result

level count
ALERT
10
DEBUG
110085
ERROR
99332

level	count
ALERT	10
DEBUG	110085
ERROR	99332

Empty aggregation result

If you don't provide an input record and don't use the by clause, the summarize command will return a single empty aggregate result for all the aggregate functions.

Example 5

In this example, all filtered out results return an empty aggregation record, with count returning 0 and all other fields returning null.
```
1data record(a = 1, b = "string1"), record(a = 2, b = "string2"), record(a = 3, b = "string1")
2| filter a > 3
3| summarize count(), sum(a), avg(a), min(a), max(a), collectArray(b), collectDistinct(b), percentiles(a, 60), takeAny(a)
```
Query result

count() sum(a) avg(a) min(a) max(a) collectArray(b) collectDistinct(b) percentiles(a, 60) takeAny(a)
0

Example 6

In this example, empty aggregation result is shown, even though the count function isn't used.

1data record(a = 1, b = "string1"), record(a = 2, b = "string2"), record(a = 3, b = "string1")
2| filter a > 3
3| summarize sum(a), avg(a), min(a), max(a), collectArray(b), collectDistinct(b), percentiles(a, 60), takeAny(a)

Query result

sum(a)	avg(a)	min(a)	max(a)	collectArray(b)	collectDistinct(b)	percentiles(a, 60)	takeAny(a)

Aggregation commands

fieldsSummary

makeTimeseries

summarize

Related topics