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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...