topic Use input file to create a search and use a match command in Getting Data In

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

tnkoehn — Wed, 13 Feb 2013 22:44:54 GMT

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?

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

jonuwz — Thu, 14 Feb 2013 01:10:40 GMT

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

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

tnkoehn — Mon, 28 Sep 2020 13:19:16 GMT

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?

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

jonuwz — Fri, 15 Feb 2013 13:06:03 GMT

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

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

tnkoehn — Fri, 15 Feb 2013 15:06:31 GMT

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

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

jonuwz — Fri, 15 Feb 2013 15:54:45 GMT

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

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

tnkoehn — Mon, 28 Sep 2020 13:19:40 GMT

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.

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

tnkoehn — Fri, 15 Feb 2013 16:00:00 GMT

Is it because there is an underscore?

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

jonuwz — Wed, 27 Feb 2013 23:30:49 GMT

Yep - underscore is punctuation