Dashboards & Visualizations

How to achieve color bar based on condition?

hagar71
Explorer

Hello Splunkers

I want to customize the color of the bars based on condition like the example where ID, DateChanged,PlannedDate_1 and PlannedDate_2 are columns and the Date format is dd.mm.yyyy HH:mm and there is a dublication in the ID so I wanted to take the latest Dates 

Green --> All the ID with the DateChanged = no

Red --> All the ID with the DateChanged = yes AND PlannedDate_1 and PlannedDate_2 have a gap of less than 14 days

yellow --> All the ID with the DateChanged = yes AND PlannedDate_1 and PlannedDate_2 have a gap between 14-20 days

Green --> All the ID with the DateChanged = yes AND PlannedDate_1 and PlannedDate_2 have a gap of more than 20 days


Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Usually, events are returned from the index search in reverse time order, i.e. newest first; the dedup command will keep the first occurrence of each value in the dedup'd field(s), so this should work

index=test
| dedup ID
... rest of your search

For the drilldown to another search, you could pass the value of the colour clicked and add that a a condition in the search for the panel you are drilling down to.

View solution in original post

hagar71
Explorer

this is my query till now based in the if condition that I want but I want to visualize them based on their colors in the query 
index=test | eval start=strptime(PlannedDate_1,"%d.%m.%Y %H:%M") | eval end=strptime(PlannedDate_2,"%d.%m.%Y %H:%M") | eval difference = start - end
| eval red = if(difference<14 AND DateChanged="yes" ,total_time,0)
| eval yellow = if(difference<20 AND difference>14 AND DateChanged="yes",difference,0)
| eval green = if(difference>20 AND DateChanged="yes", difference, 0)
|eval gray=if(DateChanged="no", difference, 0)
| table red, yellow, green 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

red has total_time, whereas yellow, green and gray have difference. Is this correct?

Given that you appear to be wanting to use a bar(?) chart, what do you want the y-axis to represent? the count of each colour, or the sum of the amounts for each colour?

0 Karma

hagar71
Explorer

the y-axis is the count if the IDs while the RED,GREEN,YELLOW are the criticality of the difference between the two dates and for the gray one it's based on another column where the value of the column is yes or no when it's no it should be gray otherwise it should check the another condition to choose RED,YELLOW or GREEN

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
index=test | eval start=strptime(PlannedDate_1,"%d.%m.%Y %H:%M") | eval end=strptime(PlannedDate_2,"%d.%m.%Y %H:%M") | eval difference = start - end
| eval colour=case(difference<14 AND DateChanged="yes" ,"red",difference<20 AND difference>14 AND DateChanged="yes","yellow",difference>20 AND DateChanged="yes", "green",DateChanged="no","gray")
| where colour == "red" OR colour == "yelow" OR colour == "green"
| chart count by colour
0 Karma

hagar71
Explorer

and how could I visualize it?
<option name="charting.fieldColors">{"red":"#DC4E41","yellow":"#F8BE34","green":"#5CC05C","gray":"#A9A9A9"}</option> isn't working here

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Sorry, I missed a line - try adding this to the end of the search

| transpose 0 header_field=colour
0 Karma

hagar71
Explorer

last 2 questions please
if I want to take the values only for the newest IDs as the IDs are duplicated but with different dates how could I do this?
and if I want to click on the chart to show the IDs for the red, yellow and green how could I make this?
thank you so much for your support 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Usually, events are returned from the index search in reverse time order, i.e. newest first; the dedup command will keep the first occurrence of each value in the dedup'd field(s), so this should work

index=test
| dedup ID
... rest of your search

For the drilldown to another search, you could pass the value of the colour clicked and add that a a condition in the search for the panel you are drilling down to.

ITWhisperer
SplunkTrust
SplunkTrust

Colouring in charts is at the series level, not individual bars.

Have you separated your conditions into separate series?

0 Karma

hagar71
Explorer

No I didn't, how could I do that?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It depends on your events and what you are trying to achieve. To be honest, it may not be possible to achieve what you are hoping for.

What events do you have to work with, and what is your current search?

0 Karma

hagar71
Explorer

I am working with csv file and I stopped in index=test becuse I don't know how could I create if condition in Splunk 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

If you want more help, you will have to be more specific as to what exactly you have tried. I lost my mind-reading license after a misunderstanding with an African Prince!

0 Karma
Get Updates on the Splunk Community!

Discover Powerful New Features in Splunk Cloud Platform: Enhanced Analytics, ...

Hey Splunky people! We are excited to share the latest updates in Splunk Cloud Platform 9.3.2408. In this ...

Splunk Classroom Chronicles: Training Tales and Testimonials

Welcome to the "Splunk Classroom Chronicles" series, created to help curious, career-minded learners get ...

Access Tokens Page - New & Improved

Splunk Observability Cloud recently launched an improved design for the access tokens page for better ...