Hi,
I need to generate a report like this:
appName | buck | count
abc | <=1 minute | 5
abc | >1 min. && <=10 min. | 0
abc | >10 min. && <=30 min. | 5
xyz | <=1 minute | 0
xyz | >1 min. && <=10 min. | 1
xyz | >10 min. && <=30 min. | 15
my query is doing the job for the most part, except when the count is zero, splunk does not show a entry for it, so instead it gives the following (notifice row #2 and #4 is missing )
appName | buck | count
abc | <=1 minute | 5
(I need it to show a zero count row here)
abc | >10 min. && <=30 min. | 5
(I need it to show a zero count row here)
xyz | >1 min. && <=10 min. | 1
xyz | >10 min. && <=30 min. | 15
here's my search
search | eval buck=case(waitTimeSec <= 60, "<= 1min", waitTimeSec <= 600, "> 1min && <=10 min ", waitTimeSec <= 1800, ">10min && <=30 min.") | stats count(event) as count by appName, buck
any pointer is appreciated. thanks.
thanks for info. I followed the example you provided, it sort of works but now I cannot get it to group by appName first when I'm using rangemap. here's my new query
search |rangemap field=waitTimeSec "1-60"=0-60 "61-600"=61-600 "601-6000000"=601-6000000, "6000001-1600000"=6000001-1600000 |top limit=0 range |inputlookup append=true ntfn-lookup.csv |stats max(count) as mycount by range | sort range
here's the result
range mycount
1-60 4
61-600 7
601-6000000 14
6000001-1600000 0
here's my lookup.csv
range, count
1-60, 0
61-600, 0
601-6000000,0
6000001-1600000,0
I need it to group by appName first , so I tried
|stats max(count) as mycount by appName, range | sort range
but this returns no results.
any help is appreciated, thx
Try this
search.. |rangemap field=waitTimeSec "1-60"=0-60 "61-600"=61-600 "601-6000000"=601-6000000, "6000001-1600000"=6000001-1600000 | stats count(eval(range="1-60")) as "1-60" count(eval(range="61-600")) as "61-600" count(eval(range="601-6000000")) as "601-6000000" count(eval(range="6000001-1600000")) as "6000001-1600000" by appName | untable appName buck count
This should give you an idea..
http://answers.splunk.com/answers/23839/include-zero-count-in-stats-count
As yannK said, you need to maintain a lookup
Splunk will not return a value for field that does not contains values.
you can create a lookup with your list of buck, and use a join type-outer to match your results to it, and display the missing values.
see this example : http://answers.splunk.com/answers/125101/how-to-show-count-as-0-for-rangemap-if-a-range-does-not-exi...