#!/usr/bin/python
# This script will parse a Linux wtmp file for login data and generate SQL for
#   a login tracking database
# In its current form, it will properly parse output on CentOS 5.5
# This is meant as an example, not a full-fledged analysis solution
#
# WARNING: This script is pretty embarassing.  Please don't laugh.
#          It was designed as a proof of concept for a demo, not high-grade use
#          in an enterprise environment. Suggestions welcome, bugfixes not
#          promised.
#
# Clear pitfalls:
# - Any gap of more than a full calendar year between logins, or between wtmp
#   start date and first login, and the years will be off
#
# Version 0.01
#
# By: Phil Hagen (phil < at > lewestech.com)
# (C) 2011 Lewes Technology Consulting, LLC
# http://stuffphilwrites.com
#
# 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/>.
#
# Database schema expected:
# CREATE TABLE `logins` (
#   `id` int(11) unsigned NOT NULL auto_increment,
#   `userid` varchar(20) NOT NULL,
#   `systemname` varchar(20) NOT NULL default '',
#   `src_ip` int(10) unsigned NOT NULL,
#   `logintime` datetime NOT NULL,
#   `logouttime` datetime NOT NULL,
#   `interesting` enum('Y','N','-') NOT NULL default '-',
#   PRIMARY KEY  (`id`),
#   KEY `src_ip` (`src_ip`),
#   KEY `userid` (`userid`)
# );

import re
import subprocess
import time, datetime
from time import localtime, strptime
import os

# variables you may want to change
wtmpfile = '/var/log/wtmp'
cmdline = 'last -i -f %s' % wtmpfile
systemname = 'mysystem'

# some constants
# regex to match a login record, capturing user, IP, login date/time,
#   duration in days (optional), duration in hours:minutes
linuxre = '^(\S+)\s+\S+\s+([0-9\.]+)\s+\S+\s+(\S+\s+\S+\s+\S+)\s+\S+\s+\S+\s+\((?:([0-9]+)\+)?([0-9:]+)\)'
# regex to match the line indicating wtmp start time, capturing month, year
wtmpstartre = '^wtmp begins \S+\s+(\S+)\s+\S+\s+\S+\s+(\S+)'
# format string describing login time
logintime_format = '%b %d %H:%M %Y'
# regex to match login time, capturing month, day, time
logintimere = '(\S+)\s+(\S+)\s+(\S+)'


# Create an empty list to hold the output lines.  Since Linux shows entries in
#   reverse chrono order, we need to take the temporary hit on memory usage
lines = []

# Run the "last" command and capture the output
p = subprocess.Popen(cmdline, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
# Loop through the results
while 1:
	rawline = p.stdout.readline()
	if rawline != '':
		# trim newline character
		rawline = rawline.rstrip()
		# are we on the line that indicates the start of the wtmp file?
		st = re.match(wtmpstartre, rawline)
		if st:
			# rip out the year and month the file starts
			(month, year) = st.groups()
			month = strptime(month,'%b').tm_mon
			year = int(year)
		else:
			# add the record line to the list
			lines.append(rawline)
	else:
		break
# reverse the list to put it into chronological order
lines.reverse()

# loop through the entries, now in date order
for line in lines:
	# are we on a line that reflects a login?
	m = re.match(linuxre, line)
	if m:
		# extract the relevant fields
		(username, rip, logintime, session_days, session_hours) = m.groups()
		# logins less than 1 day will have session_days equal to None
		if session_days == None:
			session_days = ''
		# pull the month and day in the login record
		lt = re.match(logintimere, logintime)
		if lt:
			(newmonth, newday, newtime) = lt.groups()
			newmonth = strptime(newmonth,'%b').tm_mon
		# detect year rollover
		if newmonth < month:
			year = year+1
			month = newmonth

		# convert the string indicating the logon time from text to a
		#   datetime object
		logintime = datetime.datetime.fromtimestamp(time.mktime(time.strptime(logintime+' '+str(year), logintime_format)))

		# display the SQL INSERT statement for copypasta
		print "INSERT INTO logins (`userid`, `systemname`, `src_ip`, `logintime`, `logouttime`) VALUES ('%s', '%s', INET_ATON('%s'), '%s', ADDTIME('%s', '%s %s'));" % (username, systemname, rip, logintime, logintime, session_days, session_hours)