How to filter index from inputlook and avoid 10k subsearch limit?

[mtruji]

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.

[yuanliu · Accepted solution]

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

[mtruji]

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