How to get stats count to include zero count?

[Upas02]

Hi,
I have a lookup file like this -

EngineName
Engine1
Engine2
Engine3

I need to find the engine where event count is zero for last 5 minutes.

But when I am checking the number of events for each engine using this query -

index=myindex [ | inputlookup PriorityEngines | fields EngineName ] | stats count by EngineName

It shows only engines which have more than zero event count

How do I get the name of engines with zero event count in last 5 mins?
I saw few other questions in splunk answers but the queries were too complex and they didnt work for me.
Isnt there any standard way to include zero count value?

[KailA · Accepted solution]

Hello,

What about something like that

index=myindex  [ | inputlookup PriorityEngines | fields EngineName ]
| stats count as cnt_EngineName by EngineName
| append 
   [ inputlookup PriorityEngines | fields EngineName ]
| fillnull cnt_EngineName
| stats sum(cnt_EngineName) as count BY EngineName

It should do the trick, right ?

Kail

[Upas02]

@KailA
Awesome, it worked perfect. Thanks a ton.

[FrankVl]

What you are doing is filtering the data in the index based on what is in the lookup, that will never show you any results other than what is in the index. So you would need to start from the lookup and then add the info from the index.

| inputlookup PriorityEngines | fields EngineName
| eval count = 0
| append [
  | search index=myindex | stats count by EngineName
]
| stats max(count) as count by EngineName

[Upas02]

@FrankVl
This solution is not working for me for 2 reasons,
First, I have more than 300 engines in my index and about 19 engines in the lookup. I do not want to get count for 300 engines, interested only in the 19 engines that are there in my lookup.

Secondly, the event count is coming incorrect for some of the engines.

[FrankVl]

In that case you could still add the subsearch into the appended search similar to how you had it before.

How the event count can be wrong I don’t know. I’m using the same stats command you had originally.

[Upas02]

But the main point still remains that I need to see engines with event count as 0 which I am not able to see. Only events with greater than zero is coming in the results.
Result i am getting -
EngineName count
Engine1 225
Engine2 567

Result I want to see -
EngineName count
Engine1 225
Engine2 567
Engine3 0
Engine4 0

[FrankVl]

Not sure what you did exactly, but the following should work just fine. It is pretty much the same as the other answer which you accepted:

 | inputlookup PriorityEngines | fields EngineName
 | eval count = 0
 | append [
   | search index=myindex [ | inputlookup PriorityEngines | fields EngineName ] | stats count by EngineName
 ]
 | stats max(count) as count by EngineName

[lugoon]

This works and makes total sense. First initialize values in the lookup table and then craft an appended search that will perform stats from the search and populate the lookup table.

It's Gold Jerry, Gold Jerry!