topic Re: Help with Splunk search query and Lookup in Splunk Search

Help with Splunk search query and Lookup

Shashank_87 — Wed, 30 Sep 2020 01:35:08 GMT

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.

Best Regards

Re: Help with Splunk search query and Lookup

gcusello — Fri, 02 Aug 2019 09:31:16 GMT

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

Re: Help with Splunk search query and Lookup

Shashank_87 — Fri, 02 Aug 2019 10:08:06 GMT

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

Re: Help with Splunk search query and Lookup

woodcock — Tue, 06 Aug 2019 00:21:02 GMT

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)