Cheat Sheet for chart/stats

The flexibility of the chart/stats operators helps you construct powerful queries. Here are some simplified examples for using them.

I want to count occurrences of a field.

Group the events by a field and count how many names are in each group.

...| chart count (name) by Destination Hostname

Group the events by a field and count how many unique (distinct) names are in each group.

...| chart count (distinct name) by Destination Hostname

I want to work with mathematical operations.

I want to see the summation of Bytes In for every group of events. The events/rows are grouped by Destination Hostname.

...| chart sum (Bytes In) by Destination Hostname

I want to see the average of Bytes In for every group of events. The events/rows are grouped by Destination Hostname.

...| chart avg (Bytes In) by Destination Hostname

I want to see the minimum of Bytes In for every group of events. The events/rows are grouped by Destination Hostname.

...| chart min (Bytes In) by Destination Hostname

I want to see the maximum of Bytes In for every group of events. The events/rows are grouped by Destination Hostname.

...| Chart max (Bytes In) by Destination Hostname

Use date functions to understand the timing of events.

Get the date of the oldest (earliest) event/row for every group of events/rows. The events/rows are grouped by Destination Hostname.

...| chart earliest (Normalized event time) by Destination Hostname

Get the date of the newest (latest) event/row for every group of events/rows. The events/rows are grouped by Destination Hostname.

...| chart latest (Normalized event time) by Destination Hostname

I want to use a synonym for a chart operator.

The chart operator can be replaced by stats during the execution of a queries.

...|chart count (name) by Destination Hostname

I want to use aliases of aggregated fields.

Operator Chaining Levels: The level is a number that reflects the operator chaining level of the query. Every level can be identified by how many pipes symbols are in the query.

Destination Hostname is not null | eval test = abs (Bytes in) | chart count (name) by Destination Hostname

Level 0

Level 1 (first pipe)

Level 2 (second pipe)

The following is an example of a label's structure of an aggregate field.

...| chart count (name) by Destination Hostname 

The field will be named by default as: function_field_(Operator_chaining_level)

Aliases for aggregate fields: This can be used to replace the default name of an aggregate field and to provide a meaningful description of the new field.

...|chart count (name) as NumberOfNames by Destination Hostname

I want to use combined functions for the same query.

Several functions can be applied to every group of events/rows. For every group:

• Retrieve how many Names are in each group.

• Retrieve the summation of Bytes In.

• Retrieve the newest event/row based on Normalized Event Time.

...| chart count (name), sum (Bytes In), latest (Normalized event time) by Destination Hostname

I want to use one aggregate field and more than one "group by" field.

The events or rows that share the same Destination Hostname and the same Agent Severity will be organized in the same group.

...|chart count (name) by Destination Hostname, Agent Severity

I want to combine functions in the same query and use more than one "group by" field.

For every group of events/rows that share the same Agent Severity and the same Destination Hostname:

• Get how many Names every group has.

• Get the summation of Bytes In.

• Get the newest event/row based in Normalized Event Time.

...|chart count (name), sum (Bytes In), latest (Normalized event time) by Agent Severity, Destination Hostname

I want to use an alias for more than one aggregate field and more than one "group by" field.

For every group of events or rows that share the same Agent Severity and the same Destination Hostname:

• Get the summation of Bytes In and rename the field as TotalBytesIn.

• Get the newest event/row based in Normalized Event Time and rename the field as: LastRegister

...| chart sum (Bytes In) as TotalBytesin, latest (Normalized event time) as LastRegister by Agent Severity, Destination Hostname

I want to group events by time buckets.

Time bucket expressions require the fieldset used in the query to contain at least one of the following time fields:

• Normalized event time

• Device Received Time

• Database Receipt Time

How does a time bucket work? Based on an input table that contains the one of the fields described above, the algorithm organizes the events based on the time bucket. This bucket consists of a number and a time unit, (second (s), minute (m), hour(h), day (d)).

Bucket timing: Bucket timing takes this value and organizes the rows, based on the distribution of time. The following are examples of time bucket usage.

• 1h: Rows will be grouped in chunks that are distributed hourly.

• 2d: Rows will be grouped in chunks that are distributed every 2 days.

• 1s: Rows will be grouped in chunks that are distributed every 1 second.

Combination of unit times: Combinations can be addressed as the following examples:

• 2d1h: Rows will be grouped in chunks that are distributed every 2 days and 1 hour.

• 2d1s: Rows will be grouped in chunks that are distributed every 2 days and 1 second.

• 1h1s: Rows will be grouped in chunks that are distributed every 1 hour and 1 second.

The units respect the order of magnitude of time. For example, in 1s1h, every 1 second and 1 hour is not valid. The correct syntax is 1hs.

Examples of time buckets

The events/rows are grouped using the field N.E.T, distributing the events in groups of 1 hour. The events that match the sames time bucket and the same Agent Severity will be organized into the same group. Then returns how many names contains every group.

...| chart count (name) by Agent Severity span Normalized Event Time = 1h

The events/rows are grouped using the default time field selected, distributing the events in groups of 1 hour. The events that match the same time bucket and the same Agent Severity will be organized in the same group. Then, the query returns how many names every group contains.

...| chart count (name) by Agent Severity span = 1h

The events/rows are grouped using the default time field selected, distributing the events in groups of 1 hour and 2 seconds. The events that match the same time bucket and the same Agent Severity will be organized in the same group. Then, the query returns how many names every group contains.

...| chart count (name) by Agent Severity span 1h2a

 

For additional information about the construction and usage of the chart/stats operators, see the chart/stats overview, Syntax and Structure for chart/stats, and Aggregate Functions for chart/stats.

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