topic Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query in Splunk Search

Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

fncds3 — Mon, 28 Sep 2020 12:18:45 GMT

I have a video player that logs the following:

Video Starts - When a user clicks play and the first frame of the video is delivered
Video Start Errors - When a user clicks play and the first frame of the video is not delivered (due to an error)
Playback Errors - When a user clicks play, and the first frame of the video is delivered, and an error is thrown any time after

Please note the following two events are NOT logged: user closes the video, or the video naturally completes playback.

I've successfully created a query that shows the total user "attempts" to play a video (presented on a Splunk dashboard), and graphs the results in a pie chart split into two chunks:

A. Successful Video Playback Sessions
B. Errored Out Video Sessions

I have the above accomplished thus far, but I also need a the table of the results (same query, different panel and panel visualization type on the dashboard) to display the % each of the above A and B) represent vs. the total attempts.

I'm calculating A and B via the following query:

This query gives me the pie chart I need in the first dashboard chart panel, but not the % in table that I need in the second dashboard table panel.

I've tried the TOP command (instead of table) and adding in the logic to show the % A and B represent of the total attempts, but the top command returns the results in a single row, and doesn't graph nicely in the pie chart.

Any suggestions?

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

tfletcher_splun — Wed, 22 Aug 2012 17:56:44 GMT

sourcetype=videoplaybacklog
| stats count(eval(event="VIDEO_START")) as VS, count(eval(event="PLAYBACK_ERROR")) as PE, count(eval(event="VIDEO_START_ERROR")) as VSE 
| eval A=(VS-PE)/(VS+VSE) * 100
| eval B=(VSE+PE)/(VS+VSE) * 100 
| table A B 
| transpose 
| rename column as Type 
| rename "row 1" as "Percent"

That gets you the percent, you could run your search and this one to get the two visualizations, Or just this one to fuel both, but then you'd lose the count in the tooltip. The best thing is probably to go to advanced xml and use a Post Process

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

fncds3 — Wed, 22 Aug 2012 20:12:38 GMT

Thank you for the help! Unfortunately I need both count and percentage in the table view, and the above changes you suggest replace count. Any ideas?

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

tfletcher_splun — Mon, 28 Sep 2020 12:18:53 GMT

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

fncds3 — Thu, 23 Aug 2012 18:48:32 GMT

The table format appears correctly when I run the search, and the "Type" column has the correct variable names. However there is no data in the results under the "Count" or "Percent" columns (not even "0" appears, just blank spots). I thought maybe my Splunk instance was on the fritz, so I sent the query to my pie chart and still no dice (no chart appeared).

Any ideas?

(Thank you for your continued help by the way!)

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

tfletcher_splun — Mon, 28 Sep 2020 12:19:34 GMT

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

fncds3 — Fri, 24 Aug 2012 17:45:22 GMT

The query runs, but returns "No results found.".

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

fncds3 — Fri, 24 Aug 2012 17:49:08 GMT

I tried disassembling the query to see if each individual part worked, and I'm not getting any results from the following:

sourcetype=videoplaybacklog
| stats count by event
| rename event as type
| table type

I wanted to see what "stats count by event" would show, and I still get "No results found.".

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

tfletcher_splun — Mon, 28 Sep 2020 12:19:52 GMT

I was making assumptions about the structure of your data, event was meant to be the field that contained the values: PLAYBACK_ERROR VIDEO_START_ERROR etc. If you want more help post at least 1 of each type of event returned by:
sourcetype=videoplaybacklog

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

lguinn2 — Fri, 24 Aug 2012 18:31:20 GMT

I think that adding the last 3 lines below should add the percentage to your orignal data

sourcetype=videoplaybacklog
| stats count(eval(event="VIDEO_START")) as VS, count(eval(event="PLAYBACK_ERROR")) as PE, count(eval(event="VIDEO_START_ERROR")) as VSE 
| eval A=VS-PE 
| eval B=VSE+PE 
| table A B 
| transpose 
| rename column as Type 
| rename "row 1" as "count"
| eventstats sum(count) as total
| eval percent = round(count*100/total, 0)
| fields - total

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

fncds3 — Tue, 28 Aug 2012 15:15:55 GMT

This did the trick. Thank you for you help!

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

fncds3 — Tue, 28 Aug 2012 15:18:40 GMT

tfletcher, thank you for your continued help throughout this process, I appreciate it!

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

cmak — Thu, 07 Feb 2013 22:12:32 GMT

Amazing 🙂

Re: Forcing Results Into a Pie Chart and Calculating Percentages In a Table - From the Same Query

splunkreal — Fri, 22 Oct 2021 12:33:23 GMT

| rename column as Type 
| rename "row 1" as "count"

was the trick, I think space between row and 1 makes bad effect.