Munin Plugin for nZEDb (WIP)

nzedb_

#!/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;

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.