topic Re: How do you create a lookup from a range of values? in Splunk Search

How do you create a lookup from a range of values?

siva_cg — Tue, 04 Dec 2018 22:04:01 GMT

Hi,

I am trying to create a report in which I would like to get the field value by looking into a range of values that are stored as start and end values in 2 different fields.

For example: lookup with below data
ProductName,ProductIDStart,ProductIDEnd
Choc,1,10
IceCream,11,20
Biscuit,21,30

Index data as below

index=a,type=purchase,productid=25

End report:
type,productid,ProductName
purchase,25,Biscuit

I tried to use where condition with inputlookup, but that didn't work.
index=a |table type,productid |eval ProductName=[|inputlookup lookupname where productid>ProductIDStart AND productid

Re: How do you create a lookup from a range of values?

nagarjuna280 — Wed, 05 Dec 2018 00:44:18 GMT

Re: How do you create a lookup from a range of values?

siva_cg — Wed, 05 Dec 2018 18:50:08 GMT

Hi @nagarjuna280,
Thank you for the response. I have tried the below search but I am getting only ProductName in the end results. What could be the issue?

Re: How do you create a lookup from a range of values?

macadminrohit — Wed, 05 Dec 2018 22:41:01 GMT

Try this :

| makeresults 
| eval Text="a,purchase,25" 
| eval index=mvindex(split(Text,","),0) 
| eval type=mvindex(split(Text,","),1) 
| eval productid=mvindex(split(Text,","),2) 
| table _time productid type index 
| map search="| inputlookup  yourlookup.csv where ProductStart< "$productid$" AND ProductEnd > "$productid$""

Re: How do you create a lookup from a range of values?

macadminrohit — Wed, 05 Dec 2018 22:41:45 GMT

Replace the search above table command with your main search.

Re: How do you create a lookup from a range of values?

siva_cg — Fri, 07 Dec 2018 10:41:10 GMT

Hi @macadminrohit ,
Sorry for the late response. I am not getting any results in this case.

Re: How do you create a lookup from a range of values?

sdchakraborty — Fri, 07 Dec 2018 11:14:29 GMT

Hi,
I created test.csv lookup according to your problem like below,

ProductName,ProductIDStart,ProductIDEnd
Choc,1,10
IceCream,11,20
Biscuit,21,30

this is my indexed data(data.txt) in main index,

type,productid
purchase,25
purchase,14

The below search I am using to look for the range,

source="data.txt" sourcetype="csv" 
|  table type,productid
|  map search="|inputlookup test |  where ProductIDEnd > $productid$ AND ProductIDStart < $productid$| eval type = $type$ productid=$productid$ | table type,productid,ProductName "

Hope that will answer your question.

Sid

Re: How do you create a lookup from a range of values?

siva_cg — Fri, 07 Dec 2018 13:57:44 GMT

Hi @sdchakraborty ,
Sorry. It didn't give me expected output. I am expecting ProductName, type and productid in the output but details from index are missing using map command.

Re: How do you create a lookup from a range of values?

sdchakraborty — Fri, 07 Dec 2018 14:04:52 GMT

Inside the map search you can pass those filed values from outer search just like we passed productId. So please add the below command inside the map search end.

| table $peoductname$, $productid$ ...
The output will have those fields as well. Let me know how it goes.

Re: How do you create a lookup from a range of values?

siva_cg — Fri, 07 Dec 2018 15:10:51 GMT

Hi @sdchakraborty,
Modifying the query a little more gave me the output as expected. Thank you all for the help. Final query I used is as below:

Re: How do you create a lookup from a range of values?

sdchakraborty — Fri, 07 Dec 2018 15:47:50 GMT

I have updated my answer as well. Please accept is as answer when you get time. Thanks.

Re: How do you create a lookup from a range of values?

macadminrohit — Fri, 07 Dec 2018 16:19:58 GMT

That might be because you didnt put your lookup. I tested this in my environment and it worked like a charm.

Re: How do you create a lookup from a range of values?

blablabla — Wed, 06 Dec 2023 07:53:38 GMT

I do not recommend to use map. It is an extremely slow command.

As far as I know Splunk unfortunately does not support range lookups. We also had this issue and at the end we transformed our lookup file in a way, that every value of the range is a single row.

It leads to large lookup files but performing the lookup is still much more performant than map or similar commands

Re: How do you create a lookup from a range of values?

blablabla — Wed, 06 Dec 2023 07:57:13 GMT

Transform your lookup in a way, that every productID has a row. Then you can use the lookup in its native way.

It will lead to large lookup files, but the lookup itself is still very performant.

Every workaround with map, subsearch etc. will be slow and imperformant.