How to log user playback of video files?

[Bosse_B]

I am using RPi4 devices with OSMC Kodi.
The video sources are mp4 files in a server side directory tree with similar files in separate directories.
The head of the tree is mounted by nfs to the RPi4 device and the /.kodi/userdata/sources.xml file contains the structure of how the list of files is displayed to the user.
The server side files are regularly updated (old files vanish and new are added) so the sources.xml holds directory links and Kodi will populate the display with the video files therein when the user navigates there.

Now I would like to have the user viewing start action logged into a file where there is a timestamp (ISO format) and the name of the file being viewed. Like so:
2025-04-05 16:35 Videofilename.mp4

Is this already available (if so how do I enable it and where is the logfile) or can it be done? If so how?

I previously offered the videos in a web access format and there I could add a logging function upon view start, but now I have moved to OSMC/Kodi for user ease of use etc. And I am now missing the statistics…
TIA

[sam_nazarko]

Hi,

@Bosse_B

Now I would like to have the user viewing start action logged into a file where there is a timestamp (ISO format) and the name of the file being viewed. Like so:
2025-04-05 16:35 Videofilename.mp4

Is this already available (if so how do I enable it and where is the logfile) or can it be done? If so how?

I think you should pay a professional developer for this. It’s trivial; but not something we’re going to assist with directly.

From your post history it’s pretty clear that this is not being used at your summer house anymore but rather some commercial environment.

[Bosse_B]

@sam_nazarko

From your post history it’s pretty clear that this is not being used at your summer house anymore but rather some commercial environment.

Wrong.
I still use the 2 OSMC systems sitting at the summer home, but obviously not now during wintertime…

I am retired from my job in electronics development and programming since 14 years and this new OSMC installation is intended for my old business associate who is also retired (80+ years).
The reason for the logging question is that I want to be able to check for how long to keep the videos, i.e. when he has viewed them I could purge them.
When he used my web access system I got the logging via the php functions serving out the video stream.
But it was a less than optimal solution and with OSMC he can use his living room TV rather than a small laptop screen.

So I have built this OSMC instance with an OpenVPN client that will connect back to my home LAN if the video share is not directly accessible (like it is when testing at my own LAN). After connection it will mount the video share and KODI can then show the videos.

But I don’t want to keep the videos longer than necessary so a log of which has been viewed would be useful.
Maybe the logging could be done on the Ubuntu server hosting the videos at my home, but I don’t know how that could happen since it is just an nfs file share as seen from the server and hard to see from where the request is coming…
The only process that is actively handling the video file viewed by him is the KODI mediaserver on the OSMC box AFAICT.

But it might be a not so simple task to implement, I had hoped there would be some kind of built-in function I could hook in to.

[fzinken]

@Bosse_B

i.e. when he has viewed them I could purge them.

Isn’t that the function of the “viewed” field in the Kodi Database

[JimKnopf]

Simple idea

$ sqlite3
sqlite> .open /home/osmc/.kodi/userdata/Database/MyVideos131.db
sqlite> select [c00],[c22] from [movie_view] where [playcount] >= 1;
sqlite> select [c00],[c18] from [episode_view] where [playcount] >= 1;
sqlite> .quit

[Bosse_B]

@JimKnopf

select [c00],[c18] from [episode_view] where [playcount] >= 1;

I am assuming that something will happen when using these commands…
But in my case this happens (ll is an alias for my most frequent ls -la with “sensible” timestamp format):

osmc@osmc-rpi4:~/.kodi/userdata/Database$ ll
total 4396
drwxr-xr-x  3 osmc osmc    4096 2025-04-07 20:07 .
drwxr-xr-x 10 osmc osmc    4096 2025-02-24 12:47 ..
-rw-r--r--  1 osmc osmc 1339392 2025-04-07 20:07 Addons33.db
drwxr-xr-x  2 osmc osmc    4096 2022-05-26 11:21 CDDB
-rw-r--r--  1 osmc osmc   28672 2022-03-13 21:37 Epg13.db
-rw-r--r--  1 osmc osmc   32768 2023-09-26 17:18 Epg16.db
-rw-r--r--  1 osmc osmc  225280 2022-03-13 21:37 MyMusic82.db
-rw-r--r--  1 osmc osmc  225280 2024-08-25 11:14 MyMusic83.db
-rw-r--r--  1 osmc osmc  405504 2023-06-18 18:44 MyVideos119.db
-rw-r--r--  1 osmc osmc  466944 2024-08-14 22:06 MyVideos121.db
-rw-r--r--  1 osmc osmc 1150976 2025-04-07 18:04 MyVideos131.db
-rw-r--r--  1 osmc osmc   49152 2022-03-13 21:37 TV38.db
-rw-r--r--  1 osmc osmc   57344 2023-09-26 17:18 TV40.db
-rw-r--r--  1 osmc osmc   57344 2024-08-25 11:14 TV46.db
-rw-r--r--  1 osmc osmc  421888 2025-04-07 17:16 Textures13.db
-rw-r--r--  1 osmc osmc   20480 2022-03-13 21:37 ViewModes6.db
osmc@osmc-rpi4:~/.kodi/userdata/Database$ sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open MyVideos131.db
sqlite> select [c00],[c22] from [movie_view] where [playcount] >= 1;
sqlite> select [c00],[c18] from [episode_view] where [playcount] >= 1;
sqlite> .quit

