Hi folks! I recently found a chunk of audio media just gone. It was totally my fault due to an untested LFTP script. Anyway I spent years building my catalog so I am pretty bummed. Now, II know PMS wasnt the root cause, but hopefully there are bread crumbs, So, the million dollar question is through the older logs, are db file, can i find a reference to the deleted content, where do I look and how. The goal would be to write a SQL query, or simple script to parse the log to help create a list for me so I know what go find.
If your Plex library hasn’t updated yet (i.e. it still lists tracks that are no longer on your filesystem), you may be able to use WebTools-NG’s FindMedia tool to find differences between what files Plex has registered and what’s on your filesystem. If Plex doesn’t list those tracks anymore, the information might still be in a backup database. If so, the following query would list all file paths in the server, which could then be compared against the file paths that actually exist on your system:
SELECT file FROM media_parts WHERE length(file) > 0 ORDER BY file;
If you wanted to limit it to just music library file paths:
SELECT file FROM media_parts
INNER JOIN media_items ON media_parts.media_item_id=media_items.id
INNER JOIN library_sections ON media_items.library_section_id=library_sections.id
WHERE library_sections.section_type=8 AND length(file) > 0 ORDER BY file;
And if you want some basic metadata attached to each track:
SELECT
sections.name AS Library,
gitems.title || " - " || pitems.title || " - " || items.`index` || " - " || items.title AS Title,
parts.file as FilePath
FROM metadata_items items
LEFT OUTER JOIN metadata_items pitems ON items.parent_id=pitems.id
LEFT OUTER JOIN metadata_items gitems ON pitems.parent_id=gitems.id
INNER JOIN media_items media ON media.metadata_item_id=items.id
INNER JOIN media_parts parts ON parts.media_item_id=media.id
INNER JOIN library_sections sections ON items.library_section_id=sections.id
WHERE items.metadata_type=10
ORDER BY Library, gitems.title, pitems.originally_available_at, items.`index` ASC, items.title;
which will output something like
Library
Title
FilePath
Music
Artist1 - Album1 - 1 - Track1
/path/to/track1.mp3
Music
Artist1 - Album1 - 2 - Track2
/path/to/track2.flac
…
…
…
It doesn’t do any filtering based on what actually exists on your filesystem, but if you combine it with a script outside of Plex that lists all the files that do exist, you could find what’s different between the two.
This is super helpful thank you. I have 2 mac minis that I need to look through, and to go back further, chromebox, rasberry Pii and intel nuc. I can eliminate the new mac mini, and focus on the older boxes to see what turns up. I will reply back on the weekend.
