Change color of Splunk graph based on string value

[danielpellarini]

I have seen this question and this docs page, together with a few other questions on the topic, but I am having some issues getting this to work on a new dashboard panel. The underlying search string is this:

sourcetype="csv" QuestionNumber=1 | stats count by Answer | sort -count

And the results are of the following form:

-------------------------
Answer      |       count 
-------------------------
Very good   |        100
Good        |        200
Ok          |         50
Bad         |          9
Very bad    |          2

In the bar graph that gets created from this table, I would like the bars for "Bad" and "Very Bad" to be displayed in red, the one for "Ok" in yellow and the ones for "Good" and "Very good" in green. This is the XML code for this dashboard panel (I have removed some lines that are not relevant):

<row>
    <panel>
      <chart>
        <title>Test</title>
        <search>
          <query>source="/sourcetype="csv" QuestionNumber=1 | stats count by Answer| sort -count
          | eval bad = if(Answer=="Very bad" OR Answer=="Bad",AnswerCode,null())
          | eval ok = if(Answer=="Ok",AnswerCode,null())
          | eval good = if(Answer=="Very good" OR Answer=="Good",AnswerCode,null())
          </query>
          <earliest></earliest>
          <latest></latest>
        </search>
        <option name="charting.chart">bar</option>
        <option name="charting.fieldColors">{"bad":0xFF0000, "ok":0xFFA500, "good":0x73A550}</option>
      </chart>
    </panel>
  </row>

But the colors don't change with this. If I use the count value as the value to check against, this is working, so I am thinking I might be overseeing something very obvious...

Update

After the suggestions below, I have updated my search to the following:

source=sourcetype="csv" QuestionNumber=1            
 | eval bad = if(Answer=="Very bad" OR Answer=="Bad",1,0)
 | eval ok = if(Answer=="Ok",1,0)
 | eval good = if(Answer=="Very good" OR Answer=="Good",1,0)
 | stats count by bad ok good| sort -count

And now I get the opposite result: the whole chart is red 😄 Also, there is no "bad" level, but I see a "count" label, which shouldn't be there.

[jkat54 · Accepted solution]

The field names have to match exactly and have to be "charted" somehow. In your example, you chart the Answer field, but you used eval to create good, bad, and ok fields (you used if statement wrong but that's what you were trying to do with eval). So you're charting the original field and not seeing good, bad, and ok fields. Also, I always put the evals before the stats command out of preference.

See my example below:

<query>source="/sourcetype="csv" QuestionNumber=1            
| eval bad = if(Answer=="Very bad" OR Answer=="Bad",1,0)
| eval ok = if(Answer=="Ok",1,0)
| eval good = if(Answer=="Very good" OR Answer=="Good",1,0)
| search bad=1 OR ok=1 OR good=1
| stats count by bad ok good| sort -count
</query>

You could also use `match` with the `if` for finding "*bad*" ,etc.   Match lets you use regex to determine the match so the example below will work when ok is oK and when its OK and when its OKay, etc.  

<query>source="/sourcetype="csv" QuestionNumber=1            
| eval bad = if(match(Answer,".*[bB][aA][dD].*"),1,0)
| eval ok = if(match(Answer,".*[oO][kK].*|.*[oO][kK][aA][yY].*"),1,0)
| eval good = if(match(Answer,"*[vV][eE][rR][yY].*[gG][oO][oO][dD].*|.*[gG][oO][oO][dD].*"),1,0)
| search bad=1 OR ok=1 OR good=1
| stats count by bad ok good sort -count
</query>

Then your fieldColors charting option will be as follows:

<option name="charting.fieldColors">
{"bad:0xFF0000,"ok":0xFFA500,"good":0x73A550}
</option>

In the first example our eval statement makes bad=1 for every event that matches "Very bad" or "Bad". Then the search command focuses only on events where bad =1, ok =1, or good = 1. Finally the stats command gives a count of the bad, ok, and good fields, which can then be interpreted by your fieldcolors settings.

[jkat54]

Try this too, its my original answer but count on the appropriate fields:

 source=sourcetype="csv" QuestionNumber=1            
  | eval bad = if(Answer=="Very bad" OR Answer=="Bad",1,"")
  | eval ok = if(Answer=="Ok",1,"")
  | eval good = if(Answer=="Very good" OR Answer=="Good",1,"")
  | stats c(bad) AS bad c(ok) AS ok c(good) AS good| sort -count

Maybe change those "" to null() instead.

[danielpellarini]

Thanks for all the help. So this last example works, but I had to add a stats count as Total so I could have the total number of answers on the Y axis, otherwise I would have the "bad" field on there and the graph would be missing a bar.

Coloring works fine now, but for some reason sorting by count does not work (i.e. the "Very good" bar always comes before the "Good" one, even though the Good one are more numerous. I guess this doesn't have much to do with coloring though.

Thanks again!

[jkat54]

ah ha... so now you need to name your fields like this.

1. bad
2. good
3. ok
4. very bad
5. very good

So that they show in the order you like. See my upvoted and honorably mentioned but not accepted as answer... answer.... here: https://answers.splunk.com/answers/66734/bar-chart-color.html#answer-66769

Thanks for accepting my answer!

[danielpellarini]

Ok the solution to this last bit was simple when I looked at the table output of the search: the column with the values was named "Very bad", so all I had to do was add | rename "Very bad" as Count | sort -Count 🙂

[danielpellarini]

Thanks for the hint! I have updated the question, there seems to be some progress now after editing the search string, but there are still a couple of things that don't look quite right...

[jmallorquin]

Hi,

I think that you should put the values in the xml in the same way, (case sensitive)

Hope i help you

[danielpellarini]

Yes thanks, that should be fine I think. it looks like the problem might be in the search somewhere.