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?
@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
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)
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. 🙂
@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
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
@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 ?
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.