HOWTO: query data from PLEX on a Mac

General Discussions Tips, Tricks & How-Tos
61

Something that doesn’t make sense to me.

When I click on the Filters and choose Unwatched in my Movies library, the number returned is 151.

When I run the following query:
sqlite> select count(*) from metadata_item_settings where view_count = 0; 154

Why the discrepancy?

62

I share my library with family and I am trying to figure out if any of the movies have never been watched.

I do a select count(*) from metadata_items; and I get 13839, which I assume is a count of all items in all my libraries.

I do a select count(*) from metadata_items where library_section_id=2; and get back 574, which is the number of movies in my movies library.

I do a select count(*) from metadata_item_settings where view_count=0; and I get 2246.

I am assuming that there are 2246 unviewed items across all my libraries.

What I want is the unviewed count in just my movies library and then somehow figure out which movies those are.

For fun I played around and found that I have 1 item that has been viewed 528 times. I would love to find out what it is.

Any help would be appreciated.

63

@stuchido said:
I share my library with family and I am trying to figure out if any of the movies have never been watched.

I do a select count(*) from metadata_items; and I get 13839, which I assume is a count of all items in all my libraries.

I do a select count(*) from metadata_items where library_section_id=2; and get back 574, which is the number of movies in my movies library.

I do a select count(*) from metadata_item_settings where view_count=0; and I get 2246.

I am assuming that there are 2246 unviewed items across all my libraries.

What I want is the unviewed count in just my movies library and then somehow figure out which movies those are.

For fun I played around and found that I have 1 item that has been viewed 528 times. I would love to find out what it is.

Any help would be appreciated.

Have a look at this tool
http://forums.plex.tv/discussion/254248/rel-exporttools/p1

64

Thanks sa2000 for the info.
I installed it and ran it and the issue is that although it produces a nice report, the report is only about what my account has viewed, and not all users.

Querying the database directly, I believe, gives me info for all users.

What my intention is, I would like to see if I have any media that no one has ever watched, and then delete it.

65

So, I’m a moron, but in transferring all my media in to the NVIDEA_SHIELD folder, I accidentally (truly) deleted that folder AFTER I had put everything in it. I know, I know. SO, I want to print a list of everything from my com.plexapp.plugins.library.db file using sqlite as instructed…but it keeps saying Permission denied. huh? Anyone?

66

@stuchido said:
Querying the database directly, I believe, gives me info for all users.

What my intention is, I would like to see if I have any media that no one has ever watched, and then delete it.

Let me know if you still want to try this. You’re thinking is a bit off. The database only tracks what is watched, not un-watched so you need to “select” the watched items and subtract that from your total.

@jintsjason said:
So, I’m a moron, but in transferring all my media in to the NVIDEA_SHIELD folder, I accidentally (truly) deleted that folder AFTER I had put everything in it. I know, I know. SO, I want to print a list of everything from my com.plexapp.plugins.library.db file using sqlite as instructed…but it keeps saying Permission denied. huh? Anyone?

You can’t access the DB on the Shield unless you’re rooted. It’s an Android restriction.

67

@anon18523487 said:

@jintsjason said:
So, I’m a moron, but in transferring all my media in to the NVIDEA_SHIELD folder, I accidentally (truly) deleted that folder AFTER I had put everything in it. I know, I know. SO, I want to print a list of everything from my com.plexapp.plugins.library.db file using sqlite as instructed…but it keeps saying Permission denied. huh? Anyone?

You can’t access the DB on the Shield unless you’re rooted. It’s an Android restriction.

Silly me. I forgot. You can download the database using PlexWeb. You can then query the database to obtain the information you want.

68

Thanks. So, not the web server, but the web viewing app? And will I be able to print everything title in the DB through it? I just need a list of everything in my DB so I can begin to replace what I lost…that’s literally all I want to do. Print a simple list of everything, watched or unwatched.

69

Oh, and I don’t need to download it from the Shield…the db is on a Mac Mini…

70

@jintsjason said:
Thanks. So, not the web server, but the web viewing app? And will I be able to print everything title in the DB through it? I just need a list of everything in my DB so I can begin to replace what I lost…that’s literally all I want to do. Print a simple list of everything, watched or unwatched.

