Query sguildb for alert name then do name lookup

I wrote a Perl script to query for a specific alert name and then get the NetBIOS or DNS name of the systems that triggered the alert. This script is useful to me mainly as an automated reporting tool. An example would be finding a list of systems associated with a specific spyware alert that is auto-categorized within Sguil. The alert will never show up in the RealTime alerts but I use the script to get a daily email on the systems triggering the alert.

Some alerts aren't important enough to require a real-time response but may need remediation or at least to be included in statistics. I don't know if this would be useful for others as it is written, but parts of it might be.

As always with Perl, I welcome suggestions for improvement since I'm by no means an expert. (By the way, does anyone else have problems with Blogger formatting when using <pre> tags?)

#!/usr/bin/perl
#
# by nr
# 2008-05-11
#   Script to query db for a specific alert
#   and do name lookup based on source IP address in results

use strict;

# Requires MySQL, netbios and DNS modules
use Mysql;
use Net::NBName;
use Net::DNS;

my $host = "localhost";
my $database = "sguildb";
my $tablename = "event";
my $user = "sguil";
my $pw = "PASSWORD";
my $alert = 'ALERT NAME Here';

# Set the query
my $sql_query = "SELECT INET_NTOA(src_ip) as src_ip,count(signature) as count FROM $tablename \
    WHERE $tablename.timestamp > DATE_SUB(UTC_DATE(),INTERVAL 24 HOUR) AND $tablename.signature \
    = '$alert' GROUP BY src_ip";

# perl mysql connect()
my $sql_connect = Mysql->connect($host, $database, $user, $pw);

print "\"$alert\" alerts in the past 24 hours: \n\n";
print "Count    IP Address  Hostname\n\n";

my $execute = $sql_connect->query($sql_query); # Run query

# Fetch query results and loop
while (my @result = $execute->fetchrow) {
    my @hostname; # 1st element of this array is used later for name queries
    my $ipresult = $result[0]; # Set IP using query result
    my $count = $result[1]; # Set alert count using query result
    my $nb_query = Net::NBName->new; # Setup new netbios query
    my $nb = $nb_query->node_status($result[0]);
    # If there is an answer to netbios query
    if ($nb) {
        my $nbresults = $nb->as_string; # Get query result
        # Split at < will make $hostname[0] the netbios name
        # Is there a better way to do this using a substitution?
        @hostname = split /</, $nbresults;
    } else {
        # Do a reverse DNS lookup if no netbios response
        # May want to add checks to make sure external IPs are ignored
        my $res = Net::DNS::Resolver->new;
        my $namequery = $res->query("$result[0]","PTR");
        if ($namequery) {
            my $dnsname = ($namequery->answer)[0];
            $hostname[0] = $dnsname->rdatastr;
        } else {
            $hostname[0] = "UNKNOWN"; # If no reverse DNS result
        }
    }
format STDOUT =
@>>>    @<<<<<<<<<<<<<< @*
$count, $ipresult,      $hostname[0]
.
    write;
}

Tweet