Plex DB where is the flag for --analyze-deeply

General Discussions
1

Anyone knows where in plex database is stored info if file had --analyze-deeply preformed on it.

I was pretty sure it would be in media_items field media_analysis_version but they are all set to 5 even after i manually triggered deep analyzation with cli on completely new movie.

The flag should be somewhere as I assume Plex does not try to preform in Scheduled tasks deep analyzation on all media you have in library.

2

If you get info on an item, and view the XML you will the the XML for this item. Then in the URL, if you add &includeBandwidths=1 it’ll included the deep analysis info. At the part level, you should see deepAnalysisVersion="2" if it has run.

3

Thanks for answer gbooker02 but do you know where deepAnalysisVersion=“2” is stored in
/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db

4

I presume you are asking for something like this:

sqlite> select count(*) from media_parts where extra_data like '%deepAnalysisVersion=2%';
905
5

Great thats it

6

@gbooker02
How could I get (with sql) metadata_item_id for files that do not have deepAnalysisVersion=2.

What is the coloration between media_items and media_parts .( on what field to join )

7

I made a simple script to run cli scanner on all files that dont have analyzation info.

#!/usr/bin/env python3
from subprocess import call
import os
import requests
import sqlite3
import sys

conn = sqlite3.connect('/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db')

c = conn.cursor()
c.execute('select metadata_item_id from media_items where bitrate is null')
items = c.fetchall()
conn.close()

print("To analyze: " + str( len(items) ))

for row in items:
	os.system('LD_LIBRARY_PATH=/usr/lib/plexmediaserver PLEX_MEDIA_SERVER_APPLICATION_SUPPORT_DIR=/var/lib/plexmediaserver/Library/Application\ Support /usr/lib/plexmediaserver/Plex\ Media\ Scanner -a -o ' + str(row[0]))
	os.system('sleep 1')
	print(str(row[0]))

Now i would like to make another one that would do deep analyzation on all files that are missing it eg Plex\ Media\ Scanner --analyze-deeply -o + str(row[0], however since media_parts are individal file Iam not sure how to get metadata_item_id that i would send to scanner.

8

@Ajki Note: the bitrate attribute is not associated with the deep analysis; it comes from the normal (shallow) analysis. You’ll have to look for the deepAnalysisVersion=2 in the extra_data.

As per joins, in media_parts the column media_item_id is the id in media_items. In media_items, the metadata_item_id is the id in metadata_items. Also, deep analysis is only performed on video types, not music nor pictures. The metadata_type values for those deeply analyzed are 1, 4, and 12.

9

@gbooker02 hmm thats a bit weird since both ID fields are auto increment or i misunderstood something.
Still not sure on what to join to make it work in query bellow.

select media_item_id from media_items
join media_parts on ??
join metadata_items on ??
where 
media_parts.extra_data not like '%deepAnalysisVersion=2%' 
and metadata_items.metadata_type="1" 
or metadata_items.metadata_type="4" 
or metadata_items.metadata_type="12";
10

Perhaps this is better. BTW, I like to explicitly name the tables in the joins:

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=2%'
  and meta.metadata_type in (1, 4, 12)
  and part.file != "";

The last, part.file != "" is there because some metadata items reference remote files such as trailers and other extras. I select the metadata item id since that is the ID you give to the deep analysis.

11

@gbooker02 So i can run /Plex Media Scanner --analyze-deeply -o XXX on those meta.id results.

12

You’ll need the LD_LIBRARY_PATH and PLEX_MEDIA_SERVER_APPLICATION_SUPPORT_DIR set, but yeah.

13

Thanks again :slight_smile:

14

@Ajki said:
I made a simple script to run cli scanner on all files that dont have analyzation info.

#!/usr/bin/env python3
from subprocess import call
import os
import requests
import sqlite3
import sys

conn = sqlite3.connect('/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db')

c = conn.cursor()
c.execute('select metadata_item_id from media_items where bitrate is null')
items = c.fetchall()
conn.close()

print("To analyze: " + str( len(items) ))

for row in items:
	os.system('LD_LIBRARY_PATH=/usr/lib/plexmediaserver PLEX_MEDIA_SERVER_APPLICATION_SUPPORT_DIR=/var/lib/plexmediaserver/Library/Application\ Support /usr/lib/plexmediaserver/Plex\ Media\ Scanner -a -o ' + str(row[0]))
	os.system('sleep 1')
	print(str(row[0]))

Now i would like to make another one that would do deep analyzation on all files that are missing it eg Plex\ Media\ Scanner --analyze-deeply -o + str(row[0], however since media_parts are individal file Iam not sure how to get metadata_item_id that i would send to scanner.

I’d try and avoid holding the database open longer then needed or using cursors. Here’s a simple way to do stuff like this on windows.

Create a batch file and put this inside the file: (DeepAnalysis.bat)
c::
cd \sqlite
sqlite3 < DeepAnalysis.txt
DeepAnalysisRunning.bat

Create a text file name DeepAnalysis.txt with this contents:
.open ‘C:\PlexData\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db’
.output DeepAnalysisRunning.bat
SELECT '“C:\Program Files (x86)\Plex\Plex Media Server\Plex Media Scanner.exe” --analyze-deeply --item ’ || 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=2%’
and meta.metadata_type in (1, 4, 12)
and part.file != “”;

It assumes sqlite3 is in the same directory as the batch file. If not adjust with paths.
This batch file starts sqlite3 with instructions to open the text file.
The text file opens the database and sets an output file (named as a batch file) and outputs the scanner lines needed to run sequentially one after another.
The batch file then executes the 2nd batch file it just created.

This way you only have the database open for a very short period of time and can generate thousands of commands to run. Play with it and you’ll see how it works.

Carlo