Bar Chart Color change for dynamically changing string values of one field

[ak9092]

Hello,

I have very basic query which is giving me the desired results and visualization but even after lot of researching what I am not able to do is color the bars according to the field values.

Below is something what my query looks like -

my search ...| chart count by status

now this status fields has around 15-20 status values like complete,pending, repair, canceled, posted,etc, etc..

so as of now my Bar chart has Status field on X-axis with the field values name mapped to it like complete, cancelled,etc and my Y -axis has the count of those statuses.

Now what i want is to keep the visualization as it is and change colors of few statuses like green for Complete, red for canceled, amber for repair and so on.

I tried

charting.fieldColors">{"COMPLETE":#32a838,"CANCELED":#e81324,"REPAIR":#FFC200}

but is not helping.

I also tried to transposing rows to column with which i am able to change the colors but then the mapping of field values onto to the Y-axis is  being removed and converted to legends, which is not looking good.

Can this be achieved by keeping my current visualization intact ?

I have gone through multiple pages here on Splunk Answers but no luck.

Thanks in advance.

[niketn · Accepted solution]

@ak9092 This question has definitely been answered multiple times before. But even I am having difficulty searching for my own answer. So let me documented one more time.

The reason why you get only one series color for | stats count by status is that you have only one series i.e. count (also displayed in the Legend).
When you use transpose you get series colors as all status values become column and hence show up in Legend. However, you loose values on x-axis because of which you can not show series names on x-axis but as Legend due to this approach.

What you need to use is following kind of eval  | eval {field1}=value

<yourMainSearch>
| stats count by status
| eval {status}=count
| fields - count

The above will create Column names as Status as well as Row Name as Status and for each column row combination populate corresponding count.

The Column/Bar chart however, will not stick together as this kind of creates matrix form of data. So you would need to enable Stacked mode for the chart.

Following is the Simple XML Code for Run Anywhere example which generates Status and corresponding count similar to the question (replace with your existing search). Please try out and confirm!

<dashboard>
  <label>Field Color for Count by Status</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>| makeresults
| fields - _time
| eval status="COMPLETE,PENDING,REPAIR,CANCELED,POSTED"
| makemv status delim=","
| mvexpand status
| eval count=random()
| table status count
| eval {status}=count
| fields - count</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">auto</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</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.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">{"COMPLETE":#32a838,"CANCELED":#e81324,"REPAIR":#FFC200,"PENDING":#F1813F,"POSTED":#006D9C }</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">327</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
</dashboard>

[ak9092]

@niketn Thanks a lot for documenting it once again, this was something i was looking for .

Just one more question though - how do i sort the statuses by count ?

[niketn]

@ak9092 I am glad you found this helpful. For sorting just add the sort command  😉.

| sort - count

Do upvote the answer/comments that helped.

[ak9092]

oops ..my bad @niketn . Actually i did use sort before, but was viewing data in stats table itself and since the values was separated didn't paid much attention that it had already sorted 🙂 ..anyways thanks again for your help

[thambisetty]

Values should match in results and charting.fieldColors.

below is the example:

{"foo\: bar": 0xffff00, foo: 0xff0000, "foobar": 0x000000}

Escape the following special characters in a key or string value with double quotes:

[]{}(),:" example in your case: 
<option name="charting.fieldColors">{"Complete":0x9BE8AB,"Cancelled":0xFF0000}</option>
you can change color codes.