#!/usr/bin/perl -w

# Purpose: ensure that data/log devices don't get filled
# up for a particular database. This lets you know if the
# empty percent gets below a particular threshold.
#
# It is meant to be run as cron job, although you might want to put
# the actual call into a shell script so you don't have
# usernames/passwords floating around...

# Copyright (c) 2000 intes.net, inc.

use strict;
use DBI;
use Data::Dumper qw( Dumper );
use Number::Format;
use Getopt::Long qw( GetOptions );

# Yes, I know that Getopt::Long allows you to declare defaults for
# options, but we need to reference the default variable in usage() below

my $DEFAULT_WARN_PERCENT = 30;

{
 # Get all the options and ensure they're consistent

 my ( $OPT_verbose, $OPT_help, $OPT_warn_percent );
 my ( $OPT_database, $OPT_server, $OPT_user, $OPT_password );
 my ( $OPT_sybase );
 GetOptions( 'help' => \$OPT_help, 'verbose+' => \$OPT_verbose,
             'warn_percent=s' => \$OPT_warn_percent,
             'sybase=s' => \$OPT_sybase,
             'D=s' => \$OPT_database, 'S=s' => \$OPT_server, 
             'U=s' => \$OPT_user,     'P=s' => \$OPT_password );

 if ( $OPT_help ) { print usage(); exit(0) }

 $ENV{SYBASE} = $OPT_sybase  if ( $OPT_sybase );

 $OPT_warn_percent ||= $DEFAULT_WARN_PERCENT;
 $OPT_warn_percent = $OPT_warn_percent / 100  if ( $OPT_warn_percent > 1 );

 # Open up the db handle and execute the sp

 my $DSN = "database=$OPT_database";
 $DSN   .= ";server=$OPT_server"       if ( $OPT_server );
 my $dbh = DBI->connect( "DBI:Sybase:$DSN", $OPT_user, $OPT_password,
                         { RaiseError => 0, ChopBlanks => 1 } ) 
               || die "Cannot connect!\nError: $DBI::errstr\n";
 $dbh->{RaiseError} = 1;
 my ( $sth );
 eval {
   $sth = $dbh->prepare( "sp_helpdb $OPT_database" );
   $sth->execute;
 };
 if ( $@ ) {
   die "Could not prepare/execute stored procedure for finding db info: $@";
 }

 # Keep track of the rows -- the first is general db information; 2
 # .. n are information about the devices, n+1 .. last - 1 are segment
 # info (sometimes) and the last is just the return status, which
 # should always be 0

 my $row_count = 1;
 my $db_info = {};

 # Note that we use the DBD::Sybase-specific method for fetching
 # multiple result sets from the same query

 do {
   while ( my $row = $sth->fetchrow_arrayref ) {

     # The first item is the overall definition

     if ( $row_count == 1 ) {
       $db_info->{name}       = $row->[0];
       $db_info->{owner}      = $row->[2];
       $db_info->{device_num} = $row->[3];
       $db_info->{created_on} = $row->[4];
       $db_info->{options}    = $row->[5];
       $db_info->{total_size} = translate_mb_to_bytes( $row->[1] );
     }

     # These describe the space used on the devices
     # 0 = name; 1 = 'xxx MB' (size); 2 = type; 3 = size free (in pages??)

     elsif ( $row->[3] ) {
       my $device_size = translate_mb_to_bytes( $row->[1] );
       my $type = ( $row->[2] =~ /^log/ ) ? 'log' : 'data';
       push @{ $db_info->{devices} }, { type => $type, name => $row->[0],
                                        total_size => $device_size,
                                        free_size  => $row->[3] * 1000 };
       $db_info->{"${type}_size"} += $device_size;
       $db_info->{"${type}_free"} += $row->[3] * 1000;
     }

     $row_count++;
   }
 } while ( $sth->{syb_more_results} );

 # Don't need the db handle anymore

 $dbh->disconnect;

 # Calculate the space used 

 $db_info->{log_used}  = $db_info->{log_size} - $db_info->{log_free};
 $db_info->{data_used} = $db_info->{data_size} - $db_info->{data_free};

 # Now display some info -- we only display the message if either our
 # log or data devices is below our warning percentage

 my $log_empty  = $db_info->{log_free} / $db_info->{log_size};
 my $data_empty = $db_info->{data_free} / $db_info->{data_size};
 if ( $log_empty < $OPT_warn_percent or $data_empty < $OPT_warn_percent ) {

   # Open a Number format object so we can format consistently   
   my $x = Number::Format->new( -mega_suffix => ' MB' );
   my $hostname = `hostname`;
   chomp $hostname;
   print <<WARN;
Warning! Resources are getting low in Sybase database!
Threshold used: 
   Log or Data Empty Percent < @{[ sprintf( '%5.2f%%', $OPT_warn_percent * 100 ) ]}

    Host: $hostname
Database: $db_info->{name}
  Server: $OPT_server
   Owner: $db_info->{owner}
 Created: $db_info->{created_on}

Total Size: @{[ $x->format_bytes( $db_info->{total_size} ) ]}

 Data Size: @{[ $x->format_bytes( $db_info->{data_size}, 2 ) ]}
 Data Used: @{[ $x->format_bytes( $db_info->{data_used}, 2 ) ]}
 Data Free: @{[ $x->format_bytes( $db_info->{data_free}, 2 ) ]}
     Empty: @{[ sprintf( '%5.2f%%', $data_empty * 100 ) ]}

  Log Size: @{[ $x->format_bytes( $db_info->{log_size}, 2 ) ]}
  Log Used: @{[ $x->format_bytes( $db_info->{log_used}, 2 ) ]}
  Log Free: @{[ $x->format_bytes( $db_info->{log_free}, 2 ) ]}
     Empty: @{[ sprintf( '%5.2f%%', $log_empty * 100 ) ]}

Please fix the situation ASAP!
WARN
 }

 exit(0)  unless ( $OPT_verbose );
 print "Yes, this is a lame excuse for a verbose report:\n\n",
       Dumper( $db_info ), "\n"; 
}

