How to accelerate a search that uses values from a dropdown?

[klim]

I have a dashboard that has dropdowns to pick a value for a certain field. For example a dropdown for gender there is male or female and another is a dropdown for age: old, middle, or young. The search uses those values as prefilters like this: index=* gender=$gender_token$ age=$age_token$ | timechart count 

If I wanted to accelerate this how would I do that since the gender and age fields could be so many different values. I can't just take the current search and accelerate it. I have accelerated searches like this before by using bucket and stats like this index=* | bucket _time | stats count by gender, age, _time and then in the dashboard add this part to the end | where gender=$gender_token$ AND age=$age_token$ | timechart sum(count) but the search size is quite big compared to the regular timechart search (10x).

I was hoping that I could do something like index=* | timechart count by gender,age  and then have a where clause afterwards but that isn't an option. 

[jacobpevans]

Greetings @klim,

Correct me if I'm misunderstanding, but I doubt your search is so big that you can't do a "one-time" load into memory of the results, and then filter them in-memory instead of creating a new search to send back to the indexers. Below is a run-anywhere dashboard of what I'm talking about. The base search runs on dashboard load, however, changing either of the two drop-downs does NOT initiate any kind of reload. Instead, they filter the results that have already been retrieved by the base search. In my example, changing the time selector DOES require a new search because the time selector changes the base search itself.

<form>
  <search id="baseSearch">
    <query>index=_internal
| table _time index sourcetype source host component _raw</query>
    <earliest>$timeSelector.earliest$</earliest>
    <latest>$timeSelector.latest$</latest>
  </search>
  <description>https://community.splunk.com/t5/Dashboards-Visualizations/How-to-accelerate-a-search-that-uses-values-from-a-dropdown/m-p/540310#M37024</description>
  <fieldset submitButton="false">
    <input type="time" token="timeSelector" searchWhenChanged="true">
      <label>Time Selector</label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
    <input type="dropdown" token="sourcetype" searchWhenChanged="true">
      <label>sourcetype</label>
      <choice value="*">All</choice>
      <default>*</default>
      <prefix>sourcetype="</prefix>
      <suffix>"</suffix>
      <initialValue>*</initialValue>
      <fieldForLabel>sourcetype</fieldForLabel>
      <fieldForValue>sourcetype</fieldForValue>
      <search base="baseSearch">
        <query>dedup sourcetype | fields sourcetype | sort sourcetype</query>
      </search>
    </input>
    <input type="dropdown" token="component" searchWhenChanged="true">
      <label>component</label>
      <choice value="*">All</choice>
      <default>*</default>
      <prefix>component="</prefix>
      <suffix>"</suffix>
      <initialValue>*</initialValue>
      <fieldForLabel>component</fieldForLabel>
      <fieldForValue>component</fieldForValue>
      <search base="baseSearch">
        <query>dedup component | fields component | sort component</query>
      </search>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search base="baseSearch">
          <query>search $sourcetype$ $component$</query>
        </search>
        <option name="drilldown">cell</option>
      </table>
    </panel>
  </row>
</form>

If that doesn't help, let me know what I misunderstood, and I'll be happy to update my answer. 

[klim]

Thanks for the reply @jacobpevans 

I'm not sure if my search is that big but doing what you described would take a very long time and the size of the search would be very big. Currently a simple timechart for a week takes 3 min to run with a size of 20 MB and 10 million events. I need to be able to run these searches for a max duration of one month. 

[jacobpevans]

I see, I see. How recent does the data need to be? Another option would be to combine the elements from my original post with a | loadjob as your base search instead. 

If you do it that way, you would run the base search over the past month every hour? Every day? Only during working hours? It depends on your use case. Either way, the base search would not search anything at all. Instead, it would just pull the results from the latest scheduled search immediately, and then you could apply your filters to that data set. If you still need a time picker, you'd have to build a custom one that uses the where command instead.

[klim]

Thanks for the quick response again. Are you suggesting that I run the search index=* | table field1 field2 field3 _time and run that every day? I think that would be a very big file.

In the past I've taken a timechart query and run it hourly and then loaded it in the panel and appending the current data to it. But that was a specific time period. I don't know how I would be able to do that for when a user picks a custom time period. 

Edit: that would actually work for a timechart. disregard that. How would I do that for a stats panel though?