Splunk Search

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

mtruji
Engager

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.

Labels (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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

 

 

 

View solution in original post

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

 

 

 

0 Karma

mtruji
Engager

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

 

Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...