How to combine watch history from two servers using sqlite?

Plex Media Server Metadata & Adding Files
8

The following should work-

$ sqlite3 com.plexapp.plugins.library.db
then

sqlite> .mode insert
sqlite> .output watch.sql
sqlite> SELECT account_id,
       guid,
       rating,
       view_offset,
       view_count,
       last_viewed_at,
       created_at,
       updated_at,
       skip_count,
       last_skipped_at,
       changed_at,
       extra_data,
       last_rated_at
  FROM metadata_item_settings;

will give you a file watch.sql with the auto incrementing index value removed

INSERT INTO "table" VALUES(133633,'plex://movie/5d77684e61141d001fb18417',NULL,NULL,1,'2020-09-14 23:46:29','2020-09-14 22:38:37','2020-09-14 23:46:29',0,NULL,3304454,'',NULL);
INSERT INTO "table" VALUES(133633,'plex://movie/5d776b56594b2b001e6d6f40',NULL,NULL,1,'2020-09-18 22:44:18','2020-09-18 21:30:35','2020-09-18 22:44:18',0,NULL,3362344,'',NULL);
INSERT INTO "table" VALUES(133633,'plex://movie/5d776b0c9ab544002150686d',NULL,NULL,1,'2020-09-20 13:00:24','2020-09-20 10:24:52','2020-09-20 13:00:24',0,NULL,3452489,'',NULL);
INSERT INTO "table" VALUES(133633,'plex://movie/5d776d3bfb0d55001f59acea',NULL,NULL,1,'202I0-10-01 17:11:49','2020-09-20 13:38:28','2020-10-01 17:11:49',0,NULL,4118422,'',NULL);

Do that for the local and remote DBs
combine the watch.sql files into one and rename “table” to “metadata_item_settings”

empty all the watched info from the DESTINATION DB

sqlite3 com.plexapp.plugins.library.db "DELETE FROM metadata_item_views;"
sqlite3 com.plexapp.plugins.library.db "DELETE FROM metadata_item_settings;"
sqlite3 com.plexapp.plugins.library.db "DELETE FROM statistics_bandwidth;"
sqlite3 com.plexapp.plugins.library.db "DELETE FROM statistics_media;"
sqlite3 com.plexapp.plugins.library.db "DELETE FROM statistics_resources;"
sqlite3 com.plexapp.plugins.library.db "DROP INDEX index_title_sort_naturalsort;"

and them import the combined watch.sql file

Done !

Now make sure you create a copy of the DBs before doing any of this

2 Likes