#!c:\perl\bin\perl.exe

use lib "C:\\Perl";
use DBI;
use DBD::mysql;

# # # # # # # # # # # #
#Variable declarations#
# # # # # # # # # # # #

my ($user, $pass) = ("root", "pass"); #credentials for SQL database
my $table_data = q/DBI:mysql:database=information_schema;host=localhos
+t;port=13308/;

my @tblname;

#-------------------------------------------------------------
#  main script
#  TODO: break down into smaller methods/routines.
#------------------------------------------------------------

my @tblname = get_tables($table_data, $user, $pass);

#Connect to the data source and get a handle for that connection
my $data_source = q/DBI:mysql:database=probedb;host=localhost;port=133
+08/;
#Connect to the data source and get a handle for that connection
my $dbh = DBI->connect($data_source, $user, $pass) || die "Can't conne
+ct to $data_source: $DBI::errstr";

foreach my $table_name (@tblname) {

    # TODO: the contents of this loop should probably be a method in i
+tself...

    my $NCquery= <<EOF;
SELECT id, text FROM $table_name 
WHERE alarmcode LIKE '%Trap%Calltouch%'
EOF
    my $sth=$dbh->prepare($NCquery);

    my $updateNC= <<EOF;
UPDATE $table_name SET severity = ? 
WHERE id = ? AND modtime > ?
EOF

    my $udh=$dbh->prepare($updateNC);

    # TODO: $sth->fetchall_hashref/arrayref may be better here...
    $sth->execute();
    while ( @row = $sth->fetchrow_array ) { 
        my ($NCid, $NCsevno) = @row;

        # TODO: add default $newstat value if no match??
        if  ($NCsevno =~ /alarmState=(\d)/i){
            $newstat = 1 if $1 == 2;
            $newstat = 5 if $1 == 1;
            $newstat = 3 if $1 == 3; #ack?#
        }
        
        # TODO: where is $ntime set to a value?? - please fix!!
        $udh->execute($newstat, $NCid, $ntime);
    }
    print "$table_name\n";
}

my $query=<<EOF;
SELECT name FROM managedobject 
WHERE displayname like '%TSEPAL%-CT%' 
AND type LIKE '%Windows%'
EOF

my $sth=$dbh->prepare($query);
$sth->execute();
while ( @row = $sth->fetchrow_array ) {
    #update all matching alarms to newest severity
    update($row[0], $table_name);
}
$sth->finish;
$dbh->disconnect;

#-------------------------------------------------------------
#  get_tables
#  get list of tables from database
#------------------------------------------------------------

sub get_tables {
    my @tblname;
    my $dbtbl = DBI->connect($table_data, $user, $pass) || die "Can't 
+connect to $data_source: $DBI::errstr";

    my $tblname = <<EOF;
SELECT table_name FROM `TABLES` 
where table_name like 'event\_%'
EOF

    my $sthtbl=$dbtbl->prepare($tblname);
    $sthtbl->execute();
    while ( @row = $sthtbl->fetchrow_array ) {
    my $table_name = $row[0];
    push(@tblname, $table_name); 
    }
    $sthtbl->finish;
    $dbtbl->disconnect;
    return @tblname;
}

#----------------------------------------------------
# update
# TODO: put description here
#----------------------------------------------------

sub update {
    my ($id, $table_name) = @_;

    my $alQuery=<<<EOF
SELECT source, severity, ttime, text 
FROM $table_name 
WHERE source = ? AND alarmcode LIKE '%Trap%Calltouch%'
EOF

    my $updateNC=<<EOF;
UPDATE $table_name 
SET severity = ? 
WHERE source = ? AND ttime < ? 
and text like ?
and alarmcode LIKE '%Trap%Calltouch%'
EOF


    my $query=<<EOF;
SELECT severity 
FROM alert
WHERE source = ? 
ORDER BY severity ASC LIMIT 1
EOF

    my $updateMO=<<EOF
UPDATE managedobject 
SET status = ? 
WHERE name = ?
EOF

    my $alqH=$dbh->prepare($alQuery);
    my $udncH=$dbh->prepare($updateNC);
    my $qH=$dbh->prepare($query);
    my $moH=$dbh->prepare($updateMO);

    $alqh->execute($id);
    while ( @row = $alqh->fetchrow_array ) {
        my ($NCsource, $NCsev, $NCmodtime, $NCmmessage) = @row;
        # TODO: should $NCmsg be $NCmmessage as $NCmsg is not declared
+....????
        if  ($NCmsg =~ /alarmDescription=([^\s+])/i){
            $NCmmessage = $1;
        }

        $udncH->execute($NCsev, $NCsource, $NCmodtime, "%$NCmmessage%"
+);
    }

    $qH->execute($id);
    while ( @row = $sth->fetchrow_array ) {
        $sev = $row[0];
    }

    $moH->execute($sev, $id);
}
[download]