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

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

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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!