Use of Lookup with search query

[Shashank_87]

Hi, I need some help related to a search query. My search query has a field called "holdings" which contain data like this -
"U14355|U114576|D105994". And i have a lookup with 600 unique codes like D123456, D876845 etc. (All starts with D)
Now I want to search only those events where one of the holdings also present in the lookup values.
For example - event with holdings as "U14355|U114576|D105994" should only return if D105994 is also present in the lookup.
Any help is much appreciated. Below query is obviously wrong -

index=test sourcetype="test_logs" "Customer Cart:"
| rex field=_raw "holdings = \'{(?[^}]+)}\'"
| join holdings [| inputlookup codes.csv]

[jpolvino]

One way to do it:

| makeresults
| eval holdings="U14355|U114576|D105994"
| makemv delim="|" holdings
| mvexpand holdings

This breaks up your holdings field into 3 separate events that can be compared against the field in your lookup.

In your case, it would look something like:

index=test sourcetype="test_logs" "Customer Cart:"
| rex field=_raw "holdings \= \'{(?[^}]+)}\'" 
| makemv delim="|" holdings
| mvexpand holdings
| lookup codes.csv (column) AS holdings

Where (column) is the column in your CSV that has the code you're looking for.

[Shashank_87]

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

Also this lookup command has to be supplied with OUTPUT fields. what should i write there? I only have one field in the lookup.

[jpolvino]

If you want to preserve the event count for your holdings field, then use a temp field like n:
index=test sourcetype="test_logs" "Customer Cart:"
| rex field=_raw "holdings = \'{(?[^}]+)}\'"
| eval n=split(holdings,"|")
| mvexpand n
| lookup codes.csv (column) AS n OUTPUT (field you want to see)
At the end just list the field you want to see as output.