I want to see a table of the hosts that are in the first search but not in the second.

[ehastings1982]

We have firewalls sending SYSLOG into us. We also get traffic logs from the firewalls. What Im trying to do is first get a list of all hosts (easy enough), and then search for the ones that have not sent a message that contains a field value.

So my searches would look like this

Search 1:

host=*

Search 2:
policy_id=23

Now I want to see a table of the hosts that are in the first search but not in the second.

[aholzer · Accepted solution]

host=* policy_id!=23

This will get you all hosts that do not have a policy_id of 23

[aholzer]

That's exactly right. Your "if it was policy 23 mark pol23 as 1" explanation goes across 2 pipes, the eval and the stats sum, but your explanation is as good as what I could give 🙂

[ehastings1982]

AMAZING!. Now so I can learn this can you explain it? My attempt:

get all the hosts | count each time each device used each policy | if it was policy 23 mark pol23 as 1 | if there is a 0 in the no23 display the table of hosts ?

[aholzer]

Ah, I think I understand your use case now.

host=* | stats count by host, policy_id | eval pol23 = if(policy_id=23,1,0) | stats sum(pol23) as no23 by host | where no23=0 | table host

This should get you a list of hosts excluding all hosts that have even one event that had a policy_id=23 in it.

[ehastings1982]

That will just filter my results to not show anything with policy_23. What I want to know is what hosts has no results for policy_id=23.