Splunk Search

Use of Lookup with search query

Shashank_87
Explorer

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]

0 Karma

jpolvino
Builder

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.

0 Karma

Shashank_87
Explorer

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

0 Karma

jpolvino
Builder

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...