Splunk Search
Highlighted

Why is using base searches causing major performance issues on my dashboard?

Explorer

Working on making dashboards to help report on activity.

To make the dashboards as performant as possible, I'm using base searches.

However, it appears that using base searches throughout a dashboard (1 search, but used in all the panels) almost causes an exponential increase in the size of the search - which causes the search to prematurely terminate.

Doing a straight search for one of the panels got: 98,803 results, and took up 0.29 MB.

Just turning that 1 search into a 1 panel dashboard using a base search got same number of results, but took up 83.66 MB.

Which seems completely ridiculous.
If I trim down results to just the last 2 weeks (instead of the last month), I get around 45,000 results, and the dashboard load only takes up 7MB.

I'm trying to figure out if I'm doing something wrong? Or should I not use base queries at all - as they seem to constantly cause performance issues??

The dashboard:

<form>
  <label>Usage</label>
  <fieldset autoRun="false" submitButton="true">
    <input type="dropdown" token="tenantid_tok">
      <label>Domain</label>
      <default>All</default>
      <choice value="">All</choice> 
    </input>
    <input type="time" token="date_tok" searchWhenChanged="true">
      <label>Time Range</label>
      <default>
        <earliest>-1mon@d</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <search id="login_checkpoints">
    <query>index=myIndex sourcetype=mySource $tenantid_tok$ NOT CrashReport NOT ErrorReport ("TYPE1" OR "TYPE2")
            | `expand_checkpoints`
            | fields - _raw
            | spath input=checkPoint output=eventName path=EventName
            | where match(eventName,"TYPE1") or match(eventName,"TYPE2") 
            | spath input=checkPoint output=userId path=UserId
            | spath input=checkPoint output=tenantId path=TenantId
            | spath input=checkPoint output=eventTime path=EventTime
            | spath input=checkPoint output=metaData path=MetaData
            | spath input=metaData output=userType path=USER_TYPE
            | spath input=checkPoint output=deviceId path=DeviceId
            | spath input=checkPoint output=deviceModel path=DeviceModel
            | spath input=checkPoint output=deviceOS path=DeviceOS
            | spath input=checkPoint output=appVersion path=Version
            | eval deviceType=if(like(deviceOS,"Android%"),"Android","iOS")
            | eval fullUserName=userId+"@"+tenantId
            | fields - checkPoint
            | fields eventName,userId,tenantId,eventTime,metaData,userType,deviceId,deviceModel,deviceOS,appVersion,deviceType,fullUserName</query>
    <earliest>$date_tok.earliest$</earliest>
    <latest>$date_tok.latest$</latest>
  </search>
  <row>
    <panel>
      <title>Users</title>
      <single>
        <title>Total</title>
        <search base="login_checkpoints">
          <query>chart dc(fullUserName)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="trendInterval">auto</option>
      </single>
    </panel>
    <panel>
      <title>Devices</title>
      <single>
        <title>Total</title>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | chart count(deviceId)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
      </single>
    </panel>
  </row>
</form>

-Thanks!

Highlighted

Re: Why is using base searches causing major performance issues on my dashboard?

Legend

All queries are executed on the server (index/search) and only final results are sent to the client. In most cases, the final results is only a small subset of the number of events. However, in your case, since you do not have a tranforming command in your base search, the query returns all the events (there a limit of 500K), increasing the size. Try adding the chart command to your base search and eliminate fields in your subsearch. One way could be

*Base Search*

... | chart dc(deviceId) as device_count dc(fullUserName) user_count

*Sub Search*

| fields user_count

View solution in original post

Highlighted

Re: Why is using base searches causing major performance issues on my dashboard?

Explorer

@sundareshr - Thanks for the quick response!

The problem is, the end result for the dashboard is going to have lots of other panels on it, too. So, I wouldn't be able to use a single 'transforming command'. (ex. I want to show a pie chart of what different OS's are being used, and what different device types are being used, and show a line chart of number of users per day).

Now, all of this derives from the same data, but, do I need to still break it into separate base queries - that have a transform command at the end?

Or, does the 'table' command count as a transforming command? I would have thought the 'fields' command at the end would limit the end data to just those final results...

-Thanks!

0 Karma
Highlighted

Re: Why is using base searches causing major performance issues on my dashboard?

Legend

Unfortunately, the table command does not reduce the number of rows. If you share all the queries (just the queries) we can come up with a way to optimize the base search. Or the other option is you can create 2-3 base searches, doesn't have to be only one. You will still see the benefits of reducing the number of concurrent searches.

One option could be to do a stats count by deviceId fullUserName os etc etc etc in the base search and then do a stats dc(deviceId) OR stats count by os in the sub-search.

Hope this gives you some ideas. If not, do share the queries and we can come up with a optimized solution

