Splunk Search

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

klinek
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!

1 Solution

sundareshr
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

the_wolverine
Champion

What version of Splunk are you seeing this in? There may be a bug you are encountering depending on the version -- exactly what I don't know without more details but there's a one that will crash your browser if you have multiple panels and one or more is rendering a chart. Bug in 6.3, fixed in 6.4.

0 Karma

klinek
Explorer

@sundareshr - Sure, here is the whole dashboard (slightly modified, to protect the innocent! 😉 ).

I really appreciate the quick responses, and I hope you can help us get to a good pattern we can follow with other dashboards we are working to create.

-Thanks!

<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>
  <row>
    <panel>
      <single>
        <search base="login_checkpoints">
          <query>dedup fullUserName
            | where match(userType,"INTERNAL")
            | chart count(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>
        <option name="afterLabel">Internal</option>
      </single>
      <single>
        <search base="login_checkpoints">
          <query>dedup fullUserName
            | where match(userType,"EXTERNAL")
            | chart count(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>
        <option name="beforeLabel">External</option>
      </single>
    </panel>
    <panel>
      <single>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | where match(deviceType,"iOS")
            | 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>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="trendInterval">auto</option>
        <option name="afterLabel">iOS</option>
      </single>
      <single>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | where match(deviceType,"Android")
            | 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>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="trendInterval">auto</option>
        <option name="beforeLabel">Android</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <title>Unique User Logins</title>
      <chart>
        <search base="login_checkpoints">
          <query>timechart dc(fullUserName) as "Unique Users"</query>
        </search>
        <option name="charting.chart">line</option>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">collapsed</option>
        <option name="charting.axisTitleY.visibility">collapsed</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">none</option>
        <option name="height">325</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Application Versions</title>
      <chart>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | eval versionAndDevice=appVersion+" ("+deviceType+")"
            | chart count by versionAndDevice useother=false</query>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.001</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="height">375</option>
      </chart>
    </panel>
    <panel>
      <title>Device OS</title>
      <chart>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | chart count by deviceOS useother=false</query>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.001</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="height">375</option>
      </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>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="drilldown">row</option>
        <option name="dataOverlayMode">none</option>
        <option name="count">15</option>
      </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>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="drilldown">row</option>
        <option name="dataOverlayMode">none</option>
        <option name="count">15</option>
      </table>
    </panel>
  </row>
</form>
0 Karma

sundareshr
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

klinek
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

sundareshr
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

ppablo
Retired

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

sundareshr
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

klinek
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

soumyasaha2506
Loves-to-Learn

@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

sundareshr
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

klinek
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...