Skip to content

Instantly share code, notes, and snippets.

@ThePeePs
Last active Aug 28, 2020
Embed
What would you like to do?
Munin Plugin for nZEDb (WIP)
#!/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;
@ThePeePs
Copy link
Author

ThePeePs commented Mar 10, 2017

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment