- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to transform vertical table (with KV pair) into wide column table ?
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.