Splunk Search

Comparing lists from two searches

BryanScovill
Explorer

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.

Tags (2)
0 Karma
1 Solution

BryanScovill
Explorer

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!

View solution in original post

0 Karma

BryanScovill
Explorer

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!

0 Karma

somesoni2
Revered Legend

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)
0 Karma

BryanScovill
Explorer

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"

0 Karma

Vijeta
Influencer

@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)
0 Karma

BryanScovill
Explorer

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

0 Karma

Vijeta
Influencer

@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
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...