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

[donfarland]

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.

[stephanefotso · Accepted solution]

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]

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]

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

| fields -S1_Temp, S2_Temp