Can one do less than greater than comparisons from fields in a lookup table?

[beattiedb]

I have a lookup table that is composed of beginning IP and ending IP addresses for a location and the name of the associated location.
A search returns the sourceIp of the splunkAgent that produced some data.
I want to determine what the location of the splunkAgent is by finding which of 20 or so possible IP ranges the sourceIp address falls in.
Can this be done and if so what does the command(s) look like to do the lookup and comparison?

[woodcock]

You need to convert your lookup from beginning and ending IP addresses to CIDR ranges instead. Then you can create a lookup definition with CIDR(<your field name here) on that field name and match your IPs that way.

[ololdach]

Hi, as stated above, lookup won't help. You could, however, try this one instead, using a join with inputlookup to get your values:
... deleted Plan A that isn't working in this case, as previously I used tokens instead of query fields... let's move to plan B...

This time it works, I've just tested it wit the following prices.csv:

product_id,effective_start,effective_end,price
foo,1560962373,1570962373,66
foo,1570962373,1590962373,69
bar,1550962373,1580962373,96
bar,1580962373,1590962373,99

And this is the query to solve your question:

|makeresults | eval product_id="foo" | eval time=now() 
| join max=0 product_id [|inputlookup prices.csv]|where time>effective_start AND time<effective_end

Note:
1. The join is still necessary, because you want one event per match, not one event in total with multi-value fields for price and dates that you would get from a lookup
2. As always, the first line is just to recreate some meaningful data for the join. Exchange with your own query to get the product_id
3. Use your own lookup instead of my prices.csv and for being a lazy typist I omitted the _date in the time names

[thanchen]

I have tried this, but with no luck. Seems that inputlookup's where clause can only filter it's own fields, my _time field was from before join.

My current solution is like:

search something | join type=left max=0 product_id [
             | inputlookup http_status | table effective_start_date, effctive_end_date, price ]
             | where (_time>effective_start_date) AND (_time<effective_end_date)

[ololdach]

Please take a look at the corrected and tested answer above. My apologies: I didn't test with your data the first time I tried. My bad!

[somesoni2]

If you lookup table has IP addresses in CIDR notation, you'd need to setup a Lookup definition with match type as CIDR before you could use lookup command. See below post for the same.

https://answers.splunk.com/answers/305211/how-to-match-an-ip-address-from-a-lookup-table-of.html

This post also has query that you can use to match your search data with lookup and add location field of matching value.

[thanchen]

My situation is a little different, not IP range but time range for price。I‘m looking for something like this:

 ... | lookup price (  _time>effective_start_date AND  _time<effctive_end_date) outputnew price AS history_price

Unfortunately, this doesn't match lookup syntax.