#!/usr/bin/perl -w
###########################################################################
### ###
### NetworkLens E-Mail Alert Agent ###
### ###
### NetworkLens ###
### Copyright (C) 2002, I-Link Incorporated ###
### ###
### This program is free software; you can redistribute it and/or ###
### modify it under the terms of the GNU General Public License as ###
### published by the Free Software Foundation; either version 2 of ###
### the License, or (at your option) any later version. ###
### ###
### This program is distributed in the hope that it will be useful, ###
### but WITHOUT ANY WARRANTY; without even the implied warranty of ###
### MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the ###
### GNU General Public License for more details. ###
### ###
### You should have received a copy of the GNU General Public License ###
### along with this program; if not, write to the Free Software ###
### Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. ###
### ###
### Description: This script checks for new or modified events in ###
### the "lens_event" table that match defined queries ###
### in the "lens_query" table. If matching entries are ###
### found, an email is sent. ###
### ###
### PLEASE NOTE! You must change the $base_url to match your system ###
### ###
### TO DO: Add error checking! ###
### ###
### Change Log: ###
### ###
### 01/24/2002 Greg Bailey ###
### Initial Version ###
### ###
###########################################################################
###########################################################################
# Configuration settings #
###########################################################################
$dbi_datasource = "DBI:mysql:lens:localhost:3306";
$dbi_username = "root";
$dbi_password = "";
$from_email = "NetworkLens <lens\@networklens.com>";
$base_url = "http://lens.i-link.net/detail.php?id=";
###########################################################################
# Package definitions #
###########################################################################
use DBI;
use IO::Socket;
use POSIX;
###########################################################################
# send_email #
###########################################################################
sub send_email
{
$query = $_[0];
$email_val = $_[1];
$message = $_[2];
open( SENDMAIL, "| /usr/sbin/sendmail -t" )
or die "Can't fork for sendmail: $!\n";
printf( SENDMAIL "From: %s\n", $from_email );
printf( SENDMAIL "To: %s\n", $email_val );
printf( SENDMAIL "Subject: Events matching query [%s]\n", $query );
printf( SENDMAIL "\n" );
printf( SENDMAIL "%s\n", $message );
close( SENDMAIL )
or warn "sendmail didn't close nicely";
}
###########################################################################
# process_filter #
###########################################################################
sub process_filter
{
$user = $_[0];
$query = $_[1];
$email_val = $_[2];
$last_notify = $_[3];
my $sth;
my @row;
#-=====================================================================-#
#-= Retrieve the qualifiers for the specified user/query combo =-#
#-=====================================================================-#
$sql = sprintf( "select status_b, status_val, severity_b, severity_val, "
. " created_min_b, created_min_val, created_max_b, created_max_val, "
. " modified_min_b, modified_min_val, modified_max_b, modified_max_val, "
. " ip_addr_b, ip_addr_qual, ip_addr_val, "
. " ref_id_b, ref_id_qual, ref_id_val, "
. " category_b, category_qual, category_val, "
. " summary_b, summary_qual, summary_val "
. " from lens_query "
. " where query = '%s' "
. " and user = '%s'", $query, $user );
$sth = $dbh->prepare( $sql );
$sth->execute();
if ( @row = $sth->fetchrow_array )
{
$status_b = $row[0];
$status_val = $row[1];
$severity_b = $row[2];
$severity_val = $row[3];
$created_min_b = $row[4];
$created_min_val = $row[5];
$created_max_b = $row[6];
$created_max_val = $row[7];
$modified_min_b = $row[8];
$modified_min_val = $row[9];
$modified_max_b = $row[10];
$modified_max_val = $row[11];
$ip_addr_b = $row[12];
$ip_addr_qual = $row[13];
$ip_addr_val = $row[14];
$ref_id_b = $row[15];
$ref_id_qual = $row[16];
$ref_id_val = $row[17];
$category_b = $row[18];
$category_qual = $row[19];
$category_val = $row[20];
$summary_b = $row[21];
$summary_qual = $row[22];
$summary_val = $row[23];
$sql = ' select id, '
. ' lens_status.long_desc status, '
. ' lens_severity.long_desc severity, '
. ' created, '
. ' modified, '
. ' ip_addr, '
. ' ref_id, '
. ' category, '
. ' summary, '
. ' lens_severity.color '
. ' from lens_event, lens_status, lens_severity '
. ' where lens_event.status = lens_status.status '
. ' and lens_event.severity = lens_severity.severity '
. ' and ( lens_event.created > ' . "'" . $last_notify . "' "
. ' or lens_event.modified > ' . "'" . $last_notify . "' ) ";
if ( $status_b ) { $sql .= sprintf( "and lens_event.status in (%s) ", $status_val ); }
if ( $severity_b ) { $sql .= sprintf( "and lens_event.severity in (%s) ", $severity_val ); }
if ( $created_min_b ) { $sql .= sprintf( "and created >= '%s' ", $created_min_val ); }
if ( $created_max_b ) { $sql .= sprintf( "and created <= '%s' ", $created_max_val ); }
if ( $modified_min_b ) { $sql .= sprintf( "and modified >= '%s' ", $modified_min_val ); }
if ( $modified_max_b ) { $sql .= sprintf( "and modified <= '%s' ", $modified_max_val ); }
if ( $ip_addr_b )
{
if ( $ip_addr_qual eq "=" ) { $sql .= sprintf( "and ip_addr = '%s' ", $ip_addr_val ); }
elsif ( $ip_addr_qual eq "LIKE" ) { $sql .= sprintf( "and ip_addr like '%s' ", $ip_addr_val ); }
elsif ( $ip_addr_qual eq "IN" ) { $sql .= sprintf( "and ip_addr in (%s) ", $ip_addr_val ); }
elsif ( $ip_addr_qual eq "NOT LIKE" ) { $sql .= sprintf( "and ip_addr not like '%s' ", $ip_addr_val ); }
elsif ( $ip_addr_qual eq "NOT IN" ) { $sql .= sprintf( "and ip_addr not in (%s) ", $ip_addr_val ); }
}
if ( $ref_id_b )
{
if ( $ref_id_qual eq "=" ) { $sql .= sprintf( "and ref_id = %s ", $ref_id_val ); }
elsif ( $ref_id_qual eq "IN" ) { $sql .= sprintf( "and ref_id in (%s) ", $ref_id_val ); }
elsif ( $ref_id_qual eq "NOT IN" ) { $sql .= sprintf( "and ref_id not in (%s) ", $ref_id_val ); }
}
if ( $category_b )
{
if ( $category_qual eq "=" ) { $sql .= sprintf( "and category = '%s' ", $category_val ); }
elsif ( $category_qual eq "LIKE" ) { $sql .= sprintf( "and category like '%s' ", $category_val ); }
elsif ( $category_qual eq "IN" ) { $sql .= sprintf( "and category in (%s) ", $category_val ); }
elsif ( $category_qual eq "NOT LIKE" ) { $sql .= sprintf( "and category not like '%s' ", $category_val ); }
elsif ( $category_qual eq "NOT IN" ) { $sql .= sprintf( "and category not in (%s) ", $category_val ); }
}
if ( $summary_b )
{
if ( $summary_qual eq "=" ) { $sql .= sprintf( "and summary = '%s' ", $summary_val ); }
elsif ( $summary_qual eq "LIKE" ) { $sql .= sprintf( "and summary like '%s' ", $summary_val ); }
elsif ( $summary_qual eq "IN" ) { $sql .= sprintf( "and summary in (%s) ", $summary_val ); }
elsif ( $summary_qual eq "NOT LIKE" ) { $sql .= sprintf( "and summary not like '%s' ", $summary_val ); }
elsif ( $summary_qual eq "NOT IN" ) { $sql .= sprintf( "and summary not in (%s) ", $summary_val ); }
}
$sql .= 'order by lens_event.status, '
. ' lens_event.severity, '
. ' id desc';
$sth = $dbh->prepare( $sql );
$sth->execute();
if ( $sth->rows > 0 )
{
$mesg = "NetworkLens has detected new and/or changed events\n";
$mesg .= "matching your defined query [" . $query . "].\n";
$mesg .= "\n";
while ( @row = $sth->fetchrow_array )
{
$id = $row[0];
$status = $row[1];
$severity = $row[2];
$created = $row[3];
$modified = $row[4];
$ip_addr = $row[5];
$ref_id = $row[6];
$category = $row[7];
$summary = $row[8];
$mesg .= sprintf( "Event [%d]: See %s%d\n", $id, $base_url, $id );
$mesg .= sprintf( " Status: %s\n", $status );
$mesg .= sprintf( " Severity: %s\n", $severity );
$mesg .= sprintf( " Category: %s\n", $category );
$mesg .= sprintf( " Summary: %s\n", $summary );
$mesg .= sprintf( " Created: %s\n", $created );
if ( defined $modified )
{
$mesg .= sprintf( " Modified: %s\n", $modified );
}
if ( defined $ip_addr )
{
$mesg .= sprintf( " IP Address: %s\n", $ip_addr );
}
if ( defined $ref_id )
{
$mesg .= sprintf( " Reference ID: %d\n", $ref_id );
}
$mesg .= "\n";
}
&send_email( $query, $email_val, $mesg );
}
}
#-=====================================================================-#
#-= Update the lens_query last_notify field =-#
#-=====================================================================-#
$sql = sprintf( "update lens_query "
. " set last_notify = now() "
. " where query = '%s' "
. " and user = '%s'", $query, $user );
$dbh->do( $sql );
}
###########################################################################
# Connect to the NetworkLens database #
###########################################################################
$dbh = DBI->connect( $dbi_datasource, $dbi_username, $dbi_password,
{ RaiseError => 1 } )
or die "connecting: $DBI::errstr\n";
###########################################################################
# Main Loop #
###########################################################################
#-=======================================================================-#
#-= List of event filters to process =-#
#-=======================================================================-#
$sth = $dbh->prepare( "select user, query, email_val, last_notify "
. " from lens_query "
. " where email_b = 1" );
$sth->execute();
while ( @row = $sth->fetchrow_array )
{
$user = $row[0];
$query = $row[1];
$email_val = $row[2];
$last_notify = $row[3];
&process_filter( $user, $query, $email_val, $last_notify );
}
###########################################################################
# Disconnect from the NetworkLens database #
###########################################################################
$dbh->disconnect()
or die "disconnecting: $DBI::errstr\n";
###########################################################################
### END OF SOURCE FILE ###
########################################################################### |