Search for items NOT matching in a lookup from stats table

[martincd537]

I have found many answers but none seem to satisfy what I am trying to accomplish.

I am looking through some juniper logs and am making a table of IP's that are talking to a number of hosts. (will have a threshold). But I want to then take those noisy IP's and compare them to a lookup table of mine.

My search:

index=juniper src_ip!=10.10.254.* | stats dc(dest_ip) as IP_Count by src_ip | where IP_Count>50 |

Where would I insert my 'NOT' lookup command or if that is something I would even use?

This works: index=juniper src_ip!=10.10.24.* | stats dc(dest_ip) as IP_Count by src_ip | where IP_Count>50 | lookup server_assets IP_Address as src_ip

But I dont want to see the things listed in this, I want to see the ones that do not match in the lookup.

Any Idears?

Thanks.

[martincd537]

That is what I ended up doing. Thought there might be a different way. But thank you, I will leave it like that.

Ended up with this:

index=juniper src_ip!=10.10.24.* | stats dc(dest_port) as Port_Count by src_ip | where Port_Count>50 | lookup server_assets IP_Address as src_ip | where isnull(DNSName) | table src_ip Port_Count

[acharlieh · Accepted solution]

If we have your lookup create an additional field, then we can filter and show only those that are not in your lookup (e.g. those that do not have the new field)... Like so:

 <base> | stats ... | where ... | lookup server_assets IP_Address as src_ip OUTPUT IP_Address | where isnull(IP_Address) 

[martincd537]

That is what I ended up doing. Just from my readings, I thought it may have been a round about way to do it, but gets the job done.

Thanks,

Ended up with this:

index=juniper src_ip!=10.10.24.* | stats dc(dest_port) as Port_Count by src_ip | where Port_Count>50 | lookup server_assets IP_Address as src_ip | where isnull(DNSName) | table src_ip Port_Count