sub translate_mb_to_bytes {
 my $text = shift;
 $text =~ s/\s*$//;
 if ( $text =~ s/MB$// ) {
   $text = $text * 1000000;
 }
 return $text;
}

sub usage {
 return <<USAGE;

view_sybase_size - Script to ensure that the data/log devices are not
filling up in a Sybase database.

Usage:

 view_sybase_size -U user -P password -D database [ -S server ]
                 [ --verbose ] [ --warn_percent=50 ] 
                 [ --sybase /opt/sybase-11.9.2 ]
                 [ --help ]

Sample cron usage:

 # Do a check every night at 2 am -- if everything is ok then nothing
 # is displayed -- errors get emailed via cron's parameters

 0 2 * * * /home/sybase/scripts/view_sybase_size -U www -P www -D mydb

However, it's recommended for security reasons that you
put the above call into a shell script and call that
from cron. Otherwise your login information will be in 
the subject of the email cron sends to you...

Options:

 --warn_percent (optional)

 Percentage to use for testing whether to return an error message. If
 the emptiness either the data or log devices fall below this number,
 then we display the warning.

 Example

   view_sybase_size --warn_percent=60

 Would send a warning whenever either the log or data segment is less
 than 60% empty.

 The current default set in this script is $DEFAULT_WARN_PERCENT%

 -S (optional)

 Database server we should connect to. This is only optional if you
 have the DSQUERY environment variable set, otherwise DBD::Sybase will
 not be able to open a connection.

 -D

 Database we should display the information for.

 -U

 Username for database.

 -P

 Password for database.

 --sybase (optional)

 Set the $ENV{SYBASE} variable if necessary.

 --help (optional)

 Displays this information

 --verbose (optional)

 Displays details about the database and its devices. (Currently very
 crude.)

USAGE
}

__END__