topic Re: How to transpose a table? (without using Transpose command) in Splunk Search

How to transpose a table? (without using Transpose command)

zacksoft — Wed, 09 Sep 2020 13:31:00 GMT

My Table looks like this

VF_Price Huyndai_Price Jaguar_Price
345 412 542

I want it to look like the following

VF_Price 345
Huyndai_Price 412
Jaguar_Price 542

I need to transpose the table without using transpose command. I cannot use transpose command, 'cos I am using some invisible _<fields> that I am passing to my alert, those invisible field becomes visible and show in my transposed table, if I use 'transpose' command. Hence I am trying to figure out , how can I achieve this without using 'transpose'.

Re: How to transpose a table? (without using Transpose command)

thambisetty — Wed, 09 Sep 2020 13:44:38 GMT

without transpose command also, the other invisible fields will be affected

Re: How to transpose a table? (without using Transpose command)

rrovers — Wed, 09 Sep 2020 14:21:55 GMT

maybe when you use untable and xyseries you can get what you want.

For example:

index=_internal
| stats count as aantal by sourcetype
| untable sourcetype aantal waarde
| xyseries aantal sourcetype waarde

Re: How to transpose a table? (without using Transpose command)

zacksoft — Wed, 09 Sep 2020 14:50:30 GMT

Thanks @rrovers
Would this work if I have 4 fields .
example :-
Vehicle Grocery Tax Education
120 23 5 45

and to make it
Vehicle 120
Grocery 23
Tax 5
Education 45

| untable Vehicle Grocery Tax Education
| XYseries Vehice Grocery TAx Education

like this ?

Re: How to transpose a table? (without using Transpose command)

niketn — Wed, 09 Sep 2020 16:10:48 GMT

@zacksoft for the community to assist you better please add more details about your use case. How are you creating the hidden fields for Alert? Can you not create them after transpose?

Re: How to transpose a table? (without using Transpose command)

rnowitzki — Wed, 09 Sep 2020 16:13:55 GMT

Hi @zacksoft,

this seems to work:

While I was playing around with the data, due to a typo I added a field in the untable command that does not exist, that's why I have foo in it now. I don't fullly understand why, but it gives the result you asked for.

But I am not sure about your _fields that you mentioned. I guess they won't be available, as with the transpose command.

BR
Ralph

Re: How to transpose a table? (without using Transpose command)

rrovers — Thu, 10 Sep 2020 05:29:00 GMT

I assumed vehicle, grocery and tax are not the field names but the values of a field. Let's say the name of the field is "object". The values 120, 23, 5, 45 are values of a field named "cost".

The search should be something like this

| stats count as cost by object
| untable object cost waarde
| xyseries cost object waarde