How to find matching values in lookup?

[POR160893]

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?????

[richgalloway · Accepted solution]

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

[FrankVl]

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.

[POR160893]

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

[FrankVl]

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

[POR160893]

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

[FrankVl]

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

[richgalloway]

Yes, I removed the URL.

[POR160893]

Thanks Richard!