f 用Perl 查核AWS SQL Server RDS 的系統LOG 內容 ~ 迪貝之家

Pages

用Perl 查核AWS SQL Server RDS 的系統LOG 內容

#!/usr/bin/perl
use strict;
use warnings;
use DateTime;
use DateTime::Format::Strptime;
use DateTime::Duration;
use DBI;
use v5.16;

my $interval = shift;
my $dt_low = DateTime->now(time_zone => 'Asia/Taipei') - DateTime::Duration->new( hours => $interval );
say "low limit time : ", $dt_low->strftime( '%Y-%m-%d %H:%M:%S' );
my $low_epoch= $dt_low -> epoch;

my $dbh = DBI->connect("dbi:Sybase:server=yourserver in freetds.conf", 'userlogin', 'password', {PrintError => 0});
die "Unable for connect to server $DBI::errstr"
unless $dbh;
my @log_file;
my $sth;

$sth = $dbh->prepare(" EXEC master.dbo.sp_enumerrorlogs");
if($sth->execute) {
while(my @dat = $sth->fetchrow) {
push @log_file, $dat[0];
}
}

$sth=undef;
my @sorted_log_file = sort { $a <=> $b } @log_file;
foreach my $i (0 .. $#sorted_log_file) {
my $sql = 'EXEC rdsadmin.dbo.rds_read_error_log '. $i . ',1';
$sth = $dbh->prepare( $sql );
if($sth->execute) {
while(my @dat = $sth->fetchrow) {
if ( $dat[2] =~ m/error|failed|Server is listening on/i && $dat[2] !~ m/Attempting to cycle error log|Logging SQL Server messages in file|The error log has been reinitialized|DBCC CHECKDB/i && $dat[1] !~ m/logon/i ) {
my $strp=DateTime::Format::Strptime->new(pattern =>'%B %d %Y %I:%M:%S:%N%P',time_zone => 'Asia/Taipei',);
my $data_dt=$strp->parse_datetime($dat[0]);
if ($low_epoch < $data_dt->epoch ){say @dat;}
}
}
}
}