How to achieve color bar based on condition?

[hagar71]

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

[hagar71]

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 

[ITWhisperer]

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?

[hagar71]

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

[ITWhisperer]

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

[hagar71]

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

[ITWhisperer]

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

| transpose 0 header_field=colour

[hagar71]

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 

[ITWhisperer · Accepted solution]

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]

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

Have you separated your conditions into separate series?

[hagar71]

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

[ITWhisperer]

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?

[hagar71]

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 

[ITWhisperer]

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!