Dashboards & Visualizations

Can you help me optimize my query with a base search to create multiple graphs in a dashboard?

wajeeh911
Engager

I'm trying to optimize my queries in my dashboard using base searches, but I'm having a bit of trouble.

I have two base searches which are reusable, but I'm not sure how to incorporate it into my dashboard

<search id="First_Base_Search">
       <query>index=Auto log!=null location=farm</query>
   </search>

<search id="Second_Base_Search">
       <query>rename growTime as Duration | stats perc50(totalTimes) as s50,  perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
   </search>

Dashboard:

<dashboard>
  <label>Perf Tests</label>
  <row>
    <panel>
      <chart>
        <title>September Chart</title>
        <search>
          <query>index=Auto log!=null location=farm fruit=apple | rename growTime as Duration | stats perc50(totalTimes) as s50,  perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
          <earliest>$earliest$</earliest>
          <latest>$latest$</latest>
          <refresh>1m</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">none</option>
        <option name="refresh.display">none</option>
        <option name="charting.lineWidth">3</option>
      </chart>
    </panel>
    <panel>
      <chart>
        <title>November Chart</title>
        <search>
          <query>index=Auto log!=null location=farm fruit=pear | rename growTime as Duration | stats perc50(totalTimes) as s50, perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
          <earliest>$earliest$</earliest>
          <latest>$latest$</latest>
          <refresh>1m</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">none</option>
        <option name="refresh.display">none</option>
        <option name="charting.lineWidth">3</option>
      </chart>
    </panel>
    <panel>
      <chart>
        <title>December Chart</title>
        <search>
          <query>index=Auto log!=null location=farm fruit=apricot | rename growTime as Duration | stats perc50(totalTimes) as s50, perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
          <earliest>$earliest$</earliest>
          <latest>$latest$</latest>
          <refresh>1m</refresh>
          <refreshType>delay</refreshType>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">none</option>
        <option name="refresh.display">none</option>
        <option name="charting.lineWidth">3</option>
      </chart>
    </panel>
  </row>
</dashboard>
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@wajeeh911,

Here is an example of base search / post process search - http://docs.splunk.com/Documentation/Splunk/7.2.0/Viz/Savedsearches#Examples_2

In your case, First_Base_Search can be used in all panels since the base search is same.

 <dashboard>
   <label>Perf Tests</label>
    <search id="First_Base_Search">
        <query>index=Auto log!=null location=farm</query>
    </search>   
   <row>
     <panel>
       <chart>
         <title>September Chart</title>
         <search base="First_Base_Search">
           <query>search fruit=apple | rename growTime as Duration | stats perc50(totalTimes) as s50,  perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
           <earliest>$earliest$</earliest>
           <latest>$latest$</latest>
           <refresh>1m</refresh>
           <refreshType>delay</refreshType>
         </search>
         <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
         <option name="charting.chart">line</option>
         <option name="charting.drilldown">none</option>
         <option name="refresh.display">none</option>
         <option name="charting.lineWidth">3</option>
       </chart>
     </panel>
     <panel>
       <chart>
         <title>November Chart</title>
         <search base="First_Base_Search">
           <query>search fruit=pear | rename growTime as Duration | stats perc50(totalTimes) as s50, perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
           <earliest>$earliest$</earliest>
           <latest>$latest$</latest>
           <refresh>1m</refresh>
           <refreshType>delay</refreshType>
         </search>
         <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
         <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
         <option name="charting.chart">line</option>
         <option name="charting.drilldown">none</option>
         <option name="refresh.display">none</option>
         <option name="charting.lineWidth">3</option>
       </chart>
     </panel>
     <panel>
       <chart>
         <title>December Chart</title>
         <search base="First_Base_Search">
           <query>search fruit=apricot | rename growTime as Duration | stats perc50(totalTimes) as s50, perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
           <earliest>$earliest$</earliest>
           <latest>$latest$</latest>
           <refresh>1m</refresh>
           <refreshType>delay</refreshType>
         </search>
         <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
         <option name="charting.chart">line</option>
         <option name="charting.drilldown">none</option>
         <option name="refresh.display">none</option>
         <option name="charting.lineWidth">3</option>
       </chart>
     </panel>
   </row>
 </dashboard>
Happy Splunking!

View solution in original post

teunlaan
Contributor

I understand what you are trying to do, but I'm not sure it is possible.
You can't combine 2 basesearch, but in your current setup, you could make a macro for for the "second_base_search"

first your basesearch: Base searches should be a post process search, that returns values you can use all other searches. So you need to include your fruit field, but also your _time
Also you need to secify a timerange for your basesearch, in your case is over 3 months (what you need to split in your second search
It probaly looks somefing lite this:


<query>index=Auto log!=null location=farm fruit=* | rename growTime as Duration | stats perc50(totalTimes) as s50, perc90(totalTimes) as s90 by Duration fruit _time| table Duration, s50, s90 fruit _time</query>
<earliest>$earliest$</earliest>
<latest>$latest$</latest>
<refresh>1m</refresh>
<refreshType>delay</refreshType>

Second part is somethin like:


<query>| search fruit=apple *** some search you get only the correct timespan" | untable Duration, percentile, value | chart first(value) over Duration by percentile</query>
</search>

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@wajeeh911,

Here is an example of base search / post process search - http://docs.splunk.com/Documentation/Splunk/7.2.0/Viz/Savedsearches#Examples_2

In your case, First_Base_Search can be used in all panels since the base search is same.

 <dashboard>
   <label>Perf Tests</label>
    <search id="First_Base_Search">
        <query>index=Auto log!=null location=farm</query>
    </search>   
   <row>
     <panel>
       <chart>
         <title>September Chart</title>
         <search base="First_Base_Search">
           <query>search fruit=apple | rename growTime as Duration | stats perc50(totalTimes) as s50,  perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
           <earliest>$earliest$</earliest>
           <latest>$latest$</latest>
           <refresh>1m</refresh>
           <refreshType>delay</refreshType>
         </search>
         <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
         <option name="charting.chart">line</option>
         <option name="charting.drilldown">none</option>
         <option name="refresh.display">none</option>
         <option name="charting.lineWidth">3</option>
       </chart>
     </panel>
     <panel>
       <chart>
         <title>November Chart</title>
         <search base="First_Base_Search">
           <query>search fruit=pear | rename growTime as Duration | stats perc50(totalTimes) as s50, perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
           <earliest>$earliest$</earliest>
           <latest>$latest$</latest>
           <refresh>1m</refresh>
           <refreshType>delay</refreshType>
         </search>
         <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
         <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
         <option name="charting.chart">line</option>
         <option name="charting.drilldown">none</option>
         <option name="refresh.display">none</option>
         <option name="charting.lineWidth">3</option>
       </chart>
     </panel>
     <panel>
       <chart>
         <title>December Chart</title>
         <search base="First_Base_Search">
           <query>search fruit=apricot | rename growTime as Duration | stats perc50(totalTimes) as s50, perc90(totalTimes) as s90 by Duration | table Duration, s50, s90 | untable Duration, percentile, value | chart first(value) over Duration  by percentile</query>
           <earliest>$earliest$</earliest>
           <latest>$latest$</latest>
           <refresh>1m</refresh>
           <refreshType>delay</refreshType>
         </search>
         <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</option>
         <option name="charting.chart">line</option>
         <option name="charting.drilldown">none</option>
         <option name="refresh.display">none</option>
         <option name="charting.lineWidth">3</option>
       </chart>
     </panel>
   </row>
 </dashboard>
Happy Splunking!
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 ...