[unisog] perl MySQL/Nessus wrapper

Michael Holstein michael.holstein at csuohio.edu
Fri Feb 24 21:29:01 GMT 2006


I just posted this to full-disclosure in response to a question .. some 
here might find it useful as well.

This is a perlscript to import Nessus data into MySQL. I also have 
reporting scripts that can email various departments based on a table of 
who's responsible for what IP (or subnet) .. I was going to clean this 
up one day and post it on a webpage .. but alas.

I have other scripts which integrate nmap results, ciscoworks data, wins 
data, etc -- and if you happen to use Ciscoworks, the reporting scripts 
can tell you where an offending user actually is (and what the 
username/workstation name is from wins).

Happy to share ... email off-list.

(oh .. by the way .. this was one of my first Perl/SQL programming 
efforts a few years ago .. be kind .. suggestions for improvement gladly 
accepted).

Cheers,

Michael Holstein CISSP GCIA
Cleveland State University

--SQL STUFF--

The 'nessusstats' part isn't needed for just storing the results -- 
that's so I can generate reports efficiently). There are other bits in 
there for other parts of this system I cobbled together -- like tables 
that store nmap results and such -- thus you see entries in some tables 
for stuff like that. Remove what you don't need.

CREATE TABLE ipmain (
   idmain int(10) unsigned NOT NULL auto_increment,
   mainip int(10) unsigned NOT NULL default '0',
   lastnmap datetime NOT NULL default '0000-00-00 00:00:00',
   lastnessus datetime NOT NULL default '0000-00-00 00:00:00',
   ipowner varchar(40) default NULL,
   PRIMARY KEY  (idmain),
   KEY xip (mainip)
) TYPE=MyISAM;

CREATE TABLE nessusresults (
   idnessus int(10) unsigned NOT NULL auto_increment,
   domain varchar(15) NOT NULL default '',
   nessushost int(10) unsigned NOT NULL default '0',
   service varchar(40) NOT NULL default '',
   scriptid int(10) unsigned NOT NULL default '0',
   risk tinyint(3) unsigned NOT NULL default '0',
   timestamp datetime NOT NULL default '0000-00-00 00:00:00',
   msg text,
   PRIMARY KEY  (idnessus),
   KEY xidnessus (idnessus),
   KEY knessushost (nessushost),
   KEY knessushost2 (nessushost,service)
) TYPE=MyISAM;

CREATE TABLE nessusstats (
   idstat int(10) unsigned NOT NULL auto_increment,
   domain varchar(15) NOT NULL default '',
   nessushost int(10) unsigned NOT NULL default '0',
   service varchar(40) NOT NULL default '',
   scriptid int(10) unsigned NOT NULL default '0',
   risk tinyint(3) unsigned NOT NULL default '0',
   timestamp datetime NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY  (idstat),
   KEY xidstat (idstat),
   KEY kstat (nessushost),
   KEY kstst2 (nessushost,service)
) TYPE=MyISAM;

-- NESSUS STUFF --

Here is how you run nessus, then involke this script :

/usr/local/nessus/bin/nessus -x -c myrcfile.rc -T nbe -q nessus_host 
1241 nessus_user nessus_passwd ipinputfile outputfile.nbe

cat outputfile.nbe | nice -n 20 ./nessusimport.pl

-- IMPORT SCRIPT --

#!/usr/local/bin/perl
use Net::SMTP;
use Date::Manip;
our $TZ = 'US/Eastern';
use DBI();

#####DATABASE PARAMETERS#####

$DATABASE="--dbname--";
$HOST="--hostname--";
$USERNAME="--username--";
$PASSWORD="--password--";

#connect to the database server
#DBI->trace(1, "trace.log"); #uncomment to log all DBI stuff
$dbh = DBI->connect("DBI:mysql:database=$DATABASE;host=$HOST", 
$USERNAME, $PASSWORD, {'RaiseError' => 1}) || die "Unable to connect: 
$dbh->errstr\n";


######MAIN PROGRAM LOOP######

