Hi, I am struggling to form my search query along with lookup. So the scenarios is like this -
I have a search query which gets a web service response in which there is a tag "identifier" and this tags occurs multiple times in the same event with values like like P123456, D123465 etc.
On other side I have a lookup which contains some of these identifiers (only D*). Now I only want to retrieve those UNIQUE events which matches any of the identifier from the lookup.
I have written a query but not sure if that is correct (don't want to use join) and if someone can help me with a better search that would be highly appreciated.
index=test sourcetype=sales_log getProdDetails "soap response"
| rex field=_raw "=(?[^|]+)"
| rex field=_raw "identifier>(?[^<]+)" max_match=200
| eval productIdentifierNew = mvfilter(productIdentifier LIKE "D%")
| mvexpand productIdentifierNew
| join productIdentifierNew max=0
[| inputlookup productIdentifier.csv
| rename productIdentifier as productIdentifierNew]
Best Regards
Like this:
index=test sourcetype=sales_log getProdDetails "soap response"
| rex field=_raw "identifier>(?<productIdentifierNew>D[^\<]+)" max_match=200
| lookup productIdentifier productIdentifierNew OUTPUT productIdentifier AS KeepMe
| where isnotnull(KeepMe)
Hi Shashank_87,
to not use join is a good idea!
in fact you don't need to use join command with a lookup because the lookup command is a join!
so you could try something like this:
index=test sourcetype=sales_log getProdDetails "soap response"
| rex field=_raw "=(?[^\|]+)"
| rex field=_raw "identifier>(?[^\<]+)" max_match=200
| eval productIdentifierNew = mvfilter(productIdentifier LIKE "D%")
| mvexpand productIdentifierNew
| lookup productIdentifier.csv productIdentifier AS productIdentifierNew OUTPUT interesting fields
| table ....
Bye.
Giuseppe
@gcusello
Hi Giuseppe, Thanks for your swift response. I tried using lookup command but what should i be putting in the interesting fields? So my lookup only contains one field "productIdentifier" and fields I want is uniqueID which is present in the events so that I can dedup it afterwards to get the unique events.