I lost a hard drive and wanted to get a list of all my lost media files so i wrote this query and would like to share it if somebody else is going to need it someday.
How to use:
- Open the Plex Database in a SQLite Browser (Google for DB Browser for SQLite)
- Open your Plex DB (Located in: Plex Media Server/Plug-in Support/Database)
- Go to tab “Run SQL”
- Paste this code and hit the “Play” button
SELECT library_sections.name AS Libary, metadata_series.title as Series, metadata_season.'index' AS Season, metadata_media.title AS Title FROM media_items
INNER JOIN metadata_items as metadata_media
ON media_items.metadata_item_id = metadata_media.id
LEFT JOIN metadata_items as metadata_season
ON metadata_media.parent_id = metadata_season.id
LEFT JOIN metadata_items as metadata_series
ON metadata_season.parent_id = metadata_series.id
INNER JOIN section_locations
ON media_items.section_location_id = section_locations.id
INNER JOIN library_sections
ON library_sections.id = section_locations.library_section_id
If you’re looking for a filtered list by media location (path where the media is stored) go to the table “section_locations” and enter the ids in the where clause of the following query.
SELECT library_sections.name AS Libary, metadata_series.title as Series, metadata_season.'index' AS Season, metadata_media.title AS Title FROM media_items
INNER JOIN metadata_items as metadata_media
ON media_items.metadata_item_id = metadata_media.id
LEFT JOIN metadata_items as metadata_season
ON metadata_media.parent_id = metadata_season.id
LEFT JOIN metadata_items as metadata_series
ON metadata_season.parent_id = metadata_series.id
INNER JOIN section_locations
ON media_items.section_location_id = section_locations.id
INNER JOIN library_sections
ON library_sections.id = section_locations.library_section_id
WHERE media_items.section_location_id IN(100,101,102)
This is hopefully going to safe you some time.