Since nothing happened on my commands it seems like the data is not there (I selected the db file with the most recent timestamp)…

But I don’t know how info about these downloaded and edited mp4 video files are getting into the database…

[JimKnopf]

You are sure that there are movies already watched?

[Bosse_B]

I am using OSMC myself to view these “movies”. So they are definitely viewed constantly.
At least by me…

They are not “Cinema Movies”, but rather downloads from Internet live streaming video that I have a process on my Ubuntu server to get and save on disk as mp4 formatted video files. That is done using ffmpeg.
Each such file is 65 minutes long and is viewed via KODI from a file folder selection.

But I don’t know how they should wind up in a database, they are put in the sources.xml file as directories (from an nfs mount to the file server) containing the video files and KODI will list them when I navigate the directories to them. It is just a traverse of a folder system on disk…

[JimKnopf]

@Bosse_B

Each such file is 65 minutes long and is viewed via KODI from a file folder selection.

That seems to be the point. These videos are not scraped into the movie db? Is it by just your used naming convention which prevents this or is this private video material?
I apologize for this misunderstanding that we are not talking about commercial films, then.

[Bosse_B]

I have never understood the concept of “scraping” in regard to private video files…
To me the system is a player of the video files located on a server connected to via the LAN.
Nothing more or less.
There is no information about these videos to be had on the Internet or elsewhere, it is like a Super8 film of the kids from 1981 having been converted to electronic format as a file. That does not even have sound…
But these are downloaded from the internet in real time.
Regarding the file naming they are named as:
2025-04-05_19_Friendly_Name_of_source_stream.mp4
Where the initial timestamp part is when the recording started.

PS: The server ALSO hosts all of these converted old Super8 and SonyCamera and cellphone videos of the family… Also impossible to “scrape”. DS

[fzinken]

@Bosse_B

But I don’t know how they should wind up in a database,

They are in the database (not movies but files) as long as you add them as source (type none).
I don’t have a local database on hand, but I think this should work.

select [strFilename] from [files] where [playcount] >= 1;

[Bosse_B]

@fzinken

They are in the database (not movies but files) as long as you add them as source (type none).
I don’t have a local database on hand, but I think this should work.

select [strFilename] from [files] where [playcount] >= 1;

Thanks, nice!
This gave the output, basically a list of files…

Question #1:
Is there a timestamp also associated with the logging or is there one record for each file where playcount gets incremented for each play start?
Would be nice to be able to weed out old play instances…

Question #2:
Can the sqlite call be scripted (bash) so as to create a display on starting a script.
The command I used from above involves getting an interactive prompt and typing in commands…

[Bosse_B]

Progress:
So I checked the schema of the [files] table and found out how to make the sqlite call show the timestamp of the view too:

sqlite> select  [lastPlayed], [strFilename] from  [files] where [playcount] >= 1;

The result is a list of files played with the last timestamp up front.

So how can I embed this command into a bash script in a way that would only display the final file list on screen? Is it even possible?
Or do I need something like python (which I am not versed in) to do it?

[Bosse_B]

So I found out how to go about it using bash.
Here is a working script for reference, which shows the 20 latest entries:

#!/bin/bash

cmd="sqlite3 /home/osmc/.kodi/userdata/Database/MyVideos131.db"
sql="select  [lastPlayed], [playcount], [strFilename] from  [files] where [playcount] >= 1 order by [lastPlayed];"
$cmd "$sql" | tail -n 20
exit

[Bosse_B]

So I have verified that my script using the sqlite3 commend does work.
But it is unclear to me what is the requirement for a video to get into the database?
I started a video and moved up about 10-15 min, but it was not recognized.
But when I jumped to close to the end and then exited, then it did show up in the script output.
So it seems like there is a threshold somewhere for it to be added. My two tests show that one has to be within 4-5 minutes of the end when exiting in order for it to be on the database list.
Is there a configuration for this threshold?
Or is there a different database entry/marker for videos that have been started but not completely watched?
I tried to set the limitation in my script to 0 but that did not change the output.
Seems like a video does not get into the database until it has been almost completely watched…

[darwindesign]

@Bosse_B

But it is unclear to me what is the requirement for a video to get into the database?

There is an entry in the database somewhere for playback time status that is seperate from play count.

@Bosse_B

So it seems like there is a threshold somewhere for it to be added. My two tests show that one has to be within 4-5 minutes of the end when exiting in order for it to be on the database list.
Is there a configuration for this threshold?

It is a percentage. There are settings for the threshold of how much you have to have watched before it marks it as partially watched and sets the time counter, as well as a separate setting for how much of a show you have to have watched before it advances the play count. Both are set via an advancedsettings.xml file and can be found in Kodi’s wiki for that file.

[Bosse_B]

Thank you very much for this information!
The Kodi advancedsettings wiki page is most useful and now I am able to customize some of the annoyances I have found when using KODI!
Now added to my KODI/OSMC notes.