Syntax and Structure for chart/stats
The chart/stats operators apply aggregate operations over a collection of events and/or results from an operator chaining query. They display search results for specified fields in the Search Results table.
For additional information about the construction and usage of the chart/stats operators, see the chart/stats overview, Aggregate Functions for chart/stats, and Cheat Sheet for chart/stats.
Syntax
...| chart <function> ( <decorator> field ) as <customName> , …, by <field>, <…moreFields> span <timeField> = timeBucket ...| stats <function> ( <decorator> field ) as <customName> , …, by <field>, <…moreFields> span <timeField> = tiemBucket
Operator name: | chart , | stats
The user can use chart or stats to set an aggregation query in the search bar. Chart and stats keywords are considered synonyms, and the behavior and results will be the same.
Functions: <function> ( <decorator> field ) as <customName>
Chart and stats operators can apply the following list of functions over a group of events:
Input within < > (angle brackets) indicates users may enter their own input:
... | rename <source_name> as <NewSourceName>
| Aggregate Function | Dataype |
|---|---|
| Count Count Distinct |
All datatypes |
| Min, Max | Numbers |
| Average (Agg.), Standard deviation (Stdev), Sum |
Numbers |
| Earliest, Latest | Dates |
The following examples highlight several scenarios for how events are counted.
-
Example that counts the names and sum of all Bytes in for every group of events, organized by Category Outcome:
...| chart count (name), sum(bytes in) by category outcome
This example organizes the events in groups, which have the same category outcome, then it counts the names and sums all the "bytes in" values of every group. Note that the functions enclose the field that is going to be used as an aggregation parameter for every group. More than one function should be separated by a comma. The search results table displays one column per aggregate function.
-
Example that counts the names and sum of all Bytes in for every group of events, organized by Category Outcome, and Destination Hostname:
...| chart count (name), sum(bytes in) by category outcome, destination hostname
This example organizes the events into groups with the same category outcome and same destination hostname. It then counts the names and sums all the "bytes in" values of every group. Note that the group fields are separated by a comma.
-
Example that counts the names and sum of all Bytes in for every group of events organized by Category Outcome and Destination Hostname: It changes the default name for the count result to MyCount.
...| chart count (name) as MyCount, sum(bytes in) by category outcome, destination hostname
The default name for the columns created in chart and stats operators can be renamed.
-
Example that counts the distinct names and sums all Bytes in for every group of events organized by Category Outcome:
...| chart count (distinct name), sum(bytes in) by category outcome
Using distinct flag for the count function makes the operator count only different names. Names that are repeated are not counted again.
Aliases that contain special characters have the following syntax restrictions:
| Special Characters | Restrictions | Examples |
|---|---|---|
| +, *, &, !, - , = , <, >, | | Need to be enclosed in single/double quotes when they are reused and the search works as expected. | | rename file path as 'FP+DEV' | chart count ( 'FP+DEV' ) by 'FP+DEV' |
| @, #, +, ?, /, ^, [], {}, _ , *, ., ~, $, % | Do not need to be enclosed in single/double quotes when they are reused and the search runs as expected. | | rename file path as 'FP$DEV' | chart count ( FP$DEV ) by FP$DEV |
| \ |
When a backslash is used in an alias name, add an additional backslash \ to escape the character. It does not need to be enclosed in single/double quotes when it is reused and the search runs as expected. The outcome field name should show only one backslash. |
| rename file path as 'FP\\DEV' | chart count ( FP\\DEV ) by FP\\DEV |
Grouping Events
The chart/stats pipe operators group events based on two strategies: by field and by time bucket. By default chart and stats operators need at least one "by field" in order to group data.
The chart/stats Aggregate Functions are discussed in detail in a separate topic.
The following examples are based on a simplified version of the Events Table below.
-
Example of grouping by the Name field: The events displayed in the table below will be grouped into 4 elements because there are only 4 different values for the files Name.
| Name | Number of Events |
|---|---|
| ArstType | 1 |
| BCtType | 2 |
-
Example of grouping by more than one field: In this scenario, the chart/stats operator will group the events based on the combination of the Name and Category Outcome fields. Grouping by more than one field will create summary rows whose values of Name and Category Outcome are organized in different combinations.
| Name | Category Outcome |
Number of Events |
|---|---|---|
| ArstType | /Failure | 1 |
| BCtType | /Failure | 1 |
| BCtType | /Interrupt | 1 |
| FtType | /Success | 1 |
| MSType | /Interrupt | 2 |
-
Example of a Time bucket grouping by 1h: Time bucket grouping organizes events based on time buckets (periods of time used to group events for a specific time scale). For example, if the time bucket is 1 hour, all events are going to be grouped by an hourly scale. This means that events that occurred in different minutes are considered to be part of the same group. Chart and stats operators can create bucket scales of seconds, minutes, hours, and days. Combinations of time bucket, such as 2d1h, are also allowed.
| Name | Number of Events |
|---|---|
| 12/29/22 10:00:00/000 |
3 |
| 12/29/22 11:00:00.000 |
2 |
| 12/29/22 12:00:00.000 |
1 |
Time bucket trouping and field grouping can be used together. However, the time bucket grouping is executed in the first group, then the operator is applied to the remaining group rules as defined by the fields.
"Group by" Expressions
Group by expressions: by <field>, <…moreFields>
The group by expressions are separated by commas. The fields used in this part of the query work to organize the events. There is a hierarchy for the fields used.
... | chart count (name), sum(bytes in)
This organizes the query syntax by category outcome, destination and hostname. The operator creates groups based on the combinations of category outcome and destination hostname.
Time bucket grouping: span <timeField> = tiemBucket
This is another way to apply grouping based on time buckets. A time bucket can be set using the following syntax:
Simple scale: 1d (one day), 2m (two minutes), 35s (thirty five seconds)
Complex scale: 1d2m (one day and two minutes). Complex scales must be organized with the highest order period of time to the lowest.
| Time Bucket | Representation |
|---|---|
| Days | 1d, 20d |
| Hours | 4h |
| Minutes | 2m |
| Seconds | 12s |
Allowed syntax to define span expressions (grouping by time)
...| chart count (name), sum(bytes in) by category outcome span 1h (Date-picker field time is used)
...| chart count (name), sum(bytes in) by category outcome span = 1h (Date-picker field time is used)
...| chart count (name), sum(bytes in) by category outcome span Normalized event time = 1h
Rules and exceptions
-
All chart/stats commands accept only one field in the input.
-
The input field must contain a column that exists in the database.
-
Span expressions can be used only once if the time column is available:
...| chart count (name), sum(bytes in) by category outcome | chart count (name), sum(bytes in) by category outcome span 1h
In the example above, the second span expression cannot be used since the first pipe expression removed the time field from the fieldset.
...| top 5 name | chart count (name), sum(bytes in) by category outcome span 1h
In the example above, the span expression cannot be used since the first pipe expression removed the time field from the fieldset.
For information about other operators, functions, and syntax requirements, see Use an Operator in the Query.