Splunk Search

## Matched IP from 2 different lookup with condition

Path Finder

Hi, i have 2 lookup tables, which are lookup A and B. Both of the lookups contain field Hostname and IP.

There is some scenario like below:

Lookup A

Hostname        IP

Host   A           10.10.10.1

10.10.10.2

Host    B          172.1.1.1

Lookup B

Hostname        IP

Host   A           10.10.10.1

Host    B          172.1.1.1
172.1.1.2

Based on scenario above,  I need a result on IP which lookup A and B does not match based on Host.

But as long 1 IP in lookup A matches with lookup B, it is fine and lookup B should not have multiple IP. So, it should not match even have match IP.

For your info,both lookups have multiple IPs for a host.

Based on above lookup sample, Host A should match and Host B should not match based on my condition.

Thank you

Labels (3)

• ### stats

Tags (1)
1 Solution
SplunkTrust

Let me see if I can restate the requirement correctly:  if IP values for a given Hostname overlap in the two lookups, that's a match unless lookup A contains only one single IP.  A mismatch happens if there is zero overlap of IP for a Hostname in the two, or if lookup A contains a single IP for that Hostname.

Mathematically, this translates into a test of unique values because if there is any overlap, total number of unique IPs must be smaller than the sum of unique IPs in each lookup.  Hence

``````| inputlookup lookup_A
| stats values(IP) as IP_A by Hostname
| inputlookup lookup_B append=true
| stats values(IP) as IP_B by Hostname
| eval IP = coalesce(IP_A, IP_B)
| stats values(IP_A) as IP_A values(IP_B) as IP_B values(IP) as IP by Hostname
| eval match = if(mvcount(IP_A) == 1 OR mvcount(IP) == mvcount(IP_A) + mvcount(IP_B), "no", "yes")``````

 Hostname IP_A IP_B IP match Host A 10.10.10.110.10.10.2 10.10.10.1 10.10.10.110.10.10.2 yes Host B 172.1.1.1 172.1.1.1172.1.1.2 172.1.1.1172.1.1.2 no

Below is an emulation that you can play with and compare with real data

``````| makeresults
| eval _raw = "Hostname,IP
Host A,10.10.10.1
Host A,10.10.10.2
Host B,172.1.1.1"
| fields - _* linecount
| stats values(IP) as IP_A by Hostname
``` above emulates
| inputlookup lookup_A
| stats values(IP) as IP_A by Hostname
```
| append
[| makeresults
| eval _raw = "Hostname,IP
Host A,10.10.10.1
Host B,172.1.1.1
Host B,172.1.1.2"
| fields - _* linecount
| stats values(IP) as IP_B by Hostname]
``` subsearch emulates
| inputlookup lookup_B append=true
| stats values(IP) as IP_B by Hostname
```
| eval IP = coalesce(IP_A, IP_B)
| stats values(IP_A) as IP_A values(IP_B) as IP_B values(IP) as IP by Hostname
| eval match = if(mvcount(IP_A) == 1 OR mvcount(IP) == mvcount(IP_A) + mvcount(IP_B), "no", "yes")``````

SplunkTrust

Let me see if I can restate the requirement correctly:  if IP values for a given Hostname overlap in the two lookups, that's a match unless lookup A contains only one single IP.  A mismatch happens if there is zero overlap of IP for a Hostname in the two, or if lookup A contains a single IP for that Hostname.

Mathematically, this translates into a test of unique values because if there is any overlap, total number of unique IPs must be smaller than the sum of unique IPs in each lookup.  Hence

``````| inputlookup lookup_A
| stats values(IP) as IP_A by Hostname
| inputlookup lookup_B append=true
| stats values(IP) as IP_B by Hostname
| eval IP = coalesce(IP_A, IP_B)
| stats values(IP_A) as IP_A values(IP_B) as IP_B values(IP) as IP by Hostname
| eval match = if(mvcount(IP_A) == 1 OR mvcount(IP) == mvcount(IP_A) + mvcount(IP_B), "no", "yes")``````

 Hostname IP_A IP_B IP match Host A 10.10.10.110.10.10.2 10.10.10.1 10.10.10.110.10.10.2 yes Host B 172.1.1.1 172.1.1.1172.1.1.2 172.1.1.1172.1.1.2 no

Below is an emulation that you can play with and compare with real data

``````| makeresults
| eval _raw = "Hostname,IP
Host A,10.10.10.1
Host A,10.10.10.2
Host B,172.1.1.1"
| fields - _* linecount
| stats values(IP) as IP_A by Hostname
``` above emulates
| inputlookup lookup_A
| stats values(IP) as IP_A by Hostname
```
| append
[| makeresults
| eval _raw = "Hostname,IP
Host A,10.10.10.1
Host B,172.1.1.1
Host B,172.1.1.2"
| fields - _* linecount
| stats values(IP) as IP_B by Hostname]
``` subsearch emulates
| inputlookup lookup_B append=true
| stats values(IP) as IP_B by Hostname
```
| eval IP = coalesce(IP_A, IP_B)
| stats values(IP_A) as IP_A values(IP_B) as IP_B values(IP) as IP by Hostname
| eval match = if(mvcount(IP_A) == 1 OR mvcount(IP) == mvcount(IP_A) + mvcount(IP_B), "no", "yes")``````

SplunkTrust

Assuming IP comes in from the look up as a multi-value field, you could try something like this:

``````| inputlookup lookup_A
| eval tag="A"
| append
[ | inputlookup lookup_B
| eval tag="B" ]
| mvexpand IP
| stats values(tag) as tag by Hostname IP
| nomv tag
| where tag="B"``````
Path Finder

Hi @ITWhisperer , is there any way to match the ip without expand the ip?

Get Updates on the Splunk Community!