Re: Interface flap detection in Perl

It would be a very sane idea to make sure you're using placeholders in all database commands that use parameters. For example, replace my $iface_result = $dbh->prepare("UPDATE device_iface_cache SET interf +ace_status='$stat', lastcheck='$date', hostname='$host' WHERE hostnam +e='$host' AND interface_name='$int_table{$key}';"); $iface_result->execute();
[download]
with

my $iface_result = $dbh->prepare("UPDATE device_iface_cache SET interf
+ace_status=?, lastcheck=? WHERE hostname=? AND interface_name=?");
$iface_result->execute($stat, $date, $host, $int_table{$key});
[download]

(no need to set hostname again where you select on it) or even easier, using DBIx::Simple with SQL::Abstract:

$db->update('device_iface_cache',
    { interface_status => $stat, lastcheck => $date },
    { hostname => $host, interface_name => $int_table{$key} }
);
[download]

(DBIx::Simple uses a database handle that replaces DBI's $dbh, but is not exactly the same thing. Hence the different name $db. You get it by connecting through DBIx::Simple->connect instead of DBI->connect.)

Comment on Re: Interface flap detection in Perl Select or Download Code

Replies are listed 'Best First'.
Re^2: Interface flap detection in Perl by sunnyfedora99 (Novice) on Apr 13, 2011 at 14:11 UTC
When I do that on a select statement, i get the following error messages. Use of uninitialized value in string ne at intstatDB.pl line 452. Use of uninitialized value in concatenation (.) or string at intstatDB.pl line 456. if($rate_total == $crit){ #Add in flap detection, also, if an interface is down +check x4 #before alarming. $stat = "DOWN"; append_log ("CRITICAL: Interface $int_table{$key} is D +OWN.\n"); append_status("CRITICAL: Interface $int_table{$key} is + DOWN.\n"); my $sth = $dbh->prepare("SELECT interface_status FROM +device_iface_cache WHERE interface_name=? and hostname=?"); $sth->execute($int_table{key}, $host); #my $current_status = $dbh->prepare("SELECT interface_ +status FROM device_iface_cache WHERE interface_name='$int_table{$key} +' and hostname='$host';"); #$current_status->execute; my ($f5_iface_status); $f5_iface_status=$sth->fetchrow_hashref(); #print "$f5_iface_status->{interface_status}\n"; if($f5_iface_status->{interface_status} ne $stat) { #my $iface_result = $dbh->prepare("UPDATE device_i +face_cache SET interface_status='$stat', lastcheck='$date', hostname= +'$host' WHERE hostname='$host' AND interface_name='$int_table{$key}'; +"); #$iface_result->execute(); my $iface_result = $dbh->prepare("UPDATE device_if +ace_cache SET interface_status=?, lastcheck=? WHERE hostname=? AND in +terface_name=?"); $iface_result->execute($stat, $date, $host, $int_t +able{$key}); append_log ("STATUS CHANGED: Interface $int_table{ +$key} Was: $f5_iface_status->{interface_status} ------- Now: $stat\n" +); } my $result = $dbh->prepare("UPDATE device_iface_cache +SET interface_status=?, lastcheck=? WHERE hostname=? AND interface_na +me=?"); $result->execute($stat, $date, $host, $int_table{$key} +); } [download] I've used placeholders before as test, and worked fine. Do you know what's going on here.	[reply] [d/l]
Re^3: Interface flap detection in Perl by moritz (Cardinal) on Apr 13, 2011 at 14:20 UTC
Which one is line 452? Perl 6 - second systems done right	[reply]
Re^4: Interface flap detection in Perl by sunnyfedora99 (Novice) on Apr 13, 2011 at 14:39 UTC
`if($f5_iface_status->{interface_status} ne $stat) {` It's got to do something with '$int_table{key}' from the SELECT statement. I took that out and it works fine. But it makes sense having that in there.	[reply] [d/l]
Re^4: Interface flap detection in Perl by sunnyfedora99 (Novice) on Apr 14, 2011 at 09:09 UTC
It's deffo with this following line `my $ret = $sth->execute($int_table{key}, $host);` [download] it doesn't like `$int_table{key}` that in there for some reason. Any clues anyone???	[reply] [d/l] [select]