Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement