Splunk Search

How to show a count of "0" for hosts with no events in my search results?

harjotsidhu82
New Member

I am trying to do a search for certain hosts and get counts on the number of events available for each host while trying to get the time of last log event. I am unable to get the resulting table to show all hosts even if the event count is 0.

host="server1" OR host="server2" OR host="server3" OR host="server4" | eval time=strftime(_time, "%b %d, %Y %r") |chart count as "TotalEvents", earliest(time) as "LastLog" by host

I get the following result now:

host TotalEvents LastLog
server1 25 Oct 27, 2015 11:29:56 AM
server2 1025 Sep 10, 2015 09:52:02 AM
server4 58 Sep 24, 2015 09:49:02 AM

I want server3 to show up in the table below even if there are no matching events:

host TotalEvents LastLog
server1 25 Oct 27, 2015 11:29:56 AM
server2 1025 Sep 10, 2015 09:52:02 AM
server3 0
server4 58 Sep 24, 2015 09:49:02 AM

0 Karma

woodcock
Esteemed Legend

Build a lookup file called my_hosts.csv that has every server in it with just 1 field named host; then do this:

index="YouShouldAlwaysSpecifyAnIndex" AND sourcetype="AndSourcetypeToo" AND [|inputlookup my_hosts.csv]
| eval time=strftime(_time, "%b %d, %Y %r")
| inputlookup append=t my_hosts.csv
| chart count(_time) AS "TotalEvents", earliest(time) AS "LastLog" BY host

prakashaig
Explorer

this helps, thank you!

woodcock
Esteemed Legend

If this solves it then come back an click Accept to close the question an be sure to UpVote any helpful comments or answers

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi harjotsidhu82,

take a look at this answer which handles the same topic https://answers.splunk.com/answers/176466/how-to-use-eval-if-there-is-no-result-from-the-bas-1.html

Hope this helps ...

cheers, MuS

0 Karma

prakashaig
Explorer

no the link doesnt help me. please explain it how should we get the host which doesnt bring up any value for the search query.

0 Karma

MuS
SplunkTrust
SplunkTrust

This a different use case, and I provide another link to a very good post https://www.duanewaddle.com/proving-a-negative/ that explains step by step how this can be done.

cheers, MuS

0 Karma

prakashaig
Explorer

no, the link doesnt help. can you please help me with the answer here.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!