Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Parse /var/log/secure for hacking attempts and insert into a SQL DB
#!/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;
}
}
}
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
--
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment