plexUHDDupeWaste.py

#!/usr/bin/env python3

import sqlite3

# /var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases
dbPath = "./com.plexapp.plugins.library.db"
conn = sqlite3.connect(dbPath)
c = conn.cursor()
c2 = conn.cursor()

uhdTitles = c.execute('''SELECT metadata_items.title, media_items.id AS media_items_id, metadata_items.id AS metadata_items_id, media_items.width, media_parts.file, media_parts.size
FROM media_items
INNER JOIN metadata_items ON media_items.metadata_item_id=metadata_items.id
INNER JOIN media_parts ON media_items.id=media_parts.media_item_id
WHERE media_items.width > 2000 AND metadata_items.metadata_type=1''')

diskSize = 0

for row in uhdTitles:
    title = row[0]
    mediaId = row[1]
    metadataId = row[2]

    titleParts = c2.execute('''SELECT metadata_items.title, media_items.id AS media_items_id, metadata_items.id AS metadata_items_id, media_items.width, media_parts.file, media_parts.size
FROM media_items
INNER JOIN metadata_items ON media_items.metadata_item_id=metadata_items.id
INNER JOIN media_parts ON media_items.id=media_parts.media_item_id
WHERE media_items.width < 2000 AND metadata_items.id = "%s"''' % metadataId);
    
    for part in titleParts:
        filePath = part[4]
        fileSize = part[5]

        print(f"{filePath}")

        diskSize += fileSize

#print(f"Final low res dupe total is {diskSize}")