Splunk Search

Using wildcard in mvfind

Poojitha
Path Finder

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

Labels (4)
Tags (3)
0 Karma

Poojitha
Path Finder

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

0 Karma

Poojitha
Path Finder

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)

RegionTest_loc_method2
sh Bangalore Test
Chennai
Hyderbad
Bangalore
test China 1
India

China

Loc USA 2
London
USA
0 Karma

dtburrows3
Builder

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

 

 

dtburrows3_0-1702571641341.png


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.

dtburrows3_0-1702572041470.png

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

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

dtburrows3
Builder

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.

dtburrows3_0-1702528191322.png

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

and actually you probably mean

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

bowesmana
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...