Splunk Search

How to transpose rows of one column into multiple columns?

cjkar
Engager

I currently have multiple entries in the VALUES column for each host.

The table currently looks like: 

hostnameVALUES
HOST1

ENV1

APP1

LOC1

 

HOST2

ENV2

APP2

LOC2

 

I would like the table to read as:

hostnameENVAPPLOC
HOST1ENV1APP1LOC1
HOST2

ENV2

APP2

LOC2

 

I am essentially trying to transpose the column "VALUE" and create 3 separate columns with the custom headings "ENV,APP and LOC" 

Labels (2)
Tags (3)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@cjkar 

I think simple eval can help you on this. Can you please try this with your search?

YOUR_SEARCH | eval ENV=mvindex(VALUES,0),APP=mvindex(VALUES,2),LOC=mvindex(VALUES,2)

 

My Sample Search :

| makeresults 
| eval _raw="hostname,VALUES
HOST1,ENV1|APP1|LOC1
HOST2,ENV2|APP2|LOC2" | multikv forceheader=1 | eval VALUES=split(VALUES,"|")
| rename comment as "Upto now is for sample data only"
| table hostname,VALUES | eval ENV=mvindex(VALUES,0),APP=mvindex(VALUES,2),LOC=mvindex(VALUES,2)


 KV

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@cjkar 

I think simple eval can help you on this. Can you please try this with your search?

YOUR_SEARCH | eval ENV=mvindex(VALUES,0),APP=mvindex(VALUES,2),LOC=mvindex(VALUES,2)

 

My Sample Search :

| makeresults 
| eval _raw="hostname,VALUES
HOST1,ENV1|APP1|LOC1
HOST2,ENV2|APP2|LOC2" | multikv forceheader=1 | eval VALUES=split(VALUES,"|")
| rename comment as "Upto now is for sample data only"
| table hostname,VALUES | eval ENV=mvindex(VALUES,0),APP=mvindex(VALUES,2),LOC=mvindex(VALUES,2)


 KV

cjkar
Engager

Worked perfectly thanks @kamlesh_vaghela !

0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...