Deeper Analytics for multi user systems - without hitting the SQLLITE file.

[postnick]

I serve to a few people in my family and I also manage my libraries on different storage methods (My issue due to space/cost issue.)

GOAL

Identify books that have been read per user so i can work on my archive or cleanup processes. But i see there is no way in the main web interface at this time.

CURRENT PROCESS

I copy my ABSdatabase.sqlite and copied it to a local machine first and began querying the database file. I'll provide some examples after this initial post.

DESIRED OUTPUT

I'd like to have a way to generate better analytic inside of the tool, or even try to create my own metrics easier on live data without having to copy the database file.

[postnick]

Example SQL for some high level data. This is quickly thrown together and not in my ideal formatting yet.

But it shows me my title, author, some stats on length, and I try to use the amount of time listened vs total length to infer it's finished rather than rely on the 'isfinished' flag in the mediaProgresses table. This 98% can be adjusted i'm sure. This also gets around where I may have marked as finished without ever playing the file because I didn't know what I was doing int he early days and set this up a few times before getting my current server in place.

SELECT 
    u.username,
    li.title AS book_title,
    li.authorNamesFirstLast,

    p.isFinished,
  case 
    when isFinished = 1 or (p.currentTime / p.duration)*100 > 98 THEN 'Finished'
    else 'Not Finished'
  end as Status ,
    round(b.duration,0) as Book_Time,
    round(p.duration,0) as Progress_Book_Time,
    round(p.currentTime,0) as Current_Progress_Time,
    round(p.duration - p.currentTime,0) as time_remain,
    round(p.currentTime / p.duration,4)*100 as percent_done

FROM mediaProgresses p
JOIN users u ON p.userId = u.id
JOIN libraryItems li ON p.mediaItemId = li.mediaId
join books b on b.id = li.mediaId
WHERE p.isFinished in (0,1)

order by 
  li.title,
  round(p.currentTime / p.duration,4)*100 desc