Dashboards & Visualizations

Need help in creating a stacked bar chart graph showing completion percentage

maverick27
Explorer

Hello Splunk experts, 

I'm pretty new to splunk and I would like your help in forming a query for the following requirement.

I would like to create a bar chart for each OEM (total of 5 seperate bar charts widgets since we have 5 OEMs) based on the completion progress of NCAPTest. So, these events will be pushed to Splunk every Monday. The x-axis should show the timestamp(_time) in the following format(YYYY-MM-DD) and the y axis should show stacked bar graph where bottom portion of the bar should show completed count(NCAPTest=Yes) along with the completion percentage and the top portion should show the remaining count(NCAPTest=No).

This is how the data looks like:

6 Nov, 2023 events:

OEMModelTypeNCAPTest
HondaCivicSedanNo
HondaCR-VSUVYes
HondaFitHatchbackNo
VWJettaSedanYes
VWTiguanSUVYes
VWGolfHatchbackNo
TataHarrierSUVYes
TataTiagoHatchbackNo
TataAltrozHatchbackNo
KiaSeltosSUVNo
KiaForteSedanNo
KiaRioHatchbackNo
HyundaiElantraSedanNo
HyundaiKonaSUVYes
Hyundaii20HatchbackNo

 

13 Nov 2023 events:

HondaCivicSedanYes
HondaCR-VSUVYes
HondaFitHatchbackNo
VWJettaSedanYes
VWTiguanSUVYes
VWGolfHatchbackNo
TataHarrierSUVYes
TataTiagoHatchbackNo
TataAltrozHatchbackYes
KiaSeltosSUVNo
KiaForteSedanYes
KiaRioHatchbackYes
HyundaiElantraSedanNo
HyundaiKonaSUVYes
Hyundaii20HatchbackNo

 

20 Nov 2023 events:

HondaCivicSedanYes
HondaCR-VSUVYes
HondaFitHatchbackYes
VWJettaSedanYes
VWTiguanSUVYes
VWGolfHatchbackYes
TataHarrierSUVYes
TataTiagoHatchbackYes
TataAltrozHatchbackYes
KiaSeltosSUVYes
KiaForteSedanYes
KiaRioHatchbackYes
HyundaiElantraSedanYes
HyundaiKonaSUVYes
Hyundaii20HatchbackYes

 

Any help is greatly appreciated. 

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults
| eval _time=strptime("6 Nov 2023","%d %b %Y")
| eval _raw="OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	No
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	No
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	No
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	No
Tata	Altroz	Hatchback	No
Kia	Seltos	SUV	No
Kia	Forte	Sedan	No
Kia	Rio	Hatchback	No
Hyundai	Elantra	Sedan	No
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	No"
| append
    [| makeresults
    | eval _time=strptime("13 Nov 2023","%d %b %Y")
| eval _raw="OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	Yes
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	No
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	No
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	No
Tata	Altroz	Hatchback	Yes
Kia	Seltos	SUV	No
Kia	Forte	Sedan	Yes
Kia	Rio	Hatchback	Yes
Hyundai	Elantra	Sedan	No
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	No"]
| append
    [| makeresults
    | eval _time=strptime("20 Nov 2023","%d %b %Y")
| eval _raw="OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	Yes
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	Yes
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	Yes
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	Yes
Tata	Altroz	Hatchback	Yes
Kia	Seltos	SUV	Yes
Kia	Forte	Sedan	Yes
Kia	Rio	Hatchback	Yes
Hyundai	Elantra	Sedan	Yes
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	Yes"]
| multikv forceheader=1
| table _time OEM	Model	Type	NCAPTest
``` The lines above create sample events in line with your example ```
``` Count total by time and OEM ```
| eventstats count as total by _time OEM
``` Count by time OEM total and test result ```
| stats count by _time OEM total NCAPTest
``` Determine percentages ```
| eval count=round(100*count/total,2)
``` Separate yes and no percentages ```
| eval {NCAPTest}=count
``` Gather no and yes percentages by time and OEM ```
| stats values(No) as No values(Yes) as Yes by _time OEM
``` Fill nulls with zero percent ```
| fillnull value=0.00 Yes No

For visualisation, use column chart with trellis

ITWhisperer_0-1701079624323.png

 

View solution in original post

maverick27
Explorer

This is a follow up question though.. I was wondering if we can display the NCAPTest='Yes' or 'No' count along with percentage? And any way to add a percentage symbol(%) on the y axis which has intervals of 50?

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults
| eval _time=strptime("6 Nov 2023","%d %b %Y")
| eval _raw="OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	No
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	No
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	No
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	No
Tata	Altroz	Hatchback	No
Kia	Seltos	SUV	No
Kia	Forte	Sedan	No
Kia	Rio	Hatchback	No
Hyundai	Elantra	Sedan	No
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	No"
| append
    [| makeresults
    | eval _time=strptime("13 Nov 2023","%d %b %Y")
| eval _raw="OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	Yes
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	No
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	No
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	No
Tata	Altroz	Hatchback	Yes
Kia	Seltos	SUV	No
Kia	Forte	Sedan	Yes
Kia	Rio	Hatchback	Yes
Hyundai	Elantra	Sedan	No
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	No"]
| append
    [| makeresults
    | eval _time=strptime("20 Nov 2023","%d %b %Y")
| eval _raw="OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	Yes
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	Yes
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	Yes
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	Yes
Tata	Altroz	Hatchback	Yes
Kia	Seltos	SUV	Yes
Kia	Forte	Sedan	Yes
Kia	Rio	Hatchback	Yes
Hyundai	Elantra	Sedan	Yes
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	Yes"]
| multikv forceheader=1
| table _time OEM	Model	Type	NCAPTest
``` The lines above create sample events in line with your example ```
``` Count total by time and OEM ```
| eventstats count as total by _time OEM
``` Count by time OEM total and test result ```
| stats count by _time OEM total NCAPTest
``` Determine percentages ```
| eval count=round(100*count/total,2)
``` Separate yes and no percentages ```
| eval {NCAPTest}=count
``` Gather no and yes percentages by time and OEM ```
| stats values(No) as No values(Yes) as Yes by _time OEM
``` Fill nulls with zero percent ```
| fillnull value=0.00 Yes No

For visualisation, use column chart with trellis

ITWhisperer_0-1701079624323.png

 

maverick27
Explorer

You are a genius. Thank you so much @ITWhisperer . Much appreciated! 🙂 

0 Karma
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...