Firewall drop stats / pfsense firewall log analyzer

[azpoulton]

Hello,

Is there anyway to view stats for blocked connections by the firewall? I found this old post http://forum.pfsense.org/index.php/topic,22941.msg118123.html#msg118123 and I am interested in the stats portion, but not the based on a rule portion. I don't care what firewall rule is causing the block. I guess what I am looking for is a firewall log analyzer that would tell me the number of times a connection to a specific port has been blocked (e.g. 22, 80, 3306 etc) and by what IP. I don't care if it is built into pfsense or an analyzer on another machine with the logs being sent by syslog. I found webfwlog http://webfwlog.sourceforge.net/ and fwlogwatch http://fwlogwatch.inside-security.de/, but it does not support the pf log format. Splunk was also a dead end http://answers.splunk.com/questions/10508/parsing-pfsense-logs . I did see the glTail http://doc.pfsense.org/index.php/Realtime_Remote_pfSense_Log_Visualization_with_glTail but that was much more than what I wanted.  Any help or suggestion would be appreciated.

Thanks

[m4rcu5]

Ow i am subscribing to this one!

This is exactly what i was looking at while back. My main goals is to monitor the IP's that produce high traffic on logged ports and the ones that run against the blocked ones. This would help find out who is trying to mess with services they should not.

The glTail does look good, unfortunately i could not get it to work, i would not parse the log of pfSense 2.0.

-m4rcu5

[azpoulton]

m4rcu5,

I ended up writing a VERY simple and rough solution in python that used regular expression to find the parts I wanted and submit them into a MySQL database. If you are interested I will send you the code.

UPDATE: Here is the code

#!/usr/bin/python

import re,urllib2,MySQLdb,datetime,os
from urllib import urlopen
from xml.dom.minidom import parse, parseString
from xml.etree import ElementTree as ET

#API key for ipinfodb.com
apikey = "GET YOUR OWN KEY"
#import the file
input = open('/home/user/pfsenseparser/grepped.log', 'r')
#error log
error_output = open('/home/user/pfsenseparser/error.log', 'a')
#output files
output = open('final.txt', 'a')

# this allows for the IP to Lat/Long conversion
url = "http://api.ipinfodb.com/v2/ip_query.php?key="+apikey+"&ip="

#MySQL Connect
db = MySQLdb.connect("localhost","pfsenseparser","user","password")
cursor = db.cursor()

#what time is it?
now = datetime.datetime.now()
error_output.write('<------Started at: ' + now.strftime("%Y-%m-%d %H:%M") + '--->\n') 

#number of new entries
num_new_data = 0
num_exist_data = 0

#testing variable no SQL or file write if set to 0
testing = 1

for line in input:

#www.txt2re.com
#################################################

	re1='((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))'	# Month 1
	re2='.*?'	# Non-greedy match on filler
	re3='((?:(?:[0-2]?\\d{1})|(?:[3][0,1]{1})))(?![\\d])'	# Day 1
	re4='.*?'	# Non-greedy match on filler
	re5='((?:(?:[0-1][0-9])|(?:[2][0-3])|(?:[0-9])):(?:[0-5][0-9])(?::[0-5][0-9])?(?:\\s?(?:am|AM|pm|PM))?)'	# HourMinuteSec 1
	re6='.*?'	# Non-greedy match on filler
	re7='(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)(?![\\d])'	# Uninteresting: ipaddress
	re8='.*?'	# Non-greedy match on filler
	re9='((?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?))(?![\\d])'	# IPv4 IP Address 1
	re10='.*?'	# Non-greedy match on filler
	re11='(\\d+)'	# Integer Number 1
	re12='.*?'	# Non-greedy match on filler
	re13='((?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?))(?![\\d])'	# IPv4 IP Address 2
	re14='.*?'	# Non-greedy match on filler
	re15='(\\d+)'	# Integer Number 2

	rg = re.compile(re1+re2+re3+re4+re5+re6+re7+re8+re9+re10+re11+re12+re13+re14+re15,re.IGNORECASE|re.DOTALL)
	m = rg.search(line)
	if m:
	        month1=m.group(1)
	        day1=m.group(2)
	        time1=m.group(3)
	        ipaddress1=m.group(4)
	        int1=m.group(5)
	        ipaddress2=m.group(6)
	        int2=m.group(7)
	        #print "("+month1+")"+"("+day1+")"+"("+time1+")"+"("+ipaddress1+")"+"("+int1+")"+"("+ipaddress2+")"+"("+int2+")"+"\n"

