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?
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
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
Yep - underscore is punctuation
Is it because there is an underscore?
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.
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
Works perfectly! You are a genius! Thank you, thank you, thank you!
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
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?