topic Re: Filter out a subset of items based on another value in a CSV lookup in Splunk Search

Filter out a subset of items based on another value in a CSV lookup

kiddsupreme — Tue, 29 Sep 2020 18:59:01 GMT

Hello again,

So lets say I have a CSV file that looks like the following:

node_code   region_code
SAN          AMERICAS
JPN          APAC
NYC          AMERICAS
CHN          APAC
FRA          EMEA
NUR          EMEA

And lets say my search is the following:

<query>errorcode=$errorcode_tok$ | dedup em_event_alert | eval dv_node=upper(dv_node) | rex field=dv_node "(?P&lt;testnode&gt;\w{3})" | stats count by testnode</query>

And just for reference:

Field Definitions
- dv_node = The string that holds the hostname of devices
- em_event_alert = A unique alert ID #

What I'm trying to do is create a dropdown with a list of Regions. So in this example, if the user selects AMERICAS from the dropdown, I want to filter the search results to only display those corresponding items... in our case, SAN & NYC would be the values displayed.

As you can see in the search, it queries for the selected errorcode (don't worry, I already have that functionality figured out), dedup's, and then it takes the hostname, and makes the whole thing uppercase. Finally, we strip out just the first 3 letters (that's how I am able to match it up to the node_code field in the CSV). But I can't seem to determine how to start to create that search query. Any ideas would definitely be appreciated. Thanks in advance.

Re: Filter out a subset of items based on another value in a CSV lookup

nryabykh — Thu, 12 Apr 2018 05:08:59 GMT

Hi!

I believe the easiest way is the following:

% your query %
| lookup lookup_filename.csv node_code AS testnode 
| search region_code=$token_drilldown$
| fields - region_code

Of course, you must change lookup_filename.csv and $token_drilldown$ with the names of your lookup and drilldown token.

Re: Filter out a subset of items based on another value in a CSV lookup

kiddsupreme — Thu, 12 Apr 2018 18:11:50 GMT

Not sure I follow in regards to the $token_drilldown$ variable. I've never used drilldown because as far as I know, its based on you clicking on something to go down further. I just want to utilize a dropdown menu with those values.

If you can go into more detail, maybe it would make sense; right now, I'm not seeing it.

Re: Filter out a subset of items based on another value in a CSV lookup

nryabykh — Fri, 13 Apr 2018 06:05:00 GMT

I'm sorry for typo, I meant $token_dropdown$ (not $token_drilldown$ ) as token from your dropdown list.

With the lookup command you'll create a new field region_code corresponding to testnode. And with search command you'll filter out all region codes except selected in dropdown list.