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!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...