Reporting

How to find matching values in lookup?

POR160893
Builder

Hi,

I have the following query:

 

index = ABC
| eval domain=mvindex(split(EMAIL_TXT, "@"), 1)
| stats dc(EMAIL_TXT) AS Count_EmailAddress, values(domain) as domain values(EMAIL_TXT) as Email_Address, values(STRT_DTS) AS Start_Date by IP_ADDR
| where Count_EmailAddress >1
| sort -Count_EmailAddress

 



I also have a lookup with a list of IPs:
|inputlookup HighRiskDomain 

How do I create a new field called "HighRiskIP" that would have with "Yes" or "No" values, depending on whether the IP for that given row matches an IP on the lookup?????

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Someone really needs to do a .conf talk on the differences between inputlookup and lookup because they seem to be confused often.

To find out if a given value is in a lookup table, use the lookup command.  Assuming the lookup table contains a single field called "IP_ADDR", this should do it.

index = ABC
| eval domain=mvindex(split(EMAIL_TXT, "@"), 1)
| stats dc(EMAIL_TXT) AS Count_EmailAddress, values(domain) as domain values(EMAIL_TXT) as Email_Address, values(STRT_DTS) AS Start_Date by IP_ADDR
| where Count_EmailAddress >1
| sort -Count_EmailAddress
```Look up the IP address.  
The ip_addr_exists field will be null if the address is not in the table.```
| lookup HighRiskDomain IP_ADDR OUTPUT ip_addr_exists
| eval HighRiskIP = if(isnotnull(ip_addr_exists), "Yes", "No")

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Someone really needs to do a .conf talk on the differences between inputlookup and lookup because they seem to be confused often.

To find out if a given value is in a lookup table, use the lookup command.  Assuming the lookup table contains a single field called "IP_ADDR", this should do it.

index = ABC
| eval domain=mvindex(split(EMAIL_TXT, "@"), 1)
| stats dc(EMAIL_TXT) AS Count_EmailAddress, values(domain) as domain values(EMAIL_TXT) as Email_Address, values(STRT_DTS) AS Start_Date by IP_ADDR
| where Count_EmailAddress >1
| sort -Count_EmailAddress
```Look up the IP address.  
The ip_addr_exists field will be null if the address is not in the table.```
| lookup HighRiskDomain IP_ADDR OUTPUT ip_addr_exists
| eval HighRiskIP = if(isnotnull(ip_addr_exists), "Yes", "No")

 

---
If this reply helps you, Karma would be appreciated.

FrankVl
Ultra Champion

First of all: you may want to remove the URL under the lookup name 🙂

There's multiple ways to do this, but in the end it comes down to doing a | lookup command and then some eval to translate the output of the lookup command to yes and no. One way would be to add a column in your lookup called "HighRiskIP" with value "yes" on each row. Then you can do:

 

...your current query...
| lookup HighRiskDomain IP as IP_ADDR OUTPUT HighRiskIP
| eval HighRiskIP = coalesce(HighRiskIP,"no")

 

Note: you may need to replace "IP" in the lookup command with whatever the ip address field in your lookup is called.

If you don't want to add additional columns to the lookup table, you can also do something like this:

 

...your current query...
| lookup HighRiskDomain IP as IP_ADDR OUTPUT IP as HighRiskIP
| eval HighRiskIP = if(isnull(HighRiskIP),"no","yes")

 

Note: use above as inspiration, don't just copy paste it and trust my code is bug free, I haven't tested these.

0 Karma

POR160893
Builder

Do not see the URL you are referring to btw .....

0 Karma

FrankVl
Ultra Champion

Looks like that has already been fixed (probably a copy paste mistake) 🙂

0 Karma

POR160893
Builder

I made no change myself .... hopefully it wasn't too fatal 🙂

0 Karma

FrankVl
Ultra Champion

It was a link to your splunk environment I think. Perhaps @richgalloway was so kind to strip it for you.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Yes, I removed the URL.

---
If this reply helps you, Karma would be appreciated.

POR160893
Builder

Thanks Richard!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...