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!

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...