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