Splunk Search

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

sjou20
New Member

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

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

Tags (3)
0 Karma

somesoni2
Revered Legend

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
0 Karma

strive
Influencer

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

yannK
Splunk Employee
Splunk Employee

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

Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...