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!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...