Splunk Search

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

Cuyose
Builder

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"
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Builder

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

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
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

tlmayes
Contributor

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.

0 Karma

tlmayes
Contributor

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"

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