Last active
December 4, 2022 02:51
-
-
Save ThePeePs/bdb443f62173dcdae06297b843ab2a3a to your computer and use it in GitHub Desktop.
Munin Plugin for nZEDb (WIP)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/perl | |
=encoding utf8 | |
=head1 NAME | |
nzedb_ - Munin plugin to display misc nzedb stats. | |
=head1 CONFIGURATION | |
This script is used to generate data for several graphs. To generate | |
data for one specific graph, you need to create a symbolic link with a | |
name like nzedb_<GRAPH> to this script. | |
To get a graph over numbers of users use nzedb_users | |
=head1 APPLICABLE SYSTEMS | |
Any MySQL platform, tested by the author on MySQL 5.1.29 and 5.0.51 | |
=head1 CONFIGURATION | |
This script is used to generate data for several graphs. To generate | |
data for one specific graph, you need to create a symbolic link with a | |
name like nzedb_<GRAPH> to this script. | |
connection parameters - use this in your plugin configuration file. | |
[nzedb_*] | |
env.mysqlconnection DBI:mysql:<yourdatabase>;host=127.0.0.1;port=3306 | |
env.mysqluser <user> | |
env.mysqlpassword <password> | |
When createing syminks in your servicedir, use the following: | |
nzedb_releases Graph of current nuber of releases | |
nzedb_category Graph of current number of releases per category | |
nzedb_category_short Graph of current number of releases per parent category | |
nzedb_api Graph of API requests and NZB downloads | |
nzedb_users Graph of current registered user accounts | |
nzedb_cbp Graph of current number of Collections, Binaries, and Parts | |
nzedb_predb Graph of current PreDB tables, and the number that has been matched. | |
=head1 DEPENDENCIES | |
=over | |
=item DBD::mysql | |
=back | |
=head1 THANKS | |
Code Originally writen by Jan Astrup (cryzeck@synIRC), adapted by ThePeePs | |
for nZEDb. This is still a work in progress. | |
A special thanks to Kjell-Magne Øierud for the mysql_ plugin in munin which | |
gave me the inspiration and reusable code to create this plugin. | |
=head1 LICENSE | |
Copyright (C) 2012 Jan Astrup (cryzeck@synIRC) | |
This program is free software; you can redistribute it and/or modify | |
it under the terms of the GNU General Public License as published by | |
the Free Software Foundation; version 2 dated June, 1991. | |
This program is distributed in the hope that it will be useful, but | |
WITHOUT ANY WARRANTY; without even the implied warranty of | |
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU | |
General Public License for more details. | |
You should have received a copy of the GNU General Public License | |
along with this program; if not, write to the Free Software | |
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, | |
USA. | |
=cut | |
use warnings; | |
use strict; | |
use utf8; | |
use DBI; | |
use Data::Dumper; | |
use File::Basename; | |
use Munin::Plugin; | |
#-- CONFIG --# | |
my %config = ( | |
'dsn' => $ENV{'mysqlconnection'} || 'dbi:mysql:nzedb', | |
'user' => $ENV{'mysqluser'} || 'test', | |
'password' => $ENV{'mysqlpassword'} || 'test1', | |
); | |
my %defaults = ( | |
global_attrs => { | |
args => '--base 1000 -l 0', | |
scale => 'no', | |
}, | |
data_source_attrs => { | |
draw => 'AREA', | |
}, | |
); | |
my %graphs = (); | |
$graphs{releases} = { | |
config => { | |
global_attrs => { | |
title => 'Releases', | |
vlabel => 'Releases', | |
}, | |
data_source_attrs => { | |
min => '0', | |
}, | |
}, | |
data_sources => [ | |
{name => 'releases', label => 'Releases'}, | |
{name => 'nfo', label => 'Releases with NFO', draw => 'LINE1'}, | |
{name => 'renamed', label => 'Releases Renamed', draw => 'LINE1'}, | |
], | |
}; | |
$graphs{category} = { | |
config => { | |
global_attrs => { | |
title => 'Releases by Category', | |
vlabel => 'Releases', | |
}, | |
data_source_attrs => { | |
min => '0', | |
draw => 'LINE1', | |
type => 'GAUGE', | |
}, | |
}, | |
data_sources => [ | |
{name => '3000', label => 'Audio'}, | |
{name => '3010', label => 'Audio - MP3'}, | |
{name => '3020', label => 'Audio - Video'}, | |
{name => '3030', label => 'Audio - Audiobook'}, | |
{name => '3040', label => 'Audio - Lossless'}, | |
{name => '3060', label => 'Audio - Foreign'}, | |
{name => '3999', label => 'Audio - Other'}, | |
{name => '7000', label => 'Books'}, | |
{name => '7010', label => 'Books - Magazines'}, | |
{name => '7020', label => 'Books - Ebook'}, | |
{name => '7030', label => 'Books - Comics'}, | |
{name => '7040', label => 'Books - Technical'}, | |
{name => '7060', label => 'Books - Foreign'}, | |
{name => '7999', label => 'Books - Other'}, | |
{name => '1000', label => 'Console'}, | |
{name => '1010', label => 'Console - NDS'}, | |
{name => '1020', label => 'Console - PSP'}, | |
{name => '1030', label => 'Console - Wii'}, | |
{name => '1040', label => 'Console - Xbox'}, | |
{name => '1050', label => 'Console - Xbox 360'}, | |
{name => '1060', label => 'Console - WiiWare/VC'}, | |
{name => '1070', label => 'Console - XBOX 360 DLC'}, | |
{name => '1080', label => 'Console - PS3'}, | |
{name => '1110', label => 'Console - 3DS'}, | |
{name => '1120', label => 'Console - PS Vita'}, | |
{name => '1130', label => 'Console - WiiU'}, | |
{name => '1140', label => 'Console - Xbox One'}, | |
{name => '1180', label => 'Console - PS4'}, | |
{name => '1999', label => 'Console - Other'}, | |
{name => '0', label => 'Other'}, | |
{name => '10', label => 'Other - Misc'}, | |
{name => '20', label => 'Other - Hashed'}, | |
{name => '2000', label => 'Movies'}, | |
{name => '2010', label => 'Movies - Foreign'}, | |
{name => '2030', label => 'Movies - SD'}, | |
{name => '2040', label => 'Movies - HD'}, | |
{name => '2045', label => 'Movies - UHD'}, | |
{name => '2050', label => 'Movies - 3D'}, | |
{name => '2060', label => 'Movies - BluRay'}, | |
{name => '2070', label => 'Movies - DVD'}, | |
{name => '2080', label => 'Movies - WEB-DL'}, | |
{name => '2999', label => 'Movies - Other'}, | |
{name => '4000', label => 'PC'}, | |
{name => '4010', label => 'PC - 0day'}, | |
{name => '4020', label => 'PC - ISO'}, | |
{name => '4030', label => 'PC - Mac'}, | |
{name => '4050', label => 'PC - Games'}, | |
{name => '4060', label => 'PC - Phone-IOS'}, | |
{name => '4070', label => 'PC - Phone-Android'}, | |
{name => '4999', label => 'PC - Phone-Other'}, | |
{name => '5000', label => 'TV'}, | |
{name => '5010', label => 'TV - WEB-DL'}, | |
{name => '5020', label => 'TV - Foreign'}, | |
{name => '5030', label => 'TV - SD'}, | |
{name => '5040', label => 'TV - HD'}, | |
{name => '5045', label => 'TV - UHD'}, | |
{name => '5060', label => 'TV - Sport'}, | |
{name => '5070', label => 'TV - Anime'}, | |
{name => '5080', label => 'TV - Documentary'}, | |
{name => '5999', label => 'TV - Other'}, | |
{name => '6000', label => 'XXX'}, | |
{name => '6010', label => 'XXX - DVD'}, | |
{name => '6020', label => 'XXX - WMV'}, | |
{name => '6030', label => 'XXX - XviD'}, | |
{name => '6040', label => 'XXX - x264'}, | |
{name => '6045', label => 'XXX - UHD'}, | |
{name => '6060', label => 'XXX - Imageset'}, | |
{name => '6070', label => 'XXX - Packs'}, | |
{name => '6080', label => 'XXX - SD'}, | |
{name => '6090', label => 'XXX - WEB-DL'}, | |
{name => '6999', label => 'XXX - Other'}, | |
], | |
}; | |
$graphs{category_short} = { | |
config => { | |
global_attrs => { | |
title => 'Releases by Parent Category', | |
vlabel => 'Releases', | |
}, | |
data_source_attrs => { | |
min => '0', | |
draw => 'LINE1', | |
type => 'GAUGE', | |
}, | |
}, | |
data_sources => [ | |
{name => 'audio', label => 'Audio'}, | |
{name => 'books', label => 'Books'}, | |
{name => 'console', label => 'Console'}, | |
{name => 'misc', label => 'Misc'}, | |
{name => 'movies', label => 'Movies'}, | |
{name => 'pc', label => 'PC'}, | |
{name => 'tv', label => 'TV'}, | |
{name => 'xxx', label => 'XXX'}, | |
], | |
}; | |
$graphs{api} = { | |
config => { | |
global_attrs => { | |
title => 'API Requests / Downloads', | |
vlabel => 'API / DOWNLOAD', | |
}, | |
data_source_attrs => { | |
min => '0', | |
draw => 'LINE1', | |
type => 'GAUGE', | |
}, | |
}, | |
data_sources => [ | |
{name => 'request', label => 'API Requests'}, | |
{name => 'download', label => 'Downloads'}, | |
], | |
}; | |
$graphs{cbp} = { | |
config => { | |
global_attrs => { | |
title => 'Collections, Binaries, and Parts Counts', | |
vlabel => 'Number of Records', | |
args => '--base 1000 --logarithmic' | |
}, | |
data_source_attrs => { | |
min => '0', | |
draw => 'AREA', | |
type => 'GAUGE', | |
}, | |
}, | |
data_sources => [ | |
{name => 'col', label => 'Collections'}, | |
{name => 'bin', label => 'Binaries', draw => 'STACK'}, | |
{name => 'part', label => 'Parts', draw => 'STACK'}, | |
{name => 'missed', label => 'Missing Parts', draw => 'LINE1'}, | |
{name => 'repair', label => 'Parts Repair', draw => 'LINE1'}, | |
], | |
}; | |
$graphs{users} = { | |
config => { | |
global_attrs => { | |
title => 'Registered Users', | |
vlabel => 'Usercount', | |
}, | |
data_source_attrs => { | |
min => '0', | |
}, | |
}, | |
data_sources => [ | |
{name => 'users', label => 'Users'}, | |
], | |
}; | |
$graphs{predb} = { | |
config => { | |
global_attrs => { | |
title => 'PreDB', | |
vlabel => 'Count', | |
args => '--base 1000 --logarithmic', | |
}, | |
data_source_attrs => { | |
min => '0', | |
}, | |
}, | |
data_sources => [ | |
{name => 'punmatched', label => 'Unmatched PreDB'}, | |
{name => 'pmatched', label => 'Matched Pr-eDB', draw => 'LINE1'}, | |
], | |
}; | |
our $data; | |
sub config { | |
my $graph_name = shift; | |
die 'Unknown graph ' . ($graph_name ? $graph_name : '') | |
unless $graphs{$graph_name}; | |
my $graph = $graphs{$graph_name}; | |
my %conf = (%{$defaults{global_attrs}}, %{$graph->{config}{global_attrs}}); | |
while (my ($k, $v) = each %conf) { | |
print "graph_$k $v\n"; | |
} | |
print "graph_category nzedb\n"; | |
my $i = 0; | |
for my $ds (@{$graph->{data_sources}}) { | |
my %ds_spec = ( | |
%{$defaults{data_source_attrs}}, | |
%{$graph->{config}{data_source_attrs}}, | |
%$ds, | |
); | |
while (my ($k, $v) = each %ds_spec) { | |
# 'name' is only used internally in this script, not | |
# understood by munin. | |
next if ($k eq 'name'); | |
if ($k eq 'draw' && $v eq 'AREASTACK') { | |
printf("%s.%s %s\n", | |
clean_fieldname($ds->{label}), $k, ($i ? 'STACK' : 'AREA')); | |
} | |
else { | |
printf("%s.%s %s\n", clean_fieldname($ds->{label}), $k, $v); | |
} | |
$i++; | |
} | |
} | |
return 0; | |
} | |
sub main { | |
my $graph = substr(basename($0), length('nzedb_')); | |
my $command = $ARGV[0] || 'show'; | |
my %commands = ( | |
'config' => \&config, | |
'show' => \&show, | |
); | |
die "Unknown command: $command" unless exists $commands{$command}; | |
return $commands{$command}->($graph); | |
} | |
sub show { | |
my $graph_name = shift; | |
die 'Unknown graph ' . ($graph_name ? $graph_name : '') | |
unless $graphs{$graph_name}; | |
my $graph = $graphs{$graph_name}; | |
run_queries($graph_name); | |
for my $ds (@{$graph->{data_sources}}) { | |
printf "%s.value %s\n", clean_fieldname($ds->{label}), ($data->{$ds->{name}} ? $data->{$ds->{name}} : '0'); | |
} | |
return 0; | |
} | |
sub db_connect { | |
my $dsn = "$config{dsn};mysql_connect_timeout=5"; | |
return DBI->connect($dsn, $config{user}, $config{password}, { | |
RaiseError => 1, | |
PrintError => 0, | |
FetchHashKeyName => 'NAME_lc', | |
}); | |
} | |
sub run_queries { | |
my $updater = shift; | |
$data = {}; | |
my $dbh = db_connect(); | |
my %updaters = ( | |
'releases' => \&update_releases, | |
'users' => \&update_users, | |
'api' => \&update_requests, | |
'category' => \&update_category, | |
'category_short' => \&update_category_short, | |
'cbp' => \&update_cbp, | |
'predb' => \&update_predb, | |
); | |
die "Unknown updater: $updater" unless exists $updaters{$updater}; | |
$updaters{$updater}->($dbh); | |
} | |
sub update_requests { | |
my ($dbh) = @_; | |
my %queries = ( | |
request => 'select count(*) as requests from user_requests where timestamp > now() - INTERVAL 5 MINUTE;', | |
download => 'select count(*) as downloads from user_downloads where timestamp > now() - INTERVAL 5 MINUTE;', | |
); | |
for my $name ( qw(request download) ) { | |
my $query = $queries{$name}; | |
my $sth = $dbh->prepare($query); | |
$sth->execute(); | |
while (my $row = $sth->fetch) { | |
$data->{$name} = $row->[0]; | |
} | |
$sth->finish(); | |
} | |
} | |
sub update_category { | |
my ($dbh) = @_; | |
my $sth = $dbh->prepare('select count(*) as releases, categories.title, categories.id from releases LEFT JOIN categories ON releases.categories_id = categories.ID group by releases.categories_id'); | |
$sth->execute(); | |
if ($@) { die $@; } | |
while (my $row = $sth->fetch) { | |
$data->{$row->[2]} = $row->[0]; | |
} | |
} | |
sub update_category_short { | |
my ($dbh) = @_; | |
my $sth = $dbh->prepare('SELECT PARTITION_NAME AS category, SUM(TABLE_ROWS) AS count FROM information_schema.PARTITIONS WHERE TABLE_NAME = \'releases\' AND TABLE_SCHEMA = \'nzedb\' GROUP BY PARTITION_NAME'); | |
$sth->execute(); | |
if ($@) { die $@; } | |
while (my $row = $sth->fetch) { | |
$data->{$row->[0]} = $row->[1]; | |
} | |
} | |
sub update_releases { | |
my ($dbh) = @_; | |
my $sth = $dbh->prepare('SELECT (SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_NAME = \'releases\' AND TABLE_SCHEMA = \'nzedb\') AS releases, SUM(IF(nzbstatus = 1 AND nfostatus = 1,1,0)) AS nfo,SUM(IF(isrenamed = 1,1,0)) AS renamed | |
FROM releases'); | |
eval { | |
$sth->execute(); | |
}; | |
if ($@) { die $@; } | |
my $row = $sth->fetchrow_hashref(); | |
$data->{'releases'} = $row->{'releases'}; | |
$data->{'nfo'} = $row->{'nfo'};; | |
$data->{'renamed'} = $row->{'renamed'}; | |
$sth->finish(); | |
} | |
sub update_users { | |
my ($dbh) = @_; | |
my $sth = $dbh->prepare('SELECT count(*) as users from users'); | |
eval { | |
$sth->execute(); | |
}; | |
if ($@) { die $@; } | |
my $row = $sth->fetchrow_hashref(); | |
$data->{users} = $row->{'users'}; | |
$sth->finish(); | |
} | |
sub update_cbp { | |
my ($dbh) = @_; | |
my $collections = 0; | |
my $binaries = 0; | |
my $parts = 0; | |
my $repair = 0; | |
my $missed = 0; | |
my $sth = $dbh->prepare('SELECT TABLE_NAME AS name, TABLE_ROWS As count FROM information_schema.TABLES WHERE TABLE_SCHEMA = \'nzedb\''); | |
eval { | |
$sth->execute(); | |
}; | |
if ($@) {die $@; } | |
while (my $row = $sth->fetch) { | |
if ($row->[0] =~ /collections_/) { | |
$collections += $row->[1]; | |
} elsif ($row->[0] =~ /binaries_/) { | |
$binaries += $row->[1]; | |
} elsif ($row->[0] =~ /^parts_/) { | |
$parts += $row->[1]; | |
} elsif ($row->[0] =~ /missed_parts_/) { | |
$missed += $row->[1]; | |
} elsif ($row->[0] =~ /partrepair_/) { | |
$repair += $row->[1]; | |
} | |
} | |
$data->{'col'} = $collections; | |
$data->{'bin'} = $binaries; | |
$data->{'part'} = $parts; | |
$data->{'repair'} = $repair; | |
$data->{'missed'} = $missed; | |
$sth->finish; | |
} | |
sub update_predb { | |
my ($dbh) = @_; | |
my $sth = $dbh->prepare('SELECT (SELECT TABLE_ROWS FROM information_schema.TABLES WHERE table_name = \'predb\' AND TABLE_SCHEMA = \'nzedb\') AS predb, SUM(IF(predb_id > 0,1,0)) AS predb_matched, COUNT(DISTINCT(predb_id)) AS distinct_predb_matched | |
FROM releases r'); | |
eval { | |
$sth->execute(); | |
}; | |
my $row = $sth->fetchrow_hashref(); | |
$data->{'punmatched'} = $row->{'predb'} - $row->{'distinct_predb_matched'}; | |
$data->{'pmatched'} = $row->{'predb_matched'}; | |
$sth->finish; | |
} | |
exit main() unless caller; | |
1; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I've cleaned up some of the queries (so they are faster, with less overhead), added a PreDB graph, and added some extra stats to the Releases graph.