How to compare one value from another in a lookup?

[w344423]

I got a number in my first lookup and i want to compare this number with a start and end number in a lookup, how do i do it?

| inputlookup IPaddress
| table IPtoNumber IPAddress UserID

Expected output in table format

IPtoNumber IPAddress UserID
100               1.1.1.1       john

Base on the above result, i looked up another table that had the country name

| inputlookup geoIP
| table startNumber EndNumber Country

Expected output in table format

startNumber EndNumber Country
1                     10                 Somewhere1
11                   90                 Somewhere2
91                   100               Somewhere3

How do I pass the IPtoNumber from IPaddress lookup into geoIP lookup and return me the Country in geoIP?

[DalJeanis]

The smart thing to do here is to create a better lookup table

 | inputlookup geoIP
 | table startNumber EndNumber Country
 | eval EndNumber=EndNumber+1
 | eval IPtoNumber=mvrange(startNumber, EndNumber)
 | mvexpand IPtoNumber
 | table IPtoNumber Country 

This creates one record per IPtoNumber between startNumber and EndNumber. Whether that is reasonable is going to depend on how big your number ranges are. You can run the above by itself to see if the resulting output is less than 50K results. If so, then you can use that as a direct match.

 | inputlookup IPaddress
 | table IPtoNumber IPAddress UserID
 | join type=left IPtoNumber [ that whole inputlookup search  above ]

---

If there are more than about 25K records out of that search, then you might be better off doing something more like this...

 | inputlookup IPaddress
 | table IPtoNumber IPAddress UserID

 | rename COMMENT as "add in all the GeoIP records, keep only needed fields from both types"
 | inputlookup append=t geoIP
 | table startNumber EndNumber Country  IPtoNumber IPAddress UserID

 | rename COMMENT as "if it's a GeoIP record, calculate all the IPtoNumber values"
 | eval EndNumber=EndNumber+1
 | eval IPtoNumber=coalesce(IPtoNumber,mvrange(startNumber, EndNumber))
 | mvexpand IPtoNumber

 | rename COMMENT as "roll the GeoIP record info over to the IPaddress records then drop the GeoIP records"
 | eventstats max(Country) as Country by IPtoNumber
 | where isnotnull(IPAddress)

[mstjohn_splunk]

hi @w344423,

Did the answer below solve your problem? If so, please resolve this post by approving it. If not, keep us updated so that someone else can help solve your problem.

Also, if you're feeling generous, give out an upvote to the user that helped ya. Our users love them upvotes. 🙂

[renjith_nair · Accepted solution]

@w344423,

Try

|inputlookup IPaddress | table IPtoNumber IPAddress UserID
|lookup geoIP  startNumber as  IPtoNumber OUTPUTNEW Country as C1
|lookup geoIP  EndNumber as  IPtoNumber OUTPUTNEW Country as C2
|eval Country=coalesce(C1,C2)|table  IPtoNumber IPAddress UserID Country

[w344423]

Hi Renjith,

dont seems to be able to get any data, heres my query,

Table where all the data are and needs to compare with another KV,
| inputlookup IPs
| table IPtoInt IP

Output,
IPtoInt IP

3707717043 220.255.69.179
982689272 58.146.165.248
1947134216 116.14.233.8
3707757158 220.255.226.102
1947125255 116.14.198.7

Lookup KV,
| inputlookup GeoIPCountry
| table IPtoIntStart IPtoIntEnd Country

Output,
IPtoIntStart IPtoIntEnd Country
16777216 16777471 Australia
16777472 16778239 China
16778240 16779263 Australia
16779264 16781311 China
16781312 16785407 Japan

How do i get the value of IPtoInt from IPs and match the value between IPtoIntStart IPtoIntEnd from GeoIPCountry and return the country, which will have the final output of,

IPtoInt IP Country

3707717043 220.255.69.179 Singapore
982689272 58.146.165.248 Singapore
1947134216 116.14.233.8 Singapore
3707757158 220.255.226.102 Singapore
1947125255 116.14.198.7 Singapore

[renjith_nair]

@w344423,
Sorry I didnt get that. If you take 3707717043 as an example, how do you match it with Country? Because in the second lookup, I cant see Singapore. Do you mean to say that, 3707717043 is a number between IPtoIntStart and IPtoIntEnd ?

[w344423]

there are more fields in the second lookup, those where just a example becuase there are more than 10k of lines inside. and yes 3707717043 is a number between IPtoIntStart and IPtoIntEnd.

so i want to lookup the number i have in the first lookup/index and compare to the second lookup table. in that table i got IPtoIntStart, IPtoIntEnd and country. so if that numbers falls within that range it will return country.