The pivot operator turns each unique field value into a field name (rendered as a column header).

Syntax ​

Scope | pivot DataFields over ColumnFields [by LabelField]

Arguments ​

• Scope: The events to search.
• DataFields: One or more fields to populate the cells of the output table. Wildcards are not supported for field names.
• ColumnFields: One or more fields whose unique values become column headers (field names).
• LabelField: An additional field added to the left-most column of the output table, used to label the rows (events). If not specified, defaults to _time.

Nested and Bracketed Field Names ​

You can specify nested fields by using dot notation (foo.bar). Cribl Search flattens each nested path to a top-level field, replacing every period with an underscore, so foo.bar becomes foo_bar and my.field becomes my_field.

The flattened name is what appears in your results. If you pivot by my.field, the label column is named my_field. If you pivot more than one nested Data Field, the flattened name also becomes the column name prefix.

Cribl Search doesn’t flatten bracketed field names, such as ["a.b"]. Those keep their literal name. For the best performance on lakehouse engines and v2 Datasets, use project-rename to give those fields a flat name before you pivot.

Aggregation Behavior ​

Each cell of the output table holds a single value, so pivot behaves as an aggregation. Cribl recommends running pivot after a summarize operation, so that you control how Cribl Search collapses multiple values into one cell.

If you don’t provide a matching summarize, Cribl Search automatically applies take_any to each DataFields entry before pivoting, grouped by the LabelField and all ColumnFields. This prevents duplicate rows for the same cell. The following two searches are equivalent:

pivot dataField over column by label

summarize dataField=take_any(dataField) by column, label
| pivot dataField over column by label

Because take_any returns an arbitrary value, results are non-deterministic when more than one event maps to the same cell. Write your own summarize when you need predictable cell values:

summarize total=sum(amount) by column, label
| pivot total over column by label

When you omit the LabelField, group by _time instead, because LabelField defaults to _time:

summarize total=sum(amount) by _time, column
| pivot total over column

When Cribl Search Skips the Automatic Aggregation ​

Cribl Search skips take_any only when a summarize immediately precedes the pivot and groups by exactly the LabelField and all ColumnFields. The order of the group-by fields doesn’t matter. Cribl Search still applies take_any when:

• Another operator sits between the summarize and the pivot, such as extend.
• The summarize group-by omits a ColumnFields entry.
• The summarize group-by omits the LabelField.
• The summarize group-by includes fields beyond the LabelField and ColumnFields.

That last case still needs the automatic aggregation, because a broader group-by can produce more than one row per cell.

When you pre-aggregate nested fields, reference the flattened names in the pivot, because summarize flattens nested paths the same way:

summarize sum(foo.bar) by my.field, x.z
| pivot sum_foo_bar over x_z by my_field

Results ​

For each unique value(s) combination of the ColumnFields, pivot returns a column whose:

• Column header (field name) is taken from that ColumnFields value(s).
• Column cells (field values) are populated by the DataFields, calculated separately for each returned event.

Column Names ​

Cribl Search builds each column name from the ColumnFields values, and from the DataFields names when you pivot more than one Data Field:

A null ColumnFields value appears in the column name as the literal string null.

Cells Without Data ​

When a combination of LabelField and ColumnFields values matches no events, the resulting cell depends on where the search runs:

• Searches over v1 Datasets populate the cell with 0.
• Searches over v2 Datasets, and searches that run on a lakehouse engine, leave the cell blank.

Don’t rely on 0 to mean “no data” if you might move a search between v1 and v2 Datasets.

Examples ​

Turn each unique value of the colLabel field (Red, Blue, and Green) into a column, and populate the columns’ cells with random 1-100 numbers:

dataset=$vt_dummy event < 10
| extend dataField = floor(rand() * 100),
         colLabel = case(event % 3 == 0, 'Red', event % 3 == 1, 'Blue', 'Green')
| pivot dataField over colLabel by event

Perform a pivot on two fields, colLabel1 and colLabel2. Because this search pivots two Data Fields, Cribl Search prefixes each column name with the Data Field name, producing columns such as dataField1: Red, yes:

dataset=$vt_dummy event < 10
| extend dataField1 = floor(rand() * 100),
         dataField2 = 200 - dataField1,
         colLabel1 = case(event % 3 == 0, 'Red', event % 3 == 1, 'Blue', 'Green'),
         colLabel2 = iif(event % 2 == 0, 'yes', 'no')
| pivot dataField1, dataField2 over colLabel1, colLabel2 by event