Listing watched files on kodi (xbmc)

list-watched-files.sql

select p.strPath || f.strFilename
from files f
join path p
    on p.idPath = f.idPath
where f.playCount > 0;

Listing watched files on kodi (xbmc).md

Listing watched files on kodi (xbmc)

To list the full path of all watched files that are still accessable: (assuming the sql query was saved to list-watched-files.sql)

sqlite3 ~/.kodi/userdata/Database/MyVideos90.db < list-watched-files.sql | xargs -L 1 -I filename ls "filename" 2>/dev/null

Explanation

Get the list of watched files from the Kodi database

Kodi uses sqlite databases, located in ~/.kodi/userdata/Database/. The video database is MyVideosXX.db, where XX is the version (MyVideos90.db on OSMC on 2015-05-09)

The basic structure (full details on the kodi wiki):

• The playCount column on table files can be used to check if a file (strFilename) was played (how many times, so > 0)
• The files table links to the path table, which containts the full path (the strPath column). This contains a trailing slash

So to get the full path of played files, you can concatenate (|| in sqlite) the strPath and strFilename columns:

$ sqlite3 ~/.kodi/userdata/Database/MyVideos90.db
select p.strPath || f.strFilename
from files f
join path p
    on p.idPath = f.idPath
where f.playCount > 0;

Filter out the list of files not on the device using xargs and ls

The database will list all files watched over time. Since the files might have been moved/deleted, you need to filter the file to get existing files.
You can pipe the output of sqlite to xargs, and have it execute a command to test that the file is still present on the device:

• invoke command for each line of input: -L 1
• use filename as parameter. This will allow escapping it, which is needed if the file contains spaces or symbols: -I filename ... "filename"
• use ls to list the file. this will print to stderr when the file is not found, so redirect it to /dev/null

So you can pipe the output of sqlite to:
xargs -L 1 -I filename ls "filename" 2>/dev/null

If you're using mysql rather than sqlite as the storage for kodi, the default behavior of mysql doesn't support || for concatenation. Either do:

SET sql_mode=(SELECT CONCAT(@@sql_mode,',PIPES_AS_CONCAT'));

first, or change the query to use CONCAT(p.strPath, f.strFilename) instead of p.strPath || f.strFilename:

select CONCAT(p.strPath, f.strFilename)
from files f
join path p
    on p.idPath = f.idPath
where f.playCount > 0;