#!/usr/bin/perl -w

# Date: 20150418
# Author: Daniel Marsh
# Email: daniel@stiw.org
 
#############################################################################
## 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 3 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, see <http://www.gnu.org/licenses/>.
#############################################################################

# report script partnered with ossec-vt-query.pl

# define library path - this is for libs installed via CPAN shell.
use lib '/home/username/perl5/lib/perl5';

# include the libraries we need
use strict;
use warnings;

use DBI qw(:sql_types); # cannot forget this... big problems otherwise ;)

use VT::API;
use Data::Dumper;
use DBD::SQLite;

# config zone
my $dbfile = "/var/local/ossec-vt.db"; # Location of database to use
# end config zone

my $report;
my $header = "The following files have been reported as potential malware.\n";

# open connection to database
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", "", "");

# grab the list of filenames that have been reported as potential malware
# SQL Query: select * from files, checksums where checksums.virus = 1 and files.checksum=checksums.checksum;
my $sth = $dbh->prepare("select files.filename, files.checksum, checksums.virus, computers.computer from files, checksums, computers where (checksums.checked = 1 and checksums.virus = 1) and files.filename=computers.file and files.checksum=checksums.checksum" );
$sth->execute;
while (my $row = $sth->fetch ) {
        foreach my $val (@{$row}) {
        # put together a pretty report
                chomp($val);
                $report .= "$val : ";
        }
        $report .= "\n";
}
if( defined $report ) {
        print $header.$report;
}

$sth = $dbh->prepare("select count(*) from files" );
$sth->execute;
my $r = $sth->fetch;

print "Files checked: $r->[0]\n";
exit;