https://community.splunk.com/t5/Splunk-Search/Use-of-Lookup-with-search-query/m-p/427338#M122340 <P>One way to do it:</P> <PRE><CODE>| makeresults | eval holdings="U14355|U114576|D105994" | makemv delim="|" holdings | mvexpand holdings </CODE></PRE> <P>This breaks up your holdings field into 3 separate events that can be compared against the field in your lookup.</P> <P>In your case, it would look something like:</P> <PRE><CODE>index=test sourcetype="test_logs" "Customer Cart:" | rex field=_raw "holdings \= \'{(?[^}]+)}\'" | makemv delim="|" holdings | mvexpand holdings | lookup codes.csv (column) AS holdings </CODE></PRE> <P>Where (column) is the column in your CSV that has the code you're looking for.</P> Mon, 05 Aug 2019 12:17:36 GMT jpolvino 2019-08-05T12:17:36Z https://community.splunk.com/t5/Splunk-Search/Use-of-Lookup-with-search-query/m-p/427337#M122339 <P>Hi, I need some help related to a search query. My search query has a field called "holdings" which contain data like this - <BR /> "U14355|U114576|D105994". And i have a lookup with 600 unique codes like D123456, D876845 etc. (All starts with D)<BR /> Now I want to search only those events where one of the holdings also present in the lookup values.<BR /> For example - event with holdings as "U14355|U114576|D105994" should only return if D105994 is also present in the lookup.<BR /> Any help is much appreciated. Below query is obviously wrong -</P> <P>index=test sourcetype="test_logs" "Customer Cart:"<BR /> | rex field=_raw "holdings = \'{(?[^}]+)}\'" <BR /> | join holdings [| inputlookup codes.csv]</P> Wed, 30 Sep 2020 01:36:00 GMT https://community.splunk.com/t5/Splunk-Search/Use-of-Lookup-with-search-query/m-p/427337#M122339 Shashank_87 2020-09-30T01:36:00Z https://community.splunk.com/t5/Splunk-Search/Use-of-Lookup-with-search-query/m-p/427338#M122340 <P>One way to do it:</P> <PRE><CODE>| makeresults | eval holdings="U14355|U114576|D105994" | makemv delim="|" holdings | mvexpand holdings </CODE></PRE> <P>This breaks up your holdings field into 3 separate events that can be compared against the field in your lookup.</P> <P>In your case, it would look something like:</P> <PRE><CODE>index=test sourcetype="test_logs" "Customer Cart:" | rex field=_raw "holdings \= \'{(?[^}]+)}\'" | makemv delim="|" holdings | mvexpand holdings | lookup codes.csv (column) AS holdings </CODE></PRE> <P>Where (column) is the column in your CSV that has the code you're looking for.</P> Mon, 05 Aug 2019 12:17:36 GMT https://community.splunk.com/t5/Splunk-Search/Use-of-Lookup-with-search-query/m-p/427338#M122340 jpolvino 2019-08-05T12:17:36Z https://community.splunk.com/t5/Splunk-Search/Use-of-Lookup-with-search-query/m-p/427339#M122341 <P>@jpolvino Thank you for your response. mvexpand increases the eventcount. What i need is only unique sessions. Can i do dedup uniqueID after this. Will it be valid?</P> <P>Also this lookup command has to be supplied with OUTPUT fields. what should i write there? I only have one field in the lookup.</P> Mon, 05 Aug 2019 13:15:47 GMT https://community.splunk.com/t5/Splunk-Search/Use-of-Lookup-with-search-query/m-p/427339#M122341 Shashank_87 2019-08-05T13:15:47Z https://community.splunk.com/t5/Splunk-Search/Use-of-Lookup-with-search-query/m-p/427340#M122342 <P>If you want to preserve the event count for your holdings field, then use a temp field like n:<BR /> index=test sourcetype="test_logs" "Customer Cart:"<BR /> | rex field=_raw "holdings = \'{(?[^}]+)}\'" <BR /> | eval n=split(holdings,"|")<BR /> | mvexpand n<BR /> | lookup codes.csv (column) AS n OUTPUT (field you want to see)<BR /> At the end just list the field you want to see as output.</P> Wed, 30 Sep 2020 01:36:06 GMT https://community.splunk.com/t5/Splunk-Search/Use-of-Lookup-with-search-query/m-p/427340#M122342 jpolvino 2020-09-30T01:36:06Z