topic How to rotate a table using transpose, remove the first row, and rename the column headers? in Splunk Search

How to rotate a table using transpose, remove the first row, and rename the column headers?

HattrickNZ — Thu, 04 Jun 2015 02:44:35 GMT

i have this search which gives me:
... | stats max(field1) as foo max(field2) as bar max(field3) as la by subname2
which gives me this:

subname2    foo     bar     la
SG  300000  160000  100000
US  300000  160000  60000

If i use transpose with this
... | stats max(field1) as foo max(field2) as bar max(field3) as la by subname2 | transpose
I get

column  row 1   row 2
subname2    SG  US
foo     300000  300000
bar     160000  160000
la  100000  60000

Now how do I remove the the 1st row of the table "subname2 SG US" and use this as my column headers?

I know I can do this:
...| rename column as subname2 | rename "row 1" as SG | rename "row 2" as US
to rename the column headers.

But how do I remove the first row? And is this the best way of doing this?

Ultimately I want this:

subname2    SG  US
foo     300000  300000
bar     160000  160000
la  100000  60000

NOTE: A smilar question has been asked here

Re: How to rotate a table using transpose, remove the first row, and rename the column headers?

acharlieh — Thu, 04 Jun 2015 03:14:38 GMT

Is using transpose a requirement? What about using a combination of untable and xyseries?

Such as:

... | stats max(field1) as foo max(field2) as bar max(field3) as la by subname2 | untable subname2 field value | xyseries field subname2 value | rename field as subname2

Edit: actually one tweak to change the first column name back 🙂

Re: How to rotate a table using transpose, remove the first row, and rename the column headers?

HattrickNZ — Thu, 04 Jun 2015 03:32:14 GMT

tks, I think this is exactly what I am looking for.

transpose was the only way I could think of at the time.

Re: How to rotate a table using transpose, remove the first row, and rename the column headers?

bhawkins1 — Tue, 07 Feb 2017 17:11:27 GMT

transpose should have an optional parameter over which just does | untable <over> a b | xyseries a <over> b

Re: How to rotate a table using transpose, remove the first row, and rename the column headers?

ksubramanian198 — Thu, 09 Aug 2018 13:23:01 GMT

Hi , Please try the below and let me know the result
| stats max(field1) as foo max(field2) as bar max(field3) as la by subname2
| transpose header_field=column
| fields- column

Re: How to rotate a table using transpose, remove the first row, and rename the column headers?

tmurata_splunk — Sat, 08 Sep 2018 14:59:54 GMT

I think this is easier.

... 
 | transpose header_field=subname2
 | rename column as subname2

Re: How to rotate a table using transpose, remove the first row, and rename the column headers?

dhirendra761 — Wed, 10 Apr 2019 14:57:46 GMT

Right this one easier and quickly too...!! thanks 🙂