while ( <STDIN> )
   {
         @results = split '\||\|\|';
         @results[6] =~ tr/;/\n/;
         @results[6] =~ tr/"/'/;
         @results[5] = "7";
         @results[5] = '1' if (@results[6] =~ "Risk factor : Critical");
         @results[5] = '1' if (@results[6] =~ "Risk factor : Serious");
         @results[5] = '1' if (@results[6] =~ "Risk factor : High");
         @results[5] = '2' if (@results[6] =~ "Risk factor : Medium");
         @results[5] = '2' if (@results[6] =~ "Risk factor : Medium/Low");
         @results[5] = '3' if (@results[6] =~ "Risk factor : Low/Medium");
         @results[5] = '3' if (@results[6] =~ "Risk factor : Low");
         @results[6] =~ s`Risk factor : Critical``;
         @results[6] =~ s`Risk factor : High``;
         @results[6] =~ s`Risk factor : Serious``;
         @results[6] =~ s`Risk factor : Medium``;
         @results[6] =~ s`Risk factor : Medium/Low``;
         @results[6] =~ s`Risk factor : Low/Medium``;
         @results[6] =~ s`Risk factor : Low``;
         for (@results[0]) { s/^\s+//;s/\s+$//; }
         for (@results[1]) { s/^\s+//;s/\s+$//; }
         for (@results[2]) { s/^\s+//;s/\s+$//; }
         for (@results[3]) { s/^\s+//;s/\s+$//; }
         for (@results[4]) { s/\<//g;s/^\s+//;s/\s+$//; }
         for (@results[5]) { s/^\s+//;s/\s+$//; }
         for (@results[6]) { s/^\s+//;s/\s+$//;s/\'/\\'/g;}
         my $ip = &dot2dec(@results[2]);
         next unless ($ip > 0);
         $timestamp = UnixDate(@results[4], '%Y-%m-%d %H:%M:%S');
         &findmainip($ip);

#condition 1 (entry is a timestamp for end of host scan)
         if (@results[0] eq "timestamps" and @results[3] =~ 
'host_end|host_start') {
                 &updatemainip($ip,$timestamp);
#               print "Condition 1 Matched\n";
                 }
#condition 2 (entry is a result record)
         if (@results[0] eq "results" and @results[5] < 7) {
                 &findnessustimestamp($ip);

&updatenessus(@results[1],$ip, at results[3], at results[4], at results[5], at nessustime[1], at results[6]);

&updatestats(@results[1],$ip, at results[3], at results[4], at results[5], at nessustime[1]);
#               print "Condition 2 Matched\n";
                 }
         else {
                 next;
                 }

}

#####GLOBAL SUBROUTINES#####

#turn dotted quad into decimal
sub dot2dec {
         my $address = @_[0];
         ($a, $b, $c, $d) = split '\.', $address;
         $decimal = $d + ($c * 256) + ($b * 256**2) + ($a * 256**3);
         return $decimal;
          }

#turn decimal into dotted
sub dec2dot {
         my $address = @_[0];
         $d = $address % 256; $address -= $d; $address /= 256;
         $c = $address % 256; $address -= $c; $address /= 256;
         $b = $address % 256; $address -= $b; $address /= 256;
         $a = $address;
         $dotted="$a.$b.$c.$d";
         return $dotted;
         }

#find IP in master table
sub findmainip {
         my $query = $dbh->prepare("select idmain,mainip from ipmain 
where mainip = '@_[0]'");
         $query->execute || die "Unable to locate IP in table ipmain: 
$dbh->errstr\n";
         @mainip = $query->fetchrow_array;
         return @mainip;
         }

#update/add IP&timestamp in master table
sub updatemainip {
         my $query = $dbh->prepare("select * from ipmain where 
mainip=@_[0]");
         $query->execute || die "Unable to locate IP in table ipmain: 
$dbh->errstr\n";
         @mainip = $query->fetchrow_array;
                 if (@mainip[0]) {
                 $dbh->do("update ipmain set lastnessus='@_[1]' where 
idmain='@mainip[0]'") || die "problem with updatemainip 1:$dbh->errstr\n";
#               print "updated values lastnessus=@_[1] where 
idmain=@mainip[0]\n";
                 }
                 else {
                 $dbh->do("insert into ipmain (mainip,lastnessus) values 
('@_[0]','@_[1]')") || die "problem with updatemainip 2:$dbh->errstr\n";
#               print "inserted values mainip=@_[0], lastnessus=@_[1]\n";
                 }
         return;
         }

#find last nessus timestamp for some IP
sub findnessustimestamp {
         my $query = $dbh->prepare("select idmain,lastnessus from ipmain 
where mainip='@_[0]'") || die "problem with findnessustimestamp: 
$dbh->errstr\n";
         $query->execute || die "Unable to locate nessus timestamp in 
table ipmain: $dbh->errsrt\n";
         @nessustime = $query->fetchrow_array;
         return @nessustime;
         }

#update/add nessus results records in nessusresults table
sub updatenessus {
         my $query = $dbh->prepare("select * from nessusresults where 
nessushost='@_[1]' and scriptid='@_[3]'") || die "problem with 
updatenessus 1:$dbh->errstr\n";
         $query->execute || die "Unable to locate record in 
NessusResults: $dbh->errstr\n";
         @nessus = $query->fetchrow_array;
                 if (@nessus[0]) {
                 $dbh->do("update nessusresults set domain='@_[0]', 
nessushost='@_[1]', service='@_[2]', scriptid='@_[3]', risk='@_[4]', 
timestamp='@_[5]', msg='@_[6]' where idnessus='@nessus[0]'") || die 
"problem with updatenessus 2: $dbh->errstr\n";
#               print "updated values domain=@_[0], host=@_[1], 
service=@_[2], script=@_[3], risk=@_[4], time=@_[5], msg=@_[6]\n";
                 }
                 else {
                 $dbh->do("insert into nessusresults 
(domain,nessushost,service,scriptid,risk,timestamp,msg) values 
('@_[0]','@_[1]','@_[2]','@_[3]','@_[4]','@_[5]','@_[6]')") || die 
"problem with updatenessus 3: $dbh->errstr\n";
#               print "inserted values domain=@_[0], host=@_[1], 
service=@_[2], script=@_[3], risk=@_[4], time=@_[5], msg=@_[6]\n";
                 }
         return;
         }

sub updatestats {
         $dbh->do("insert into nessusstats 
(domain,nessushost,service,scriptid,risk,timestamp) values 
('@_[0]','@_[1]','@_[2]','@_[3]','@_[4]','@_[5]')") || die "problem with
updatestats 1: $dbh->errsrt\n";
#       print "inserted stats values domain=@_[0], host=@_[1], 
service=@_[2], script=@_[3], risk=@_[4], time=@_[5]\n";
         return;
         }


More information about the unisog mailing list