topic Re: Column to row conversion in Splunk Search

Column to row conversion

gauravmishra15 — Wed, 06 Sep 2017 03:20:52 GMT

Hi friends

I am facing an issue where I have to consolidate and convert the data from Column to rows. The sample data looks like this -

Datetime result duration Dash1 Dash2 Dash3
05-09-2017 20:45:11 SUCCESS 11.07 3.91 3.33 3.34
05-09-2017 20:44:01 SUCCESS 20.57 7.94 6.62 5.32
05-09-2017 20:43:06 SUCCESS 19.98 7.89 6.40 5.14
05-09-2017 20:42:05 SUCCESS 21.29 8.13 6.90 5.82
05-09-2017 20:41:18 SUCCESS 16.38 5.98 5.69 4.18

I have to change data in this format

Datetime result Name ResponseTime

05-09-2017 20:45:11 SUCCESS duration 11.07

05-09-2017 20:45:11 SUCCESS Dash1 3.91

05-09-2017 20:45:11 SUCCESS Dash2 3.33

05-09-2017 20:45:11 SUCCESS Dash3 3.34

05-09-2017 20:44:01 SUCCESS duration 20.57
05-09-2017 20:44:01 SUCCESS Dash1 7.94

05-09-2017 20:44:01 SUCCESS Dash2 6.62

05-09-2017 20:44:01 SUCCESS Dash3 5.32

Is there a way we can achieve this in SPLUNK ?

Re: Column to row conversion

HiroshiSatoh — Wed, 06 Sep 2017 05:49:57 GMT

Try this!

(your search)
| eval wk="duration="+duration+","+"Dash1="+Dash1+","+"Dash2="+Dash2+","+"Dash3="+Dash3
| makemv delim="," wk| mvexpand wk
| rex field=wk "^(?<Name>.*)=(?<ResponseTime>.*)$"
| table Datetime,result,Name,ResponseTime

Re: Column to row conversion

niketn — Wed, 06 Sep 2017 09:12:57 GMT

@gauravmishra15, Unless data in table 1 is your raw data, can you share your existing query which gets you table 1 with Datetime result duration Dash1 Dash2 Dash3? It would be better if we plotted required table 2 directly from your raw events rather than creating table 1 to table 2.