Splunk Search

How to transform vertical table (with KV pair) into wide column table ?

James_wang
Engager

Hi Support,

Host, Key, Value
A, Username, root
A, Address, 1.1.1.1
A, Type, AIX
B, Username, admin
B, Address, 2.2.2.2
B, Type, Windows

How to transform above table to following

Host, Type, Address, Username
A, AIX, 1.1.1.1, root
B, Windows, 2.2.2.2, admin

Thank you.
James Wang

somesoni2
Revered Legend

If the first output with Host,Key and Value fields is result of a search and you want to transform it to 2nd table using SPL, try something like this

your current search giving fields Host,Key,Value
| xyseries Host Key Value

Alternate/equivalent method

your current search giving fields Host,Key,Value
| chart values(Value) over Host by Key

lguinn2
Legend

Updated answer: I forgot that the eval command has a super useful option that will work here:

yoursearchhere
| eval {Key} = Value
| fields - Key Value
| selfjoin

Although I hope that you will still consider the option at the end if you are going to use these fields often.

First, this is not Support, this is a community forum! But there are lots of people here who can answer questions. Some of us work for Splunk, but most don't. Welcome!

You can do this:

yoursearchhere
| eval Username = if(Key=="Username",Value,null())
| eval Address = if(Key=="Address",Value,null())
| eval Type = if(Key=="Type",Value,null())
| selfjoin Host

However, it would be better if this type of field extraction is done automatically by Splunk for you. This can get tedious if the list of keys is long, and it isn't very flexible. If you put this in transforms.conf

[parse_keys_and_values]
REGEX =.*? ,.*?,(.*?),(.*?)
FORMAT = $1::$2

and this in props.conf

[yoursourcetypehere]
REPORT-extractmyfields = parse_keys_and_values

Then Splunk will automatically create the fields; each field will be named based on the Key and its Value will be set to the Value.
You will still need to do the selfjoin if you want to combine all the fields for a host though.

Get Updates on the Splunk Community!

Buttercup Games: Further Dashboarding Techniques

Hello! We are excited to kick off a new series of blogs from SplunkTrust member ITWhisperer, who demonstrates ...

Message Parsing in SOCK

Introduction This blog post is part of an ongoing series on SOCK enablement. In this blog post, I will write ...

Exploring the OpenTelemetry Collector’s Kubernetes annotation-based discovery

We’ve already explored a few topics around observability in a Kubernetes environment -- Common Failures in a ...