topic Re: Column chart color change if threshold is hit in Splunk Search

Column chart color change if threshold is hit

newbie09 — Mon, 05 Aug 2019 12:49:11 GMT

Currently, i have a column chart with the default color blue. I want these default color to change if a certain count threshold is met.

Like, red for count >10, orange for <=10 and > 5, green for <=5.

Re: Column chart color change if threshold is hit

jpolvino — Mon, 05 Aug 2019 12:56:46 GMT

I'm pretty sure your question can be answered by the information in this post:
How to customize bar chart colors based on the values?

Re: Column chart color change if threshold is hit

newbie09 — Tue, 06 Aug 2019 09:25:06 GMT

i already read through those but it doesnt work for my case.

search| where (errorCode = 1 OR errorCode = 2 OR errorCode = 3 )
|stats count by errorCode

--> this is my current search returning column chart (x axis = errorCode(1,2,3) & y axis = count). The bar is defaulted to color blue. my objective is to change the color according to some threshold

search| where (errorCode = 1 OR errorCode = 2 OR errorCode = 3 )
|stats count by errorCode
|eval Critical = if(Error_Count >30,Critical,0)
|eval Warning = if(Error_Count >20,Warning,0)
|eval Normal = if(Error_Count >0,Normal,0)

--> when i tried above, it doesn't suit my objective as my xaxis becomes errorCode ,Critical,Warning,Normal.  With the erroCode bars still defaulted to blue

Re: Column chart color change if threshold is hit

niketn — Tue, 06 Aug 2019 16:17:31 GMT

@newbie09 try the following, I have introduced a Server block as well but you can get rid of the same as per your need.

 <yourMainSearch> errorCode IN (1,2,3)
| stats count as Error_Count by errorCode
| eval Threshold_Color=case(Error_Count>0 AND Error_Count<=20, "1. Normal", Error_Count>20 AND Error_Count<=30, "2. Warning",Error_Count>50 AND Error_Count<=100, "3. Critical",true(),"4. Severe")
| xyseries errorCode Threshold_Color Error_Count

Then apply the fieldColors as per Threshold_Color field created.

        <option name="charting.fieldColors">{"1. Normal": 0x53A051, "2. Warning": 0xF8BE34, "3. Critical": 0xF1813F, "4. Severe": 0xDC4E41}</option>

Following is a run anywhere simple XML dashboard example based on Splunk's _internal index for three components as sample:

<dashboard>
  <label>Chart Color by Threshold</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd component IN ("ExecProcessor", "SearchAssistant","TimeoutHeap") 
| stats count as Error_Count by component 
| eval Threshold_Color=case(Error_Count>0 AND Error_Count<=10, "1. Normal", Error_Count>10 AND Error_Count<=50, "2. Warning",Error_Count>50 AND Error_Count<=100, "3. Critical",true(),"4. Severe")
| xyseries component Threshold_Color Error_Count</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">column</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.fieldColors">{"1. Normal": 0x53A051, "2. Warning": 0xF8BE34, "3. Critical": 0xF1813F, "4. Severe": 0xDC4E41}</option>
      </chart>
    </panel>
  </row>
</dashboard>

Re: Column chart color change if threshold is hit

newbie09 — Wed, 07 Aug 2019 01:08:07 GMT

exactly what i needed. Thanks mate!

Re: Column chart color change if threshold is hit

newbie09 — Wed, 07 Aug 2019 05:12:17 GMT

@niketnilay
Just noticed that it actually creates another column chart for each of the Threshold_Colors.

