How to create a bar chart with colors based on an external field?

[shayhibah]

Im trying to create bar chart base don table and to color the columns by field that is not part of the table.
For example:

**my_search... | eval risk_order=case(app_risk=="High",0, app_risk=="Critical",1) | stats count as "Logs" by appi_name ,risk_order | sort 10 -risk_order -"Logs" | table appi_name , "Logs"**

If I visualize it, I see that every bar as the same color (which is based on field "Logs").
I would like to change the color of the bar based on app_risk field.
each value of app_risk should use different color.

How can I do it?

[niketn · Accepted solution]

[UPDATED ANSWER]
Adding logic for sorting the results

1) streamstats command is used to add serial number column after results are sorted as per need.
2) printf() evaluation function is used to pad with zeros (2 zeros in the example below), to allow string sort of results up-to 2 digits of precision.
3) Final pipe with replace() evaluation function is used to remove padded serial number from result.

index=_internal sourcetype=splunkd log_level IN ("ERROR","WARN")
 | stats count by component log_level
 | eval log_level=case(log_level=="ERROR",0,log_level=="WARN",1)
 | sort 10 - log_level count
 | streamstats count as sno
 | eval component=printf("%02d",sno).component
 | fields - sno
 | xyseries component log_level count
 | fillnull value=0 "0","1"
 | rename "0" as "ERROR" "1" as "WARN"
 | eval component=replace(component,"^\d+","")

---

@shayhibah, your screenshot did not get uploaded (may be you missed hitting the enter key before and after attached image).

However, based on your sample code provided you can try the following run anywhere example based on Splunk's _internal index which has component field instead of appi_name and log_level instead of app_risk.

index=_internal sourcetype=splunkd log_level IN ("ERROR","WARN")
| stats count by component log_level
| eval log_level=case(log_level=="ERROR",0,log_level=="WARN",1)
| sort 10 - log_level count
| xyseries component log_level count
| fillnull value=0 "0","1"
| rename "0" as "ERROR" "1" as "WARN"

I have used xyseries command to invert the table to plot results as per requirement. The fillnull command has been used to place 0 value instead of null. The rename command in he final pipe gives the columns a meaningful name i.e. ERROR and WARN instead of 0 and 1 respectively in the given example.

[shayhibah]

Thank you @niketnilay, it works!

[azulgrana]

This worked great for me. Thanks @niketnilay

[shayhibah]

@niketnilay
Right now the data is sorted by alphabetical - the sort isn't working.
Do you have any idea for this one?

[niketn]

@shayhibah I have updated my answer with the sorting logic. Try out and confirm!

[shayhibah]

works perfectly! I appreciate it Niket

[niketn]

Glad it worked... do up vote 🙂

[mkamal18]

Hello,

You should do 2 things:

In the search command, you should add the following: .... |chart count as total by app_risk,risk_order | eval redCount = if(app_risk=="high",total,0) | eval greenCount = if(app_risk=="Critical", total, 0) | fields Age redCount greenCount

And then in the xml you should add this option for example in the considered panel : {"redCount":0xFF0000,"greenCount":0x73A550}

[niketn]

What is your current output? Can you add mock screenshot of current and expected output?

[shayhibah]

right now it looks like this:
every appi_name has the same color (which is based on "logs" field)

expected output will be that the 2 upper bars will be in red and the other in orange (since only the first 2 have app_risk =1)