Comparing lists from two searches

[BryanScovill]

I've been trying to research this for a couple of days and haven't been able to find anything just right. I am attempting to run a search (firewall source traffic with no DNS) and taking those results and searching our DHCP records so as to remove any DHCP addresses. Dynamic DNS ends up leaving those IPs nameless and my actual goal is to identify IP in use that haven't not been assigned (ie pilfered IPs)

So, basically search1 & search2 generate lists of IPs and I want a list of IPs that are in search1 but not search2.

This is one of many, many attempts...

index=firewall src_ip="" src_zone!=outside
| dedup src_ip
| lookup dnslookup clientip AS src_ip OUTPUT clienthost AS src_host
| search NOT src_host="*"
| table src_zone,src_ip,src_host
| join src_ip type=left
[ search index=dhcp
| eval src_ip=coalesce(DHCP_ip, DHCP_renewed), filter_out="y"
| table src_ip, filter_out]
| where not like(filter_out, "y")

Any guidance would be appreciated.

[BryanScovill · Accepted solution]

Thanks all. The many hints got us there. Here is where we ended up.

(index=firewall src_ip="" src_zone!=outside earliest=-24h ) OR (index=dhcp DHCP_ip="" OR DHCP_renewed="" earliest=-26h)
| fields src_ip, src_zone, index, DHCP_ip, DHCP_renewed
| eval dhcpip=coalesce(DHCP_ip, DHCP_renewed)
| lookup dnslookup clientip AS src_ip OUTPUT clienthost AS src_host
| search NOT src_host="*"
| eval src_ip=coalesce(dhcpip,src_ip)
| chart count by src_ip, index | where dhcp=0

Gracias!

[somesoni2]

Give this a try (no join)

index=firewall src_ip="" src_zone!=outside 
| dedup src_ip 
| lookup dnslookup clientip AS src_ip OUTPUT clienthost AS src_host 
| search NOT src_host="*" 
| table src_zone,src_ip,src_host 
| WHERE NOT ([ search index=dhcp 
| eval src_ip=coalesce(DHCP_ip, DHCP_renewed)| table src_ip ] 

OR

index=firewall src_ip="" src_zone!=outside 
 | dedup src_ip 
 | lookup dnslookup clientip AS src_ip OUTPUT clienthost AS src_host 
 | search NOT src_host="*" 
 | table src_zone,src_ip,src_host 
 | append
 [ search index=dhcp 
 | eval src_ip=coalesce(DHCP_ip, DHCP_renewed), filter_out="y" 
 | table src_ip, filter_out] 
 | stats values(*) as * by src_ip 
 | where ISNULL(filter_out)

[BryanScovill]

Wouldn't the second option just add the list from the subsearch to the the original search? I thought that was what append did.

The first one seems promising, but "tables" is fighting with me. I am thinking that comparing lists via tables may be a no go, so I am experimenting with "fields"

[Vijeta]

@BryanScovill Try this

index=firewall src_ip="" src_zone!=outside 
| dedup src_ip 
| lookup dnslookup clientip AS src_ip OUTPUT clienthost AS src_host 
| search NOT src_host="*" 
| table src_zone,src_ip,src_host 
| join src_ip type=left 
[ search index=dhcp 
| eval src_ip=coalesce(DHCP_ip, DHCP_renewed), filter_out="y" 
| table src_ip, filter_out] 
| where ISNULL(filter_out)

[BryanScovill]

No Joy. I don't get why but data in the dhcp index still shows up. 😞

[Vijeta]

@BryanScovill Subserach has a limit of returning upto 10,000 results and therefore you are getting incomplete results making the logic not working.
As @somesoni2 suggested please avoid using join. Try something like this query-

 (index=firewall src_ip="" src_zone!=outside ) OR (index=dhcp)| eval dhcp=coalesce(DHCP_ip, DHCP_renewed) 
 | dedup src_ip 
 | lookup dnslookup clientip AS src_ip OUTPUT clienthost AS src_host 
 | search NOT src_host="*" 
 | eval src_ip=coalesce(dhcp,src_ip)
| stats count(eval(index=firewall) ) as firewall, count(eval(index=dhcp)) as dhcp by src_ip| where dhcp=0