topic Re: Using wildcard in mvfind in Splunk Search

Using wildcard in mvfind

Poojitha — Thu, 14 Dec 2023 04:03:09 GMT

Hi All,

I am facing error using wildcard in multivalue field. I am using mvfind to find a string.

eval test_loc=case(isnotnull(Region,%bangalore%), Bangalore)

I am just giving part of eval statement here

Example : Region = "sh bangalore Test"

The above eval statement should work on this Region and set test_loc = Bangalore.

I tried passing * and % (*bangalore*, %bangalore%) , but am getting error.

Please help me.

Thanks ,
poojitha NV

Re: Using wildcard in mvfind

bowesmana — Thu, 14 Dec 2023 04:10:15 GMT

It requires regex, so you can't use SQL style % nor simple wildcard, use .*

Re: Using wildcard in mvfind

bowesmana — Thu, 14 Dec 2023 04:11:18 GMT

and actually you probably mean

eval test_loc=case(isnotnull(mvfind(Region,".*bangalore.*")), Bangalore)

Re: Using wildcard in mvfind

dtburrows3 — Thu, 14 Dec 2023 04:30:53 GMT

Few different ways to approach this if I understand you problem correctly

| makeresults | fields - _time | eval Region=split("Bangalore|seattle|bangalore|Galveston|sh bangalore Test", "|") ``` Different Eval Methods ``` | eval test_loc_method1=mvfilter(match(Region, "(?i)bangalore")), test_loc_method2=mvdedup( case( isnull(Region), null(), mvcount(Region)==1, if(match(Region, "(?i)bangalore"), "Bangalore", null()), mvcount(Region)>1, mvmap(Region, if(match(Region, "(?i)bangalore"), "Bangalore", null())) ) ) ``` Rex Method ``` | rex field=Region "(?<rec_loc>(?i)bangalore)"

Mostly just depends on how you want the outputted eval field to look.

test_loc_method2 gives a clean single value result with a hardcoded result given that the regex pattern is found somewhere in the multivalue field.

Re: Using wildcard in mvfind

Poojitha — Thu, 14 Dec 2023 05:32:10 GMT

Thanks @bowesmana - mvmap so it iterates like a for loop and checks. What if I want to do multiple checks.
Example, I want to check for different regions.

mvcount(Region)>1, mvmap(Region, if(match(Region, "(?i)bangalore"), "Bangalore", null())

Here it iterates and checks for banglore. What if I want to check for Singapore , USA , China and so on. ? Its becoming quite challenging, please help me

Example : Test_loc_method2 is the output I want from Region (mv field)

Region	Test_loc_method2
sh Bangalore Test Chennai Hyderbad	Bangalore
test China 1 India	China
Loc USA 2 London	USA

Re: Using wildcard in mvfind

bowesmana — Thu, 14 Dec 2023 06:23:00 GMT

I think you can match() on an MV field directly, e.g.

| eval test=if(match(city, "(?i)bangalore|hyderbad|chennai"), 1, 0)

Re: Using wildcard in mvfind

dtburrows3 — Thu, 14 Dec 2023 16:40:52 GMT

So using this method below I believe will do it.

``` This join is to pull in an array of all Regions you want to search for in the 'Region' multivalue field ``` ``` There are other way to make the list (hardcoded, macros, lookups) I'm just using a lookup as a POC for if the list is large and is easy to maintain ``` | join type=left [ | inputlookup list_of_regions | stats values(list_of_regions) as list_of_regions | eval list_of_regions_array=mv_to_json_array(list_of_regions) | fields - list_of_regions ] ``` Convert array to multivalue field of all regions to search for ``` | eval list_of_regions=json_array_to_mv(list_of_regions_array) | fields - list_of_regions_array ``` Use the regions multivalue field to build a regex ``` | eval list_of_regions_regex="(?i)(".mvjoin(list_of_regions, "|").")" ``` pipe in the regex build from regions into this eval to loop through multivalue fields ``` | eval Test_loc_method2=case( isnull(Region), null(), mvcount(Region)==1, if(match(Region, $list_of_regions_regex$), replace(Region, ".*".$list_of_regions_regex$.".*", "\1"), null()), mvcount(Region)>1, mvmap(Region, if(match(Region, ".*".$list_of_regions_regex$.".*"), replace(Region, ".*".$list_of_regions_regex$.".*", "\1"), null())) ) | fields - list_of_regions, list_of_regions_regex ``` Pipe in matches that returned into the 'list_of_regions' lookup to pull back a formatted version of the match. Note: This lookup definition must have case sensitivity turned off for this part to work as intended. ``` | lookup list_of_regions list_of_regions as Test_loc_method2 OUTPUT list_of_regions as formatted_matched_region

Full SPL I used to generate this output

| makeresults | fields - _time | eval Region=split("sh Bangalore Test|Chennai|Hyderbad", "|") | append [ | makeresults | fields - _time | eval Region=split("test China 1|India| ", "|") ] | append [ | makeresults | fields - _time | eval Region=split(" |Loc USA 2|London", "|") ] | append [ | makeresults | fields - _time | eval Region=split("lowercased china to test|New York|usa (America)", "|") ] ``` This join is to pull in an array of all Regions you want to search for in the 'Region' multivalue field ``` ``` There are other way to make the list (hardcoded, macros, lookups) I'm just using a lookup as a POC for if the list is large and is easy to maintain ``` | join type=left [ | inputlookup list_of_regions | stats values(list_of_regions) as list_of_regions | eval list_of_regions_array=mv_to_json_array(list_of_regions) | fields - list_of_regions ] ``` Convert array to multivalue field of all regions to search for ``` | eval list_of_regions=json_array_to_mv(list_of_regions_array) | fields - list_of_regions_array ``` Use the regions multivalue field to build a regex ``` | eval list_of_regions_regex="(?i)(".mvjoin(list_of_regions, "|").")" ``` pipe in the regex build from regions into this eval to loop through multivalue fields ``` | eval Test_loc_method2=case( isnull(Region), null(), mvcount(Region)==1, if(match(Region, $list_of_regions_regex$), replace(Region, ".*".$list_of_regions_regex$.".*", "\1"), null()), mvcount(Region)>1, mvmap(Region, if(match(Region, ".*".$list_of_regions_regex$.".*"), replace(Region, ".*".$list_of_regions_regex$.".*", "\1"), null())) ) | fields - list_of_regions, list_of_regions_regex ``` Pipe in matches that returned into the 'list_of_regions' lookup to pull back a formatted version of the match. Note: This lookup definition must have case sensitivity turned off for this part to work as intended. ``` | lookup list_of_regions list_of_regions as Test_loc_method2 OUTPUT list_of_regions as formatted_matched_region

Note: I created a lookup for this example with CSV named "list_of_regions.csv" and with lookup definition named "list_of_regions". On the definition I turned off the case-sensitivity to allow for a formatted region to be returned on the last step if desired.

You don't necessarily have to use a lookup for this method to work, I just found that if the list gets large that storing them in lookups sometimes makes things easier to maintain.

If you only really need to use the list of regions for a single search you could probably just have them hardcoded into the search itself (Or just build the hardcoded regex based of your list)

I just was sharing how you can sometimes pipe in $token$ into an eval function and it seemed to fit your use-case here.

And for reference of what the lookup looks like here is a screenshot of what I used for this.

Re: Using wildcard in mvfind

Poojitha — Sun, 17 Dec 2023 07:41:27 GMT

Thanks @bowesmana : I was able to achieve using case statement and the regex you gave. Thanks a lot 🙂