Splunk Search

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

andreafebbo
Communicator

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

1 Solution

rjthibod
Champion

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>

View solution in original post

0 Karma

rjthibod
Champion

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>

View solution in original post

0 Karma

andreafebbo
Communicator

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

0 Karma

rjthibod
Champion

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.

0 Karma

andreafebbo
Communicator

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

0 Karma

rjthibod
Champion

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>
0 Karma

andreafebbo
Communicator

I works great!
Thanks a lot!
:)

0 Karma

dinaabdelhakam
Path Finder

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

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!