Parse /var/log/secure for hacking attempts and insert into a SQL DB

parse-secure.pl

#!/usr/bin/perl

#########################################################################
# Parse /var/log/secure for attacks and log the attacks to a database.
# 11/16/2011
# evandhoffman@gmail.com
#########################################################################

use strict;
use warnings;

use DBI;
use Date::Parse;
use Time::ParseDate;

my @patterns = (        qr/(\w{3} \d{2} \d{2}:\d{2}:\d{2}).+invalid user ([\w]+) from ([\d\.]+) port/,
                        qr/(\w{3} \d{2} \d{2}:\d{2}:\d{2}).+User ([\w]+) from ([\d\.]+) not allowed because/,
                        qr/(\w{3} \d{2} \d{2}:\d{2}:\d{2}).+Failed password for (root) from ([\d\.]+) port/ );

my $dbh = DBI->connect("dbi:Pg:dbname=sshlog", 'sshlog', 'sshlog', {AutoCommit => 1});

die "Unable to connect to db" unless $dbh;

### Find the most recent record in the table.  This is so we can ignore records we don't add stuff to the DB twice.

my $sth = $dbh->prepare(qq{
        select extract(EPOCH FROM max(datetime)) as newest from ssh_hack_attempts
});
$sth->execute();
my $result = $sth->fetchrow_hashref();
my $newest_record = $result->{'newest'};
#print "Newest record: $newest_record\n";

# insert into ssh_hack_attempts (datetime, remote_addr, username) values (to_timestamp('Nov 01 2011 00:00:00','Mon DD YYYY HH24:MI:SS'), '127.0.0.1', 'evan');
$sth = $dbh->prepare(qq{
        insert into ssh_hack_attempts (datetime, remote_addr, username) values (to_timestamp(?), ?, ?)
});

while(<>) {
        #Nov 16 07:29:55
        chomp;
        foreach my $pat (@patterns) {
                if (my ($date, $user, $ip) = ( $_ =~ $pat )) {
                        my $epoch = parsedate($date);
                        if ($epoch > $newest_record) {
                #       print "$epoch\t$ip\t$user\n";
                        $sth->execute($epoch, $ip, $user);
                        }

                #       my ($ss,$mm,$hh,$day,$month,$year,$zone) = strptime($date);
                #       print "$year-$month-$day $hh:$mm:$ss\t$ip\t$user\n";
                        #next;
                }
        }
}

ssh_hacking_attempts.sql

Password:
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: ssh_hack_attempts; Type: TABLE; Schema: public; Owner: sshlog; Tablespace:
--

CREATE TABLE ssh_hack_attempts (
    id integer NOT NULL,
    datetime timestamp with time zone NOT NULL,
    remote_addr inet NOT NULL,
    username character varying(255)
);


ALTER TABLE public.ssh_hack_attempts OWNER TO sshlog;

--
-- Name: ssh_hack_attempts_id_seq; Type: SEQUENCE; Schema: public; Owner: sshlog
--

CREATE SEQUENCE ssh_hack_attempts_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.ssh_hack_attempts_id_seq OWNER TO sshlog;

--
-- Name: ssh_hack_attempts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: sshlog
--

ALTER SEQUENCE ssh_hack_attempts_id_seq OWNED BY ssh_hack_attempts.id;


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: sshlog
--

ALTER TABLE ssh_hack_attempts ALTER COLUMN id SET DEFAULT nextval('ssh_hack_attempts_id_seq'::regclass);


--
-- Name: ssh_hack_attempts_pkey; Type: CONSTRAINT; Schema: public; Owner: sshlog; Tablespace:
--

ALTER TABLE ONLY ssh_hack_attempts
    ADD CONSTRAINT ssh_hack_attempts_pkey PRIMARY KEY (id);


--
-- Name: idx_hack_date; Type: INDEX; Schema: public; Owner: sshlog; Tablespace:
--

CREATE INDEX idx_hack_date ON ssh_hack_attempts USING btree (datetime);


--
-- PostgreSQL database dump complete
--