Sonarr: Indexer stats

Created on 17 Sep 2013 · 20Comments · Source: Sonarr/Sonarr

It would be helpful to list how much a particular indexer is used, especially when considering to donate to a site a second time around.

Response time/errors could also be tracked to give an overview of the indexers health over a period of time to see if its worth keeping.

enhancement low

Source

SonarrBot

Most helpful comment

It is possible to do this manually. If you only want to get the results say once every 6 months when reviewing which indexers to keep donating to.
From Reddit: https://www.reddit.com/r/usenet/comments/3rq3rf/does_sonarr_record_which_indexer_an_nzb_was/cwu2e41

Thanks for that. I work with SQL daily and I can see your query doesn't need to have a subquery in it. I've simplified it quite a bit, like this:
http://pastebin.com/564K3qhn
For those wondering, to run this, get a copy of the nzbdrone.db file from C:ProgramDataNzbDrone if you're on Windows.
Then download the SQL Lite Browser from here http://sqlitebrowser.org/
Run the program, open the database, and run that query in the "Execute SQL" tab. This is mine: http://i.imgur.com/3OHyFxC.png

Offrinna01 on 15 Sep 2016

👍2

All 20 comments

Comment from andrekolmeijer on Trello:

Would be awesome! Something with download stats per indexer would be really nice. Maybe just a display of a simple chart or something like that. Would be nice to be able to see which indexers are used mostly vs. which ones are inactive or not working anymore.

SonarrBot on 24 Jun 2014

Comment from markus101 on Trello:

@andrekolmeijer why did you make this exact same comment on two separate card? One being this one, the correct one and another being one that has indexer in the title. (I've removed your comments that do not add value to the card), you can use the voting feature to cast your vote.

SonarrBot on 24 Jun 2014

Comment from m0ul on Trello:

this would be useful for deciding to keep a indexer.

SonarrBot on 1 Dec 2014

Comment from trash71 on Trello:

ditto,
i got now a collection of several indexers.
if sonarr would keep a history /statics of which indexers are being used i could dump the indexers that are hardly or never used anyway.

SonarrBot on 3 Apr 2015

Is there any way to increase the priority of this? It would be very useful for helping users identify which indexers are worth keeping around and which premium indexers are worth paying for. This could lead to decreased rss processing overhead from by helping users identify indexers which are seldom used, and removing them.

thegr8brian on 30 Jul 2015

Could this be as simple as adding a line to the History tab with the name of the indexer that we grabbed the file from? Graphs/Charts while smart in their own way, are perhaps excessive; but allowing users to see where the download has come from is useful

baz1860 on 30 Jul 2015

You can view the indexer by hovering over the grabbed icon (left column) on history already, we'e not going to add a column since it only applies to one data type.

No plans to alter the priority at this time.

markus101 on 30 Jul 2015

I'm familiar with the per grab history on hover which is useful in it's own way, but I think what's missing is an aggregate view per indexer. This could also be useful for seeing if you are reaching api limits for a given indexer. I agree that graphs and visualizing the data may be overkill. Even response time averages would be more of a nice to have for me.

My guess is that most users would appreciate a simple count of the number of times an indexer was used for a grab and perhaps a % uptime to highlight unreliable indexers.

thegr8brian on 30 Jul 2015

You're not going to easily see the number of API calls made to a single indexer based on grabs alone and we don't currently track each API call made to an indexer, though we are starting to track errors.

Visualization would be a bonus, but I think we'd be looking at a numerical representation to start.

markus101 on 31 Jul 2015

If the indexer used is already stored in history per grab, would all the data be available to see a simple numerical # grabs per indexer? This is the piece of information of most value for identifying an indexers usefulness.

thegr8brian on 31 Jul 2015

Ugly but works:

[Wed Jun 17 23:57.10] <Taloth> SELECT SUBSTR(SUBSTR(h.Data,INSTR(h.Data,'indexer') + 11), 0, INSTR(SUBSTR(h.Data,INSTR(h.Data,'indexer') + 11), '"')) AS Indexer, MAX(h.Date) as Last, COUNT(DISTINCT h.DownloadId) as SuccesfullDownloads
[Wed Jun 17 23:57.10] <Taloth> FROM History h JOIN History s ON h.DownloadId = s.DownloadId WHERE h.EventType = 1 AND s.EventType = 3
[Wed Jun 17 23:57.10] <Taloth> GROUP BY Indexer
[Wed Jun 17 23:57.37] <Taloth> i'm almost throwing up after that SUBSTR, but hey, it works.

Taloth on 31 Jul 2015

Offrinna01 on 15 Sep 2016

👍2

definitely would be great to have just grabbed/failed for each indexer

spooky69 on 16 Mar 2017

👍1

@Taloth your query took 17+ min on keith's db before I killed it. Tried @Offrinna01 query, it took less than a sec:

SELECT SUBSTR(DATA, INSTR(DATA, '"indexer": ') + 12, INSTR(SUBSTR(DATA, INSTR(DATA, '"indexer": ') + 12), '",') - 1) AS IndexerName, COUNT(*) AS GrabCount FROM History WHERE DATA LIKE '%indexer%' GROUP BY IndexerName ORDER BY GrabCount DESC;

thezoggy on 16 Feb 2018

Duh. I did say it was a disgusting query. 🤢 The statistics PR does a bit more, but I never finished it and kinda was like 'lets get v3 out first'.

Taloth on 16 Feb 2018

How would one extend this query to include failures from said indexers...

On Feb 16, 2018 9:06 AM, "Taloth" notifications@github.com wrote:

Duh. I did say it was a disgusting query. 🤢 The statistics PR does a bit
more, but I never finished it and kinda was like 'lets get v3 out first'.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/Sonarr/Sonarr/issues/323#issuecomment-366260287, or mute
the thread
https://github.com/notifications/unsubscribe-auth/AAekksiDcSzJJ4jfHPs_ebYujQB0Movpks5tVZlzgaJpZM4Fi8z1
.