Similar to a PivotTable in Excel, the transpose operator allows you to take a list and turn it into a table in the Aggregates tab, as shown by the examples below.
To do so, it dynamically creates columns for aggregate search results. This allows you to change the output of a query by turning search results into fields, so you can design queries without first knowing the output schema. In this way, transpose formats the data correctly for charts in Dashboard Panels.
For example, the screenshots below represent the same data from the same time range, but the second screenshot is generated from a query using the transpose operator.
In the second screenshot, which includes the transpose operator, results are displayed more clearly.
transpose row [row fields] column [column fields] as [output fields]
transpose row [row fields] column [column fields]
The above syntax is equivalent to transpose row [row fields] column [column fields] as *
Results can be influenced in three ways:
- By using a comma separated list of variable names (such as "a, b"), only the specified output fields appear in the output table.
- By using a comma separated list of variable names, followed by a comma and a star (such as "a, b,*"), the specified output fields appear in the output table, followed by dynamic fields.
- By including a single star ("*") all dynamic fields appear in the output. Use this option when you want to add all your fields to the resulting table and when you need to use those fields for any other operator after transpose.
As a reminder, if a field name contains a special character (such as -) the character must be quoted in %"", as in %"test-zz-1". Because column names computed from data tend to include special characters, this is especially important to keep in mind when using a transpose operator.
- Transpose is not supported with the Join operator.
- Transpose supports a maximum of 300 dynamic fields (columns to be created).
Viewing errors by module
Let's say that errors are logged by module; we'd like to view errors by each module's name. Running a query similar to:
error | parse "module=*]" as
| timeslice 1m
| count as value by _timeslice, module
| transpose row _timeslice column module as [moduleName1, moduleName2, ...]
will produce results with each module represented with a distinct color, similar to:
Try changing the Stacking setting (under Change Properties) to Normal to see how graphs are affected by this option. For more information, see Chart Search Results.
View successful logins by user
Because you can use the transpose operator without prior knowledge of the fields it will generate, you can view logins by users and organization. Running a query similar to:
| parse "Successful login for user '*', organization: '*'" as user, org_id
| timeslice 1d
| count _timeslice, user
| transpose row _timeslice column user
will produce a stacked graph similar to:
View web server status codes
Use the following query to view Apache web server status codes. (Note that status_code is a pre-parsed field.)
| timeslice 1m
| count by _timeslice, status_code
| transpose row _timeslice column status_code
Results are initially returned in the Aggregates tab.
Then select the Column chart button, and under Change Properties, set the Stacking setting to Normal to create a stacked column chart.
For information on handling null fields, see isNull operator.