As topic states, I’d like to know how could I see the ‘watched’-state of a media if I only know the path and file name which doesn’t necessarily match directly to the movies/series/episode name? This is for scripting purposes.
I was looking at python-plexapi but it doesn’t seem to provide a way to seek media with the file name/path. I can only get the path if I know the media. Other API-based ideas?
I’m pretty comfortable working with databases too so if that’s the only way, I’d be happy if someone could point out where would the database be which would contain the necessary info to make this happen.
Well, it’s not optimal, needing to export the whole library to get one or two items identified but it might be the best option I got. Not like my library would even be so huge that the csv file size would also be somehow straining to the process. I’ll look into it.
In the mean time I’m still open for more straightforward ideas, if anyone happens to know some.
Well you could try querying the database with sqlite3, thing is, you will have to figure out what tables to query to get the watch state. Not obvious, I didn’t see any field named watch state… https://github.com/mutanthost/plex_schema.
An alternative if you know python is to plow through the Tautulli source and see if you can figure out how Tautulli gets that data via the plex api. The calls have to be there somewhere. https://github.com/Tautulli/Tautulli
I looked at the database earlier already though not knowing for sure if it’s the right one I’m suppose to be looking at. There was a complete watch history in metadata_items_views but I’m not yet sure if an entry gets listed only if watched enough for Plex to see it as watched or if it also lists partial watches. Need to investigate that one.
I didn’t see any other table that would easily present if a media is watched for certain user.
It does seem to only list fully watched media. I guess I’ll try going with that if I just manage to find the right connections to connect the media with the file path.
media_parts has the full file path in the file field. You can link that to meta_data_items using the media_item_id. Thing is that a movie can have more than one media_data_item so any views are really for the movie not the specific file path unless there’s only one file per movie in your database.
In my use case I’m only going to use it for certain series episodes that I now there are only ones so no need to worry about that. Btw, would your happen to know if this database inquiry would be best handled in bash or python? I’m not enough familiar with python to know if it would give me any upper hand in a case like this.
That kinda depends on what you are doing with the data. If you are producing a report, then I’d probably use bash to run sqlite3. If you are trying to query the data and do something with the results, I’d use python or perl to do it. It really boils down to comfort level with the tool and end result you are trying to get to.
Well, basicly I’m just checking if the episode is watched and the file can be deleted. Though there are some other conditions for removal which I check with other cli tools.
Now that I typed it out loud, bash does feel somewhat obvious choice here.
Btw, currently I find that guid would be the best way to join metadata_item_views with metadata_items. The views table doesn’t seem to contain the metadata_id and many entries might have same title so that’s the only one that seems to identify the media reliably. Have I misunderstood something or is this the best way to go?
I’ll guess I share my solution in detail in case someone would happen to need something like this for their own needs:
So basically with this command it returns the id of a user that has watched the media in the file path:sqlite3 "/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db" 'SELECT account_id FROM metadata_item_views INNER JOIN metadata_items ON metadata_items.guid = metadata_item_views.guid INNER JOIN media_items ON media_items.metadata_item_id = metadata_items.id INNER JOIN media_parts ON media_parts.media_item_id = media_items.id WHERE media_parts.file = [insert file path here]'
If no one has watched it, it doesn’t return anything. I’m not the only one watching on my server the id is essential for my use case.
One might need to change the path in the first parameter if your data folder is in different location. More info here.
