Creating a perl script for database monitoring

Jeylox has asked for the wisdom of the Perl Monks concerning the following question:

Hello everybody, i'm a poor lost soul who is looking for the help of a perl master. I'm trying to create a RNPE plugin in nagios to monitor database tables but i'm a little lost with the perl script. I have done a Union all in sql to have all data i need and shut the connection, i used a foreach to analyse data in each line with a condition that return a warning code if the value is too high. The real problem is that i have only the 1st line and other line are not analyse in my loop. I will be gratefull if someone as a clue about the problem.

#!/usr/bin/perl

use strict;
use warnings;

use DBI; #Activer le module DBD pour perl


# Chargement du module

use Monitoring::Plugin;


# Definition de l'environnement

BEGIN {
   $ENV{ORACLE_HOME}     = "/home/oracle/oracle/product/11.2.0/dbhome_
+1";
   
}



# Appel du module et règle de la commande


my $oracle_connector = Monitoring::Plugin->new(
  shortname => 'File attente de datatable',
  usage =>
    'Usage : %s [ -c|--critical=<threshold> ] [ -w|--warning=<threshol
+d> ]',

);



# Définition de l'argument warning

$oracle_connector->add_arg(
 spec => 'warning|w=f',                                               
+ # Nous acceptons des nombres réels
 help => 'Exit with WARNING status if less than REQUEST',
 label => 'REQUEST',
 required => 1,
  );


# Définition de l'argument critical

$oracle_connector->add_arg(
 spec     => 'critical|c=f',
 help     => 'Exit with CRITICAL status if less than REQUEST',
 label    => 'REQUEST',
 required => 1,
          );


$oracle_connector->getopts;

# Connection à la base

my $db=DBI->connect("dbi:Oracle:AXIBASE", "AXINX","AXINX")
        or die( $DBI::errstr . "\n" );


# Requetes SQL



my $sql = qq/SELECT COUNT (*)  from schema.table1 UNION ALL
SELECT COUNT (*)  from schema.table2  UNION ALL
SELECT COUNT (*)  from schema.table3/;

my $sth = $db->prepare($sql);
$sth->execute();


my $data = $sth->fetchall_arrayref();
$sth->finish;

foreach $data ( @$data) {

        (my $variable1,my $variable2,my $variable3) = @$data;
                  #  print "$variable1\n";
                  #   print "$variable2\n";
 my $code_retour = $oracle_connector->check_threshold(
 check    => $data,
 warning  => $oracle_connector->opts->warning,
 critical => $oracle_connector->opts->critical,
       );

$oracle_connector->plugin_exit( $code_retour, "File attente (@$data)" 
+);

}
[download]

 
[root@BAC libexec]# ./check_oracle_connector -w 10 -c 20
File attente de datatable CRITICAL - File attente (170)
[download]

Edit: Finally i create a variable for my table and each time i have the return code of the plugin exit only for the table i asked. Thank you everyone for the help :)

Comment on Creating a perl script for database monitoring Select or Download Code

Replies are listed 'Best First'.
Re: Creating a perl script for database monitoring by hippo (Archbishop) on Apr 08, 2016 at 12:37 UTC
`foreach $data ( @$data) {` That doesn't look right. You have the same variable in the iterator as in the list over which you are iterating. At best, that's confusing. Eg. instead: `foreach my $thisone (@$data) {` [download] You might also consider some more meaningful name than `$data` but that's purely a stylistic tip.	[reply] [d/l] [select]
Re^2: Creating a perl script for database monitoring by Jeylox (Initiate) on Apr 08, 2016 at 13:09 UTC
Thank you for the quick reply :) I have too use the ->plugin_exit( each time to return a code (0=OK,1=UNKNOWN,2=CRITICAL,3=WARNING) from the data. If I rename the $data i have uncompiled result. `File attente de datatable CRITICAL - File attente (ARRAY(0x10841e8) ARRAY(0x1084230) ARRAY(0x1084278))`	[reply] [d/l]
Re^3: Creating a perl script for database monitoring by SimonPratt (Friar) on Apr 08, 2016 at 13:25 UTC
Thats because when you change `foreach $data ( @$data) {` to `foreach my $thisone ( @$data) {`, you must also change `(my $variable1,my $variable2,my $variable3) = @$data;` to `(my $variable1,my $variable2,my $variable3) = @$thisone;` and change `check => $data,` to `check => $thisone,`	[reply] [d/l] [select]
Re^3: Creating a perl script for database monitoring by poj (Abbot) on Apr 08, 2016 at 13:49 UTC
The docs say "You can specify $value as an array of values and each will be checked against the thresholds." so try without the loop. `my $data = $sth->fetchall_arrayref(); $sth->finish; my $code_retour = $oracle_connector->check_threshold( check => $data, warning => $oracle_connector->opts->warning, critical => $oracle_connector->opts->critical, ); $oracle_connector->plugin_exit( $code_retour, "File attente (@$data)" +);` [download] poj	[reply] [d/l]
Re^4: Creating a perl script for database monitoring by Jeylox (Initiate) on Apr 08, 2016 at 15:23 UTC
Re^5: Creating a perl script for database monitoring by poj (Abbot) on Apr 08, 2016 at 15:41 UTC
Re: Creating a perl script for database monitoring by Corion (Patriarch) on Apr 08, 2016 at 12:33 UTC
In your loop over your tables, you call `->plugin_exit(`, which will likely exit your script immediately. Maybe you can find out how to check more than one metric from within a single script?	[reply] [d/l]