Can you search a lookup table without specifying a particular field?

[Cuyose]

Is something like this possible? Basically a freetext search of a lookup table to return the associated rows?

|inputlookup L_EC2InstanceDetails|search "127.0.0.1"

[somesoni2 · Accepted solution]

Not directly but may be using this workaround. Basically create a fields which will combine values from all columns and then search your freetext string in that combined field to filter rows in lookup.

|inputlookup L_EC2InstanceDetails | eval combined="" | foreach * [eval combined=if("<<FIELD>>"!="combined",combined."#".'<<FIELD>>',combined) ] | search combined=*127.0.0.1* | fields - combined

[Cuyose]

Damn, thats slick, thank you much! Crazy it takes that kind of work. I was going under the assumption Splunk would treat the lookup as a pseudo index by default since its a csv.

[somesoni2]

Splunk treats lookups as static tables and not raw events, that's why the freetext search that you do for events with _raw fields would not work.

Another approach (similar) would be this (without combining so memory usage should be lower for this version)

|inputlookup L_EC2InstanceDetails 
| eval keeprow="N" 
| foreach * [eval keeprow=if(match('<<FIELD>>',"127\.0\.0\.1"),"Y",keeprow) ] 
| where keeprow="Y" | fields - keeprow

[DalJeanis]

@tlmayes - I edited your comment to mark the code and restored the deleted <FIELD> code -- use the 101 010 button or indent four spaces or mark with grave accents (`) before and after the code snippet, and that will keep the web from deleting stuff.

For this use case, you can just calculate the match mask in a line before the foreach, then pass it in. That would look something like this...

| eval keeprow="N"
 | eval mymatch=if(isnull(somefield),".",somefield)
| foreach * [eval keeprow=if(match('<<FIELD>>',"$somefield$"),"Y",keeprow) ]

Notes -

Remember that match only has to match a single chunk of a field, not the entire field, so "." is enough. "*" would only match a single asterisk, so it's probably not what you want.

Parenthesis have a special grouping (and/or capturing) function in a regex. They didn't hurt anything in that example, but they didn't help anything either.

If you are matching IP addresses, then compare the cidrmatch function to see if it would help.

[tlmayes]

Hmm... not seeing the forest. Are you suggesting in your code that "somefield" is the "fieldname" of choice? If so, I cannot do this, since I need the ability to search All fields in the lookup table for a keyword. My first query does not, but searches only on a known keyword, not a wildcard, and searches across ALL fields equally.

[tlmayes]

Your response is incredibly helpful. I have a very similar problem, with a twist. I have several lookup tables with thousands of rows that I have presented to users in a tabbed dashboard.

Need to add a "search" button in the dashboard so that by default the page returns everything, and optionally a user can search for a keyword. Using your example, how can I use search for everything, vice single keyword:

| inputlookup ArcSight_Zone_Data_subnets.csv 
| eval keeprow="N"
| foreach * [eval keeprow=if(match('<<FIELD>>',"(noDNS)"),"Y",keeprow) ]
| where keeprow="Y" | fields - keeprow

Wildcard (attempt, but fails)

| inputlookup ArcSight_Zone_Data_subnets.csv 
| eval keeprow="N"
| foreach * [eval keeprow=if(match('<<FIELD>>',"(*)"),"Y",keeprow) ]
| where keeprow="Y" | fields - keeprow

The parentheses around "(noDNS)" are necessary due to the inclusion of a dashboard search "token"