topic Re: Colorized bar graph with log scale overlay in Dashboards & Visualizations

Colorized bar graph with log scale overlay

rikinet — Mon, 09 Oct 2023 12:07:01 GMT

I have time series data like this:

_time
digital_value: can be either 0.1 or 1 (see Note)
analog_value: can be 0, 100, 500, 1000, 5000, 10000

Note) It's actually 0 or 1, but 0 doesn't show in a bar graph.

I want to plot this data in a diagram like this:

X axis = _time
digital_value=0.1 as a red bar
digital_value=1 as a green bar
analog_value as an overlaid line graph, with log scale Y axis

To colorize digital_value, I understand I must split it into two series, like this:

| digital_value_red = if(digital_value=0.1, 0.1, null()) | digital_value_green = if(digital_value=1, 1, null()) | fields -digital_value

However, this creates two bars per data point, where only the non-null one is shown and the other one leaves a gap. That way, I don't have equally spaced bars along the X axis any more. See this example:

So, stacked bars? Yes, but that doesn't work with log scale Y axis for the overlaid line graph.

So, calculate log(analog_value) and plot that a linear Y axis? While that produces a proper visual, you can't read the value of analog_value any more (only it's log).

Any ideas how I can achieve a colorized bar graph + log scale overlay?

Re: Colorized bar graph with log scale overlay

bowesmana — Tue, 10 Oct 2023 02:17:10 GMT

@rikinet Just make the chart show a stacked chart and as you have only a single value per time, it will show one or the other

Here's an example

<dashboard> <label>colourgreen</label> <row> <panel> <chart> <search> <query>| makeresults count=20 | streamstats c | eval _time=now() - (c * 60) | eval digital_value=if (random() % 2 == 1, 0.1, 1) | eval analog_value=mvindex(split("0,100,500,1000,5000,10000",","), random() % 6) | fields - c | eval digital_value_red = if(digital_value=0.1, 0.1, null()) | eval digital_value_green = if(digital_value=1, 1, null()) | fields - digital_value </query> <earliest>-24h@h</earliest> <latest>now</latest> <sampleRatio>1</sampleRatio> </search> <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.abbreviation">none</option> <option name="charting.axisX.scale">linear</option> <option name="charting.axisY.abbreviation">none</option> <option name="charting.axisY.scale">linear</option> <option name="charting.axisY2.abbreviation">none</option> <option name="charting.axisY2.enabled">1</option> <option name="charting.axisY2.scale">log</option> <option name="charting.chart">column</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.overlayFields">analog_value</option> <option name="charting.chart.showDataLabels">none</option> <option name="charting.chart.sliceCollapsingThreshold">0.01</option> <option name="charting.chart.stackMode">stacked</option> <option name="charting.chart.style">shiny</option> <option name="charting.drilldown">none</option> <option name="charting.fieldColors">{digital_value_red: 0xFF0000, digital_value_green: 0x00FF00}</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.mode">standard</option> <option name="charting.legend.placement">right</option> <option name="charting.lineWidth">2</option> <option name="height">406</option> <option name="trellis.enabled">0</option> <option name="trellis.scales.shared">1</option> <option name="trellis.size">medium</option> </chart> </panel> </row> </dashboard>

Re: Colorized bar graph with log scale overlay

rikinet — Tue, 10 Oct 2023 19:43:43 GMT

Thank you @bowesmana for your comprehensive reply and example!

It works fine - but unfortunately it still doesn't get the logarithmic scale on the overlay right. While setting

<option name="charting.axisY2.scale">log</option>

does not yield any validation error, it simply doesn't work as expected. Your example image also shows a linear secondary Y axis.

When editing this dashboard in the graphical editor, I get an error when I try to change the Y axis to logarithmic.

Maybe there is just no possible way in Splunk to do what I want to do?

Re: Colorized bar graph with log scale overlay

bowesmana — Tue, 10 Oct 2023 20:27:51 GMT

Ahh.. When you are stacking a bar chart, you cannot use log scale on the left hand Y-axis and it gives an error, but when setting Log on the Chart Overlay right hand axis, it does not give an error but ignores log.

I did't realise it restricted the RH axis.

What if you added

| eval analog_value=log(analog_value,10)

it would have the same effect, although not with the right numbers ...

Re: Colorized bar graph with log scale overlay

rikinet — Tue, 10 Oct 2023 22:55:02 GMT

Yes, I also had that idea:

So, calculate log(analog_value) and plot that a linear Y axis? While that produces a proper visual, you can't read the value of analog_value any more (only it's log).

But the illegibility of the true values still bothers me, which is why I was hoping for an even more perfect solution, somehow... Maybe there is none.