Splunk Search

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

fncds3
Explorer

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?

0 Karma
1 Solution

lguinn2
Legend

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

View solution in original post

lguinn2
Legend

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
Motivator
| rename column as Type 
| rename "row 1" as "count"

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

* If this helps, please upvote or accept solution if it solved *
0 Karma

cmak
Contributor

Amazing 🙂

0 Karma

fncds3
Explorer

This did the trick. Thank you for you help!

0 Karma

tfletcher_splun
Splunk Employee
Splunk Employee
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

0 Karma

fncds3
Explorer

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

0 Karma

tfletcher_splun
Splunk Employee
Splunk Employee

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

0 Karma

fncds3
Explorer

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

0 Karma

fncds3
Explorer

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

0 Karma

tfletcher_splun
Splunk Employee
Splunk Employee

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

0 Karma

fncds3
Explorer

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

0 Karma

tfletcher_splun
Splunk Employee
Splunk Employee

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

0 Karma

fncds3
Explorer

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?

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...