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.
host=* policy_id!=23
This will get you all hosts that do not have a policy_id of 23
host=* policy_id!=23
This will get you all hosts that do not have a policy_id of 23
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 🙂
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 ?
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.
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.