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:
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 |
13 Nov 2023 events:
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 |
20 Nov 2023 events:
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 |
Any help is greatly appreciated.
| 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
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?
| 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
You are a genius. Thank you so much @ITWhisperer . Much appreciated! 🙂