topic How to create a bar chart with colors based on an external field? in Dashboards & Visualizations

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

shayhibah — Tue, 28 Aug 2018 08:27:57 GMT

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?

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

niketn — Tue, 28 Aug 2018 08:47:32 GMT

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

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

shayhibah — Tue, 28 Aug 2018 08:55:24 GMT

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)

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

mkamal18 — Tue, 29 Sep 2020 21:02:30 GMT

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}

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

niketn — Tue, 29 Sep 2020 21:04:21 GMT

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

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

shayhibah — Tue, 28 Aug 2018 09:38:04 GMT

Thank you @niketnilay, it works!

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

shayhibah — Tue, 28 Aug 2018 09:53:50 GMT

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

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

niketn — Tue, 28 Aug 2018 13:51:14 GMT

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

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

shayhibah — Tue, 28 Aug 2018 14:21:41 GMT

works perfectly! I appreciate it Niket

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

niketn — Tue, 28 Aug 2018 15:03:08 GMT

Glad it worked... do up vote 🙂

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

azulgrana — Mon, 30 Dec 2019 00:55:21 GMT

This worked great for me. Thanks @niketnilay