Hi I would like to know how I can determine which files in my media collection have not been accessed for that last X amount of days.
I was going to use a bash script and use the atime of the files to do this, but I have found out that PMS does not alter the atime on files when you play them. So I guess that means I need to pull some sort of play/access data from the PMS databases themselves and I was after some direction about how to ensure I am connecting the dots between the tables in the com.plexapp.plugins.library.db file.
I have found:
Absolute path to filenames: com.plexapp.plugins.library.db → media_parts.file
ID: com.plexapp.plugins.library.db → media_parts.id OR media_parts.media_item_id
Where can I find info about when a media item is played or accessed by the user, and which database fields should I be using to link the data across database tables accurately?
My end aim is to extract a list of filenames that haven’t been accessed in the last X days.
I’m using WebTools to see what I can export. Why do some last_viewed_at values have zero month values like: 2023-00-14 ? and others can say “N/A”. I guess N/A means there’s no value set for the field, but what’s the go with the zero month values?
I rebuilt my PMS a few months back, so I don’t know what metadata, if any, is saved online and brought across to the new installation. Are the zero month values a corruption somehow?
I exported All data for my movie library. Most of the entries for “Last Viewed At” were correct. However, a few were garbled/nonsense (ex: NaN-aN-aN NaN:aN:aN).
I created a custom export level with just the title and Last Viewed At. All the info was correct.
Not saying you can only export two fields, but cut it down to the fields you need. A side benefit is that the export will run much faster.
N/A means there is no info in the database for that field.
It looks like Last Viewed At has info only for the person running the report (the person logged into Plex via WebTools).
I exported the info using WebTools and Tautulli, then looked at movies I had not watched, but others with access to my server have watched. The field has N/A, not the date/time the other users watched the movie.
Let me see if there is a field for the last time any user watched the movie.
I just had a friend watch a few items on my PMS via their account and nothing updates in metadata_item_views table from what I can see.
Where does data about what other users watch get stored?
I also ran a bash script that searches the whole database for a string and I can’t find anywhere that accounts.name or accounts.id occurs in the database besides the accounts table.
It’s showing up in the Plex dashboard as a watch. Where does the dashboard pull its data from? Is it the com.plexapp.plugins.library.db file or is that data stored elsewhere?
What I noticed when I rebuilt my PMS was that the watch data for the movies seemed to appear from nowhere, as I didn’t back anything up to do with watch data and import it to the new server. It’s like that data came from somewhere online. Does Plex store watch data online and restore it for you? If they do, could the external user/friend watch data be stored online and not locally?
That’s a different thing. The Dashboard shows live data. i.e. before these are even stored in the DB.
Let the user mark a few movies as watched (or let him/her watch them completely if they have the time). Then look at the database again.
I’ve been doing poking around in the database file and have found these significant tables and fields so far. My interest is to be able to identify how long it has been since an “item” (a media file on your hard disk) has been interacted with by a viewer or user. So far I’ve found a number of relevant values, but I’m struggling to make a database relationship to the media_parts.file field which holds the absolute path to the item’s file on disk, to take us from the fields that tell us how long since the item has been interacted with to the item’s filename.
I’ll keep looking to see if I can find the relevant fields that connect the dots, but here is what I’ve found:
*** Users / Accounts
com.plexapp.plugins.library.db → accounts
Fields of interest:
- id: the account ID number (relates to account_id in most other tables)
- name: human readable username for this account
*** Per-user COMPLETE view data is at:
com.plexapp.plugins.library.db → metadata_item_views
Fields of interest:
- account_id: the user account connected to the item view entry
- guid: ID value for the media item (relates to metadata_items.guid - general format is plex://mediatype/long-hash-id-value)
- title: the item’s human-readable Title
- viewed_at: UNIX timestamp of date and time the item was recorded as viewed
*** Playback resumption status:
com.plexapp.plugins.library.db → metadata_item_settings
Notes: does not have a unique ID to link to media_parts table
Fields of interest:
- account_id: the user account connected to the item view entry
- guid: ID value for the media item (relates to metadata_items.guid - general format is plex://mediatype/long-hash-id-value)
- view_offset: NULL means item is not set for resumption on playback, otherwise an integer value shows the playback resumption position
- last_viewed_at: UNIX timestamp of date and time when the item was last played
*** Item Information:
com.plexapp.plugins.library.db → metadata_items
Fields of interest:
- guid:- (relates to guid in most other tables)
- title: for movie name
- year: for movie year
com.plexapp.plugins.library.db → media_parts
Fields of interest:
- file: Absolute path to filename
select DISTINCT
mdi.id
,mdi.guid
,mdi.title
,mp.file
from metadata_items as mdi
LEFT JOIN metadata_item_settings as mis on ( mdi.guid = mis.guid AND mis.guid IS NULL AND (mdi.metadata_type = 4 or mdi.metadata_type = 1)) --Only episodes and movies
INNER JOIN media_items as mi on mdi.id = mi.metadata_item_id
INNER JOIN media_parts as mp on mi.id = mp.media_item_id
WHERE mp.file <> ''
This also finds music tracks that haven’t been played so if you’re here reading this and using this, make sure you test the query first and adjust it to meet your exact needs.
