topic Re: Column chart colors based on percentage of value in All Apps and Add-ons

Column chart colors based on percentage of value

jonydupre — Thu, 17 Oct 2019 09:14:10 GMT

Hi all,

I've made a couple of colomn charts to monitor healthy machines. But I would like to give the bars certain colors based on a % of unhealthy machines. I tried to google this but I didn't find something that would work for me.

The searches are as followed:

index=linux host=*test* earliest=-1d@d latest=@d "healthcheck: System not healthy" | dedup host | stats count by host
| stats count as TotalA
| appendcols 
[search index=linux host=*test* earliest=@d latest=now "healthcheck: System not healthy" | dedup host | stats count by host 
| stats count as TotalB]
| appendcols 
[search index=linux host=*test* earliest=-2d@d latest=-1d@d "healthcheck: System not healthy" | dedup host | stats count by host 
| stats count as TotalC]
| eval Yesterday=TotalA 
| eval Today=TotalB
| eval Daybeforethat=TotalC
| fields HealthchecksError, Daybeforethat, Yesterday, Today

This search gives the following chart:

These colors are added with the following option:

<option name="charting.seriesColors">[0xFFA07A,0xF08080,0xCD5C5C]</option>

If I want to base the colors on a percentage of the values given by the search, should I add this as an option? Or is this supposed to be written in the search? If someone could help me out with, for example, turn color red if percentage is >10?

I've used the static color changes before in the source code but with a growing company, this isn't efficient.

Thanks a lot!

New pictures

Re: Column chart colors based on percentage of value

jonydupre — Mon, 21 Oct 2019 07:00:15 GMT

Could anyone maybe help me out?

Re: Column chart colors based on percentage of value

Graham_Hanningt — Mon, 21 Oct 2019 08:51:25 GMT

