topic Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation in Splunk Search

variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

guilmxm — Mon, 16 Jun 2014 09:00:04 GMT

Hi,

I have data indexed with variable fields (csv data indexed as csv by Splunk) such as:

timestamp device1 device2 device3 device4
XXXXXXXX        X       X       X       X

Where X are timestamps and values.

I would like to transform these data with Splunk to get:

timestamp device value
XXXXXXXXX device1    X
XXXXXXXXX device2    X
XXXXXXXXX device3    X
XXXXXXXXX device4    X

Currently, I can manipulate data to stats and chart them using:

| timechart max(*) As *

Then i can filter by fields name or doing any other operation.

But i would like to aggregate them (sum) by time interval, easy when devices are arranged in uniq fields, more complicated with variable fields i can't know in advance.

I found a way to stats count/max or whatever by device, but i can't get to include time:

<search stuff>
| stats max(*) As *
| transpose
| rex device=device^count\((?<column>.*?)\)
| rename column as device | rename "row 1" as value

I would have like the same kind of stuff to include the timestamp with the goal to re-arrange the data

Thank you very much for any help !

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

alterdego — Mon, 16 Jun 2014 14:05:55 GMT

Your example, and the result you are looking, for is almost the exact example (#3) used in the Search Reference manual for the mvexpand command.

Please take a look:
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Mvexpand

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

guilmxm — Mon, 16 Jun 2014 17:41:59 GMT

Hi alterdego,

Thanks a lot for your answer, i think too this should do the trick.

The #3 is little bit different from my data, my fields are arbitrary (and i can't know them in advance), like:

timestamp 00:45:ER 00:76:23 (csv header)
06/16/2014,00:00:00 0 0 (timestamp + value for each corresponding field)

I'm trying to adapt the example to my case...

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

guilmxm — Mon, 16 Jun 2014 18:28:26 GMT

If you have any idea how, don't hesitate please 🙂

I'm quite bad with mvexpand, multikv, makekv and so on... and i often have the same kind of need without being able as for now to manage it.. which drives me crasy !

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

somesoni2 — Mon, 16 Jun 2014 20:36:23 GMT

what version of Splunk are your using ? In the example for mvexpand, the rex command will not work for your data but , if you're on Splunk 6.x, then rex functionality can be achieved by foreach.

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

somesoni2 — Mon, 16 Jun 2014 20:56:10 GMT

See if this works (from your stats max query in the question)

<search stuff> | eval MaxTime=_time | stats max(*) As *
| transpose  | appendpipe [| where column="MaxTime" | rename "row 1" as Timestamp] | eventstats max(Timestamp) as Timestamp | where like(column,"device%") | convert ctime(Timestamp) | rename column as device, "row 1" as value | table Timestamp, device, value

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

guilmxm — Tue, 17 Jun 2014 06:02:36 GMT

Hi somesoni2,

Many thanks for your answer ! And i confirm this works great 🙂

I had an another solution (thanks to to madscient) using untable which is a little bit more simple and works great too, i'll comment this post with both solutions

But really thank you for this!

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

guilmxm — Tue, 17 Jun 2014 06:07:47 GMT

For people having the same requirement of re-arranging data with multiple arbitrary fields, 2 solutions answered to it, thanks to somesoni2 and madscient:

1. using eventstat:

<search stuff> | eval MaxTime=_time | stats max(*) As *
| transpose  | appendpipe [| where column="MaxTime" | rename "row 1" as Timestamp] | eventstats max(Timestamp) as Timestamp | where like(column,"device%") | convert ctime(Timestamp) | rename column as device, "row 1" as value | table Timestamp, device, value

Works great

2. Simpler solution using untable

<search stuff> | stats max(*) As * by _time | untable _time device value | stats max(value) as value by device

Works great

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

somesoni2 — Tue, 17 Jun 2014 13:29:56 GMT

This is (2nd solution) amazing. Good to learn a new command. Thanks

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

guilmxm — Tue, 17 Jun 2014 15:10:39 GMT

Yes it is, this works incredibly great!

Re: variable fields - re-arrange (tranpose) variables fields into one field for data aggregation

guilmxm — Tue, 17 Jun 2014 15:10:55 GMT

Thanks to Nick !