Splunk Search

Column chart color change if threshold is hit

Explorer

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.

Tags (2)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

@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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

SplunkTrust
SplunkTrust

@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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Explorer

exactly what i needed. Thanks mate!

0 Karma

Explorer

@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?

0 Karma

SplunkTrust
SplunkTrust

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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?

0 Karma

SplunkTrust
SplunkTrust

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

@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

alt text

0 Karma

SplunkTrust
SplunkTrust

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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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 ThresholdColor=case(ErrorCount>0 AND ErrorCount<=20, "1. Normal", ErrorCount>20 AND ErrorCount<=30, "2. Warning",ErrorCount>50 AND ErrorCount<=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 ErrorCount
| eval Threshold
Color=case(ErrorCount>0 AND ErrorCount<=20, "1. Normal", ErrorCount>20 AND ErrorCount<=30, "2. Warning",ErrorCount>50 AND ErrorCount<=100, "3. Critical",true(),"4. Severe")
| xyseries errorCode ThresholdColor ErrorCount

0 Karma

SplunkTrust
SplunkTrust

@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?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Explorer

@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.

0 Karma

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Builder

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?

0 Karma

Explorer

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
0 Karma