Use input file to create a search and use a match command

[tnkoehn]

I have a csv file with the following companies

ATT
LV3
MCI
QST
SPT
VZB

Since the companies can change frequently, I would like to use that file to create a search that basically accomplishes the following

sourcetype=Sonus START (ATT OR LV3 OR MCI OR QST OR SPT OR VZB) | eval tg=case(match(Ingress_TG_Name,"^(ATT|LV3|MCI|QST|SPT|VZB)"),Ingress_TG_Name,match(Selected_TG_Name,"^(ATT|LV3|MCI|QST|SPT|VZB)"),Selected_TG_Name) | where NOT isnull(tg) | stats count by tg Site

Is this possible using inputlookup or some other method?

[jonuwz · Accepted solution]

If you have a csv called companies.csv in var/run/splunk and the csv has a header of "Companies" like this

Companies
ATT
LV3
MCI
QST
SPT
VZB

You can do this :

sourcetype=Sonus START [ | inputcsv companies 
                         | stats values(Companies) as Companies
                         | eval search=mvjoin(Companies," OR ")
                         | return $search
                       ] | ...

This replaces [ ... ] with ( ATT OR LV3 OR MCI etc etc )

Its far simpler if the header in the CSV (in the example - "Companies") matches a field called "Companies" that is already extracted in the main search.

Then you can just do :

sourcetype=Sonus START [ | inputcsv companies ] | ...

because the [ ... ] expands to :

( ( Companies="ATT" ) OR ( Companies="LV3" ) OR ( Companies="MCI" ) etc )

For the second part with the regex, you can do something similar :

... | eval regex=[ | inputcsv companies
                   | stats values(Companies) as Companies 
                   | eval search="\"^(".mvjoin(Companies,"|").")\"" 
                   | return $search
                 ] | ...

But ... this will murder performance, because the regex will be calculated for each row, so you could precalculate the values and map them in.

Edit

I tested this one with sample data. The problem is that you cant pass subqueries into map as a non-quoted string.
I have a lookup table called procs.csv and it contains :

processors
nullqueue
header
previewout

Working search :

* | head 1 
| eval regex=[ 
 | inputcsv procs
 | stats values(processors) as procs
 | eval search="\"^(".mvjoin(procs,"|").")\"" 
 | return $search ]
| map search="
  search index=_internal group=pipeline [
    | inputcsv procs
    | stats values(processors) as procs
    | eval procs= \"(\".mvjoin(procs,\" OR \").\")\"
    | return $procs ]
  | eval tg=case(match(processor,$regex$),processor,match(processor,$regex$),processor)
  | stats count by tg "
| table count tg

[jonuwz]

Yep - underscore is punctuation

[tnkoehn]

Is it because there is an underscore?

[tnkoehn]

Really? That's strange because it is find things like ATT_0004_002, MCI_0323_005, etc. (which is exactly what I want it to do). Let me know if you think this is wrong.

[jonuwz]

one thing i would mention though. :

This

index=xxx (ABC OR DEF OR GHI)

Will only return rows if ABC DEF GHI are complete words in the _raw message.

words being strings surrounded by whitespace or punctuation.

i.e. Company=ABCDEF wont match

[tnkoehn]

Works perfectly! You are a genius! Thank you, thank you, thank you!

[jonuwz]

the map search was inserting "(ATT OR LV3 OR MCI OR QST OR SPT OR VZB)" - i.e. it was quoted, and looking literally for that string - updated answer

[tnkoehn]

This is excellent and it should work, but for some reason I'm getting 0 results. I know that $search_prefix$ and $regex$ are formatted correctly, but the search doesn't like them. For example, if I just replace $search_prefix$ with (ATT OR LV3 OR MCI OR QST OR SPT OR VZB) it works fine. Any ideas?