Splunk Search

How to use a lookup in a subsearch to search raw data values to include in the main search?

landen99
Motivator

I would like to import a lookup table in a subsearch for a raw value search:

index=i1 sourcetype=st1 [inputlookup user.csv | table user]

but this searches on the field user for all values from the subsearch:

index=i1 sourcetype=st1 user=val1 OR user=val2 OR ..

I really want to search on the values anywhere in the raw data:

index=i1 sourcetype=st1 [inputlookup user.csv | table user | eval _raw="*".user."*"]

so that it searches:

 index=i1 sourcetype=st1 _raw="*val1*" OR _raw="*val2*" OR ..

but my initial tests are not proving success with that approach so I am thinking that there may be an issue.

The optimum search would actually translate to:

 index=i1 sourcetype=st1 val1 OR val2 OR ..

but I think that this translates directly to the previous search with _raw=val1, etc.

The question is: How do I bring in values from a lookup table for searching the raw data before the first pipe in the search.

Tags (3)
1 Solution

somesoni2
SplunkTrust
SplunkTrust

In your first search, in subsearch, rename user to "search" ( after table command add "|rename user as search")

So if your search is this

index=i1 sourcetype=st1 [inputlookup user.csv | table user | rename user as search | format]

The resulting query expansion will be

index=i1 sourcetype=st1  ( ( "User1" ) OR ( "User2" ) ....OR ( "UserN" ) )

View solution in original post

jimodonald
Contributor

You do not need to use a subsearch in order to use a lookup table. You can refer to the lookup table with

| lookup user.csv user OUTPUT <something> 

See the tutorial for a step-by-step guide and then move on to the Knowledge Managers Manual for all the details.

landen99
Motivator

"| lookup" matches the user field value from the main search against corresponding user values in the lookup table and then pulls the lookup table's corresponding field value pairs into each event.

"[inputlookup ..| rename user as search]" filters the events by matching the user values in the subsearch against any match in the _raw field. Without the rename, the subsearch pulls all user=value pairs and filters the events based on matches to "(user=value1 OR user=value2 OR, etc.)"

0 Karma

landen99
Motivator

So how do you suggest using the values from that lookup table to search the raw events in the index i1 (for this example)? Your lookup only adds the field-value pairs from the lookup to events with user field values that correspond to the lookup table's user field values. The values in the lookup table need to be used to filter the events before the first pipe (bar) based on whether the values are found in the raw data (preferred) if possible, because the user field is currently not extracting cleanly.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

In your first search, in subsearch, rename user to "search" ( after table command add "|rename user as search")

So if your search is this

index=i1 sourcetype=st1 [inputlookup user.csv | table user | rename user as search | format]

The resulting query expansion will be

index=i1 sourcetype=st1  ( ( "User1" ) OR ( "User2" ) ....OR ( "UserN" ) )

Afiyat_09
New Member

Can anyone please help me how to do this..

I want to use my lookup ccsid.csv which only contains one column named CCS_ID 

This CCS_ID should be taken from lookup only as a subsearch output and given to main query with a different index to fetch cif_no

 

Please help, it's not taking my lookup data as input for subsearch 

0 Karma

landen99
Motivator

Renaming as search after the table worked. I have seen this renaming to "search" in the searches of others but didn't understand why until now. Thank you.

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