Splunk Search

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

siva_cg
Path Finder

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

Tags (2)
0 Karma
1 Solution

sdchakraborty
Contributor

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

View solution in original post

blablabla
Path Finder

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.

0 Karma

sdchakraborty
Contributor

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

blablabla
Path Finder

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

0 Karma

siva_cg
Path Finder

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.

0 Karma

sdchakraborty
Contributor

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.

0 Karma

siva_cg
Path Finder

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:

index=a | map search="| inputlookup lookupname | where ProductIDStart> "$productid$" AND "$productid$" < ProductIDEnd| eval productid="$productid$" |eval type="$type$" | table type,productid,ProductName"

0 Karma

sdchakraborty
Contributor

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

0 Karma

macadminrohit
Contributor

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$""
0 Karma

macadminrohit
Contributor

Replace the search above table command with your main search.

0 Karma

siva_cg
Path Finder

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

0 Karma

macadminrohit
Contributor

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

0 Karma

nagarjuna280
Communicator

Try something like this index=a | map search="| inputlookup lookupname | where ProductIDStart> $ProductID$ AND $ProductID$ < ProductIDEnd| table ProductName "| table type,productid,ProductName

index=a | map search="| inputlookup lookupname | where ProductIDStart>\" $ProductID$\" AND \"$ProductID$\" < ProductIDEnd| table ProductName "| table type,productid,ProductName

0 Karma

siva_cg
Path Finder

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?

index=a | map search="| inputlookup lookupname | where ProductIDStart> "$ProductID$" AND "$ProductID$" < ProductIDEnd| table ProductName "| table type,productid,ProductName

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...