Easiest way to determine what does and does not have index thumbnails?

General Discussions
1

What is the easiest way to see at a glance how many Movies / TV Shows have index thumbnails created? I have this set to create during maintenance and I want to see roughly how many files are left to be processed. I can run a query using a SQLLite browser I just don’t know what table to hit / field to look at. Thanks.

2

On UNIX-based server platforms, there is this little shell script

#!/bin/bash

# Set basic variables for Plex
PLEX_BINARIES=/usr/lib/plexmediaserver
PLEX_DATADIR=/var/lib/plexmediaserver

#Get script path
SCRIPTPATH="`dirname \"$0\"`"

current_file=`ps -eo args | grep '$PLEX_BINARIES/Resources/Plex New Transcoder -i' | grep -v 'grep' | sed -e 's/ -threads.*//g' -e 's/.*\///g' -e 's/-segment_format.*//g'`
current_percent=`cat $PLEX_DATADIR/Library/Application\ Support/Plex\ Media\ Server/Logs/Plex\ Media\ Server.log | grep bif |tail -n1 | sed -e 's/.*progress=//g' -e 's/.size=.*//g'`
current_speed=`cat $PLEX_DATADIR/Library/Application\ Support/Plex\ Media\ Server/Logs/Plex\ Media\ Server.log | grep bif |tail -n1 | sed -e 's/.*speed=//g' -e 's/.remaining=.*//g'`
echo -e '\n''Currently creating index for: '$current_file'\n''Current index completion: '$current_percent'%\n''Current index speed: '$current_speed'x'
cat $SCRIPTPATH/stats.sql | sqlite3 $PLEX_DATADIR/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases/com.plexapp.plugins.library.db

It requires this sql file to be present, which contains the queries you are interested in stats.sql

.headers on
.mode column
SELECT(SELECT count (*) FROM media_parts INNER JOIN media_items ON media_parts.media_item_id = media_items.id INNER JOIN metadata_items ON media_items.metadata_item_id=metadata_items.id WHERE metadata_items.metadata_type IN (1,4) AND media_parts.file NOT LIKE '' AND media_parts.extra_data LIKE '%indexes=sd') AS 'Completed Indexes',
(SELECT count (*) FROM media_parts INNER JOIN media_items ON media_parts.media_item_id = media_items.id INNER JOIN metadata_items ON media_items.metadata_item_id=metadata_items.id WHERE metadata_items.metadata_type IN (1,4) AND media_parts.file NOT LIKE '' AND media_parts.extra_data NOT LIKE '%indexes=sd') AS 'Remaining Indexes',
(SELECT count (*) FROM media_parts INNER JOIN media_items ON media_parts.media_item_id = media_items.id INNER JOIN metadata_items ON media_items.metadata_item_id=metadata_items.id WHERE metadata_items.metadata_type IN (1,4) AND media_parts.file NOT LIKE '') AS 'Total Items';

(You can paste the last 3 lines into e.g. https://sqlitebrowser.org/ on Windows and get your numbers that way.)

3

Exactly what I was looking for. Thanks!

4

And adding to @OttoKerner here:

ExportTools

Movies Level 5
TV Shows Level 4

5

What is indexes=sd?

I ask because when I run the queries I get 0 completed indexes and 1862 remaining - this can’t be right since Plex has been running a few weeks generating video thumbnails.

Should I be querying for something else?

6

Index=sd means there’s an index on that media

And again…Try ExportTools, since it was made for exporting stuff!

7

Hello all,

Thanks @OttoKerner for your help as always, these sql lines helped me a lot.

These showed me has having no completed indexes and zero remaining.
I knew for a fact that that was false as my thumbnails where fine almost everywhere.
I had to make a small change to the '%indexes=sd' condition to '%indexes=sd%' to make it work right.

For anyone having the same problem here’s my 3 corrected queries:

.headers on
.mode column
SELECT(SELECT count (*) FROM media_parts INNER JOIN media_items ON media_parts.media_item_id = media_items.id INNER JOIN metadata_items ON media_items.metadata_item_id=metadata_items.id WHERE metadata_items.metadata_type IN (1,4) AND media_parts.file NOT LIKE '' AND media_parts.extra_data LIKE '%indexes=sd%') AS 'Completed Indexes',
(SELECT count (*) FROM media_parts INNER JOIN media_items ON media_parts.media_item_id = media_items.id INNER JOIN metadata_items ON media_items.metadata_item_id=metadata_items.id WHERE metadata_items.metadata_type IN (1,4) AND media_parts.file NOT LIKE '' AND media_parts.extra_data NOT LIKE '%indexes=sd%') AS 'Remaining Indexes',
(SELECT count (*) FROM media_parts INNER JOIN media_items ON media_parts.media_item_id = media_items.id INNER JOIN metadata_items ON media_items.metadata_item_id=metadata_items.id WHERE metadata_items.metadata_type IN (1,4) AND media_parts.file NOT LIKE '') AS 'Total Items';

Enjoy !

1 Like
8

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.