chart/stats

The chart/stats operators display search results for specified fields as fields in the Search Results table.

Syntax

Aggregation Functions

The Span Function

How Do I Use This?

Syntax

...| chart count by field1, field2, field3 ... [span [time_field]=time_bucket]
...| chart {{sum | avg | min | max | } (field)}+ by field1, field2, field3 ...[span [time_field]= time_bucket]
...| chart {function (field)} as new_field_name by field [span [time_field]

For simple syntax examples, see below.

where:

field, field1, field2 are the names of event fields used in system queries.
time_bucket is the bucket size (in any combination) used for grouping events. Use d for day, h for hour, m for minute, and s for seconds. For example, 2h, 5d, 1m.
function is one of the following: count, sum, avg , min, max, latest or earliest..
new_field_name is the name you want to assign to the field (field of data) in which the function's results are displayed. For example, Total.

All chart/stats commands accept only one field in the input. For example, | stats count (device vendor) by...
The input field must contain a field that exists in the database.
If multiple fields are specified, separate the field names using commas without any spaces.
The function input field must contain numeric values for chart/stats sub-operators that are mathematical operations (sum, avg, min, max).
The mathematical operators avg and mean are equivalent.

"by" Statements

The chart/stats operators and eval, require a "by" statement. For example: eval | chart sum(AgentSeverity) by Destination HostName

Specify a field name after the "by" statement. For example: "by deviceVendor"
Fields in a "by" statement should be separated by comma. For example: ... | chart count(Name) by deviceEventCategory , name

Simple Syntax Examples

Grouping events by a field and counts how many names each group has.
```
...| chart count(name) by Destination Hostname
```

Sum of Bytes In for every group of events. These events (rows) are grouped by the field Destination Hostname.
```
...| chart count(Bytes In) by Destination Hostname
```

The events (rows) are grouped using the default time field selected, distributing the events in groups of 1 hour. The events that matches the same time bucket and the same Agent Severity will be organized in the same group. Then returns how many names contains every group.
```
...| chart sum(name) by Agent Severity span = 1h
```

Aggregation Functions

Aggregation functions only work on numeric fields. The specified fields must contain numeric values. If a field you specify is of the wrong data type, you will receive an error message like the following: "java.lang.NumberFormatException".

You can include multiple functions in the same chart/stats command. When doing so, separate each function with a comma, as shown:

... | chart count (Name) by Destination Hostname, sum(deviceCustomNumber3) by deviceEventClassId

When you include multiple functions, the search results table displays one field per function.

You can use the “as new_field_name” clause to name any field resulting from the aggregation functions, as shown:

...| chart sum(deviceCustomNumber3) as TotalStorage, avg(deviceCustomNumber3) as AverageStorage by deviceCustomNumber3

Instances of "new_field_name" should be changed by the alias name of the aggregation result.

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

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

The Span Function

In addition to grouping events defined by eval operators, the span function groups events by a time field (such as EventTime or deviceReceiptTime) and a time bucket.

The span function can only accept three fields (Normalized Event Time, Device Receipt Time, and Database Receipt Time) before the equals sign, for example:
- |chart sum(Agent Severity) by Destination hostName span Normalized Receipt Time = 1h
- |chart sum(Agent Severity) by Destination hostName span = 1h
- |chart sum(Agent Severity) by Destination hostName span 1h
The span operator is not allowed after an aggregation operator.

The span operator must use an equal sign or a supported field name. For example: span Normalized Event Time = 1h

The span operator only accepts timestamp fields. For example: span Normalized Event Time = 1h.

A span's time bucket must use one of the following types of values: numberd, numberh, numberm, and numbers
By default, the chart/stats command displays the first 10 unique values. If the span function creates more than 10 unique groups, not all of them will be displayed.

When span is included in a query, search results are grouped by the specified time bucket. For example, if span=5m, the search results will contain one row for each 5-minute span. If there are no events within a specific 5-minute span, that row will be empty.
The span function assumes a 24-hour day, all year long. If span=1d or 24h, on the day of the daylight savings time change, the event time indicated by the span_eventTime field in the search results will be different from the previous day by one hour. On the day when there are 23 hours in a day (in March), the span bucket will still include events from the last 24 hours. Similarly, on the day when there are 25 hours in the day (in November), the span bucket will include events from the last 24 hours.

How Do I Use This?

Aggregation Function Examples

Use the default chart setting (Column Chart) to specify multiple fields. In this example, a count of unique groups of deviceEventCategory and name fields is displayed and plotted.
```
... | chart count(Name) by deviceEventCategory name
```

Simple query using 1 (min) aggregation function and one group by field (min)

... | [chart | stats] aggregation_function (field_name) [as alias_name] by field_name

 chart count (Name) by Destination Hostname

Query using 1 to N aggregation functions, the result and 1 to N group by field (min)

... | [chart | stats] [aggregation_function (field_name), ... aggregation_function (field_name)] [as alias_name] by field_name

chart count (Name), sum(Agent severity) by Destination Hostname

Query using 1 to N aggregation functions, the result and 1 to N group by field (min)

... | [chart | stats] [ aggregation_function (field_name), ... aggregation_function (field_name)] [as alias_name] by field_name [, ...field_name]

chart count (Name), sum(Agent severity) by Destination Hostname, Name

Adding alias in every aggregation function

... | [chart | stats] [ aggregation_function (field_name), ... aggregation_function (field_name)] [as alias_name] by field_name [, ...field_name ]

chart count (Name) as alias_name1, sum(Agent severity) as alias_name2 by Destination Hostname, Name

Span Function Examples

If time stamp is specified for span input, it does not use parenthesis: The correct query would be:
```
...| chart count(Name) by deviceEventCategory span deviceReceiptTime = 5m
```

Destination Hostname is the time field and one hour is the time bucket:

 chart count (Name), sum(Agent severity) by Destination Hostname, Name span 1h

deviceReceiptTime is the time field and 5m (5 minutes) is the time bucket:

...| chart count(Name) by deviceEventCategory span (deviceReceiptTime) = 5m

Span is used to organize events by time frame:

... | [chart | stats] [ aggregation_function (field_name), ... aggregation_function (field_name)] [as alias_name] by field_name [, ... field_name ] span = n[s|m|h|d]|m|h|d]

