Splunk Search

How to use field values as column headers?

Print
Explorer

It is best to demonstrate with an example:

Example of data:

Example of data

And expected tesult table:

alt text

Tags (2)
1 Solution

Ayn
Legend

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).

View solution in original post

Ayn
Legend

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).

Print
Explorer

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!

0 Karma

Ayn
Legend

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...)

Print
Explorer

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).

0 Karma

Ayn
Legend

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

0 Karma

Print
Explorer

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?

0 Karma

Ayn
Legend

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

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

Print
Explorer

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'.

0 Karma

Splunker
Communicator

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-timefieldextrac...

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.

0 Karma

Print
Explorer

This was the first thing I tried 🙂

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

Unfortunately this way does not work:

alt text

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...