Splunk Search
Highlighted

extract (kv) only certain fields, or use column as headers in transpose

Engager

I have log data that looks like this

key1=val1 key2=val2 key3=val3 

The names of the keys is unknown and could be

foo=val1 bar=val2 baz=val3

However, the keys always follow the pattern

[A-Z][A-Za-z0-9]+

These key/val pairs are all counters for events that have been processed and the desire it to have a chart showing the increase in those counters over time.

I can do this:

seach_stuff | extract |  transpose|  regex column="[A-Z][A-Za-z0-9]+" | table *

And that gets me a table with the extracted (and filtered) keys as the column and the various values as the row1 - rowN columns.

| column | row1  | row2 | row3 |
--------------------------------
| key1   | val1  | val2 | val3 |
| key2   | val4  | val5 | val6 |
| key3   | val7  | val8 | val9 |
...

This won't chart the way I want (hopefully for obvious reasons) so I thought I could transpose again. However the table ends up looking like this:

| column | row1 | row2 | row3 |
--------------------------------
| column | key1 | key2 | key3 |
| row1   | val1 | val4 | val7 |
| row2   | val2 | val5 | val8 |
| row3   | val3 | val6 | val9 |
...

Essentially what I want is to be able to filter the extracted key value pairs by a regex on the key. Is there someway to do that, or some way to possibly use the first column as the headers for the table when I transpose a second time?

Tags (3)
0 Karma
Highlighted

Re: extract (kv) only certain fields, or use column as headers in transpose

SplunkTrust
SplunkTrust

Try something like this

your base search | table _time [search your base search  | transpose | regex column="[A-Za-z]+[0-9]+" | table column |rename column as search | mvcombine search delim=","]

The output should be like

_time key1 key2 key3 ...

Highlighted

Re: extract (kv) only certain fields, or use column as headers in transpose

Engager

This works, but seems really slow. The comment to my question does what I want and appears to be a lot faster. Thanks for the answer though... just more splunk to learn.

0 Karma
Highlighted

Re: extract (kv) only certain fields, or use column as headers in transpose

SplunkTrust
SplunkTrust

What happens when you do this?

search_stuff | fields + _time _raw | extract | timechart avg(*) as *

That should chart every autoextracted field over time.

0 Karma
Highlighted

Re: extract (kv) only certain fields, or use column as headers in transpose

Engager

Beautiful - works exactly as I want. Thanks!

0 Karma
Highlighted

Re: extract (kv) only certain fields, or use column as headers in transpose

SplunkTrust
SplunkTrust

Great. I've converted this to an answer so you can mark the question as solved.

0 Karma