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
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.
Not sure exactly what you want but have you looked at the transpose command?
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