@jintsjason said:
Oh, and I don’t need to download it from the Shield…the db is on a Mac Mini…

If the server runs on a Mac then you can use export tool plugin - see

Documentation here

The location of the database file is given here
https://support.plex.tv/hc/en-us/articles/201154527-Move-Viewstate-Ratings-from-One-Install-to-Another

1 Like
72

I’ve got a NAS synology and i lost all my movies cause of a crash of system files.
My Plex server is working again but i need to put all my movies back.
I was suprised to see that Plex reconnized that some movie was watched.
i would like to get a list of all of my watched lost movies.

I get a backup of my Plex database, searching, get information from forums.
i find my actual movie :

SELECT * FROM metadata_items item
LEFT JOIN metadata_item_settings settings on ( item.guid = settings.guid )
WHERE library_section_id =1;

My idea is that a part of watched lost movies are here ;
SELECT * FROM metadata_item_settings settings
WHERE view_count >=1
But i guest i cannot get the title via the guid losted files

I would like to know where to find the information “watched” ?
this is somewhere… Maybe not in the database ?
Thanks

73

That is correct to identify if something was watched. Once something is played, they get added to this table. It tracks the progress as well until it’s finished, then the viewcount gets increased. If you mark as unwatched, it resets the viewcount to 0. If it’s in this table, then it was either started watching and finished, is in progress, or the user hit “mark as watched”.

74

As an alternative, also this query might be helpful

SELECT 
	acc.name
	,mis.view_count
	,miv.title
	,miv.grandparent_title AS Show
	,miv.parent_index AS Season
FROM metadata_item_settings AS mis INNER JOIN metadata_item_views as miv on mis.guid = miv.guid
LEFT JOIN accounts AS acc on mis.account_id = acc.id
WHERE mis.view_count > 0

Will produce a result like:

image
image798×108 3.03 KB

Edit: Name column will have the name of the user who played the movie/episode
Edit 2: you can also use this as a where clause:

WHERE 
	mis.view_count > 0
AND
   miv.metadata_type = 1

1 for movies, and 4 for episodes

75

Thanks for the replies.

So, if i understand, i can find my lost movies ever seen on the table metadata_item_settings.
I try to find on google the guid on a few movies with old created date ( 2018 ) and i found a result for a movie that i lost.

But I can’t make an INNER JOIN on metadata_items_views cause that table doesn’t seem to have lost files.

The query that i use is :
SELECT
mis.view_count,
mis.guid,
miv.title
,miv.grandparent_title AS ‘Show’
,miv.parent_index AS ‘Season’,
strftime('%d/%m/%Y ‘, datetime(last_viewed_at)) AS ‘Dernière vue’,
strftime(’%d/%m/%Y ‘, datetime(created_at)) AS ‘Date création’ ,
strftime(’%d/%m/%Y ', datetime(updated_at)) AS ‘Date Mise à jour’
,*
FROM metadata_item_settings AS mis
LEFT JOIN metadata_item_views as miv on mis.guid = miv.guid
WHERE mis.view_count > 0
AND mis.guid like ‘%movie%’

Is there a way to have the title of a guid for movies wich are deleted ( in the database ) ?
Maybe i can create a script with PHP who get that guery and ask to an API Plex ?

Sorry for my english :confused:
Thanks in advance

76

I’m unclear what you are asking. If the entries been deleted from the database, there is no way to get that info. There is no normal way to removed entries from metadata_item_settings, but they can be removed from metadata_item_views. Neither of these depend on the actual movie file needing to exists. If you are not getting the results you expect, it’s possible that the watched info is not in the database anymore.

77

Thanks for the reply.
The goal is to obtain a list of movies that I watched evenif files has been deleted.
Guid of watched movie seems to be in the table metadata_item_settings but how can i get the title of a movie from his guid when the file is deleted ?
It’s seems not to be possible with the database.

So, i think i can make a PHP script to get the guids of deleted films.
Then i can search the title with the guid from an external API.
I see the MovieDB has one and maybe Plex movie DB has too

have a nice day

78

Ah I understand now. If the file was deleted and you updated the library to remove these from the Database then it is not possible to link the guid back to the movie. Plex does not currently have an API to do a lookup of the guid back to the movie title.

1 Like