Matched IP from 2 different lookup with condition

[Akmal57]

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.

Please assist on this.

Thank you

[yuanliu · Accepted solution]

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")

Your sample data gives

Hostname	IP_A	IP_B	IP	match
Host A	10.10.10.1 10.10.10.2	10.10.10.1	10.10.10.1 10.10.10.2	yes
Host B	172.1.1.1	172.1.1.1 172.1.1.2	172.1.1.1 172.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"
| multikv forceheader=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"
    | multikv forceheader=1
    | 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")

[ITWhisperer]

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"

[Akmal57]

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