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

[James_wang]

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]

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]

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.