Is it possible just to create 1 (combine to a single column chart just that the colors will be different?

Re: Column chart color change if threshold is hit

niketn — Thu, 08 Aug 2019 03:52:53 GMT

@newbie09 the reason why you previously had only one color Blue applied to your series was because you had only one series available i.e. Error Count. In order to apply different color you would need different series created. Which is what I have done through KPI status as Normal, Warning etc. You still have the Error Codes that you are interested in on the x-axis like before.

If you need series colors for distinction then you would need to have different series names as in the example.

Re: Column chart color change if threshold is hit

newbie09 — Sat, 10 Aug 2019 00:09:02 GMT

let me confirm my understanding, so basically there's no way that i can only have 1 bar chart with different colors according to threshold? i will always have 1 bar chart of each color?

Re: Column chart color change if threshold is hit

niketn — Sat, 10 Aug 2019 02:54:36 GMT

@newbie09 yes it is possible to have single bar with multiple threshold in the same bar. This can be done using Stacked Column chart option. But what is the criteria for having multiple threshold for each stack? You have not provided that in your requirement.

Requirement can not be driven by visualization. You should have visualization driven by final output data that you have.

i.e. "I want to have stacked bar chart for Count of Error Codes with Threshold" is not possible because it is missing the information about what to create stacks for.

"I have Count of Error Codes with Threshold bucketed hourly. What is the best way to visualize?" In this case Stacked Bar chart can be used because hourly buckets are used for counting Error Codes. Hence multiple stacks for each Error Codes for each hourly aggregation will fall under different Thresholds.

index=_internal sourcetype=splunkd NOT (component IN ("Metrics","PeriodicHealthReporter"))
| bin _time span=1h
| stats count as Error_Count by _time component 
| eval Threshold_Color=case(Error_Count>0 AND Error_Count<=10, "1. Normal", Error_Count>10 AND Error_Count<=20, "2. Warning",Error_Count>20 AND Error_Count<=50, "3. Critical",true(),"4. Severe")
| xyseries component Threshold_Color Error_Count

Refer to Splunk Documentation for creation of Stacked Bar Chart:
One of the the run anywhere search example is : https://docs.splunk.com/Documentation/Splunk/latest/Viz/ColumnBarCharts#Stacked_column_chart

index=_internal sourcetype=splunkd NOT (component IN ("Metrics","PeriodicHealthReporter"))
| timechart count as Error_Count by component

Or the documentation: https://docs.splunk.com/Documentation/SplunkCloud/7.2.6/Viz/LineAreaCharts#Stacked_area_chart

Re: Column chart color change if threshold is hit

newbie09 — Sat, 10 Aug 2019 14:54:07 GMT

@niketnilay

You are very helpful and apologies i should say column chart and not bar chart.

I admit i wasn't clear. But please take a look at the pic i attached.

COlor Mapping
Green <= 10
Orange >10 <=20
Red > 20

Since, Code 1 is 10 color is green
and code 2 20 color suppose to be orange and code 3 is 30 and color is suppose to be red.

https://ibb.co/r6vPMBY

Re: Column chart color change if threshold is hit

niketn — Sat, 10 Aug 2019 16:05:13 GMT

This is the output the first query I had provided gives. But I was confused with your requirement of stacking the bars/columns. Code 1 can only be either one of Green, Orange or Red but not two or three colors.

Following is the run anywhere search. The commands till | table errorCode Error_Count, generates the data as per your chart in the screenshot.

| makeresults 
| eval data="errorCode=Code 1,Error_Count=10;errorCode=Code 2,Error_Count=20;errorCode=Code 3,Error_Count=30;" 
| makemv data delim=";" 
| mvexpand data 
| rename data as _raw 
| KV 
| table errorCode Error_Count 
| eval Threshold_Color=case(Error_Count>0 AND Error_Count<=20, "1. Normal", Error_Count>20 AND Error_Count<=30, "2. Warning",Error_Count>50 AND Error_Count<=100, "3. Critical",true(),"4. Severe") 
| xyseries errorCode Threshold_Color Error_Count

Re: Column chart color change if threshold is hit

newbie09 — Wed, 30 Sep 2020 01:40:57 GMT

sorry, i tried but i'm still not getting the result i wanted.

If i use the below, i got separate chart per color as per attached pic.

https://ibb.co/WP28T9K

|myresult
| eval Threshold_Color=case(Error_Count>0 AND Error_Count<=20, "1. Normal", Error_Count>20 AND Error_Count<=30, "2. Warning",Error_Count>50 AND Error_Count<=100, "3. Critical",true(),"4. Severe")
| xyseries errorCode Threshold_Color Error_Count

If i use below, still 1 color for every bar in the column chart. pic 2 attached. It is disregarding my color threshold.

https://ibb.co/9nYR9BY

|myresult
| table errorCode Error_Count
| eval Threshold_Color=case(Error_Count>0 AND Error_Count<=20, "1. Normal", Error_Count>20 AND Error_Count<=30, "2. Warning",Error_Count>50 AND Error_Count<=100, "3. Critical",true(),"4. Severe")
| xyseries errorCode Threshold_Color Error_Count

Re: Column chart color change if threshold is hit

niketn — Sun, 11 Aug 2019 02:34:50 GMT

@newbie09 for first chart image added in your comment, seems like you are using Trellis layout. Can you turn Trellis off and see if it matches your expected output?

Re: Column chart color change if threshold is hit

newbie09 — Sun, 11 Aug 2019 06:25:00 GMT

@niketnilay

it's not the trellis fault but the multimode series.

If i only see this from the start.

I really appreciate your time helping me to point out what i'm doing wrong.

Re: Column chart color change if threshold is hit

niketn — Sun, 11 Aug 2019 16:33:06 GMT

I hope your issue is resolved. Do up-vote the comments that helped 🙂