Getting Data In

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

tnkoehn
Path Finder

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?

Tags (1)
1 Solution

jonuwz
Influencer

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

View solution in original post

jonuwz
Influencer

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
Influencer

Yep - underscore is punctuation

0 Karma

tnkoehn
Path Finder

Is it because there is an underscore?

0 Karma

tnkoehn
Path Finder

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.

0 Karma

jonuwz
Influencer

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

0 Karma

tnkoehn
Path Finder

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

0 Karma

jonuwz
Influencer

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

0 Karma

tnkoehn
Path Finder

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?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...