Mass edit watched status/date of items

Plex Media Server NAS & Devices
1

Server Version#: 1.40.4.8679

In February, I tried out a DLNA client that is clearly poorly written. It somehow rapidly marked a ton of my movies (~140 movies) watched pretty much instantly. I think it did this by trying to generate thumbnails or something. Some were already watched prior to that, some were unwatched prior.

When realizing the issue at the time I rolled back the Plex database to a recent backup and also manually deleted the ~140 plays from my Plex profile Watch History.

I never really paid attention until looking through my library recently and kept finding that some movies I know I’ve never watched are marked as watched. I’m not sure what went wrong with my restoration attempts, but if I go to my Movie library and sort by Date Viewed, then I see the ~140 movies listed on that day 6 months ago. But if I click on one of those movies and click Play History, it does NOT actually have that day listed at all. It just either has no play history (despite saying Watched) or history from when I actually did watch it possibly years ago (and also has that on my Plex profile play history). Plex Dash, similarly, only shows actual play history for those items, not the February entry.

I suspect part of the issue is that I sync with Trakt (Plex-Trakt-Sync). And perhaps an automated sync was performed after the movies were marked watched by the DLNA client but before I restored the database. Yet, Trakt only shows 28 movies “watched” on that February date. Maybe only 28 of the 140 were actually unwatched at that point in time?

Anyway, wondering how I can go ahead and remove all database entries specifically from that date in February?

2

Adding some more details. I tried using Plex Sqlite…

The following SELECT * FROM metadata_item_views WHERE viewed_at >= 1707541200 AND viewed_at <= 1707627599; shows no results. Meaning indeed my previous removals of views persisted.

However the following: SELECT * FROM metadata_item_settings WHERE last_viewed_at >= 1707541200 AND last_viewed_at <= 1707627599; does indeed return a long list of results.

But now, comically, I don’t know what to do with that. Since it’s not entries for views (where I can just delete the entry) but rather a last_viewed_at time for each movie, I’m not sure how to revert to previous viewed time (or set to unviewed if not previously viewed).

Any ideas?

3

There is no way to “revert”. The DB doesn’t keep the old value.

do you see the GUID values for episodes and movies ?

this is how you’d refine it/them.

You literally could craft (I’m close on this but NOT exast)

DELETE FROM metadata_item_settlngs WHERE Put_Column_Name_here LIKE 'plex://movie/61ed4a5819b0331e2cd45282'

This would delete the specific row for that GUID.
The GUID is how PMS identifies each item in the DB.

-OR-

You can make a nested SELECT in there IIRC ?

-OR-

You could change the DELETE to be a

DELETE FROM metadata_item_settings where .....    (put date range here)

I suspect you’re far better at SQLite3 than I am so please take my suggestion with a grain of salt.

4

Hey @ChuckPa thank you for your help as always!

I can definitely refine by last_viewed_at >= 1707541200 AND last_viewed_at <= 1707627599 because everything viewed on February 10 is impacted, so I don’t necessarily need to refine by GUID - should makes things easier.

I need to play with the database more, but I was under the impression the metadata_item_settings table only has one entry for each movie, whereas metadata_item_views has one entry for each view. So if I delete any row from metadata_item_settings won’t that totally delete the history of that item? Meaning something I watched in 2020 will still be marked unwatched if I delete the entry.

I know you’re more of an expert with the applications and installations than the database, should I cross-post to another subforum?

5

Think of it this way?

  1. delete from metadata_item_views where ( select from metadata_item_settings where date-range)
  2. delete from metadata where date-range

Thinking:

  1. Delete the view rows for those which have an entry in settings in the date rane
  2. Now go back to settings and delete those records in the date range.

I think this gets both the views table (which is dependent on the settings table) cleaned up first.
Next, you clean up the settings table.

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.