Hello,
So I have data with Regions and as of now- I have a region called A that needs to be mapped as region AA and AB. Region AA and AB have different countries in them. I want to filter my report on region AA or region AB. I am trying to create a static drop down that includes specific countries in AA or AB. I also have regions C, D, E that can remain the same in the data and use a dynamic filter for those. Is there anyway to do this? I tried creating multiple static options but then AA appeared multiple times in my drop down filter. Thank you for the help!
UPDATED TO BE EVEN SIMPLER:
The simplest way would be to create a lookup table with two columns - region
and caller_id_location_country
.
The countries in Region A would appear in two different regions, Region A and Region AA or AB.
Populate your dropdown 'reg' with ...
<query>| inputlookup mycountries.csv | stats count by region</query>
Populate your dropdown country
with ...
<query> | inputlookup mycountries.csv | where $reg$ | stats count by caller_id_location_country</query>
Or you could hard code them into the current queries...
<query>index=it_snow_call_kiosk_logs_weekly
| stats count by region
| append [| makeresults
| eval region=mvappend ("AA","AB")
| mvexpand region
| table region
]
| sort 0 region
</query>
<query>index=it_snow_call_kiosk_logs_weekly $reg$
| stats count as mycount by caller_id_location_country
| append [|makeresults
| eval regiondata=mvappend("AA,country AA1","AA,CountryAA2","AB,Country AB1", "AB,Country AB2")
| makemv regiondata
| mvexpand regiondata
| rex field=regiondata "(?<region>[^,]+),(?<caller_id_location_country>.*)"
| search $reg$
| table caller_id_location_country
]
| stats count by caller_id_location_country
</query>
Can you share your dropdown xml code, your current search (showing how that token is used), current and expected mock output?
</input>
<input type="multiselect" token="reg">
<label>Region</label>
<search>
<query>index=it_snow_call_kiosk_logs_weekly | stats count by region</query>
</search>
<fieldForLabel>region</fieldForLabel>
<fieldForValue>region</fieldForValue>
<prefix>(</prefix>
<suffix>)</suffix>
<valuePrefix>region="</valuePrefix>
<valueSuffix>"</valueSuffix>
<delimiter> OR </delimiter>
<choice value="*">All</choice>
<default>*</default>
</input>
<input type="multiselect" token="country">
<label>Country</label>
<search>
<query>index=it_snow_call_kiosk_logs_weekly $reg$ | stats count by caller_id_location_country</query>
<earliest>0</earliest>
</search>
<fieldForLabel>caller_id_location_country</fieldForLabel>
<fieldForValue>caller_id_location_country</fieldForValue>
<prefix>(</prefix>
<suffix>)</suffix>
<valuePrefix>caller_id_location_country="</valuePrefix>
<valueSuffix>"</valueSuffix>
<delimiter> OR </delimiter>
<choice value="*">All</choice>
<default>*</default>
</input>
<input type="multiselect" token="loc">
<label>Location</label>
<search>
<query>index=it_snow_call_kiosk_logs_weekly $country$ | stats count by location</query>
<earliest>0</earliest>
<latest></latest>
</search>
<fieldForLabel>location</fieldForLabel>
<fieldForValue>location</fieldForValue>
<prefix>(</prefix>
<suffix>)</suffix>
<valuePrefix>location="</valuePrefix>
<valueSuffix>"</valueSuffix>
<delimiter> OR </delimiter>
<choice value="*">All</choice>
</input>
So the region value is the one that has a region i need to split up by country and essentially in my search I want to separate my region A into two different regions so lets say they select region AA and select all for my country and location filters- then I need my search to include all of the countries from region AA - does that make sense?
Change location query to this.
index=it_snow_call_kiosk_logs_weekly $reg$ $country$ | stats count by location
Since there are overlapping in countries, we need to use both prior filters.