topic Re: Grouping by numeric range in Splunk Search

Grouping by numeric range

bermudabob — Mon, 16 Apr 2012 12:29:59 GMT

Hi,
Novice to Splunk, I've indexed some data and now want to perform some reports on it.

My main requirement is that I need to get stats on response times as follows by grouping them by how long they took. The report would look similar to the following:

	Cum. response %	Running Avg	No of Transactions
<0.5 sec	55.89	0.31	268,676
<1.0 sec	96.58	0.45	195,582
<1.5 sec	98.98	0.47	11,578
<2.0 sec	99.39	0.47	1,976

I need to group in .5 second intervals up to 5 seconds and then 1 second intervals after that up to 10 seconds, with the final row being for everything over 10 seconds. Thie field being grouped on is a numeric field that holds the number of milliseconds for the response time.

Being new to Splunk, I have no idea about how to do the grouping, so I would be grateful for suggestions.

Cheers,

Rob

Re: Grouping by numeric range

ayme — Mon, 16 Apr 2012 13:14:29 GMT

You can use the rangemap function and then do a stats grouping by the range. Something like this:

... | rangemap field=responseTime "<0.5"=0-50 "<1"=0-100 "<1.5"=0-150 "<2.0"=0-200 | stats sum(responseTime) as "Cum Response Time", avg(responseTime) as "Running Avg", count as "Number of Transactions" by range

Re: Grouping by numeric range

ziegfried — Mon, 16 Apr 2012 14:14:28 GMT

It's a little complicated, but the following search should produce a result similar to what you described:

... your search ... | eval responseTime=responseTime/1000 | sort responseTime | eventstats count as total | eval in_range=round(case(responseTime<5, floor(2*responseTime)/2+.5, responseTime<10,ceil(responseTime), responseTime>10,100000.0),1) | streamstats count as cnt avg(responseTime) as run_avg | stats first(total) as total last(run_avg) as run_avg max(cnt) as count count as cnt by in_range | sort in_range | eval range=if(in_range>10, ">= 10.0 sec","< "+tostring(in_range)+" sec") | eval pct=round(count/total*100,2) | eval run_avg=round(run_avg,2) | rename cnt as "No of Transactions" pct as "Cum. response %" run_avg as "Running Avg" | table range "Cum. response %" "Running Avg" "No of Transactions"

Re: Grouping by numeric range

echalex — Mon, 16 Apr 2012 14:22:18 GMT

The answer provided by ayme certainly works and is probably what you want. If you have a lot of ranges, you could save yourself some typing by using eval to create a field to group by. However, in this case rangemap is probably quicker and easier for you.

... | eval rt_group=if(responseTime>10000, "> 10.0 sec","< ". if(responseTime>5000, round(ceil(responseTime/1000),1) ,round(ceil(responseTime/500)*500/1000,1)) . " sec") | stats sum(responseTime), avg(responseTime), count by rt_group

Re: Grouping by numeric range

ziegfried — Mon, 16 Apr 2012 14:32:04 GMT

I don't think you can calculate the cumulative percentage and a running average this way...

Re: Grouping by numeric range

bermudabob — Mon, 16 Apr 2012 14:35:55 GMT

Thanks for that - just what I need for the grouping. I'm still trying to figure out how to do the cumulative percentage column, I thought it was something to do with streamstats, but can't seem to get it to work...

Re: Grouping by numeric range

bermudabob — Mon, 16 Apr 2012 14:59:58 GMT

This one seems to work (although I don't understand how! I'm not good at this yet...) Thanks

Re: Grouping by numeric range

ziegfried — Mon, 16 Apr 2012 15:12:20 GMT

I'll add an explanation later