From Internetworkpro

Contents 1 NAME 2 SYNOPSIS 3 DESCRIPTION 4 COPYRIGHT AND LICENSE 5 Code

NAME

CEMDB - Cisco Error Message Database

SYNOPSIS

Run this code with the emd.xml and it will populate the Cisco Error Message Database (CEMDB)

You will need XML::Simple, DBI and DBD::mysql perl modules for this, as well as the 'emd.xml' file.

You can find the emd.xml.gz (it will need to be uncompressed..) in the tarball at http://php-syslog-ng.gdd.net/current.tgz as 'scripts/cemdb/emd.xml.gz'

DESCRIPTION

CEMDB.pl allows you to populate a MySQL database with the emd.xml data (Cisco Error Message Database)

COPYRIGHT AND LICENSE

Copyright (c) 2006 Clayton Dukes, All rights reserved.

Code

#!/usr/bin/perl -w
 
use strict;
use XML::Simple qw( :strict );
use DBI;
use Data::Dumper;
use POSIX qw(strftime);
 
my $table = "emd";
my $dbadmin = "root";
my $dbpw = "CHANGEME";
my $dbname = "cemdb";
my $dbhost = "localhost";
my $dbport = "3306";
my $DEBUG = 0;
 
 
if ($DEBUG > 0) { 
    print "Table: $table\n";
    print "Adminuser: $dbadmin\n";
    print "PW: $dbpw\n";
    print "DB: $dbname\n";
    print "DB Host: $dbhost\n";
    print "DB Port: $dbport\n"; 
#exit;
}
 
my $xml_file = 'emd.xml';
my $xml_ref = new XML::Simple;
my $data = $xml_ref->XMLin("$xml_file", ForceArray => 1,  KeyAttr => []);
my $datetime = strftime "%Y-%m-%d %H:%M:%S", gmtime;
 
print Dumper($data) if ($DEBUG);
 
my $dbh = DBI->connect('dbi:mysql:'.$dbname,$dbadmin,$dbpw)
       or die("Couldn't connect to mySQL ", $dbname ," : $DBI::errstr");
 
my $cemdb_query_sql = "SELECT count(*) FROM cemdb WHERE name=?";
my $sth_query = $dbh->prepare($cemdb_query_sql);
 
my $cemdb_update_sql = "UPDATE cemdb SET message=?, explanation=?, action=?, datetime=? WHERE name=?";
my $sth_update = $dbh->prepare($cemdb_update_sql);
 
my $cemdb_ins_sql = "INSERT INTO cemdb (name, message, explanation, action, datetime) VALUES (?,?,?,?,?)";
my $sth = $dbh->prepare($cemdb_ins_sql);
 
foreach ( @{ $data->{error} } ) {
    my($explanation,$action,$message, $name);
 
        $name = $_->{code};
    if ($name) {
        print "$_->{code}\n" if ($DEBUG);
 
        $message = $_->{message};
        print "$_->{message}\n" if ($DEBUG);
 
        if ((ref $_->{explanation}) eq "ARRAY") {
            foreach my $line ( @{ $_->{explanation} } ) {
                $explanation .= $line;
                print "$line\n" if ($DEBUG);
            }
        } else {
            $explanation = $_->{explanation};
            print "$_->{explanation}\n" if ($DEBUG);
        }
 
        if ((ref $_->{action}) eq "ARRAY") {
            foreach my $line ( @{ $_->{action} } ) {
                $action .= $line;
                print "$line\n" if ($DEBUG);
            }
            }
        } else {
            $action = $_->{action};
            print "$_->{action}\n" if ($DEBUG);
        }
 
        $sth_query->execute($name) or die("$DBI::errstr");
        my ( $count ) = $sth_query->fetchrow_array();
        if( $count > 0 ) {
            print "Updating existing record for $name\n";
            #existing item found, use update
            $sth_update->execute($message, $explanation, $action, $datetime, $name) or die("$DBI::errstr");
        }
        else {
            print "Inserting new record for $name\n";
            #no items found, insert
            $sth->execute($name, $message, $explanation, $action, $datetime) or die("$DBI::errstr");
        }
    } else {
        print "\$name Variable is missing, skipping record\n"
    }
 
}
 
 
 
=back
 
=head1 SUPPORT
 
No official support for this program.
 
=head1 AUTHOR
 
Clayton Dukes (cdukes@cdukes.com)
Michael J. Freeman (mfreeman451@gmail.com)
 
Let me know if you have any ideas or suggestions on how to make this tool better
 
=head1 THANKS
 
Thank you very much to Brandon Bennet, who inspired this project and made the first
pass at it with his 'Cisco Error Scraper tool'
 
=head1 COPYRIGHT AND LICENSE
 
Copyright (c) 2006 Clayton Dukes
All rights reserved. This program is free software; you
can redistribute it and/or modify it under the same terms
as Perl itself.