0 Karma
Highlighted

Re: Why is using base searches causing major performance issues on my dashboard?

Community Manager
Community Manager

Hi @sundareshr

Just fyi, I tried converting the "answer" by @klinek to a comment under your answer, but it has too many characters to convert unfortunately. When you do get a chance to respond, just continue your comments under your answer here. Thanks!

Patrick

0 Karma
Highlighted

Re: Why is using base searches causing major performance issues on my dashboard?

Legend

I would suggest creating a base search for all panels, except the timechart panel. And repeat your search for timechart panel.

   <search id="login_checkpoints">
     <query>    everything from your base search minus the fields command in the end | eval versionAndDevice=appVersion+" ("+deviceType+")" | stats count by 
    userId, userType, deviceId, deviceModel, deviceOS, appVersion, deviceType, fullUserName versionAndDevice</query>
   </search>
   <row>
     <panel>
       <title>Users</title>
       <single>
         <title>Total</title>
         <search base="login_checkpoints">
           <query>stats dc(fullUserName)</query>
         </search>
       </single>
     </panel>
     <panel>
       <title>Devices</title>
       <single>
         <title>Total</title>
         <search base="login_checkpoints">
           <query>| stats dc(deviceId)</query>
         </search>
       </single>
     </panel>
   </row>
   <row>
     <panel>
       <single>
         <search base="login_checkpoints">
           <query>dedup fullUserName
             | where match(userType,"INTERNAL")
             | stats count(fullUserName)</query>
         </search>
       </single>
       <single>
         <search base="login_checkpoints">
           <query>dedup fullUserName
             | where match(userType,"EXTERNAL")
             | stats count(fullUserName)</query>
         </search>
       </single>
     </panel>
     <panel>
       <single>
         <search base="login_checkpoints">
           <query>dedup deviceId
             | where match(deviceType,"iOS")
             | chart count(deviceId)</query>
         </search>
       </single>
       <single>
         <search base="login_checkpoints">
           <query>dedup deviceId
             | where match(deviceType,"Android")
             | chart count(deviceId)</query>
         </search>
       </single>
     </panel>
   </row>
   <row>
     <panel>
       <title>Unique User Logins</title>
       <chart>
         <search>
           <query>REPEAT YOUR BASE SEARCH | timechart dc(fullUserName) as "Unique Users"</query>
         </search>
       </chart>
     </panel>
   </row>
   <row>
     <panel>
       <title>Application Versions</title>
       <chart>
         <search base="login_checkpoints">
           <query>dedup deviceId
             | chart count by versionAndDevice useother=false</query>
         </search>
       </chart>
     </panel>
     <panel>
       <title>Device OS</title>
       <chart>
         <search base="login_checkpoints">
           <query>dedup deviceId
             | chart count by deviceOS useother=false</query>
         </search>
       </chart>
     </panel>
   </row>
   <row>
     <panel>
       <title>Internal Devices</title>
       <table>
         <search base="login_checkpoints">
           <query>where match(userType,"INTERNAL")
             | dedup deviceId
             | chart count by deviceModel useother=false
             | sort - count</query>
         </search>
       </table>
     </panel>
     <panel>
       <title>External Devices</title>
       <table>
         <search base="login_checkpoints">
           <query>where match(userType,"EXTERNAL")
             | dedup deviceId
             | chart count by deviceModel useother=false
             | sort - count</query>
         </search>
       </table>
     </panel>
   </row
Highlighted

Re: Why is using base searches causing major performance issues on my dashboard?

Explorer

@sundareshr - Thanks again for all the help.

Your plan of attack worked!

By using the 'chart' command - that got those results down to 10.2 MB, and then separating out the timechart results, only take 0.29MB. For the same number of results as before.

So, looks like a decent set of 'design' rules would be:

1) Ensure your base query ends with a 'transforming command' - chart, stats, etc.
2) Use different queries for timecharts & individual results

Is there a good way to re-use a base query for multiple timecharts?

-Thanks!

0 Karma
Highlighted

Re: Why is using base searches causing major performance issues on my dashboard?

Legend

For timechart based panels, you can generate data using bin & stats. Something like this. This may work in your current dashboard as well.

... | bin span=15m (or whatever span you choose) _time | stats count avg max etc by _time

This will give you the following columns

_time count avg max etc

Then in your sub-search, you can use timechart

0 Karma
Highlighted

Re: Why is using base searches causing major performance issues on my dashboard?

Explorer

@sundareshr - I don't seem to be able get get bin, stats, and timechart to play nice together...

0 Karma
Highlighted

Re: Why is using base searches causing major performance issues on my dashboard?

New Member

@klinek a bit off topic, how did you get the size of the search results, i could not find anything related in thew job inspector.

0 Karma