topic Re: How to filter index from inputlook and avoid 10k subsearch limit? in Splunk Search

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

mtruji — Thu, 11 Aug 2022 17:50:09 GMT

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.

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

yuanliu — Fri, 12 Aug 2022 01:46:20 GMT

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>

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

mtruji — Mon, 15 Aug 2022 17:08:26 GMT

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