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

[fncds3]

I have a video player that logs the following:

1. Video Starts - When a user clicks play and the first frame of the video is delivered
2. Video Start Errors - When a user clicks play and the first frame of the video is not delivered (due to an error)
3. 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:

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"

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?

[lguinn2 · Accepted solution]

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

[splunkreal]

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

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

[cmak]

Amazing 🙂

[fncds3]

This did the trick. Thank you for you help!

[tfletcher_splun]

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

[fncds3]

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

[tfletcher_splun]

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

[fncds3]

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

[fncds3]

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

[tfletcher_splun]

alright so I was trying to keep your main search, but I think this is better:
sourcetype=videoplaybacklog | stats count by event | rename event as type | eval check=if(event=="PLAYBACK_ERROR","pe","vs") | eval pec=if(type=="PLAYBACK_ERROR",count,0) | eventstats sum(count) as tot by check | eventstats sum(pec) as pec | search type!="PLAYBACK_ERROR" | eval count=if(type=="VIDEO_START_ERROR",count+pec,count-pec) | eval type=if(type=="VIDEO_START_ERROR","Failed","Success") | eval pct=count/tot*100 | table type count pct

[fncds3]

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!)

[tfletcher_splun]

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 c
| eval Percent=Count/c*100
| table Type Count Percent

[fncds3]

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?