The purpose of this code will be to sit on four or more servers where work is performed and therefore watch an active log from each server. I intend on running a File::Tail script to watch these logs and then process the entries into a single MySQL database, four tables. My concern is with the use of this script on four or more servers and the possible contention/locks on tables as different servers attempt to write their data. I am wondering are there ways to change my code to help reduce this situation or whether this is a valid concern in the first place. All these servers are a minimum of 4 processor/4G memory with very low loads.
The stored data will then be used as a central advance log search tool via a PHP interface.
Again, I welcome constructive criticism as it helps me learn and grow my skill with Perl.
Thank you in Advance,
Danny
p.s. I forgot to mention the code does function as it has been tested on my development system. But short-cuts are always welcomed.
#!/usr/local/bin/perl -w use strict; use DBI; use File::Tail; # Create File::Tail object my $file; $file=File::Tail->new("/opt/app/superspy/tmp/commLogger.log"); my $line; while (defined($line=$file->read)) { chomp($line); # print "$line\n"; # Create DB connection my $db_handle = DBI->connect("dbi:mysql:database=P0TA6E01;host +=myhost02.fcc.hide.com:3306;user=xxxxxxxx;password=xxxxxxxx") || die +"Couldn't connect to database: $DBI::errstr\n"; if($line =~ /^AlertCmd~.*~.*~-reload.*/){next;} if($line =~ /^AlertCmd~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){ my $tstamp = $1; my $cmnd_id = $2; my $cmnd = $3; my $client_host = $4; my $client_user = $5; my $svr_host = $6; $svr_host =~ s/\..*//g; my $sql = "INSERT INTO ALERTS (TSTAMP,CMND_ID,CMND,CLI +ENTHOST,CLIENTUSER,SVR_HOST) VALUES (\'$tstamp\',\'$cmnd_id\',\'$cmnd +\',\'$client_host\',\'$client_user\',\'$svr_host\')\;\n"; my $statement = $db_handle->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errst +r\n"; $statement->execute() or die "Couldn't execute query '$sql': $DBI::errst +r\n"; } if($line =~ /^SendFiltered~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){ my $tstamp = $1; my $cmnd_id = $2; my $alert_id = $3; my $send_id = $4; my $state = $5; my $svr_host = $6; $svr_host =~ s/\..*//g; my $sql = "INSERT INTO FILTERED (TSTAMP,CMND_ID,ALERT_ +ID,SEND_ID,STATE,SVR_HOST) VALUES (\'$tstamp\',\'$cmnd_id\',\'$alert_ +id\',\'$send_id\',\'$state\',\'$svr_host\')\;\n"; my $statement = $db_handle->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errst +r\n"; $statement->execute() or die "Couldn't execute query '$sql': $DBI::errst +r\n"; } if($line =~ /^SendOffDuty~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){ my $tstamp = $1; my $cmnd_id = $2; my $alert_id = $3; my $send_id = $4; my $state = $5; my $svr_host = $6; $svr_host =~ s/\..*//g; my $sql = "INSERT INTO FILTERED (TSTAMP,CMND_ID,ALERT_ +ID,SEND_ID,STATE,SVR_HOST) VALUES (\'$tstamp\',\'$cmnd_id\',\'$alert_ +id\',\'$send_id\',\'$state\',\'$svr_host\')\;\n"; my $statement = $db_handle->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errst +r\n"; $statement->execute() or die "Couldn't execute query '$sql': $DBI::errst +r\n"; } if($line =~ /^SendStarted~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~( +.*)~(.*)~(.*)~(.*)/){ my $tstamp = $1; my $cmnd_id = $2; my $alert_id = $3; my $send_id = $4; my $user = $5; my $destination = $6; my $pin = $7; my $to = $8; my $message = $9; my $state = $10; my $svr_host = $11; $svr_host =~ s/\..*//g; my $address; if($pin eq ""){$address = $to;}else{$address = $pin;} my $sql = "INSERT INTO SENDS (START_TIME,CMND_ID,ALERT +_ID,SEND_ID,USER,DESTINATION,ADDRESS,MESSAGE,STATE,SVR_HOST,ACTIVE) V +ALUES (\'$tstamp\',\'$cmnd_id\',\'$alert_id\',\'$send_id\',\'$user\', +\'$destination\',\'$address\',\'$message\',\'$state\',\'$svr_host\',\ +'TRUE\')\;\n"; my $statement = $db_handle->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errst +r\n"; $statement->execute() or die "Couldn't execute query '$sql': $DBI::errst +r\n"; } if($line =~ /^SendCompleted~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){ my $tstamp = $1; my $cmnd_id = $2; my $alert_id = $3; my $send_id = $4; my $state = $5; my $svr_host = $6; $svr_host =~ s/\..*//g; my $sql = "UPDATE SENDS SET END_TIME=\'$tstamp\',STATE +=\'$state\',ACTIVE=\'FALSE\' WHERE CMND_ID=\'$cmnd_id\' AND ALERT_ID= +\'$alert_id\' AND SEND_ID=\'$send_id\' AND SVR_HOST=\'$svr_host\'\;\n +"; my $statement = $db_handle->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errst +r\n"; $statement->execute() or die "Couldn't execute query '$sql': $DBI::errst +r\n"; } if($line =~ /^SendCleared~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)/){ my $tstamp = $1; my $cmnd_id = $2; my $alert_id = $3; my $send_id = $4; my $state = $5; my $svr_host = $6; $svr_host =~ s/\..*//g; my $sql = "UPDATE SENDS SET END_TIME=\'$tstamp\',STATE +=\'$state\',ACTIVE=\'FALSE\' WHERE CMND_ID=\'$cmnd_id\' AND ALERT_ID= +\'$alert_id\' AND SEND_ID=\'$send_id\' AND SVR_HOST=\'$svr_host\'\;\n +"; my $statement = $db_handle->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errst +r\n"; $statement->execute() or die "Couldn't execute query '$sql': $DBI::errst +r\n"; } if($line =~ /^AlertError~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(.*)~(. +*)/){ my $tstamp = $1; my $cmnd_id = $2; my $alert_id = $3; my $send_id = $4; my $destination = $5; my $message = $6; my $state = $7; my $svr_host = $8; $svr_host =~ s/\..*//g; my $sql = "INSERT INTO ERRORS (TSTAMP,CMND_ID,ALERT_ID +,SEND_ID,DESTINATION,MESSAGE,STATE,SVR_HOST) VALUES (\'$tstamp\',\'$c +mnd_id\',\'$alert_id\',\'$send_id\',\'$destination\',\'$message\',\'$ +state\',\'$svr_host\')\;\n"; my $statement = $db_handle->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errst +r\n"; $statement->execute() or die "Couldn't execute query '$sql': $DBI::errst +r\n"; } $statement->finish(); $db_handle->disconnect(); }
In reply to Need Critique (Good or Bad) by onegative
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |