topic Re: How to use field values as column headers? in Splunk Search

How to use field values as column headers?

Print — Tue, 10 Apr 2012 09:37:40 GMT

It is best to demonstrate with an example:

Example of data:

And expected tesult table:

Re: How to use field values as column headers?

Splunker — Tue, 10 Apr 2012 10:10:28 GMT

There's a few ways to attack this. I'm not sure but Splunk may pick up the key/value pair and extract it as-is.

Not sure if it needs a , to separate the key/value pairs, but you can test that pretty easily (if you see a Counter #* field in the left-hand field-picker.

If you do have the fields already extracted, a simple:

(search terms) | table _time,host,Counter_#1,Counter_#2,Counter_#3

Will give you a table of your values as you describe. You might want to rename the host field as it may get mixed up with Splunk's 'host' field (the source host of the logs). Lookup the 'rename' command in the Splunk docco to do it at search time.

If Splunk is not extracting your fields automatically, here is the docco for setting it up manually:

http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Createandmaintainsearch-timefieldextractionsthroughconfigurationfiles#Examples_of_custom_search-time_field_extractions_using_field_transforms

You can also look into the 'multikv' search command, to do the extraction manually in every search command you do.

Hopefully Splunk will extract the fields automatically, and you just need to search and output your data as a table.

Hope it helps.

Re: How to use field values as column headers?

Print — Tue, 10 Apr 2012 10:35:13 GMT

This was the first thing I tried 🙂

(search) | table host, _time, counter, Value

Unfortunately this way does not work:

Re: How to use field values as column headers?

Ayn — Tue, 10 Apr 2012 11:33:02 GMT

It's pretty easy to accomplish as long as you have just two fields to grab values from, for instance _time and counter. In that case you can chart over _time by counter. Like so:

... | chart first(Value) over _time by counter

I use first here because chart needs a statistical function to handle the numerical result from Value. Because this is the only value for the event, any statistical function that returns the same value as the original will do, like avg(), min(), max(), etc.

If you want host as well it gets more complicated, because chart can't handle splitting on multiple fields like that. If it's an absolute requirement you can sort of solve it, but there's unfortunately no straightforward way of doing it (that I know of).

Re: How to use field values as column headers?

Print — Tue, 10 Apr 2012 12:11:51 GMT

Thanks Ayn!
It's not exactly what I need, but... it's realy close!
Yes, actually I need to take into account the 'host' column (and one another in reality).
In addition I ran into a problem: if you use this method, you cannot get more than 10 fields, 11th field appears as 'OTHER'.

Re: How to use field values as column headers?

Ayn — Tue, 10 Apr 2012 12:18:58 GMT

You could use stats and xyseries in combination to resolve this:

... | stats first(Value) as Value by _time,counter | xyseries _time counter Value

Re: How to use field values as column headers?

Print — Tue, 10 Apr 2012 13:07:10 GMT

Nice. But it works only for splunk chart.
Actually I'm more interested in a simple table (in order to further analysis in Excel with pivot tables).
Is there any way to solve the initial problem as long as I know all the possible values for the 'counter' field?

Re: How to use field values as column headers?

Ayn — Tue, 10 Apr 2012 13:09:08 GMT

What you get is a table. What's not working?

Re: How to use field values as column headers?

Print — Tue, 10 Apr 2012 13:33:22 GMT

Yes of course:)
But I would like to have a table with columns named by 'counter' field values and with the values from the corresponding 'Value' fields (as shown in figure 'Expected result table' above).

Re: How to use field values as column headers?

Ayn — Tue, 10 Apr 2012 13:36:12 GMT

I don't understand - this kind of table is exactly what you get by running the command I wrote. (at least it's what I get...)

Re: How to use field values as column headers?

Print — Tue, 10 Apr 2012 14:16:38 GMT

Yeah, excuse me please. You're absolutely right.
This is my mistake. I just tried to add the host and instance to my query... Thanks for your help!