Input lookup results: How to exclude results from Lookups?

[Ash]

Hi,

index=network sourcetype=cisco:asa NOT src_ip IN("10.0.0.0/8","10.0.0.1,"10.0.0.2") | bucket _time span=1m
| stats dc(dest_port) as num_dest_port dc(dest_ip) as num_dest_ip by src_ip _time
| where num_dest_port > 500 OR num_dest_ip > 500 | eval Total=num_dest_port+num_dest_ip | sort -Total | dedup src_ip |

With Lookups:

index=network sourcetype=cisco:asa NOT src_ip IN("10.0.0.0/8","10.0.0.0/8","10.0.0.1,"10.0.0.2") | search NOT [|inputlookup Blocked_IP.csv] | fields src_ip | bucket _time span=1m
| stats dc(dest_port) as num_dest_port dc(dest_ip) as num_dest_ip by src_ip _time
| where num_dest_port > 500 OR num_dest_ip > 500 | eval Total=num_dest_port+num_dest_ip | sort -Total | dedup src_ip

I am not able to exclude the results from the Lookups or if I modify the search I'm not getting any results at all. Kindly help.

[FelixLeh · Accepted solution]

So if I understand you correctly you want something like this:

index=network sourcetype=cisco:asa NOT src_ip IN("10.0.0.0/8","10.0.0.1,"10.0.0.2")
| lookup Blocked_IP src_ip OUTPUT src_ip as is_blocked
| where isnull(is_blocked)
| bucket _time span=1m
| stats dc(dest_port) as num_dest_port dc(dest_ip) as num_dest_ip by src_ip _time
| where num_dest_port > 500 OR num_dest_ip > 500
| eval Total=num_dest_port+num_dest_ip
| sort -Total
| dedup src_ip

You can lookup any csv within a search with "|lookup" and you don't need to reference the name of the csv if you have made a lookup definition for it. (e.g. if you have a csv named "my_lookup.csv" and a definition called "my_lookup" you can reference it in searches with "|lookup my_lookup")

Using "| fields src_ip" removes all fields except src_ip leading to you not seeing events.

[Ash]

Thanks you it helped. BTW what for where isnull(is_blocked) is used.

[FelixLeh]

| lookup Blocked_IP src_ip OUTPUT src_ip as is_blocked creates a field is_blocked for every event where a matching IP is found in the lookup. | where isnull(is_blocked) then removes all events where the field is_blocked has a value (all events that have a matching ip).