topic Re: how to insert an entry in the report when the count is zero in Splunk Search

how to insert an entry in the report when the count is zero

sjou20 — Tue, 15 Jul 2014 04:12:59 GMT

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.

### new info

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

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

Re: how to insert an entry in the report when the count is zero

yannK — Tue, 15 Jul 2014 05:02:40 GMT

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-exist

Re: how to insert an entry in the report when the count is zero

strive — Tue, 15 Jul 2014 11:11:39 GMT

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

Re: how to insert an entry in the report when the count is zero

somesoni2 — Tue, 15 Jul 2014 18:42:18 GMT

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