Dashboards & Visualizations

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

shayhibah
Path Finder

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?

0 Karma
1 Solution

niketnilay
Legend

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

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

View solution in original post

niketnilay
Legend

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

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

View solution in original post

shayhibah
Path Finder

Thank you @niketnilay, it works!

0 Karma

azulgrana
Path Finder

This worked great for me. Thanks @niketnilay

0 Karma

shayhibah
Path Finder

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

0 Karma

niketnilay
Legend

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

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

shayhibah
Path Finder

works perfectly! I appreciate it Niket

0 Karma

niketnilay
Legend

Glad it worked... do up vote 🙂

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

mkamal18
New Member

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}

0 Karma

niketnilay
Legend

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

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

shayhibah
Path Finder

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

alt text

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)

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!