Populating Search for custom Drop-down list taking a long time!

[monicato]

Hi all,

I'm currently working on a custom view with drop-down options. I have a populating search for the options in these drop-down menus, however, it currently take 30 minutes or more for the options to load and show up in the drop-down menus.

 <searchTemplate>sourcetype=trialdl country=$country1$ product_name=$product$ download_agent=$dlagent$ | timechart count</searchTemplate>  
 <fieldset>
 <input type="dropdown" token="country1">
   <label>Select a Country</label>
   <populatingSearch fieldForValue="country" fieldForLabel="country">
      <![CDATA[sourcetype="trialdl" | lookup geoip clientip as ip_address | stats count by country]]>
    </populatingSearch>
</input>

   <input type="dropdown" token="product">
   <label>Select a Product</label>
   <populatingSearch fieldForValue="product_name" fieldForLabel="product_name">
      <![CDATA[sourcetype=trialdl 
      product_name=* | stats count by product_name]]>
    </populatingSearch>
</input>

   <input type="dropdown" token="dlagent">
   <label>Select a Download Client</label>
   <populatingSearch fieldForValue="download_agent" fieldForLabel="download_agent">
      <![CDATA[sourcetype=trialdl 
      download_agent=* | stats count by download_agent]]>
    </populatingSearch>
</input>

 <input type="time">
  <default>Last 30 days</default>
</input>
 </fieldset>

In the code snippet above, I have three drop-down list that all take a veryyy long time to populate.

Has anyone else experienced this problem? Know a solution to this? Thanks in advance!

[lguinn2]

Yikes! I see a couple of things that would make this search run faster.

First, you haven't specified any time range, so this search runs over all time.

Second, the lookup runs over every event that is returned - even if the same country appears multiple times.

Instead of this

sourcetype="trialdl" | lookup geoip clientip as ip_address | stats count by country

Try this

sourcetype="trialdl" earliest=-24h 
| dedup ip_address 
| lookup geoip clientip as ip_address 
| stats count by country 

which will give you a sorted list of the countries that appeared in the last 24 hours. Or change the earliest=-24h to the time range of your choice.

That should be better...

[lguinn2]

Actually I think that Splunk optimizes the lookups, so it's really the lack of a time range that is hurting the performance. Nevertheless, there is nothing wrong with doing a dedup, and it might make a slight difference too.

[Ayn]

Populating searches are no different than regular searches (the only difference is what happens to the search results). So, if the dropdowns take a long time to populate, that's because the searches themselves are suboptimal. Things to consider include limiting the timerange you're searching within, limiting the number of results or unique results you grab from a search, setting up summary indexing for speeding things up, or preprocessing searches by having a saved search running every so often and then get results directly from there instead of performing the whole search when the view is loaded.

[lguinn2]

I like the idea of a scheduled search as well.

[monicato]

I just updated my post with a snippet of the code! Thanks!!

[lguinn2]

If we could see the populating search, and perhaps a snippet of the XML, we might be able to give more direct advice...

Oh, and a sense of the underlying data - how many events are there, what does the data look like, etc.