How would I display all hosts with AND without events?

[tylergps]

I'm trying to audit an environment based on Window's RDP event codes 21, 22, and 25. I'm able to display the number of RDP events for systems that have them, but for the ones that don't, I can't get them to display a value of 0 in the results.

I tried using "fillnull" but couldn't find a solution.

Search:

source="wineventlog:microsoft-windows-terminalservices-localsessionmanager/operational" (EventCode=21 OR EventCode=22 OR EventCode=25) | stats count by host

[martin_mueller · Accepted solution]

Assuming you have a list of expected hosts in a lookup file with a single column host, you can do something like this:

... | stats count by host | append [inputlookup expected_hosts | eval count = 0] | stats max(count) as max by host

[martin_mueller]

You could of course use Splunk to build this lookup, adding hosts but never removing them: http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/

[tylergps]

I think I could definitely make something work with that.

Will try outputting "host=* | stats count by host" to a lookup file then using your above answer to see if yields the desired results. Thanks!

Edit
TESTED AND WORKS GREAT! THANK YOU!

[tylergps]

I wish you would be able to do a sub search or something.

Wanted to try to avoid adding complexity with the lookup file. It may be my only solution though...

[MuS]

If you want to have all the hosts listed, even those without any events, then this is the way to go to make sure you get all hosts.