I have a question directly to @gbooker02. I am opening a new thread, since I can sadly not answer to the old thread anymore and I am not able to send you a private message.
I have a question regarding this post from 2017 since I want to update my plex scripts that i use.
I used the query used in the thread linked above but the results I get is always the same amount of files shown that are missing the deep analysis. But if I check some of my media manually, I can see that deep analysis was done.
Is the query you posted back then still valid or does it need to be updated ?
Below I have posted my attempt at getting the “actual” data. Could you please let me know if it could be used in that way aswell ?
#!/bin/bash
logfile="/share/CACHEDEV1_DATA/plexstats.log"
db="/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db"
echo " " | tee -a $logfile
echo "$(date "+%d.%m.%Y %T")" | tee -a $logfile
echo "--------------------------" | tee -a $logfile
echo "PLEX LIBRARY STATS" | tee -a $logfile
query="SELECT Library, Items \
FROM ( SELECT name AS Library, \
COUNT(duration) AS Items \
FROM media_items m \
LEFT JOIN library_sections l ON l.id = m.library_section_id \
WHERE library_section_id > 0 GROUP BY name );"
sqlite3 -header -line "$db" "$query" | tee -a $logfile
echo "--------------------------" | tee -a $logfile
echo " " | tee -a $logfile
echo "------------------------------------------" | tee -a $logfile
echo "PLEX ANALYZATION STATS" | tee -a $logfile
query="select count(*) from media_items"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} files in library" | tee -a $logfile
query="select count(*) from media_items where bitrate is null"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} files missing analyzation info" | tee -a $logfile
query="select count(*) from media_parts mp join media_items mi on mi.id = mp.media_item_id where (library_section_id = '2' OR library_section_id = '8' OR library_section_id = '24' OR library_section_id = '3' OR library_section_id = '20' ) and mp.extra_data like '%deepAnalysisVersion=%';"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} items have deep analyzation info" | tee -a $logfile
query="select count(*) from media_parts mp join media_items mi on mi.id = mp.media_item_id where (library_section_id = '2' OR library_section_id = '8' OR library_section_id = '24' OR library_section_id = '3' OR library_section_id = '20' ) and mp.extra_data not like '%deepAnalysisVersion=%';"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} items missing deep analyzation info" | tee -a $logfile
echo "------------------------------------------" | tee -a $logfile
echo " " | tee -a $logfile
echo "------------------------------------------" | tee -a $logfile
echo "PLEX INTRO STATS" | tee -a $logfile
query="select count(*) from media_parts mp join media_items mi on mi.id = mp.media_item_id where (library_section_id = '2' OR library_section_id = '8' OR library_section_id = '24') and mp.extra_data like '%intros=%';"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} items analyzed for intros" | tee -a $logfile
query="select count(*) from media_parts mp join media_items mi on mi.id = mp.media_item_id where (library_section_id = '2' OR library_section_id = '8' OR library_section_id = '24') and mp.extra_data not like '%intros=%';"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} items not analyzed for intros" | tee -a $logfile
query="select count(*) from media_parts mp join media_items mi on mi.id = mp.media_item_id where (library_section_id = '2' OR library_section_id = '8' OR library_section_id = '24') and mp.extra_data like '%intros=%%7B%';"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} items have intros" | tee -a $logfile
echo "------------------------------------------" | tee -a $logfile
echo " " | tee -a $logfile
echo "------------------------------------------" | tee -a $logfile
echo "PLEX PREVIEW THUMBNAIL STATS" | tee -a $logfile
query="select count(*) from media_parts mp join media_items mi on mi.id = mp.media_item_id where (library_section_id = '2' OR library_section_id = '8' OR library_section_id = '24' OR library_section_id = '3' OR library_section_id = '20' ) and mp.extra_data like '%indexes=%';"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} items have preview thumbnails" | tee -a $logfile
query="select count(*) from media_parts mp join media_items mi on mi.id = mp.media_item_id where (library_section_id = '2' OR library_section_id = '8' OR library_section_id = '24' OR library_section_id = '3' OR library_section_id = '20') and mp.extra_data not like '%indexes=%';"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} items missing preview thumbnails" | tee -a $logfile
echo "------------------------------------------" | tee -a $logfile
echo " " | tee -a $logfile
exit
Bonus question:
Is there any way I can only trigger the deep analysis or intro detection vie CLI for the items that are still missing it ?
It was abused too much by those on this forum so I disabled PMs.
The SQL has changed somewhat. The version has increased, there is a check on the library section, and it now runs on music as well. So:
select meta.id from metadata_items meta
join media_items media on media.metadata_item_id = meta.id
join media_parts part on part.media_item_id = media.id
where part.extra_data not like '%deepAnalysisVersion=4%'
and meta.library_section_id > 0
and meta.metadata_type in (1, 4, 10, 12)
and part.file != "";
First see https://support.plex.tv/articles/201242707-plex-media-scanner-via-command-line/
The deep analysis is done via the options --analyze-deeply --item METADATAITEMID (make the appropriate substitution)
The intro detection is much more complicated and while parts can be instigated manually, it’ll be of no use without all of it. It runs as a butler task and Plex can be configured to run it whenever media is added so stick with that.
Thank you @gbooker02 for the quick response and the update regarding the deep analysis query.
I will take a look at the link you provided. Thank you!
Just to confirm, would the query below also be a valid way to check for missing deep analysis, or is that query wrong?
query="select count(*) from media_parts mp join media_items mi on mi.id = mp.media_item_id where (library_section_id = '2' OR library_section_id = '8' OR library_section_id = '24' OR library_section_id = '3' OR library_section_id = '20' ) and mp.extra_data not like '%deepAnalysisVersion=%';"
result=$(sqlite3 -header -line "$db" "$query")
echo "${result:11} items missing deep analyzation info" | tee -a $logfile
Additional question is there a better way than the quoted above to check if the file is missing anayzation info?
Since the current query only checks if the bitrate is NULL.
Which would mean it would count for example jpeg files as not analyzed because they have no bitrate.
You likely want to know the metadata, not the media. These are different objects in the hierarchy.
This is tricky. Different metadata types have different analysis versions and different criteria on whether it needs to be run (including the file modification date being different than the last time it was run). I’m not sure there is a good way to do this outside of having the server run it.
I have one more question regarding the plex script from the 2017 thread.
How can I modify the representation of the Library list ?
I am talking about this part in that script from 2017:
query="SELECT Library, Items \
FROM ( SELECT name AS Library, \
COUNT(duration) AS Items \
FROM media_items m \
LEFT JOIN library_sections l ON l.id = m.library_section_id \
WHERE library_section_id > 0 GROUP BY name );"
sqlite3 -header -line "$db" "$query" | tee -a $logfile
You don’t want -line and you want a sql query like select name || ' (' || count(duration) || ' items)' from media_items m left join library_sections l on l.id = m.library_section_id where library_section_id > 0 group by name;
Thank you for your response the code looks like this now:
query="select name || ' (' || count(duration) || ' items)' from media_items m left join library_sections l on l.id = m.library_section_id where library_section_id > 0 group by name;"
sqlite3 -header "$db" "$query" | tee -a $logfile
As you can see it looks and works beautifully now and ist almost perfect.
The only thing is the first line which seems to be part of the query is also “printed”.
How can this be prevented ? Solved
Second question: I have a Photos Library where all my photos and videos from my cameras are stored, the total file count shown in the plex interface is about 15000 items, while this script returns the value 1120 items, any idea why it is displayed differently ?
Thank you very much, that did the trick. I appreciate the quick help!
I do have one final question:
Is it possible to get some output which just lists the TV-Show Names which are not yet analyzed for intros, so I can trigger it manually via the “Analyze” button in those TV-Shows.
