eventstats
Use the eventstats
command to obtain overall summary
statistics, optionally grouped by fields, on properties of groups identified by any grouping
command such as stats
, link
, or
timestats
. Its output will include one field for each
aggregation.
Syntax
The trend aggregate operator is not permitted with
eventstats
.
eventstats <stats_function> (<field_name>) [as <new_field_name>] [, <stats_function> (<field_name>) [as <new_field_name>]]* [by <field_name> [, <field_name>]*]
Parameters
The following table lists the parameters used with this command, along with their descriptions.
Parameter | Description |
---|---|
|
Use this parameter to specify the field according to which you want the results to be grouped. |
|
Use this parameter to specify the new name for the
field after applying the |
For the supported functions, see Functions, Positional Functions, and Time Series Analysis Functions.
Functions
The following table lists the functions available with this command, along with their examples.
Function | Examples |
---|---|
Values Lists the first 10 values for a particular field with optional delimiter. Can be applied to any field data type. |
|
Unique Lists the first 10 unique values for a particular field with optional delimiter. Can be applied to any field data type. |
|
Earliest Return the eldest non-null value for the specified field. Null will be returned if field is completely empty for a particular queries results. |
|
Latest Return the most recent non-null value for the specified field. Null will be returned if field is completely empty for a particular queries results. |
|
Average Note: This function is supported only for numeric fields. |
|
Count Note: This function uses semantics similar to sql; that is, count returns the count for all rows; however, count(field) returns the count for all rows where field is not null. |
|
Distinct Count |
|
Maximum Note: This function is supported only for numeric fields. |
|
Median Note: This function is supported only for numeric fields. |
|
Minimum Note: This function is supported only for numeric fields. |
|
Percentage Note: This function is supported only for numeric fields. |
|
Sum Note: This function is supported only for numeric fields. |
|
Standard Deviation Note: This function is supported only for numeric fields. |
|
If Null Changes the value if the value is null. Note: This function is only for eventstats, geostats, stats and timestats (not after link). |
|
Positional Functions
The following table lists the functions that are unique to this command, along with their examples.
Function | Examples |
---|---|
first Retrieves property value from the first row, as defined by the current sort order, within the retrieved result. |
Syntax:
|
last Retrieves property value from the last row, as defined by the current sort order, within the retrieved result. |
Syntax:
|
nthval Retrieves property value from the nth row, as defined by the current sort order, within the retrieved result. |
Syntax:
|
lag Retrieves property value from a row at a given offset prior to the current row. Default offset is 1. |
Syntax:
|
lead Retrieves property value from a row at a given offset after the current row. Default offset is 1. |
Syntax:
|
rownum Assigns a unique number sequentially, starting from 1, as defined by the current sort order to each row within the retrieved result. |
Syntax:
|
Time Series Analysis Functions
The following table lists the functions for time series analysis, along with their examples.
Function | Examples |
---|---|
peak Retrieves property value with peak magnitude, within the retrieved result. A higher value of magnitude indicates larger absolute values. |
Syntax:
|
peakscore Retrieves property value with normalized peak score between 0 and 1, within the retrieved result. The score can be used to compare the peaks, with the highest peak getting 1 as the score, and all other values between 0 and 1. |
Syntax:
|
valley Retrieves property value with valley magnitude, within the retrieved result. A lower value of magnitude indicates smaller absolute values. |
Syntax:
|
valleyscore Retrieves property value with normalized valley score between 0 and 1, within the retrieved result. The score can be used to compare the valleys, with the least valley getting 0 as the score, and all other values between 0 and 1. |
Syntax:
|
Use the peak and peakscore functions to analyze sequential data in Link Visualization for peak magnitude and a normalized score between 0 and 1. For example, the following query highlights the highest peaks in user response time, using the Duration field from the Access Logs Log Source:
'Log Source' = 'OCI API Gateway Access Logs'
| link span = 5minute Time, Server
| stats avg(Duration) as 'Avg. Duration'
| sort Server, 'Start Time'
| eventstats peak('Avg. Duration') as 'Peak Magnitude',
peakscore('Avg. Duration') as 'Peak Score' by Server
| highlightgroups priority = high
[ * | where 'Peak Score' > 0.9 ] as 'High Response Time - Needs Attention'
This feature can be used for searching and grouping of time series data, like identifying all Out of Memory events that happened after a spike. A higher value for magnitude indicates larger absolute values. Score can be used to compare peaks, with the highest peak getting 1 as the score, and others a value between 0 and 1.
For examples of using eventstats
command in typical
scenarios, see:
Group all the fatal logs by transaction, and get the overall average elapsed time across all the groups:
severity = fatal | link 'Transaction ID' | stats avg('Elapsed Time (System)') as 'Average Elapsed Time' | eventstats avg('Average Elapsed Time') as 'Overall Average Elapsed Time'
severity = fatal | stats avg('Elapsed Time (system)') as 'Average Elapsed Time' | eventstats avg('Average elapsed time') as 'Overall Average elapsed time'
Group all the fatal logs by entity type and transaction, and get the overall average elapsed time across all the groups with the same entity type:
severity = fatal | link 'Entity Type', 'Transaction ID' | stats avg('Elapsed Time (System)') as 'Average Elapsed Time' | eventstats avg('Average Elapsed Time') as 'Overall Average Elapsed Time' by 'Entity Type'
severity = fatal | stats avg('Elapsed Time (System)') as 'Average Elapsed Time' by 'Entity Type', 'Transaction ID' | eventstats avg('Average Elapsed Time') as 'Overall Average Elapsed Time' by 'Entity Type'
In the Link visualization, add a number to each row:
'Log Source' = 'Database Alert Logs' and Label != null and Entity = MyDB
| rename Entity as Database
| link span = 1minute Time, Database, Label
| sort Database, 'Start Time'
| eventstats rownum as 'Row Number' by Database
Identify the last event using the row number:
'Log Source' = 'Database Alert Logs' and Label != null and Entity = MyDB
| rename Entity as Database
| link span = 1minute Time, Database, Label
| sort Database, 'Start Time'
| eventstats rownum as 'Row Number' by Database
| addfields
[ * | where Label = 'Abnormal Termination'
| eventstats last('Row Number') as 'Crash Row'
]
Identify the previous and next events of a selected event:
'Log Source' = 'Database Alert Logs' and Label != null and Entity = MyDB
| rename Entity as Database
| link span = 1minute Time, Database, Label
| sort Database, 'Start Time'
| addfields
[ *
| where Label != null
| eventstats lag(Label) as 'Previous Event',
lead(Label) as 'Next Event'
]