USAGE OF SPLUNK COMMANDS : TRANSPOSE
Hi Guys!!!!
Today we have come with another interesting command which will help you a lot to deal with your visualization. We are talking about “transpose” command.
So, let’s start.
Usage of “transpose” command:
1. This command rotates the table of your result set in 90 degrees,
due to that row turns into column headers, and column values
become row items.
2. This can be very useful when you need to change the layout of an
entire table in order to improve your visualizations.
Syntax of “transpose” command:
....| transpose [int] [column_name=<string>] [header_field=<field>]
NOTE: There is no required arguments for “transpose” command.
OPTIONAL ARGUMENTS:
int: Limit the number of rows to transpose. To transpose all rows,
you have to specify specify | transpose 0, which indicates that
unlimited number of rows will be transposed. Default value is 5.
column_name: The name of the first column that you want to use for the
transposed rows. This column contains the names of the fields.
Default value is “column”.
header_field: The field in your result set to use for the names of the
columns (other than the first column) in the transposed data.
Default values are row 1, row 2, row 3, and so on.
Example: 1
First, we will show you the query without “transpose” command, then with “transpose” command, so that you will understand the difference properly. The query is mentioned below,
index=_internal sourcetype=splunkd_ui_access
| stats count by method
Explanation:
Here we have used “_internal” index and “splunkd_ui_access” sourcetype. Then using stats command we are grouping the event count by method field.
Now, in the below image you can see the visualization of this query in “Column Chart”
As, you can see in the above image “count” field is coming in Legend and “method” field values are coming in the x axis.
Now, let’s add transpose command in the same query to see the changes. The query is mentioned below,
index=_internal sourcetype=splunkd_ui_access
| stats count by method
| transpose
Explanation:
As, you can see how transpose command rotated the table of your result set in 90 degrees, due to that “method” and “count” fields are now inside “column” field as values and the values of “method” and “count” fields are coming in corresponding rows as “row 1”, “row 2”, “row 3”
Now, if you want to modify this a bit more you can add the optional arguments of “transpose” commands with this query. Please, see the below query,
index=_internal sourcetype=splunkd_ui_access
| stats count by method
| transpose 2 column_name="Field_Name" header_field=method
Explanation:
Here, we have used 2 as the value of <int> because of that only two rows are transposed to columns.
We also used “Field_Name” as the value of <column_name> argument to change the “column” field to “Field_Name” [As you can see in the above image].
We also used “method” as the value of <header_field> argument so that the header field which by defaults shows “row 1”, “row 2” and so on will be changed to the method field values(DELETE,GET is coming in the place of “row 1”, “row 2”). [As you can see in the above image].
The below image will show the result set in “Column Chart” visualization.
As, you can see in the above image “count” field is coming in x-axis and “method” field values are coming in y-axis.
[You can compare this image with image: 2 to see the difference with and without transpose command]
Example: 2
First, we will show you the query without “transpose” command, then with “transpose” command, so that you will understand the difference properly. The query is mentioned below,
index=_internal sourcetype=splunkd_ui_access
| stats count(eval(user="admin")) as user_count by app
Explanation:
Here we have used “_internal” index and “splunkd_ui_access” sourcetype. Then using stats command we are getting the count if admin user grouped by the values of “app” field.
Now, in the below image you can see the visualization of this query in “Column Chart”.
As, you can see in the above image “user_count” field is coming in y-axis and “app” field values are coming in x-axis.
Now, let’s add transpose command in the same query to see the changes. The query is mentioned below,
index=_internal sourcetype=splunkd_ui_access
| stats count(eval(user="admin")) as user_count by app
| transpose 0 column_name="Field_Name" header_field=app
Explanation:
Here we have used “| transpose 0” which transpose all the values of “app” in columns.
Also, we have used column_name=”Field_Name” which changed the name of first column name to “Field_Name”, (by default it is “column”). [As you can see in the image]
Also, we have used header_field=app, due to that the field values of “app” field have become the header, (by default it is “row 1”, “row 2”, “row 3” and so on). [As you can see in the image]
The below image will show the result set in “Column Chart” visualization.
As, you can see in the above image “user_count” field is coming in x axis and “app” field values are coming in y-axis(Legend).
[You can compare this image with image: 7 to see the difference with and without transpose command]
Hope you have understood the below topic without fail :
USAGE OF SPLUNK COMMANDS : TRANSPOSE
Happy Splunking !!