If a time field is not specified for the span function , EventTime is used as the default. For example, the following query uses EventTime by default:
```
...| chart count(Name) by deviceEventCategory span = 5m
```

Grouping with span is useful in situations when you want to find out the number of occurrences in a specific time span.

If you want to find out the total number of incoming bytes every 5 minutes on a device, you can specify a span of 5m. This example assumes that deviceCustomNumber1 field provides the incoming bytes information for these events.
```
...| chart sum(deviceCustomNumber1) by hostName span 5m
```
You want to see a sum of events by hostName in one week of events, listed by day. When a span field is specified in conjunction with an event field, the unique sets of all those fields are used for grouping.
```
...| chart sum (baseEvents) by hostName span = 1d
```

The following example uses deviceCustomNumber and deviceAddress in conjunction with span to find out the number of events (using deviceCustomNumber3) from a specific source (using deviceAddress) in one (1) hour:
```
...| chart sum(deviceCustomNumber3) by deviceAddress span=1h
```

When span is included in a query, search results are grouped by the specified time bucket. For example, if span=5m, the search results will contain one row for each 5-minute span. If there are no events within a specific 5-minute span, that row will be empty.
The span function assumes a 24-hour day, all year long. If span=1d or 24h, on the day of the daylight savings time change, the event time indicated by the span_eventTime field in the search results will be different from the previous day by one hour. On the day when there are 23 hours in a day (in March), the span bucket will still include events from the last 24 hours. Similarly, on the day when there are 25 hours in the day (in November), the span bucket will include events from the last 24 hours.

For information about other operators, functions, and syntax requirements, see Use an Operator in the Query.