Colorized bar graph with log scale overlay

[rikinet]

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?

[bowesmana]

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

[rikinet]

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?

[bowesmana]

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

[rikinet]

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.