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

[landen99]

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.

[jimodonald]

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]

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

[landen99]

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.

[somesoni2 · Accepted solution]

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]

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 

[landen99]

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.