topic Can you search a lookup table without specifying a particular field? in Splunk Search

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

Cuyose — Tue, 18 Apr 2017 19:07:31 GMT

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"

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

somesoni2 — Tue, 18 Apr 2017 19:49:10 GMT

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

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

Cuyose — Tue, 18 Apr 2017 20:17:50 GMT

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.

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

somesoni2 — Tue, 18 Apr 2017 20:25:18 GMT

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

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

tlmayes — Wed, 09 Aug 2017 13:42:46 GMT

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"

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

DalJeanis — Wed, 09 Aug 2017 13:59:14 GMT

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

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

tlmayes — Wed, 09 Aug 2017 14:28:11 GMT

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.