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/
