I want to assign specific colors to pie-parts even when the number of pie-parts is variable
Blocked parts are not always in the pie, but must always be red.
Completed part is always in the pie and must always be green.
The label of the pie are dinamic, for example:
138 ToStart, 32%
123 Progress, 21,5%
...
How can i make this work?
@aniello_cerrato since your labels are not static, you can not use fieldColors
to color the legends in Pie Chart. So the only option you are left with is seriesColors
. However, if you append count to the beginning each series then they would get sorted by count and hence you will not be able to apply seriesColors. In order to apply seriesColors you need to ensure that SPL:
1) Returns fixed number of series i.e. in case a series is missing you need to append 0 value.
2) The series are always returned in the same sequence. i.e. count and percent should be after the series name not before.
Refer to one of my older answers: https://answers.splunk.com/answers/545111/pie-chart-display-color-based-on-status-string-val.html
For your example you can try the following run anywhere example ( beginning pipes till append are used to generate data similar to your query except that status field is retained and statusFinal field has count and percent after the status.)
:
| makeresults
| fields - _time
| eval data="status=\"Completed\",count=50;status=\"Failed\",count=2;status=\"Progress\",count=10;status=\"Blocked\",count=5;status=\"ToStart\",count=5;status=\"OnHold\",count=2"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| stats sum(count) as count by status
| eventstats sum(count) as Total
| eval perc=round((count/Total)*100,2)
| fields - Total
| eval statusFinal=status." (count:".count." perc:".perc."%)"
| append
[| makeresults
| fields - _time
| eval data="status=\"Blocked\",count=0;status=\"Completed\",count=0;status=\"Failed\",count=0;status=\"OnHold\",count=0;status=\"Progress\",count=0;status=\"ToStart\",count=0"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| fields - _raw
| stats sum(count) as count by status
| eventstats sum(count) as Total
| eval perc=round((count/Total)*100,2)
| fillnull perc
| fields - Total
| eval statusFinal=status." (count:".count." perc:".perc."%)" ]
| dedup status
| sort status
| table statusFinal count perc
The command after |append
ensure all series are always present and in the same sequence so that seriesColors can be applied. While testing you can randomly take out specific series like status=\"Progress\",count=10;
to test that it creates a Process
row with 0 count
and 0%
. Please try with seriesColors
and confirm!
@aniello_cerrato since your labels are not static, you can not use fieldColors
to color the legends in Pie Chart. So the only option you are left with is seriesColors
. However, if you append count to the beginning each series then they would get sorted by count and hence you will not be able to apply seriesColors. In order to apply seriesColors you need to ensure that SPL:
1) Returns fixed number of series i.e. in case a series is missing you need to append 0 value.
2) The series are always returned in the same sequence. i.e. count and percent should be after the series name not before.
Refer to one of my older answers: https://answers.splunk.com/answers/545111/pie-chart-display-color-based-on-status-string-val.html
For your example you can try the following run anywhere example ( beginning pipes till append are used to generate data similar to your query except that status field is retained and statusFinal field has count and percent after the status.)
:
| makeresults
| fields - _time
| eval data="status=\"Completed\",count=50;status=\"Failed\",count=2;status=\"Progress\",count=10;status=\"Blocked\",count=5;status=\"ToStart\",count=5;status=\"OnHold\",count=2"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| stats sum(count) as count by status
| eventstats sum(count) as Total
| eval perc=round((count/Total)*100,2)
| fields - Total
| eval statusFinal=status." (count:".count." perc:".perc."%)"
| append
[| makeresults
| fields - _time
| eval data="status=\"Blocked\",count=0;status=\"Completed\",count=0;status=\"Failed\",count=0;status=\"OnHold\",count=0;status=\"Progress\",count=0;status=\"ToStart\",count=0"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| fields - _raw
| stats sum(count) as count by status
| eventstats sum(count) as Total
| eval perc=round((count/Total)*100,2)
| fillnull perc
| fields - Total
| eval statusFinal=status." (count:".count." perc:".perc."%)" ]
| dedup status
| sort status
| table statusFinal count perc
The command after |append
ensure all series are always present and in the same sequence so that seriesColors can be applied. While testing you can randomly take out specific series like status=\"Progress\",count=10;
to test that it creates a Process
row with 0 count
and 0%
. Please try with seriesColors
and confirm!
I have solved in this way, thanks a lot
index = "almp_step" | eval timestamp = strftime(_time, "%b-%d-%Y %H:%M") | eval ReqID = "ID"+'RequirementID' | eval TestCase = "TC "+'TestName' | eval ExecDT=if(isnull(Executiondate), "-", Executiondate) | eval Executiondate=strptime(if(isnull(Executiondate),strftime(now(), "%d/%m/%y"),Executiondate), "%d/%m/%y") | search ("Release" = "Week - WAVE 5D") AND ("IntegrationTestList" = "") AND ("RequirementID" = "") AND (timestamp = "Mar-19-2019 15:00") |
stats count as Total
count(eval('ActualStatus'="N/A")) as NA
count(eval('ActualStatus'="Passed")) as Passed
count(eval('ActualStatus'="Waiting Previous Step")) as Waiting
count(eval('ActualStatus'="To Do")) as ToDo
count(eval('ActualStatus'="Blocked")) as Blocked
count(eval('ActualStatus'="Failed")) as Failed
count(eval('ActualStatus'="Not Completed")) as NotCompleted
by "Release" "ReqID" "IntegrationTestList" "TestCase" | eval "TC Status" =
case
(
(Passed + NA) == Total, "Completed",
Failed>0, "Failed",
Blocked>0, "Blocked",
Passed>0, "Progress",
(ToDo + Waiting == Total), "ToStart",
(Waiting + NA + NotCompleted) == Total, "OnHold"
) | stats count as "#TC" by "TC Status"
| append [ | makeresults
| fields - _time
| eval data="status=\"Blocked\",count=0;status=\"Completed\",count=0;status=\"Failed\",count=0;status=\"OnHold\",count=0;status=\"Progress\",count=0;status=\"ToStart\",count=0"
| makemv data delim=";" | mvexpand data | rename data as _raw | kv | rename status as "TC Status"
| stats sum(count) as "#TC" by "TC Status"] | stats sum("#TC") as "#TC" by "TC Status" | sort "TC Status" | eval TCStatus = '#TC' + " " + 'TC Status' | fields TCStatus, "#TC"
You can assign fixed colors to known fields by using the charting.fieldColors
property in your dashboard. In your case, if you want Blocked to be red, and Completed to be green you have to add the following XML code:
<option name="charting.fieldColors">
{"Blocked": 0xFF0000, "Completed":0x009900}
</option>
All other values will then have standard colors. These colors can be modified as well with the charting.seriesColors
option - even if they are dynamic.
See https://docs.splunk.com/Documentation/Splunk/7.2.4/Viz/ChartConfigurationReference for further reference.
Hi,
my label is dynamic, so contain Blocked but also other information (for example the % of data)
Please let me know
Thanks,
A
i wonder if you can set a token to the blocked/completed fields after search and use that token in the option...hmmm
Okay, so I got you wrong there. Sorry, in that case my idea won't work. I will convert it back to a comment, as it is not usable in your case.
Label is created dinamically using %, we can have 5 different slices with dynamic label. There is a way to set a static color for 5 different slices associating to each status also used in dynamic label (for example ToStart).
Below the query used
index = "almp_step" | eval timestamp = strftime(_time, "%b-%d-%Y %H:%M") | eval ReqID = "ID"+'RequirementID' | eval TestCase = "TC "+'TestName' | eval ExecDT=if(isnull(Executiondate), "-", Executiondate) | eval Executiondate=strptime(if(isnull(Executiondate),strftime(now(), "%d/%m/%y"),Executiondate), "%d/%m/%y") | search ("Release" = "") AND ("IntegrationTestList" = "") AND ("RequirementID" = "*") AND (timestamp = "Mar-18-2019 14:00") |
stats count as Total
count(eval('ActualStatus'="N/A")) as NA
count(eval('ActualStatus'="Passed")) as Passed
count(eval('ActualStatus'="Waiting Previous Step")) as Waiting
count(eval('ActualStatus'="To Do")) as ToDo
count(eval('ActualStatus'="Blocked")) as Blocked
count(eval('ActualStatus'="Failed")) as Failed
count(eval('ActualStatus'="Not Completed")) as NotCompleted
by "Release" "ReqID" "IntegrationTestList" "TestCase" | eval "TC Status" =
case
(
(Passed + NA) == Total, "Completed",
Failed>0, "Failed",
Blocked>0, "Blocked",
Passed>0, "Progress",
(ToDo + Waiting == Total), "ToStart",
(Waiting + NA + NotCompleted) == Total, "OnHold"
) | stats count by "TC Status" | sort "TC Status" | eval TCStatus = count + " " + 'TC Status' | fields TCStatus, count
@aniello_cerrato can you please add a mock up of what you currently have as Labels for Pie Chart. From the above example is the label 138 ToStart
or 138 ToStart, 32%
.
How many maximum such slices of Pie Chart can be present?
Can you ensure through SPL that all the labels are always present, i.e. with 0 value in case it is absent?
Can you provide you current SPL as well?
Adding more details to your question will help community assist you better.
Label is created dinamically using %, we can have 5 different slices with dynamic label. There is a way to set a static color for 5 different slices associating to each status also used in dynamic label (for example ToStart).
Below the query used
index = "almp_step" | eval timestamp = strftime(_time, "%b-%d-%Y %H:%M") | eval ReqID = "ID"+'RequirementID' | eval TestCase = "TC "+'TestName' | eval ExecDT=if(isnull(Executiondate), "-", Executiondate) | eval Executiondate=strptime(if(isnull(Executiondate),strftime(now(), "%d/%m/%y"),Executiondate), "%d/%m/%y") | search ("Release" = "") AND ("IntegrationTestList" = "") AND ("RequirementID" = "*") AND (timestamp = "Mar-18-2019 14:00") |
stats count as Total
count(eval('ActualStatus'="N/A")) as NA
count(eval('ActualStatus'="Passed")) as Passed
count(eval('ActualStatus'="Waiting Previous Step")) as Waiting
count(eval('ActualStatus'="To Do")) as ToDo
count(eval('ActualStatus'="Blocked")) as Blocked
count(eval('ActualStatus'="Failed")) as Failed
count(eval('ActualStatus'="Not Completed")) as NotCompleted
by "Release" "ReqID" "IntegrationTestList" "TestCase" | eval "TC Status" =
case
(
(Passed + NA) == Total, "Completed",
Failed>0, "Failed",
Blocked>0, "Blocked",
Passed>0, "Progress",
(ToDo + Waiting == Total), "ToStart",
(Waiting + NA + NotCompleted) == Total, "OnHold"
) | stats count by "TC Status" | sort "TC Status" | eval TCStatus = count + " " + 'TC Status' | fields TCStatus, count
I can't attach the print because i don't have the grant