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
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
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.
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
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
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.
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.
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"
I have updated my answer as well. Please accept is as answer when you get time. Thanks.
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$""
Replace the search above table command with your main search.
Hi @macadminrohit ,
Sorry for the late response. I am not getting any results in this case.
That might be because you didnt put your lookup. I tested this in my environment and it worked like a charm.
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
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