Example search tested in Splunk 7.3.1 using makeresults, eval, and append commands to generate example data (three events, each with two fields: Day and Errors😞

|  makeresults 
| eval Day="Daybeforethat", Errors=20
| append 
    [| makeresults 
    | eval Day="Yesterday", Errors=40 ] 
| append 
    [| makeresults 
    | eval Day="Today", Errors=80 ] 
| eval Good = if(Errors <= 30,'Errors',null), Bad = if((Errors > 30) AND (Errors <= 50),'Errors',null), Ugly = if((Errors > 50),'Errors',null)
| table Day, Good, Bad, Ugly

Given existing search-based results with the same structure—three rows (one for each day), each with two fields ( Day and Errors)—the search effectively consists of just the last two commands ( eval Good ... and table).

Significant charting options ( chart.stackMode and fieldColors😞

<option name="charting.chart.stackMode">stacked</option>
<option name="charting.fieldColors">{"Good": 0x66cc66, "Bad": 0xcccc66, "Ugly": 0xcc6666}</option>

Example chart:

The "trick": For each day, calculate values for Good, Bad, and Ugly. Only specify a value for the applicable field; leave the other two null. For example, If a value is "Good", leave the Bad and Ugly fields null. Each day will have only a Good, Bad, or Ugly value. Stack these field values. Each stack will have only a single value, with the appropriate color.

Nit: The data value appears inside the bar rather than on top. This is a consequence of stacking. Sorry.

Re: Column chart colors based on percentage of value

Graham_Hanningt — Mon, 21 Oct 2019 09:01:40 GMT

"Daybeforethat" (your term) = "The day before yesterday" = "Eergisteren" (Dutch), right?

Re: Column chart colors based on percentage of value

Graham_Hanningt — Mon, 21 Oct 2019 09:16:51 GMT

If you wanted to expose the good/bad/ugly thresholds to the dashboard user, you could include the thresholds in the field names. For example, instead of the field name Good, use the field name Good (< 30) (or ... (< 30%): for various reasons, including simplicity, I deliberately kept the term "percentage" out of my answer).

Re: Column chart colors based on percentage of value

Graham_Hanningt — Mon, 21 Oct 2019 09:22:56 GMT

I deliberately confined my answer to Simple XML, without delving into JavaScript.

Re: Column chart colors based on percentage of value

jonydupre — Mon, 21 Oct 2019 09:23:55 GMT

Yea, I had some Dutch words in the search but I forgot to edit the picture.. That's why it's Dutch in the image.
Today
Yesterday
Daybeforethat

Vandaag
Gisteren
Eersgisteren

In that order indeed.

Re: Column chart colors based on percentage of value

jonydupre — Mon, 21 Oct 2019 09:35:17 GMT

Thanks a lot for the comprehensive answer!
So I would need to add these 2 lines to my search:

 | eval Good = if(Errors <= 30,'Errors',null), Bad = if((Errors > 30) AND (Errors <= 50),'Errors',null), Ugly = if((Errors > 50),'Errors',null)
     | table Day, Good, Bad, Ugly

The table line will replace the fields line, right? (Line 12). Also, af of right now, you made some results with makeresults
In order to create an example, the good, bad and ugly examples are static values right? Would it be possible to do something like:
Good = < 5%
Bad = < 20%
Ugly = < 30 %
Instead of static values?
Again, thanks a lot for the help! I'm quite new with Splunk so maybe I'm asking some obvious questions, but I would really like to learn.

Edit: Maybe we should focus more on getting the search to work like your example. I can always later on change from static values to percentages I think..

Re: Column chart colors based on percentage of value

Graham_Hanningt — Mon, 21 Oct 2019 13:57:54 GMT

You're welcome!

You wrote:

So I would need to add these 2 lines to my search

Yes, but it's not quite that simple.

If I interpret your original search correctly, it uses the appendcols command to append columns to a single event. By contrast, my answer requires that you have three events: one per day, each with two fields ( Day and Errors). You need to reproduce that structure based on searching your ingested events, rather than generating them as I have done using makeresults.

I don't have your data, so I would have some trouble providing you with a complete replacement for your search that would be guaranteed to work. You need to do some work to adapt my makeresults-based example. Tip: use append, as in my example, instead of appendcols.

Copy my example search into the default Splunk Search app, excluding the last eval Good... and table commands, and view the tabular results in the Statistics tab. Your search needs to reproduce that structure.

That is, you need to reproduce the structure of the results of the following search (identical to my example in the answer, but with the last eval Good... and table commands replaced with a single table command:

| makeresults 
| eval Day="Daybeforethat", Errors=20
| append 
    [| makeresults 
    | eval Day="Yesterday", Errors=40 ] 
| append 
    [| makeresults 
    | eval Day="Today", Errors=80 ] 
| table Day, Errors

(The table command is really only there to exclude the unwanted _time field generated by makeresults. I could have used fields - _time to do the same thing.)

Re: Column chart colors based on percentage of value

Graham_Hanningt — Mon, 21 Oct 2019 14:09:09 GMT

Re:

the good, bad and ugly examples are static values right?

Yes. I arbitrarily set thresholds at 30 and 50: less than or equal to 30 is "good", greater than 30 and less than or equal to 50 is "bad", greater than 50 is "ugly".

Would it be possible to do something like ... Instead of static values?

Yes.

However, a specific answer would depend on which method you want to use to specify the threshold values. For example: macro, token, lookup, search?

In yet more detail, if the answer is "token": do you want dashboard UI controls that set tokens for the threshold values, and for those tokens to be used in your search?

This is veering off the subject of your original question, and I suspect there will be existing answers (and Splunk docs) that cover this, but I'm happy to help as far as I can.

Re: Column chart colors based on percentage of value

Graham_Hanningt — Mon, 21 Oct 2019 14:22:03 GMT

I wish Splunk Simple XML allowed token references in the XML element content for chart options such as fieldColors. That might allow the possibility of alternative answers without this "stacking" kludge. I can dream. 😉

Re: Column chart colors based on percentage of value

Graham_Hanningt — Tue, 22 Oct 2019 01:44:00 GMT

Jony, To make it easier for other users to find this question in the future, would you mind correcting the typo: replace "Colomn" with "Column"?

Re: Column chart colors based on percentage of value

jonydupre — Tue, 22 Oct 2019 08:02:16 GMT

Goodmorning Graham,

Thanks for the info, after some research I decided to stick with static values for now. My search basicly counts the number of unhealthy hosts per day and counts them on the name of the day. For example, 1 day ago until now = today.

So I end up with 3 variables with the number of unhealthy systems summed up. If I understand correctly, I would need to make a new variable where I count all the unhealthy systems instead of 3 different variables. And compare that variable like you did with "Error".. Would this be a correct aproach? Or should I just use the 3 days and compare those?

Hope my question is clear.. I understand your search but for now I'm not sure what would be the best way for me to implement it in mine..

Re: Column chart colors based on percentage of value

jonydupre — Tue, 22 Oct 2019 09:18:16 GMT

So I'm trying out my search with your information and I did the following:
Used "Errors" to count all the unhealthy systems and used "Day" to sum up all days.

index=linux host=test* earliest=-1d@d latest=@d "healthcheck: System not healthy" | dedup host | stats count by host
| stats count as Errors | eval Day="Yesterday"
| appendcols 
[search index=linux host=test* earliest=@d latest=now "healthcheck: System not healthy" | dedup host | stats count by host 
| stats count as Errors] | eval Day="Today"
| appendcols 
[search index=linux host=test* earliest=-2d@d latest=-1d@d "healthcheck: System not healthy" | dedup host | stats count by host 
| stats count as Errors] | eval Day="Daybeforethat"
| eval Good = if(Errors <= 30,'Errors',null), Bad = if((Errors > 30) AND (Errors <= 50),'Errors',null), Ugly = if((Errors > 50),'Errors',null)
| table Day, Good, Bad, Ugly

Look in my original post for a picture, I can only use pictures locally saved.. But in my original post is an picture of the result.. Seems like the variables are being replaced each search.. Unlike your example where each day is visible..

Re: Column chart colors based on percentage of value

jonydupre — Tue, 22 Oct 2019 12:20:17 GMT

Hi Graham, I finally got it to work! I only need to speak with my department about deciding on which numbers we are going to choose for each color.

Thanks a lot!

Re: Column chart colors based on percentage of value

Graham_Hanningt — Wed, 23 Oct 2019 01:47:10 GMT

Well done! Sorry for the delay, I was about to reply. I'm glad you beat me to it.

Re: Column chart colors based on percentage of value

jonydupre — Wed, 23 Oct 2019 08:10:37 GMT

My bad, fixed the typo. I will also your accept your comment as solution to my question. Thanks a lot for all the assistance!