Splunk Search

Matched IP from 2 different lookup with condition

Akmal57
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.

Please assist on this.

Thank you

Labels (3)
Tags (1)
0 Karma
1 Solution

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

 

 

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

 

 

View solution in original post

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

 

 

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 B172.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
SplunkTrust
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"

Akmal57
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...