Expanded statistics section

[lovebags]

Various iterations of this request have been made before but there may be also be others ways to get this data that I am unaware of.
I would really like to be able to extract/view more detailed statistics kind of in a google analytics way. For example...

- 'Most played' is useful but having the ability to view this over a date range and also be able export the data as a csv file would be great.
- 'Users' currently has graphical data with approximate MB listing, however being able to export this would be useful to enable an overall total MB for all users (and also over time).

Perhaps these things could be expanded from the log file with some sort of script? However, I've also noticed that whenever the server is restarted the log file is erased so you'd need to do some sort of daily log file analysis. Our server now supports around 350 users and handles about 50,000 requests every 6 month period so the more data we can get the better.

[lovebags]

Have found a workaround to this expanding on a post from late last year http://forum.subsonic.org/forum/viewtopic.php?f=5&t=8553&hilit=user+statistics
This text typed into the db.view query box will yield an ordered list of MB streamed by user
select USERNAME,SUM(BYTES_STREAMED) as total_streamed from user GROUP BY USERNAME ORDER BY total_streamed DESC
Am still looking into this but as for a date range, it appears that the database only stores the date of the last played track. So to get a monthly play count I would need to run the query at the end of each month and compare.
Hoping to make some more progress with this over the next few months with the help of a friend who knows SQL much better than me!

[hakko]

Just out of curiosity, have you considered automating the gathering of statistics?

I'd guess it would be fairly easy to have the nightly update script insert (userid, bytes_streamed, current date) into a separate table, for example.

Also, the "group by username" doesn't really make sense here but I guess you know that already. It only would if you actually had a date column.

[bushman4]

Actually, in the case of an LDAP system, the group by username feature should be used, but it should be cased somehow, like LCase(Username).

Since Subsonic has case sensitive usernames, it creates a new username for each different capitalization iteration that a user uses (if the LDAP source is not case sensitive). For example, if I log on as "GSullivan" one day and "gsullivan" another and "gSullivan" another, the LDAP will say that they are all valid username, but subsonic will create three different subsonic users for me.

Wonky, I know, but that is what happens.

Glenn

[BKKKPewsey]

I think there was a feature request some time ago on removing the case sensitivity from user login.
Mind you SS is not the only app to have that "problem"

[lovebags]

Just out of curiosity, have you considered automating the gathering of statistics?

Yes this is exactly what I'm aiming to achieve.

Actually, in the case of an LDAP system, the group by username feature should be used, but it should be cased somehow, like LCase(Username).

Ah yes, I've only just recently noticed this as we use LDAP and have some instances of multiple usernames of the same user due to inconsistant logging in entries. Not a massive problem from our point of view but certainly worth noting.

[lovebags]

I've put together a couple of db.view queries that provide total GB streamed as well as total play counts...

Total GB streamed for all users
select SUM (BYTES_STREAMED)/1000000 AS "Total GB Streamed" from user

Total play count for all users
select SUM (PLAY_COUNT) AS "Total Plays" from MUSIC_FILE_INFO

I'd guess it would be fairly easy to have the nightly update script insert (userid, bytes_streamed, current date) into a separate table, for example.

I'd be very interested in finding out what tool would be best to automate this sort of query, and can subsonic be easily queried externally given the authentication requirements?

[hakko]

Should be 1 048 576 for GB, not 1 000 000 (1024*1024)

I was thinking about it the other day. I see two ways of doing it:

1. Just change the Subsonic code. Create a new table (via db.view), holding username + bytes_streams + current_date.
Change SearchService so that every night when it scans your folders, it also calls a new method that you write in SettingsService which in turn executes something like this on it's UserDao object:
insert username, bytes_streamed, current_date
select username, bytes_streamed, getdate() from user

(not sure about exact sql syntax for "get current date" for hsqldb)

If you want a better sql layout, you should create a mapping table holding user_id + username, so that username isn't repeated over and over in your added table.

2. Write an external program that basically does the sql statement above, once per night. The easiest thing would probably be to write a Java program so that you can speak JDBC with the HSQLDB database (everything's stored in just one file, called "subsonic" in the "db" directory). I'm not sure about whether it locks this file for just one process to access it though? You'd have to experiment on that. Run the external program as a cron job.


Evaluation:
pros for 1: straight forward, minimal impact on your system, just a small added statement to the Subsonic code. probably the fastest way to get something working
cons for 1: it will make it harder to update to 4.7 and so on, the more you alter the Subsonic code.

pros for 2: you can update to 4.7 any day you'd like (just check that the user table isn't structurally changed). more flexibility on scheduling.
cons for 2: the locking issue (dunno about this though).

If I was on a *ix system, I'd probably go for #2 to see if the locking is an issue.

[lovebags]

Should be 1 048 576 for GB, not 1 000 000 (1024*1024)

Ah yes, and then I also realised I was also out by a factor of 1000 so I have now changed it to...
select SUM (BYTES_STREAMED)/1073741824 AS "Total GB Streamed" from user
Thanks for the tips! The external option would be good if it could be done without affecting the system but will also look into the first option as well.

[hakko]

I accidentally noticed today that it seems like the database file gets locked by one process, which isn't super surprising. That would mean it gets harder to access it by an external process, at least via JDBC.

Another idea that came to my head is accomplishing your goal by an automatic HTTP post (invoked by an external, nightly process) to db.view.

Use some library like curl to fake a login to Subsonic, followed by a post to db.view, issuing something like "insert into my_stat_table (bytes_streamed, username, logdate) select bytes_streamed, username, $(currentDate) from user". You could (pretty) easily build a shell script doing that and make it run once per night. That's probably the easiest solution too. And it's quite future-proof and everything.