How to dynamically set the y-axis scale of a chart with the max value needed + X%?

[andreafebbo]

Hi all!

I have the following search which displays a stacked bar chart:

<index, filters and sourcetype>
| stats count as Events by  BU DatabaseName
| sort -BU, Events desc
| streamstats count as rank by BU
| where rank <= 5
| chart max(Events) as Events by BU DatabaseName
| addtotals fieldname=_total 
| sort - _total 
| fields - _total

I would like to set $variable$

<option name="charting.axisY.maximumNumber">$variable$</option>

As the max of _total + 10% (before removing it) and without using, if possible, hidden selectors or other queries.

Thanks

[rjthibod · Accepted solution]

You can try doing this with search event handlers and a base search. I am not 100% sure it will work, but it is probably the only option that does not require Javascript.

Also, you should rename _total to total, because you should not use leading underscores in fields you create. That is reserved for internal Splunk fields.

 <form>
  ...
  <search id="base_search">
    <query>
     <index, filters and sourcetype>
     | stats count as Events by  BU DatabaseName
     | sort -BU, Events desc
     | streamstats count as rank by BU
     | where rank <= 5
     | chart max(Events) as Events by BU DatabaseName
     | addtotals fieldname=total 
     | sort - total 
    </query>
    <done>
      <eval token="variable">$result.total$ * 1.10</eval>
    </done>
  </search>
  ...
  <row>
   <panel>
     <chart>
       <search base="base_search">
         <query>
           fields - total
         </query>
       </search>
       ...
       <option name="charting.axisY.maximumNumber">$variable$</option>
       ...
     </chart>
   </panel>
   ...
   </row>
 ...
</form>

[andreafebbo]

I removed the underscore from _total and I inserted:

<done>
     <eval token="variable">$result.total$ * 1.10 </eval>
   </done>

between </query> and </search>.

I put also this in place:

 <option name="charting.axisY.maximumNumber">$variable$</option>

But it seems no to work.

If I skipped something tell me please.

Thanks

[rjthibod]

I am sorry, but I totally missed the fact that your last line in the query eliminates the total field. You would have to remove that line to keep the total field in the finalized data in order to use the eval token mechanism I provided.

Just for giggles, remove the last line and see if it does work in setting the y-axis. If it does, we can use a post-processing search to render the data without total but still set the token variable in a base search.

[andreafebbo]

I does work, but how can I use a post-processing search?

[rjthibod]

You would do something like the following where you have a base search that contains the field "total" and sets the token, and then you would use a post-processing search that removes "total" from the data but populates your chart.

<form>
  ...
   <search id="base_search">
     <query>
      <index, filters and sourcetype>
      | stats count as Events by  BU DatabaseName
      | sort -BU, Events desc
      | streamstats count as rank by BU
      | where rank <= 5
      | chart max(Events) as Events by BU DatabaseName
      | addtotals fieldname=total 
      | sort - total 
     </query>
     <done>
       <eval token="variable">$result.total$ * 1.10</eval>
     </done>
   </search>
   ...
   <row>
    <panel>
      <chart>
        <search base="base_search">
          <query>
            fields - total
          </query>
        </search>
        ...
        <option name="charting.axisY.maximumNumber">$variable$</option>
        ...
      </chart>
    </panel>
    ...
    </row>
  ...
  </form>

[andreafebbo]

I works great!
Thanks a lot!
:)

[dinaabdelhakam]

Please tell me what is the output of this ? >$result.total$ this returns sum of total or what ??