##############################

		#ipaddress1 is the one we want
		#lets see if it is in the database before we ask ipinfodb
		sqlipcheck = "SELECT * FROM pfsenseparser WHERE Ip_Address = ('%s')" % (ipaddress1)
		cursor.execute(sqlipcheck)
		data = cursor.fetchall()
		ipcheck = ""
		for row in data:
			daycheck = row[2]
			timecheck = row[3]
			ipcheck = row[4]
		if ipcheck != ipaddress1:
			#This is a new IP Address
			error_output.write(ipaddress1+' = New Data\n')
			url2 = ipaddress1+"&timezone=false"
		        url3 = url+url2
		        urlobj = urllib2.urlopen(url3)
		        data = urlobj.read()
	   	        urlobj.close()
		        dom = ET.XML(data)
			city = dom.findtext("City")
			country = dom.findtext("CountryName")
			region = dom.findtext("RegionName")
			region = region.replace("'", "")
	 	        lat = dom.findtext("Latitude")
		        long = dom.findtext("Longitude")
			if testing == 1:
				cursor.execute("INSERT INTO pfsenseparser (Month,Day,Time,Ip_Address, Port_Num,Lat,Longitude,City,Country_Name,Region,Num_Connect,Type) VALUES (%s, %s, %s,%s, %s, %s, %s, %s, %s, %s, 1, \"firewall\")", (month1,day1,time1,ipaddress1,int2,lat,long,city,country,region))
				db.commit()
			        output.write(month1+","+day1+","+time1+","+ipaddress1+","+int1+","+lat+","+long+","+ipaddress2+","+int2+" "+"\n")
	        	        #output2.write(month1+","+day1+","+int1+c1+int2+","+ipaddress1+","+int1+","+lat+","+long+","+ipaddress2+","+int2+" "+"\n")
			num_new_data = num_new_data+1
		elif timecheck != time1 and daycheck != day1 and reservedip == -1:
			#This is an existing IP Address but not a duplicate
			sql2 = "SELECT Num_Connect FROM pfsenseparser WHERE Ip_Address = ('%s')" % (ipaddress1)
			cursor.execute(sql2)
			data = cursor.fetchall()
			for row in data:
				num = row[0]
				num_new = int(num)+1
				sql5 = "UPDATE pfsenseparser SET Num_Connect = ('%d') WHERE Ip_Address = ('%s')" % (num_new,ipaddress1)
				if testing == 1:
					cursor.execute(sql5)
					db.commit()
			num_exist_data = num_exist_data+1

now2 = datetime.datetime.now()
if testing == 1:
#send me a text
	os.system('echo \'parser.sh just ran with ' + str(num_new_data) + ' new entries and ' + str(num_exist_data) + ' existing entries\' | mailx youremailhere ')
	#write to error.log
	#insert timestamp and new entries into database
	if num_new_data != 0:
		cursor.execute("INSERT INTO pf_meta (Last_Run,New_Data) VALUES (NOW(),'Y')")
	else:
		cursor.execute("INSERT INTO pf_meta (Last_Run,New_Data) VALUES (NOW(),'N')")
	error_output.write('Ended at: ' + now2.strftime("%Y-%m-%d %H:%M") + ' with ' + str(num_new_data) + ' new entries and ' + str(num_exist_data) + ' existing entries\n')
else:
	 os.system('echo \'Just Testing\' | mailx youremailhere ')
db.close()

Like I said this is very rough. I have a mysql database setup with the correct tables. My end goal was to view the data in a web browser and in google maps. This is why I used the ipinfodb.com lat long lookup. If I find time I will post a more comprehensive how to on my blog. Link to follow…

Link to more info: http://blog.poultonfam.com/brad/2011/04/18/custom-pfsense-firewall-log-analyzer/