Splunk Search

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

beattiedb
New Member

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?

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

ololdach
Builder

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
Explorer

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)
0 Karma

ololdach
Builder

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!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

thanchen
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...