Playback Reporting

[Q-Droid 653]

What I see is a lot of Roku clients in those lists...

 

[TeamB 2353]

39 minutes ago, Q-Droid said:

What I see is a lot of Roku clients in those lists...

 

yeah but I have seen it on Windows Kodi Clients, Web Clients and Raspberry Pi Libreelec Clients so it is not just one type of client.

like I said, I have tried to reproduce this in so many different ways, but can not.

[vaise 307]

45 minutes ago, TeamB said:

it is very weird, the plugin just monitors the sessions and saved play state like start and stop times when a Playback Session is running, for the times to be so long the session needs to exist for that time, i.e. the playback session is stuck, I have heard of this happening but never been able to reproduce the issue in Emby, I have tried everything from terminating emby clients (killing them in Task Manager etc) to fast pause/play/leaving them in a paused state for hours and hours but the Playback session in the Emby dashboard always shows the correct state for me and thus the Playback reporting the correct times.

I have a few of these weird entries in my system also, I have absolutely no idea how they got there, one movie play session run for 6 hours in my system, that is not true and I dont know why the Emby Session did this.

Yeah you can update the times to say 1 hour to keep item play event in the system.

I have the select statement here - how to I change that to an update the records to 10800 (3 hours) - I cant get the sql syntax right :

SELECT PlayDuration
FROM PlaybackActivity
WHERE PlayDuration > 10800
ORDER BY PlayDuration DESC

I will start with setting to 3 hours, then may drop it to 2 hours, cos there are load of 30m episodes in there and most movies are under 2 hours.  May even later change to 1.5 hours.

[TeamB 2353]

3 minutes ago, vaise said:

I have the select statement here - how to I change that to an update the records to 10800 (3 hours) - I cant get the sql syntax right :

SELECT PlayDuration
FROM PlaybackActivity
WHERE PlayDuration > 10800
ORDER BY PlayDuration DESC

I will start with setting to 3 hours, then may drop it to 2 hours, cos there are load of 30m episodes in there and most movies are under 2 hours.  May even later change to 1.5 hours.

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

you need to use an SQL update command

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

UPDATE PlayDuration SET PlayDuration = <the new value>, PauseDuration = 0 WHERE PlayDuration - PauseDuration > <the limit you want to set>

i.e. cap all to 3 hours

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

UPDATE PlayDuration SET PlayDuration = (60 * 60 * 3), PauseDuration = 0 WHERE PlayDuration - PauseDuration > (60 * 60 * 3)

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

Just in case you missed it

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

 

[vaise 307]

33 minutes ago, TeamB said:

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

you need to use an SQL update command

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

UPDATE PlayDuration SET PlayDuration = <the new value>, PauseDuration = 0 WHERE PlayDuration - PauseDuration > <the limit you want to set>

i.e. cap all to 3 hours

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

UPDATE PlayDuration SET PlayDuration = (60 * 60 * 3), PauseDuration = 0 WHERE PlayDuration - PauseDuration > (60 * 60 * 3)

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

Just in case you missed it

MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!MAKE SURE YOU DO A BACKUP OF YOUR PLAYBACK REPORTING DATA FIRST!

 

I think that is missing the actual database there ?  playbackactivity ?

 

UPDATE PlaybackActivity 
SET PlayDuration = (60 * 60 * 3), PauseDuration = 0 
WHERE PlayDuration - PauseDuration > (60 * 60 * 3)

 

 

Edited March 24 by vaise

[vaise 307]

Summary of activities - I ran the above for 2 hours -

UPDATE PlaybackActivity 
SET PlayDuration = (60 * 60 * 2), PauseDuration = 0 
WHERE PlayDuration - PauseDuration > (60 * 60 * 2)

And then re-ran the outliers sql 

SELECT *, PlayDuration - PauseDuration AS PlayTime
FROM PlaybackActivity
ORDER BY PlayTime DESC
LIMIT 10

And they are all now set to 7200 as expected :

So - I then ran this script I always use to calculate the total minutes (you gave me this a while back) :

SELECT UserId, SUM(PlayDuration - PauseDuration) AS PlayTime
FROM PlaybackActivity
WHERE DateCreated >= '2023-03-24' AND DateCreated < '2024-03-24'
GROUP BY UserId
ORDER BY PlayTime DESC

But - strangely, the seconds has not changed - its still high and the same as when I started.  What have I got wrong here ? :

 

 

[TeamB 2353]

not sure, try and find one of the updated items in the Plugin Report pages and see if it is correctly set to 2 hours

[vaise 307]

10 minutes ago, TeamB said:

not sure, try and find one of the updated items in the Plugin Report pages and see if it is correctly set to 2 hours

While I could not find any specific to 2 hours exactly, I was Scrolling through the summary tab, and I can see these which should not by rights be there after that sql update - as 2 hours should be the maximum ? :

 

 

 

[TeamB 2353]

4 minutes ago, vaise said:

While I could not find any specific to 2 hours exactly, I was Scrolling through the summary tab, and I can see these which should not by rights be there after that sql update - as 2 hours should be the maximum ? :

 

 

 

if you run the update query again does tell you how many items were updated?

[vaise 307]

zero :

[vaise 307]

Its as if the app and the selects are reading a cache of what it looked like before the manual updates.......

[TeamB 2353]

33 minutes ago, vaise said:

Its as if the app and the selects are reading a cache of what it looked like before the manual updates.......

