Creating a dashboard that allows you to select a region which will then retrieve data for only customers in that region. Each customer has their own "index" and the index name is the customer name. I'd like to avoid a subsearch as it's limited to 10k rows, you can subsearch the lookup though. The region isn't included in the customer index data.
------------------------------
Lookup data set:
Region | Customer
US1 Mcdonalds
US2 Macys
AU1 Atlassian
AU2 Outback
------------------------------
The issue I have run into is when I retrieve the list of customer names from the lookup, the subsearch is limited to 10k rows, there are a ton more rows that need to be included. I created a very inefficient query which I'm unhappy about, hence why I'm here:
index="*"
[inputlookup CSS_Customers where Region = $inputregion$
| fields Customer
| rename Customer as index]
Note: I tried tstats to pull a single field, but ran into an index issue. It could be because our "index" field isn't indexed.
My first instinct is to cheat around the problem rather than trying to conquer it. In the dashboard, run a hidden search to set a token as $customer_list_tok$, then use this to limit your search. E.g.,
<search>
<query>
| inputlookup CSS_Customers where Region = $inputregion$
| stats values(Customer) as indices
</query>
<progress>
<eval token="customer_list_tok">mvjoin(indices, ",")</eval>
</progress>
</search>
<input type="dropdown" token="inputregion">
<label>Select region</label>
<choice value="US1">US1</choice>
<choice value="US1">US2</choice>
<choice value="US1">AU1</choice>
<choice value="US1">AU2</choice>
</input>
<row>
<panel>
<search>
<query>
index IN ($customer_list_tok$) blah
</query>
</search>
</panel>
</row>
If $inputregion$ token doesn't have to take the values of "US1", "US2", you can even run the same search to populate the token dynamically without the intermediary $customer_list_tok$. E.g.,
<input type="dropdown" token="inputregion">
<label>Select region</label>
<fieldForLabel>Region</fieldForLabel>
<fieldForValue>indices</fieldForValue>
<search>
<query>
| inputlookup CSS_Customers
| stats values(Customer) as indices by Region
|eval indices = mvjoin(indices, ",")
</progress>
</search>
</input>
<row>
<panel>
<search>
<query>
index IN ($inputregion$) blah
</query>
</search>
</panel>
</row>
My first instinct is to cheat around the problem rather than trying to conquer it. In the dashboard, run a hidden search to set a token as $customer_list_tok$, then use this to limit your search. E.g.,
<search>
<query>
| inputlookup CSS_Customers where Region = $inputregion$
| stats values(Customer) as indices
</query>
<progress>
<eval token="customer_list_tok">mvjoin(indices, ",")</eval>
</progress>
</search>
<input type="dropdown" token="inputregion">
<label>Select region</label>
<choice value="US1">US1</choice>
<choice value="US1">US2</choice>
<choice value="US1">AU1</choice>
<choice value="US1">AU2</choice>
</input>
<row>
<panel>
<search>
<query>
index IN ($customer_list_tok$) blah
</query>
</search>
</panel>
</row>
If $inputregion$ token doesn't have to take the values of "US1", "US2", you can even run the same search to populate the token dynamically without the intermediary $customer_list_tok$. E.g.,
<input type="dropdown" token="inputregion">
<label>Select region</label>
<fieldForLabel>Region</fieldForLabel>
<fieldForValue>indices</fieldForValue>
<search>
<query>
| inputlookup CSS_Customers
| stats values(Customer) as indices by Region
|eval indices = mvjoin(indices, ",")
</progress>
</search>
</input>
<row>
<panel>
<search>
<query>
index IN ($inputregion$) blah
</query>
</search>
</panel>
</row>
Thank you @yuanliu for the idea. I think your idea could work. A colleague suggested the below query which worked and I went with it for now:
| tstats count where index="*"
[ inputlookup CSS_Customers where Region = $inputregion$
| fields Customer
| rename Customer as index] by index, cfc, _time span=$span$
| timechart span=$span$ limit=0 sum(count) by index