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
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"
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
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"
I'll add an explanation later
This one seems to work (although I don't understand how! I'm not good at this yet...) Thanks
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
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...
I don't think you can calculate the cumulative percentage and a running average this way...