Getting Data In

inputlookup question, can't find answer anywhere

jonbalderston
Explorer

I have a lookup which works, it's not matched to a field, it has to search in the raw event.

[|inputlookup MyFile.csv| fields column_name| rename column_name as search | format]

This finds all results with names in MyFile.csv and highlights in results.

I want to output to a table with the result (what the match was from the CSV file). i.e.
time,StringFound, etc

I can do table, etc with everything else in event, just not what was matched from MyFile.csv

Tags (2)
1 Solution

jpass
Contributor

This will do the trick. I can't remember where I got it or who helped me put it together but it works...

  • This assumes your lookup table has a single field called 'phrase'.
  • For example, if you want to search for any reference to a list of IPs
  • A column called 'hit' is created
  • If your events have a unique key (like a primary key) you can at a 'transaction primarykey' to group all events that contain multiple 'hits'

    index=yourindex [| inputlookup MyFile.csv | rename phrase as search | fields search | format] | eval rawText= _raw | eval hit=[| inputlookup MyFile.csv | stats values(phrase) as query | eval query=mvjoin(query,",") | fields query | eval query = "\"".query."\""] | eval hit=split(hit,",") | mvexpand hit | eval hit=lower(hit) | eval rawText=lower(rawText) | where like(rawText,"%"+hit+"%") | TABLE *

View solution in original post

jpass
Contributor

This will do the trick. I can't remember where I got it or who helped me put it together but it works...

  • This assumes your lookup table has a single field called 'phrase'.
  • For example, if you want to search for any reference to a list of IPs
  • A column called 'hit' is created
  • If your events have a unique key (like a primary key) you can at a 'transaction primarykey' to group all events that contain multiple 'hits'

    index=yourindex [| inputlookup MyFile.csv | rename phrase as search | fields search | format] | eval rawText= _raw | eval hit=[| inputlookup MyFile.csv | stats values(phrase) as query | eval query=mvjoin(query,",") | fields query | eval query = "\"".query."\""] | eval hit=split(hit,",") | mvexpand hit | eval hit=lower(hit) | eval rawText=lower(rawText) | where like(rawText,"%"+hit+"%") | TABLE *

jpass
Contributor

sure no problem. Glad I could help someone as opposed to getting help from others.

0 Karma

jonbalderston
Explorer

It works! I had to change """.query.""" to "".query."" though, otherwise it returns an error. Thanks!!!

0 Karma

HiroshiSatoh
Champion

I think to be able to answer if there is a sample of the data and of the entire search statement.

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>