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

[wajeeh911]

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>

[teunlaan]

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>

[renjith_nair · Accepted solution]

@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>