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

[harjotsidhu82]

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

[woodcock]

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]

this helps, thank you!

[woodcock]

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

[MuS]

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

[prakashaig]

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.

[MuS]

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

[prakashaig]

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