Splunk Search

Lookup With Value Between Two Lookup Fields

dcsteve24
Explorer

I have a lookup table which contains a varying low value and a high value for many rows, along with the desired value I wish to grab. For example (these are just dummy values as the actual CSV is much larger):

LowHighDesired Value
10001499550
15001599220
16001999700

 

I work through my search input and come to a number value I want to test in the range of the low and high. (low < myNumber AND high > myNumber), to then pull the desired value.

The typical input for "myNumber" is a multivalue, containing typically three or more. I've found I have more success breaking this up with mvexpand and rejoining it again later with stats. Im just noting this in case it provides any value for the answer. So my input before I need the value may look like this:

Field1Field2Field3Field4myNumberField5
BlahBlehMehWhatever1200
1400
1520
Stuff
JustDummyDataHere1510
1625
1780
Still


or if i break it apart with mvexpand, like this:

Field1Field2Field3Field4myNumberField5
BlahBlehMehWhatever1200Stuff
BlahBlehMehWhatever1400Stuff
BlahBlehMehWhatever1520Stuff
JustDummyDataHere1510Still
JustDummyDataHere1625Still
JustDummyDataHere1780Still



I want to be able to add the Desired Value to my table based on if myNumber falls between the Low and High. The number does not match the Low or High, but will always fall between one of the ranges.

So my final output should look like this when im done:

Field1Field2Field3Field4myNumberField5DesiredValue
BlahBlehMehWhatever

1200
1400
1520

Stuff550
550
220
JustDummyDataHere1510
1625
1780
Still220
700
700

 

I can't seem to figure out how to go about this. I have no problem breaking apart the multivalue and rejoining it, I just can't figure out how to do a lookup that falls within a two fields...

Routes Ive tried:

- If I use inputlookup I can use the where command to filter out values just fine. However, if I try mapping the same search to map search="| inputlookup search here | where low < $myNumber$ AND high > $myNumber$" it doesn't work, meaning I cant seem to find a way to link the two together.

- Join doesn't allow passing of vars and there isn't a shared field to join on, so no love there

- the lookup command but it doesn't have any evaluations built in that I can find; seems to only work for exact matches.

I appreciate any assistance I can get.

Labels (2)
0 Karma
1 Solution

rnowitzki
Builder

Hi @dcsteve24 ,

Thanks for the clarification.

I found a solution for this, using map command.

 

| makeresults 
| eval myNumber="1200 1400 1520 1520 1625 1780"
| makemv myNumber
| mvexpand myNumber
|  map search="|inputlookup low_high.csv |  where high > $myNumber$ AND low < $myNumber$ | eval myNumber=$myNumber$ | fields - high low"
| stats values(myNumber) by desired

 


You only need the lines starting with the map command, the ones above are just to make a table with the "myNumber" field.

My low_high.csv looks that I used looks like:

low     high  desired
1000 1499 550
1500 1599 220
1600 1999 700

BR
Ralph

--
Karma and/or Solution tagging appreciated.

View solution in original post

rnowitzki
Builder

Hi @dcsteve24 ,

Thanks for the clarification.

I found a solution for this, using map command.

 

| makeresults 
| eval myNumber="1200 1400 1520 1520 1625 1780"
| makemv myNumber
| mvexpand myNumber
|  map search="|inputlookup low_high.csv |  where high > $myNumber$ AND low < $myNumber$ | eval myNumber=$myNumber$ | fields - high low"
| stats values(myNumber) by desired

 


You only need the lines starting with the map command, the ones above are just to make a table with the "myNumber" field.

My low_high.csv looks that I used looks like:

low     high  desired
1000 1499 550
1500 1599 220
1600 1999 700

BR
Ralph

--
Karma and/or Solution tagging appreciated.

dcsteve24
Explorer

Hi Ralph,
Thanks! I was really close with the map command but failed to think of simply adding a few evals with the properties I need to keep. That's brilliant!

Thank you so much.

rnowitzki
Builder

Hi @dcsteve24,

Sorry, maybe it's me, but I don't understand your requirement. What is the relation between myNumber, low, high and desired value?

In the table you show as final output, the desired values are not within the range of the values in "myNumber".

But in the command that you provided as one of your attempts, it looks like you want to check if "myNumber" is between low and high  (where low < $myNumber$ AND high > $myNumber$")

Could you please explain in other words, how you would like to come from the initial csv lookup to your final output?  

Thanks 
Ralph



--
Karma and/or Solution tagging appreciated.
0 Karma

dcsteve24
Explorer

Hi Ralph,
myNumber isn't the range, its a number that will fall between the ranges; basically:
- 1200 (myNumber) is between 1000 (low) and 1499 (High) so it should return the desired value (500).
- 1400 (myNumber) is between 1000 (low) and 1499 (High) so it should return the desired value (500). 
- 1520 (myNumber) is between 1500 (low) and 1599 (high) so it should return the desired value (220)
- so on so forth

To sum it up in a algebra fashion:

- I have a lookup table with a high value (Y) and low value (Z) and my desired value (A). The high and low together make a range Y-Z. In the example, one would be 1500-1599 for a range.

- I have a value that falls in one of the Y-Z ranges of the lookup table. I identified this as MyNumber in the post but we'll say X here.

- So I need a way to take X, evaluates that it falls between Y and Z (Y < X AND Z > X) from the lookup table, and returns my desired field (A) from the lookup table. 

Hope that helps clear it up?

Unfortunately I am using dummy data because of protections on the information. So I cant give you the exact data and scenario I need this for, but if we can solve the above I can work from there. I simply cant figure out how to evaluate between a range in a lookup table to get my desired value.

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