Dashboards & Visualizations

How to change bar colors in a bar chart based on values in xml?

donfarland
Explorer

I've read a bunch of questions and answers on this topic, but seem to be having a good deal of trouble getting this working. I have a chart that displays the current temperature across a number of different rooms. I've added chart overlays to visualize the minimum and maximum allowable temperatures, but would like to color the bars based on where they are. In other words, change the bar to green when it is "In Spec" and red when it is "Out of Spec". My current snapshot of XML is:

      <chart>
        <search>
          <query>index="fac_therms" NOT host="*RDC" | stats latest(S1_Temp) as "S1_Temp" latest(S2_Temp) as "S2_Temp" by host  | eval okS1Temp = if(S1_Temp&lt;=87 AND S1_Temp&gt;85,S1_Temp,0)
 | eval warnS1Temp = if(S1_Temp&lt;=85 OR S1_Temp&gt;87,S1Temp,0)</query>
          <earliest>0</earliest>
          <latest></latest>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisLabelsY.majorUnit">10</option>
        <option name="charting.axisLabelsY2.majorUnit">10</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.scale">linear</option>
        <option name="charting.axisY.maximumNumber">100</option>
        <option name="charting.axisY.minimumNumber">60</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">true</option>
        <option name="charting.axisY2.maximumNumber">100</option>
        <option name="charting.axisY2.minimumNumber">60</option>
        <option name="charting.axisY2.scale">inherit</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">maxT,minT</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">minimal</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.fieldColors">{"warnS1Temp": 0xFF0000, "okS1Temp":0x00FF00}</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
      </chart>

When I set the values for "okS1Temp" at 67-87, a 3rd bar (Green) is displayed as "okS1Temp". If I tighten the spec, the green bar is gone and nothing is displayed. I know I'm close, but I've been messing with this for a while now and hoping someone can point out what I'm doing wrong. Also, in case you didn't notice, I'd like to do the same thing for the readings from the second sensor.

Thanks in advance.

1 Solution

stephanefotso
Motivator

Hello!
I have scanned your code, and i think it should work. just make sure when setting values for okS1Temp, that these values can't be found in warnS1Temp values.
My only question is that, where are your overlayfields maxT and minT comming from?
See below your same code with okS1Temp as my overlayfield, just test it.

<dashboard>
  <label>bar colors based on values</label>
  <row>
    <panel>
      <chart>
        <searchString>index=_internal| stats latest(date_minute) as "S1_Temp" latest(date_second) as "S2_Temp" by sourcetype| eval okS1Temp = if(S1_Temp<=45 AND S1_Temp>17,S1_Temp,0)| eval warnS1Temp = if(S1_Temp<=17 OR S1_Temp>45,S1_Temp,0)</searchString>
        <earliestTime>0</earliestTime>
        <latestTime/>
        <earliest>0</earliest>
        <latest/>
        <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.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">true</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="charting.fieldColors">{"warnS1Temp": 0xFF0000, "okS1Temp":0x00FF00}</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.chart.overlayFields">okS1Temp</option>
      </chart>
    </panel>
  </row>
</dashboard>

View solution in original post

stephanefotso
Motivator

Hello!
I have scanned your code, and i think it should work. just make sure when setting values for okS1Temp, that these values can't be found in warnS1Temp values.
My only question is that, where are your overlayfields maxT and minT comming from?
See below your same code with okS1Temp as my overlayfield, just test it.

<dashboard>
  <label>bar colors based on values</label>
  <row>
    <panel>
      <chart>
        <searchString>index=_internal| stats latest(date_minute) as "S1_Temp" latest(date_second) as "S2_Temp" by sourcetype| eval okS1Temp = if(S1_Temp<=45 AND S1_Temp>17,S1_Temp,0)| eval warnS1Temp = if(S1_Temp<=17 OR S1_Temp>45,S1_Temp,0)</searchString>
        <earliestTime>0</earliestTime>
        <latestTime/>
        <earliest>0</earliest>
        <latest/>
        <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.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">true</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="charting.fieldColors">{"warnS1Temp": 0xFF0000, "okS1Temp":0x00FF00}</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.chart.overlayFields">okS1Temp</option>
      </chart>
    </panel>
  </row>
</dashboard>

donfarland
Explorer

That definitely helped. This is ultimately what I ended up with. The minV and maxV were inserted to provide horizontal lines marking the upper and lower limits.

<panel>
  <chart>
    <search>
      <query>index="pmf_therms" | stats latest(S1_Temp) as "S1_Temp" latest(S2_Temp) as "S2_Temp" by host | eval okS1Temp = if(S1_Temp&lt;87 AND S1_Temp&gt;67,S1_Temp,0) | eval okS2Temp = if(S1_Temp&lt;87 AND S2_Temp&gt;67,S2_Temp,0) | eval sevS1Temp = if(S1_Temp&lt;67 OR S1_Temp&gt;87,S1_Temp,0) | eval sevS2Temp = if(S2_Temp&lt;67 OR S2_Temp&gt;87,S2_Temp,0) | eval maxV=87 | eval minV=67</query>
      <earliest>0</earliest>
    </search>
    <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
    <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
    <option name="charting.axisLabelsY.majorUnit">10</option>
    <option name="charting.axisLabelsY2.majorUnit">10</option>
    <option name="charting.axisTitleX.visibility">collapsed</option>
    <option name="charting.axisTitleY.visibility">visible</option>
    <option name="charting.axisTitleY2.visibility">visible</option>
    <option name="charting.axisX.scale">linear</option>
    <option name="charting.axisY.maximumNumber">100</option>
    <option name="charting.axisY.minimumNumber">50</option>
    <option name="charting.axisY.scale">linear</option>
    <option name="charting.axisY2.enabled">true</option>
    <option name="charting.axisY2.maximumNumber">100</option>
    <option name="charting.axisY2.minimumNumber">50</option>
    <option name="charting.axisY2.scale">inherit</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">maxV,minV</option>
    <option name="charting.fieldColors">{"sevS1Temp": 0xd13b3b, "okS1Temp":0x7e9f44, "sevS2Temp": 0xd13b3b, "okS2Temp":0x7e9f44, "maxV": 0xebe42d, "minV": 0xebe42d}</option>
    <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
    <option name="charting.chart.stackMode">default</option>
    <option name="charting.chart.style">minimal</option>
    <option name="charting.drilldown">none</option>
    <option name="charting.layout.splitSeries">0</option>
    <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
    <option name="charting.legend.placement">none</option>
  </chart>
</panel>

The only issue I'm still working on is suppressing the output of S1_Temp and S2_Temp as its a duplicated column in a chart. I'm sure I'll figure that one out. Thanks for the help.

Masterbaker
Explorer

An old question, but to remove the fields from your search :

| fields -S1_Temp, S2_Temp

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...