it should auto commit.

try this command in the SQL Query tab

COMMIT

Or try restarting the server.

Edited March 24 by TeamB

[vaise 307]

1 minute ago, TeamB said:

it should auto commit.

Try restarting the server.

Thats next on my list.  
just a number of friends/family watching stuff currently.  Will get to it and report back.

[vaise 307]

restarted emby - still seeing the records in the summary.

Is this is any way related to database changes in the past ?

When I first ported from beta to prod a month ago, I posted that the playback reporting restore was only bringing in a small subset of the data - I have to copy the database over.

Happytoplay posted a soution to that in that thread....

[TeamB 2353]

the weird thing is if you run this

UPDATE PlaybackActivity 
SET PlayDuration = (60 * 60 * 2), PauseDuration = 0 
WHERE PlayDuration - PauseDuration > (60 * 60 * 2)

and then run this

SELECT *, PlayDuration - PauseDuration AS PlayTime
FROM PlaybackActivity
WHERE PlayTime > (60 * 60 * 2)
ORDER BY PlayTime DESC
LIMIT 10

Do you get ZERO results? (you should)

[vaise 307]

Yep - zero results.

Its crazy!

 

Its like the app is reading a different database :

 

Edited March 24 by vaise

[vaise 307]

While doing various sql's, I am also seeing loads of 0 seconds.

[vaise 307]

Im getting somewhere - what different about this statement ?

SELECT ROWID, * 
FROM PlaybackActivity 
ORDER BY PlayDuration DESC
LIMIT 50

Because it is finding other records with playduration high - probably the missing links!!!!!!

 

Edited March 24 by vaise

[vaise 307]

Found the issue - checkout these PauseDurations - 

 

Edited March 24 by vaise

[vaise 307]

In summary on all this, the commands abouve were bad due to some sort of corruption values in the pauseduration - which was the same or similar to the playduration - so they were not being picked up.  Cammands therefore used to rectify - with setting anything longer that 2 hours to 2 hours, and a pause duration higher than 5 minutes to 0 pause duration. :

SELECT ROWID, * 
FROM PlaybackActivity 
WHERE PlayDuration > 7200 AND PauseDuration > 300
ORDER BY PlayDuration DESC
LIMIT 50

UPDATE PlaybackActivity 
SET PlayDuration = (60 * 60 * 2), PauseDuration = 0 
WHERE PlayDuration > 7200 AND PauseDuration > 300

This has now left only 11 records, just over 2 hours with pause duration in the 200 seconds ish.

So..... you would think that was good - but no - the original issue is still there - as I guess most of this fix up stuff was for dates older than 1 year - and my sql SUM of the seconds is still massive for one year :

SELECT UserId, SUM(PlayDuration - PauseDuration) AS PlayTime
FROM PlaybackActivity
WHERE DateCreated >= '2023-03-24' AND DateCreated < '2024-03-24'
GROUP BY UserId
ORDER BY PlayTime DESC

produces :

 

WHICH IS SURELY TOO MANY SECONDS OF PLAYBACK FOR NORMAL USE.

 

 

 

Edited March 24 by vaise

[TeamB 2353]

but those pause and playback durations should cancel each other out.

yeah 86 Days is a lot.

if you find a pattern, let me know.

 

[Q-Droid 653]

11 hours ago, TeamB said:

yeah but I have seen it on Windows Kodi Clients, Web Clients and Raspberry Pi Libreelec Clients so it is not just one type of client.

like I said, I have tried to reproduce this in so many different ways, but can not.

What I mean is Roku are notorious for orphan/stray sessions in Emby. Is it possible that even when users properly exit that Emby still doesn't close everything out? Perhaps the devs used a "special" workaround for Roku that doesn't completely remove the session though it does end the streams.

Sure, this doesn't account for the others.

 

 

Edited March 24 by Q-Droid

[vaise 307]

I just wanted to create a Top 10 user list.  Not sure if I can trust the seconds watched.  Then I figured I could do it by number of plays….. but there are shed loads of ‘plays’ of 0 seconds (but the setting in the app is set to register not if more than 180 seconds minimum is used).  

Maybe I should delete those ?  Or all plays less that 180 seconds…..

[Happy2Play 8296]

3 hours ago, vaise said:

I just wanted to create a Top 10 user list.  Not sure if I can trust the seconds watched.  Then I figured I could do it by number of plays….. but there are shed loads of ‘plays’ of 0 seconds (but the setting in the app is set to register not if more than 180 seconds minimum is used).  

Maybe I should delete those ?  Or all plays less that 180 seconds…..

I will guess all before this option was added.  Or are they still recorded and just ignored for stats?

Ignore play sessions less than x seconds in duration

 

[speechles 1920]

Sorry for the issues. I suspect those huge pause times with huge durations are when people turn off their TV but leave their Roku happily playing with the TV off. Try it yourself. Play something on your Roku. Now turn off your TV connected to that Roku. Do you see what happens on the dashboard activity? When you turn the TV back on is the Roku still playing the item or is it paused? If it isn't paused wait longer to turn the TV back on. Then check to see if when you turn it on after waiting longer is it paused and still running the Emby app?

We have had changes in the Roku Emby Beta app to prevent this from happening and it will stop these things but they are not pushed to the store version yet. These changes will eventually make it to the Store release version. Once they do you should be able to trust your playback reports when the Roku is concerned.

 

Edited March 24 by speechles