Bucketing the results based on the values of another field

[macadminrohit]

I have created a query which tells me the RATIO of number of messages with different keywords in a field msg. One of the field is Response Time which will have various values. now i want the final result to be bucketed based on the response time . below is my query , which doesnt work :
index=servers sourcetype=xs_json msg="HTTP*"
| search actualTime=* | eval actualTime=round(actualTime,0) | rangemap field=actualTime T_LT_1_Sec=0-1000 1_T_5=1001-5000 5_T_15=5001-15000 default=T_GT_15
| stats count(eval(msg="HttpRequest")) AS REQUESTS count(eval(msg="HTTP Request Exceeded SLA")) AS ExceededSLA avg(actualTime) AS AvgRspTime by range Id Model

I have questions:

1) whether my Ratio calculation is correct, i.e is it giving me the right numbers?
2) Is the usage of range field is valid to bucket the results.

[niketn · Accepted solution]

@macadminrohit,

1) Ratio/Average is a relative term and its accuracy depends on what is your requirement. Your query currently finds the average of actualTime for each set of range, Id, Model. If this is what you need, then it is correct 🙂

2) rangemap will give you a bucket None in case actualTime is > 15000. Alternatively, you can achieve same result with case() evaluation function as well and define a default bucket for any values which do not match any of the defied cases using true() or 1==1 condition.

3) Instead of adding a pipe after your base search to filter events with actualTime field, you should move the same to the base search for better search performance i.e remove | search actualTime=* and add actualTime=* to base search.

index=servers sourcetype=xs_json msg="HTTP*" actualTime=*

[chaker]

I don't know if the ratio is correct, but you could try using the bin command to bucket actualTime

http://docs.splunk.com/Documentation/Splunk/7.1.0/SearchReference/Bin