topic transforming table output in Splunk Search

transforming table output

andersmholmgren — Wed, 14 Dec 2011 00:39:30 GMT

I have a summary index of stats in hourly buckets. I need to caculate rolled up stats for these.
The hourly stats are count, median duration, max duration.
The rollups are total count, avg median duration, max duration per day (also need for month etc)

The end result looks like

txnType   metricName                result
A         Volume                    356
A         AverageMedianDuration     300
A         MaximumDuration           400
B         Volume                    1
B         AverageMedianDuration     902
B         MaximumDuration           1200
C         Volume                    292
C         AverageMedianDuration     1000
C         MaximumDuration           2000
D         Volume                    9439
D         Average Median duration   3000
D         Max Duration              4000

Currently I have been doing separate queries for each metric (i.e. 3 queries). But as they all roll up over the same period I'm hoping to reduce that to one query. The start of the query looks like

.... | stats sum(Volume) as Volume, avg(MedianDuration) as AverageMedianDuration, max(MaximumDuration) as MaximumDuration by txnType

Which gives a result like

txnType Volume  AverageMedianDuration   MaximumDuration
A       356     300                     400
B       1       902                     1200
C       292     1000                    2000
D       9439    3000                    4000

Any ideas on how to transform this into the desired format? I guess this is like an unpivot

Re: transforming table output

BobM — Wed, 14 Dec 2011 01:34:12 GMT

Not sure exactly what you want but have you looked at the transpose command?

Re: transforming table output

andersmholmgren — Wed, 14 Dec 2011 01:39:17 GMT

yes but its not quite what I want. That just swaps the columns and rows. i.e. like

txnType A B C D
Volume 356 etc

Re: transforming table output

MartinHarper — Tue, 11 Dec 2012 23:12:59 GMT

I had a similar requirement and I solved it with splunk fieldsummary:
http://docs.splunk.com/Documentation/Splunk/5.0/SearchReference/Fieldsummary

This means you need one query for each transaction type, as opposed to one query for each metric type. That might or might not be better, depending on your data. I was lucky and had a single transaction type and 100s of metrics.

Still looking for a proper solution.