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.
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.
