Splunk® Enterprise

Search Reference

Download manual as PDF

Download topic as PDF

transpose

Description

Returns the specified number of rows (search results) as columns (list of field values), such that each search row becomes a column.

Syntax

transpose [int] [column_name=<string>] [header_field=<field>] [include_empty=<bool>]

Required arguments

None.

Optional arguments

column_name
Syntax: column_name=<string>
Description: The name of the first column that you want to use for the transposed rows. This column contains the names of the fields.
Default: column
header_field
Syntax: header_field=<field>
Description: The field in your results to use for the names of the columns (other than the first column) in the transposed data.
Default: row 1, row 2, row 3, and so on.
include_empty
Syntax: include_empty=<bool>
Description: Specify whether to include (true) or not include (false) fields that contain empty values.
Default: true
int
Syntax: <int>
Description: Limit the number of rows to transpose. To transpose all rows, specify | transpose 0, which indicates that the number of rows to transpose is unlimited.
Default: 5

Usage

When you use the transpose command the field names used in the output are based on the arguments that you use with the command. By default the field names are: column, row1, row2, and so forth.

Examples

1. Transpose the results of a chart command

Use the default settings for the transpose command to transpose the results of a chart command.

... | chart count BY host error_code | transpose


2. Count the number of events by sourcetype and transpose the results to display the 3 highest counts

Count the number of events by sourcetype and display the sourcetypes with the highest count first.

index=_internal | stats count by sourcetype | sort -count

An image that shows 2 columns. The first column lists the source types. The second column is a count of the number of events for each source type.

Use the transpose command to convert the rows to columns and show the source types with the 3 highest counts.

index=_internal | stats count by sourcetype | sort -count | transpose 3

An image that shows 4 columns. The first column are labels that for the information in the rows. The labels are sourcetype and count. The other 3 columns list the top 3 source types and the count, the number of events, for each source type.

3. Transpose a set of data into a series to produce a chart

This example uses the sample dataset from the Search Tutorial.
  • Download the data set from Add data tutorial and follow the instructions to get the tutorial data into your Splunk deployment.

Search all successful events and count the number of views, the number of times items were added to the cart, and the number of purchases.

sourcetype=access_* status=200 | stats count AS views count(eval(action="addtocart")) AS addtocart count(eval(action="purchase")) AS purchases

This search produces a single row of data. This screen image shows one row of data with three columns. The first column is "views" with a count of 34282. The second column is "add to cart" with a count of 5292. The third column is "purchases" with a count of 5224.

The value for count AS views is the total number of the events that match the criteria sourcetype=access_* status=200, or the total count for all actions. The values for addtocart and purchases show the number of events for those specific actions.

When you switch to the Visualization tab, the data displays a chart with the "34282 views" as the X axis label and two columns, one for "addtocart "and one for "purchases". Because the information about the views is placed on the X axis, this chart is confusing.

This screen image shows a column chart. There are two columns "add to cart" and "purchases". The X axis label is "34282 views". with a count of 34282. The second column is "add to cart" with a count of 5292. The third column is "purchases" with a count of 5224.

If you change to a pie chart, you see only the "views".

This screen image shows a pie chart with only the "views" information included in the chart.

Use the transpose command to convert the columns of the single row into multiple rows.

sourcetype=access_* status=200 | stats count AS views count(eval(action="addtocart")) AS addtocart count(eval(action="purchase")) AS purchases | transpose

This screen image shows three rows, one for "views", one for "add to cart" and one for "purchases". Each row displays the corresponding count.

Now these rows can be displayed in a column or pie chart where you can compare the values.

This screen image shows a pie chart with a slice for each of the rows of data. There is one for "views", one for "add to cart" and one for "purchases". Views has the biggest slice.

In this particular example, using a pie chart is misleading. The views is a total count of all the actions, not just the addtocart and purchases actions. Using a pie chart implies that views is an action like addtocart and purchases. The pie chart implies that the value for views is 1 part of the total, when in fact views is the total.

There are a few ways to fix this issue:

  • Use a column chart
  • You can remove the count AS views criteria from your search
  • You can add the table command before the transpose command in the search, for example:

sourcetype=access_* status=200 | stats count AS views count(eval(action="addtocart")) AS addtocart count(eval(action="purchase")) AS purchases | table addtocart purchases | transpose

See also

fields, stats

Answers

Have questions? Visit Splunk Answers and see what questions and answers the Splunk community has using the transpose command.

PREVIOUS
transaction 		  NEXT
trendline

This documentation applies to the following versions of Splunk® Enterprise: 6.0, 6.0.1, 6.0.2, 6.0.3, 6.0.4, 6.0.5, 6.0.6, 6.0.7, 6.0.8, 6.0.9, 6.0.10, 6.0.11, 6.0.12, 6.0.13, 6.0.14, 6.0.15, 6.1, 6.1.1, 6.1.2, 6.1.3, 6.1.4, 6.1.5, 6.1.6, 6.1.7, 6.1.8, 6.1.9, 6.1.10, 6.1.11, 6.1.12, 6.1.13, 6.1.14, 6.2.0, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 6.2.9, 6.2.10, 6.2.11, 6.2.12, 6.2.13, 6.2.14, 6.2.15, 6.3.0, 6.3.1, 6.3.2, 6.3.3, 6.3.4, 6.3.5, 6.3.6, 6.3.7, 6.3.8, 6.3.9, 6.3.10, 6.3.11, 6.3.12, 6.3.13, 6.3.14, 6.4.0, 6.4.1, 6.4.2, 6.4.3, 6.4.4, 6.4.5, 6.4.6, 6.4.7, 6.4.9, 6.4.10, 6.4.11, 6.5.1612 (Splunk Cloud only), 6.5.2, 6.5.3, 6.5.4, 6.5.5, 6.5.6, 6.5.7, 6.5.8, 6.5.9, 6.6.0, 6.6.1, 6.6.2, 6.6.3, 6.6.4, 6.6.5, 6.6.6, 6.6.7, 6.6.8, 6.6.9, 6.6.10, 6.6.11, 6.6.12, 7.0.0, 7.0.1, 7.0.2, 7.0.3, 7.0.4, 7.0.5, 7.0.6, 7.0.7, 7.0.8, 7.0.9, 7.0.10, 7.0.11, 7.0.13, 7.1.0, 7.1.1, 7.1.2, 7.1.3, 7.1.4, 7.1.5, 7.1.6, 7.1.7, 7.1.8, 7.1.9, 7.2.0, 7.2.1, 7.2.2, 7.2.3, 7.2.4, 7.2.5, 7.2.6, 7.2.7, 7.2.8, 7.2.9, 7.3.0, 7.3.1, 7.3.2, 7.3.3, 8.0.0, 6.4.8, 6.5.0, 6.5.1, 6.5.10

Comments

Quentinwl chung
Thank you for pointing this issue out ! I have added 2 notes to Example 3 to explain this and provided a few alternatives to avoid creating a misleading chart.

Lstewart splunk, Splunker
August 1, 2019

just a reminder to all reader here. The calculation of about example may be wrong.
the total views (action=*) included "addtocart" and "purchase".

So the correct calculation should be:
sourcetype=access_* status=200 | stats count AS views count(eval(action="addtocart")) AS addtocart count(eval(action="purchase")) AS purchases | eval views=views-addtocart-purchases | transpose

Otherwise, the last pie chart it will misrepresent the total views as 44,798 (34282+5292+5224) instead of 34282.

Quentinwl chung
July 23, 2019

Sideview - Thank you so much for letting us know about this issue with the Learn More link. I have fixed the problem and mapped the link to this page. It should be working now in Splunk Web.

Lstewart splunk, Splunker
October 28, 2016

In the product when you are using the "transpose" command, the "Learn more" link in the product should of course link here. However it drops you at the root of the Admin manual.
specifically -- http://localhost:8000/en-US/help?location=search_app.assist.transpose
ultimately redirects the user to
http://docs.splunk.com/Documentation/Splunk/6.5.0/Admin/LearnhowtoadministerSplunk

Sideview
October 18, 2016

Was this documentation topic helpful?

Enter your email address, and someone from the documentation team will respond to you:

Please provide your comments here. Ask a question or make a suggestion.

You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters