#!/usr/bin/env python
# -*- coding: utf-8 -*-

# Program : LILA - Live Iptables Log Analyzer 1.0
# Author  : Joachim Fix (jfix@lavabit.com)
# Email   : jfix@lavabit.com
# WWW     : https://sourceforge.net/projects/lila/
# License : GNU GPLv3

import MySQLdb, ConfigParser, sys, getopt, time, os, re, socket

def init():
	global config, cursor, mysql, cursor2, mysql2, xterm
	global mysql_poll_interval, lila_dbname, lila_table, syslog_dbname, syslog_tablename, syslog_table, log_prefix
	global print_last, showdupes, ignoredupes, dupetime, quit, nodns
	global debug_level, resolver_pdnsd, resolver_dig, resolver_host, resolver_system, dns_cachetime
	global ssh_dns_enabled, ssh_remotehost, savedns, dupecount, list_new, datetime_format, show_spt, show_dpt, spt_format, src_format, disable_blacklist
	global output_col, chain_col, max_chainlen, local_PTR
	
	local_PTR = {}
	savedns = 0
	print_last = 0 # 0 disabled, -1 ALL, wird spaeter überschrieben
	list_new = 0
	
	if os.getenv("TERM") == "xterm":	xterm = 1
	else:								xterm = 0
	
	config = ConfigParser.RawConfigParser()
	#config.read(sys.argv[0].rsplit("/",1)[0]+"/lila.cfg")
	config.read(os.path.realpath(sys.argv[0])+".cfg")
	output_col = dict(config.items("OUTPUT_COLOR_RULES"))
	chain_col = dict(config.items("CHAIN_COLOR_RULES"))
	max_chainlen = len(max(chain_col, key=len))
	showdupes = config.getint("CMDLINE_OPTIONS","always_show_dupes")
	ignoredupes = config.getint("CMDLINE_OPTIONS","never_show_dupes")
	print_last = config.getint("CMDLINE_OPTIONS","print_last")
	dupetime = config.get("CMDLINE_OPTIONS","dupetime")
	quit = config.getint("CMDLINE_OPTIONS","quit")
	resolver_pdnsd = config.getint("DNS","resolver_pdnsd")
	resolver_dig = config.getint("DNS","resolver_dig")
	resolver_host = config.getint("DNS","resolver_host")
	resolver_system = config.getint("DNS","resolver_system")
	dns_cachetime = config.get("DNS","dns_cachetime")
	nodns = config.getint("CMDLINE_OPTIONS","nodns")
	datetime_format = config.get("OUTPUT_FORMAT","datetime_format")
	show_spt = config.getint("OUTPUT_FORMAT","show_sourceport")
	show_dpt = config.getint("OUTPUT_FORMAT","show_destport")
	src_format = config.get("OUTPUT_FORMAT","sourceip_format")
	disable_blacklist = config.getint("CMDLINE_OPTIONS","disable_blacklist")
	ssh_dns_enabled = config.getint("CMDLINE_OPTIONS","ssh_dns_enabled")
	ssh_remotehost = config.get("CMDLINE_OPTIONS","ssh_remotehost")
	mysql_poll_interval = config.getint("GENERAL","poll_interval")
	debug_level = config.getint("GENERAL","debug_level")
	lila_dbname = config.get("MYSQL_LILA","db")
	lila_tablename = config.get("MYSQL_LILA","tablename")
	lila_table = lila_dbname+"."+lila_tablename
	syslog_dbname = config.get("MYSQL_SYSLOG","db")
	syslog_tablename = config.get("MYSQL_SYSLOG","tablename")
	syslog_table = syslog_dbname+"."+syslog_tablename
	log_prefix = config.get("GENERAL","log_prefix").strip("\"")
	
	########## MYSQL_INIT - START ##########
	
	# Verbindung zu lila MySQL aufbauen
	try:
		mysql = MySQLdb.connect(config.get("MYSQL_LILA","host"), config.get("MYSQL_LILA","user"), config.get("MYSQL_LILA","pass"))
		mysql.apilevel		=	config.get("MYSQL_LILA","apilevel")
		mysql.threadsafety	=	config.get("MYSQL_LILA","threadsafety")
		mysql.paramstyle	=	config.get("MYSQL_LILA","paramstyle")
		cursor = mysql.cursor()
	except:
		print "Could not connect to the MySQL LILA database. Check lila.cfg and ensure that MySQL is running.\n"
		exit(101)		
	# Verbindung zu syslog MySQL aufbauen		
	try:
		mysql2 = MySQLdb.connect(config.get("MYSQL_SYSLOG","host"), config.get("MYSQL_SYSLOG","user"), config.get("MYSQL_SYSLOG","pass"))
		mysql2.apilevel		=	config.get("MYSQL_SYSLOG","apilevel")
		mysql2.threadsafety	=	config.get("MYSQL_SYSLOG","threadsafety")
		mysql2.paramstyle	=	config.get("MYSQL_SYSLOG","paramstyle")
		cursor2 = mysql2.cursor()
	except:
		print "Could not connect to the MySQL syslog database. Check lila.cfg and ensure that MySQL is running.\n"
		exit(102)		
	
	# Datenbank erstellen falls nicht bereits vorhanden	
	try:	cursor.execute("CREATE DATABASE "+lila_dbname) #IF NOT EXISTS gibt MySQL Warnung
	except:	pass
	
	# Tabelle erstellen falls nicht bereits vorhanden (TODO: Prüfen ob Tabelle nicht nonsense columns enthält)
	cursor.execute("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=\""+lila_dbname+"\" AND table_name=\""+lila_tablename+"\"")
	table_exists = cursor.fetchone()[0]
	if table_exists:
		#Setze viewed Status zurück
		cursor.execute("UPDATE "+lila_table+" SET viewed=0") #sonst beim 2. mal kein output mit -n / if table_exists...
	else:
		#Tabelle und Columns erstellen
		cursor.execute("USE "+lila_dbname)
		cursor.execute("CREATE TABLE "+lila_tablename+" ( id MEDIUMINT UNSIGNED NOT NULL, timestamp VARCHAR(13), datetime DATETIME, chain VARCHAR(12), src VARCHAR(15), spt SMALLINT(5) UNSIGNED, dst VARCHAR(15), dpt SMALLINT(5) UNSIGNED, proto VARCHAR(8), type TINYINT UNSIGNED, code TINYINT UNSIGNED, ptr VARCHAR(150), viewed TINYINT(1) DEFAULT 0, black TINYINT(1) DEFAULT 0, PRIMARY KEY(id) )")
	########## MYSQL_INIT - END ##########
	
	try:
		opts, args = getopt.getopt(sys.argv[1:], "n:l:t:g:dqf:?omcr:s:baDiSk:x:", ["printlast=", "livelogfile=", "dupetime=", "debuglevel=", "showdupes", "quit","staticfile=","help","nodns","managetables","listnew","remotehost","search","disableblacklist","archive","nodupes","stats","advsearch","killtables","examine"])
	except getopt.GetoptError:
		usage()
		print
		print_notice("LILA: Argument error!\n\n")
		exit(2)
	
	########## Parse cmdline options and set global variables - START ##########
	for opt,arg in opts:
		if opt in ("-n", "--printlast"):
			print_last = arg
		#elif opt in ("-l", "--logfile"):
		#	logfile = arg
		elif opt in ("-t", "--dupetime"):
			dupetime = arg
		elif opt in ("-g", "--debuglevel"):
			debug_level = int(arg)
		elif opt in ("-d", "--showdupes"):
			showdupes = 1
		elif opt in ("-q", "--quit"):
			quit = 1
		#elif opt in ("-f", "--staticfile"):
		#	staticfile_path = arg
		elif opt in ("-?", "--help"):
			usage()
			sys.exit(0)
		elif opt in ("-o", "--nodns"):
			nodns = 1
		elif opt in ("-m", "--managetables"):
			manage_tables()
			exit(0)
		elif opt in ("-c", "--listnew"):
			list_new = 1
		elif opt in ("-r", "--remotehost"):
			ssh_dns_enabled = 1
			ssh_remotehost = arg
		#elif opt in ("-s", "--search"):
		#	search_query = arg
		elif opt in ("-b", "--disableblacklist"):
			disable_blacklist = 1
		#elif opt in ("-a", "--archive"):
		#	backup()
		#	exit(0)
		elif opt in ("-D", "--nodupes"):
			ignoredupes = 1
		#elif opt in ("-i", "--stats"):
		#	stats()
		#	exit(0)
		#elif opt in ("-S", "--advsearch"):
		#	adv_search_enabled = 1
		elif opt in ("-k", "--killtables"):
			show_header()
			sql_cmd, sql_cmd2 = "", ""
			if arg.lower()=="lila":
				sql_cmd = "DROP TABLE "+lila_table
			elif arg.lower()=="dns":
				sql_cmd="DROP TABLE "+lila_dbname+".dns"
			elif arg.lower()=="syslog":
				sql_cmd2 = "TRUNCATE "+syslog_table
			elif arg.lower()=="all":
				sql_cmd = "DROP TABLE "+lila_table
				sql_cmd2 = "TRUNCATE "+syslog_table
			else:
				print_notice("Invalid argument! Possible values are dns, syslog, lila or all.\n\n")
				exit(2)
			if sql_cmd:
				print_notice(sql_cmd+";\n\n")
				cursor.execute(sql_cmd)
			if sql_cmd2:
				print_notice(sql_cmd2+";\n\n")
				cursor2.execute(sql_cmd2)
			exit(0)
		#	#cmd = "sudo /bin/echo -n > "+logfile
		#	cmd = "sudo /bin/dd if=/dev/null of="+logfile+" &> /dev/null"
		#	os.system(cmd)
		#elif opt in ("-x", "--examine"):
		#	toggle_monitor(arg)
		else:
			usage()
			print
			print_notice("Argument error!\n\n")
			exit(3)
	########## Parse cmdline options and set global variables - START ##########
	
	dupetime = time_conv(dupetime)
	dns_cachetime = time_conv(dns_cachetime)
	
	try: 
		cursor.execute("USE "+lila_dbname)
		cursor.execute("CREATE TABLE dns ( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ip VARCHAR(15), pdnsd VARCHAR(60), dig VARCHAR(60), host VARCHAR(60), system VARCHAR(60), datetime DATETIME, PRIMARY key (id))")
	except: pass
	
	if print_last != "ALL":
		try: 
			print_last = int(print_last)
		except:				
			print_notice("Error: Option -n (--print_last) needs an integer or placeholder \"ALL\"\n")
			exit(6)
	else:
		print_last = -1
		
def color(name):

	if name == "red":	
		if xterm:	return "\033[91m"
		else:		return "\033[1;31m"
	elif name == "green":	
		if xterm:	return "\033[92m"
		else:		return "\033[1;32m"
	elif name == "cyan":	
		if xterm:	return "\033[96m"
		else:		return "\033[1;36m"
	elif name == "magenta":	
		if xterm:	return "\033[35m"
		else:		return "\033[0;35m"
	elif name == "lila":	
		if xterm:	return "\033[95m"
		else:		return "\033[1;35m"
	elif name == "grey":	
		if xterm:	return "\033[90m"
		else:		return "\033[1;30m"
	elif name == "yellow":	
		if xterm:	return "\033[93m"
		else:		return "\033[1;33m"
	elif name == "brown": 
		if xterm:	return "\033[33m"
		else:		return "\033[0;33m"
	elif name == "white":
		if xterm:	return "\033[97m"
		else:		return "\033[1;37m"
	elif name == "normal":			#unnoetig nur zur uebersicht
		if xterm:	return "\033[0m"
		else:		return "\033[0m" 
	else:			
		return "\033[0m"

def time_conv(string):
	
	seconds = 0
	found = -1
	if 1 in [char in string for char in ("d","h","m","s")]:
		try:
			for pos in range(0,len(string)):
				if string[pos]=="d":
					seconds += int(string[found+1:pos])*86400
					found = pos
				elif string[pos]=="h":
					seconds += int(string[found+1:pos])*3600
					found = pos
				elif string[pos]=="m":
					seconds += int(string[found+1:pos])*60
					found = pos
				elif string[pos]=="s":
					seconds += int(string[found+1:pos])*1
					found = pos
		except: pass

	try: seconds = int(string)
	except: pass

	return seconds

def isint(value):
	try:
		value = int(value)
		return 1
	except:
		return 0

def pdnsd_test():
	if ssh_dns_enabled:
		#SSH connection test 
		try: ssh_test = os.popen("ssh -o ConnectTimeout=3 "+ssh_remotehost+" 'echo -n test'").readline()
		except: pass
		if ssh_test!="test":
			print_debug(0,"pdnsd_test","SSH connection failed. pdnsd resolving disabled!\n") #SSH failed
			return 0
		try: pdnsd_cmd = "ssh "+ssh_remotehost+" 'sudo -n -u pdnsd "+re.findall("\(pdnsd\) NOPASSWD:.+pdnsd-ctl dump", os.popen("ssh -o ConnectTimeout=10 "+ssh_remotehost+" 'sudo -l'").read())[0][18:]+"'"
		except:
			print_debug(0,"pdnsd_test", "Sudo error on "+ssh_remotehost+". pdnsd resolving disabled. Did you setup sudo correctly?\n")
			return 0
		try:
			dumptest = os.popen(pdnsd_cmd+" 2>&1").readlines()#[-1][:9]
			if "password is required" in dumptest[0]:
				print_debug(0,"pdnsd_test", "Cannot execute "+pdnsd_cmd+". pdnsd disabled. Is pdnsd installed?\n")
				return 0
		except: pass
	else: #kein SSH
		if os.getenv("USER")=="root":
			pdnsd_cmd = "pdnsd-ctl dump"
		else:
			try:
				pdnsd_cmd = "sudo -n -u pdnsd "+re.findall("\(pdnsd\) NOPASSWD:.+pdnsd-ctl dump", os.popen("sudo -l").read())[0][18:]
			except:
				print_debug(0,"pdnsd_test", "Sudo error. pdnsd resolving disabled! Did you setup sudo correctly?\n")
				return 0
		try:
			dumptest = os.popen(pdnsd_cmd+" 2>&1").readlines()#[-1][:9]
			if "password is required" in dumptest[0]:
				print_debug(0,"pdnsd_test", "Cannot execute "+pdnsd_cmd+". pdnsd disabled. Is pdnsd installed?\n")
				return 0			
		except: pass			
	try:
		if dumptest[-1][:9] == "Succeeded": return 1
	except: pass
	print_debug(0,"pdnsd_test", "pdnsd-ctl dump failed. pdnsd resolving disabled! Is pdnsd started and setup correctly?\n")
	return 0	

def blacklist(table):

	sql_cmd="UPDATE "+table+" SET black=0"
	cursor.execute(sql_cmd)
	if not disable_blacklist: #disabled by default
		source_ip = config.get("OUTPUT_BLACKLIST_RULES", "source_ip").replace(" ","").split(",")
		sql_cmd = "UPDATE "+table+" SET black=1 WHERE src=\""+source_ip[0]+"\""
		for i in source_ip[1:]: sql_cmd += " OR src=\""+i+"\""
		for i in config.get("OUTPUT_BLACKLIST_RULES", "source_port").replace(" ","").split(","): 
			if isint(i): sql_cmd += " OR spt=\""+i+"\""
		for i in config.get("OUTPUT_BLACKLIST_RULES", "protocol").replace(" ","").split(","): sql_cmd += " OR proto=\""+i+"\""
		for i in config.get("OUTPUT_BLACKLIST_RULES", "destination_ip").replace(" ","").split(","): sql_cmd += " OR dst=\""+i+"\""
		for i in config.get("OUTPUT_BLACKLIST_RULES", "destination_port").replace(" ","").split(","): 
			if isint(i): sql_cmd += " OR dpt=\""+i+"\""
		#for i in config.get("OUTPUT_BLACKLIST_RULES", "hostname_contains").replace(" ","").split(","): 
		#	sql_cmd += " OR ptr LIKE \"%"+i+"%\""
		# wenn PTR wieder in current gespeichert werden kann, blacklist anpassen! momentan quick&dirty mit etwas unnötigem overhead: (nicht so gutes SQL query):
		if not nodns:
			dns_cmd = "SELECT ip FROM "+lila_dbname+".dns WHERE 0"
			for i in config.get("OUTPUT_BLACKLIST_RULES", "hostname_contains").replace(" ","").split(","): 
				dns_cmd += " OR pdnsd LIKE \"%%%s%%\" OR dig LIKE \"%%%s%%\" OR host LIKE \"%%%s%%\" OR system LIKE \"%%%s%%\"" % (i,i,i,i)
			print_debug(2,"blacklist", dns_cmd)
			try: 
				cursor.execute(dns_cmd)
				for i in cursor.fetchall()[0]: sql_cmd += " OR dst=\"%s\"" % i
			except:
				print_debug(1,"blacklist","exception: No entry found to blacklist.")
		print_debug(2,"blacklist",sql_cmd)
		cursor.execute(sql_cmd)
	return
		

def resolve_ip(ip, quotes):

	global pdnsd_dumpstring
	
	ip = ip.strip('"') # workaround
	PTR = {'pdnsd':'""','dig':'""','host':'""','system':'""'}
			
	if resolver_pdnsd:
		if os.getenv("USER") == "root": bash_cmd = "pdnsd-ctl dump"
		else: bash_cmd = "sudo -u pdnsd /usr/sbin/pdnsd-ctl dump"
		if ssh_dns_enabled: bash_cmd = "ssh "+ssh_remotehost+" '"+bash_cmd+"'"
		pdnsd_dumpstring = ""
		try: pdnsd_dumpstring = os.popen(bash_cmd).read()
		except: print_debug(0,"resolve_ip","pdnsd exception!")
		match = list(re.finditer(ip, pdnsd_dumpstring))
		if match:
			match_count = len(match)
			if match_count > 1: #nimm einfach letzten eintrag (pdnds sortiert leider alphabetisch, also nicht zwangsläufig der aktuelle, lässt sich aber nicht ändern ohne in die dns table zu schauen)
				start = match[-2].end()
				end = match[-1].start()
			else: # match_count = 1
				start = 0
				end = match[-1].start()
			PTR["pdnsd"] = "\""+re.findall(".+\.\n", pdnsd_dumpstring[start:end])[-1][:-2]+"\""
		else:
			PTR["pdnsd"] = '""'
	else:
		PTR["pdnsd"] = "NULL"
	
	if resolver_dig:
		bash_cmd = 'dig +short -x '+ip+' PTR'
		if ssh_dns_enabled: bash_cmd = "ssh "+ssh_remotehost+" '"+bash_cmd+"'"
		try: PTR["dig"] = "\""+os.popen(bash_cmd).readlines()[-1].rstrip("\n").rstrip(".")+"\"" #exception wenn leere antwort, z.B. für LAN ips
		except: print_debug(1,"resolve_ip", "dig exception!")
	else: PTR["dig"] = "NULL"

	if resolver_host:
		bash_cmd='host '+ip
		if ssh_dns_enabled: bash_cmd="ssh " + ssh_remotehost+" '"+bash_cmd+"'"
		try: PTR["host"] = "\""+os.popen(bash_cmd).readlines()[0].rstrip("\n").split(" ")[-1].rstrip(".")+"\""
		except: print_debug(1,"resolve_ip", "host exception!")
		if PTR["host"] == "\"3(NXDOMAIN)\"": PTR["host"] = '""'
	else: PTR["host"]="NULL"
		
	#if resolver3 == "system" and (PTR2 == PTR1 or PTR2 == ""): 
	if resolver_system: 
		try: PTR["system"] = "\""+socket.gethostbyaddr(ip)[0]+"\"" #gibt exception für LAN IPs
		except: print_debug(1,"resolve_ip", "system resolver exception!")
	else: PTR["system"] = "NULL"

	for resolver in PTR.keys(): 
		if "in-addr.arpa" in PTR[resolver]: PTR[resolver] = '""' #Testen!
		if not quotes: PTR[resolver] = PTR[resolver].strip('"')
		
	#print_debug(2,"test", PTR)
	#print_debug(2,"resolve_ip", "PTR[resolver]: pdnsd="+PTR["pdnsd"]+" dig="+PTR["dig"]+" 
		
	return PTR
	
	
def get_hostnames(ip, packet_date, quotes):
	
	#packet_date muss datetime object sein! 
			
	global pdnsd_dumpstring, dns_entries_count, local_PTR
	
	dns_entries_count = -1 #workaround global and -1
	
	PTR = {'pdnsd':"NULL",'dig':"NULL",'host':"NULL",'system':"NULL"} # und log
	ip = '"'+ip.strip('"')+'"' #sicherstellen, dass ip in quotes is
	
	if ip in local_PTR: 
		PTR = {'pdnsd':local_PTR[ip][0],'dig':local_PTR[ip][1],'host':local_PTR[ip][2],'system':local_PTR[ip][3],'log':local_PTR[ip][4]} # eleganter wäre dict in dict und nicht 2x strip
		if not quotes: 
			for i in PTR.keys(): PTR[i] = PTR[i].strip('"')
		print_debug(1,"get_hostnames","Using local DNS cache.")
		return PTR

	
	sql_cmd = "SELECT id, pdnsd, dig, host, system, datetime FROM "+lila_dbname+".dns WHERE ip="+ip+" ORDER BY datetime DESC LIMIT 1"
	print_debug(4, "get_hostnames", sql_cmd)
	cursor.execute(sql_cmd)
	dns_latest_packet = cursor.fetchone()
	cur_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
	if dns_latest_packet is not None: #DNS Eintrag gefunden
		
		#Prüfen ob neuester DNS Eintrag aktualisiert werden soll
		sql_cmd = "SELECT COUNT(*) FROM "+lila_dbname+".dns WHERE id="+str(dns_latest_packet[0])+" AND datetime >= DATE_SUB(\""+cur_time+"\", INTERVAL "+str(dns_cachetime)+" SECOND)"
		print_debug(4, "get_hostnames", "is_current="+sql_cmd)
		cursor.execute(sql_cmd)
		is_current = cursor.fetchone()[0]
		if debug_level>0:
			cursor.execute("SELECT COUNT(*) FROM "+lila_dbname+".dns WHERE ip="+ip)
			dns_entries_count = cursor.fetchone()[0]
		
		if not is_current: #Update latest DNS entry or create new one if changes are detected
			print_debug(1, "get_hostnames", "Found "+str(dns_entries_count)+" DNS entries in table! Latest entry needs update. (is_current = "+str(is_current)+")")
			PTR = resolve_ip(ip, True)
			cur_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime()) #Zeit nochmal aktualisieren weil lookups dauern können
			if PTR["pdnsd"].strip('"')==dns_latest_packet[1] and PTR["dig"].strip('"')==dns_latest_packet[2] and PTR["host"].strip('"')==dns_latest_packet[3] and PTR["system"].strip('"')==dns_latest_packet[4]:
				print_debug(2, "get_hostnames", "DNS values didn't change. Updating datetime of the latest entry.")
				sql_cmd = "UPDATE "+lila_dbname+".dns SET datetime=\""+cur_time+"\" WHERE id="+str(dns_latest_packet[0])
				print_debug(2, "get_hostnames", sql_cmd)
				cursor.execute(sql_cmd)
			else:
				print_debug(2, "get_hostnames", "DNS values changed. Adding new entry to database")
				sql_cmd="INSERT INTO dns (id, ip, pdnsd, dig, host, system, datetime) VALUES (NULL, "+ip+", "+PTR["pdnsd"]+", "+PTR["dig"]+", "+PTR["host"]+", "+PTR["system"]+", \""+cur_time+"\")"
				print_debug(2, "get_hostnames", sql_cmd)
				cursor.execute(sql_cmd)
							
		else: #Latest DNS entry ist aktuell
			print_debug(1, "get_hostnames", "Found "+str(dns_entries_count)+" DNS entries in table! Latest entry (id "+str(dns_latest_packet[0])+") needs no update. (is_current = "+str(is_current)+")")
		
		
		sql_cmd = "SELECT id, pdnsd, dig, host, system, datetime FROM "+lila_dbname+".dns WHERE ip="+ip+" AND datetime >= \""+str(packet_date)+"\" ORDER BY datetime ASC LIMIT 1" 
		print_debug(4, "get_hostnames", sql_cmd)
		cursor.execute(sql_cmd)
		dns_packet = cursor.fetchone()
		if dns_packet is None: #wird none wenn dns eintrag "aktuell" ist, jedoch älter als das zu untersuchende paket
			dns_packet = dns_latest_packet
		
		print_debug(1, "get_hostnames", "Using DNS entry "+str(dns_packet[5])+" with id "+str(dns_packet[0]))
		ii = 1
		for i in "pdnsd","dig","host","system":
			PTR[i] = dns_packet[ii]
			ii += 1
				
	else: #kein DNS Eintrag vorhanden
		print_debug(1,"get_hostnames","No DNS entry found. Creating new one.")
		PTR = resolve_ip(ip, True)
		cur_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime()) #Zeit nochmal aktualisieren weil lookups dauern können
		sql_cmd="INSERT INTO dns (id, ip, pdnsd, dig, host, system, datetime) VALUES (NULL, "+ip+", "+PTR["pdnsd"]+", "+PTR["dig"]+", "+PTR["host"]+", "+PTR["system"]+", \""+cur_time+"\")"
		print_debug(2,"get_hostnames", sql_cmd)
		cursor.execute(sql_cmd)
		
	if not resolver_pdnsd: PTR["pdnsd"] = '""' #quick & dirty ;)
	if not resolver_dig: PTR["dig"] = '""'
	if not resolver_host: PTR["host"] = '""'
	if not resolver_system: PTR["system"] = '""'
						
	#### create single log_PTR_string from all PTRs (no duplicates!) - start####
	PTR_log = ""
	for i in PTR.keys(): 
		if PTR[i] != "NULL" and PTR[i] != '""' and not PTR[i].strip('"') in PTR_log : PTR_log += ", "+PTR[i].strip('"')
	PTR["log"] = "\""+PTR_log[2:]+"\"" #string fängt immer mit ", " an
	#### create single log_PTR_string from all PTRs (no duplicates!) - end####
	
	if not print_last: #Bei der Analyse von älteren Paketen, lokalen DNS cache deaktivieren
		local_PTR[ip]=[ PTR["pdnsd"], PTR["dig"], PTR["host"], PTR["system"] , PTR["log"] ]
		print_debug(2,"get_hostnames","local_PTR set to "+str(local_PTR))
	
	if not quotes: 
		for i in PTR.keys(): PTR[i] = PTR[i].strip('"')
		
	print_debug(2,"get_hostnames","PTR[\"log\"] = "+PTR["log"])
	
	
	return PTR

	
def extract(log, quotation_marks, empty):

	# empty = "" # empty = "NULL"
	dict = {'chain':empty, 'src':empty, 'spt':empty, 'dst':empty, 'dpt':empty, 'proto':empty, 'type':empty, 'code':empty, 'timestamp':empty, 'datetime':empty }
	#day = empty
	#monat = {'Jan':'01','Feb':'02','Mar':'03','Apr':'04','May':'05','Jun':'06','Jul':'07','Aug':'08','Sep':'09','Oct':'10','Nov':'11','Dec':'12'}
	match = re.search(log_prefix+'\S+', log)
	if match!=None: dict["chain"] = match.group(0)[len(log_prefix):]
	match = re.search('SRC\=([0-9]+\.){3}[0-9]+', log)
	if match!=None: dict["src"]=match.group(0)[4:]
	match = re.search('SPT\=[0-9]+', log)
	if match!=None: dict["spt"]=match.group(0)[4:]
	match = re.search('DST\=([0-9]+\.){3}[0-9]+', log)
	if match!=None: dict["dst"]=match.group(0)[4:]
	match = re.search('DPT\=[0-9]+', log)
	if match!=None: dict["dpt"]=match.group(0)[4:]
	match = re.search('PROTO\=[A-Z0-9]+', log)
	if match!=None: dict["proto"]=match.group(0)[6:]
	match = re.search('TYPE\=[0-9]+', log)
	if match!=None: dict["type"]=match.group(0)[5:]
	match = re.search('CODE\=[0-9]+', log)
	if match!=None: dict["code"]=match.group(0)[5:]
	match = re.search('[0-9]+\.[0-9]+\]', log)
	if match!=None: dict["timestamp"]=match.group(0)[:-1]
	#match = re.search('[0-9]+', log)
	#if match!=None: day=match.group(0)
	#match = re.search('[0-9]+:[0-9]+:[0-9]+', log)
	#if match!=None: dict["datetime"] = str(time.localtime()[0])+"-"+monat.get(log[:3], "")+"-"+str(day)+" "+str(match.group(0))
	if dict["proto"]=="2": dict["proto"]="IGMP"

	if quotation_marks:
		for i in dict.keys():
			if dict[i] != "NULL": dict[i]="\""+dict[i]+"\""

	return dict
def sql_write(table, lila_id_start, syslog_id_start, syslog_id_end, print_progress):
	
	packet_count = syslog_id_end-syslog_id_start
	
	if print_progress:
		max_len = len(str(packet_count+1))
		sys.stdout.write(" "*(2*max_len+2))
		
	cursor2.execute("SELECT msg,datetime FROM "+syslog_table+" WHERE id>="+str(syslog_id_start)+" AND id<="+str(syslog_id_end))
	syslog_packet=cursor2.fetchall() #Paket 1 = [0][0] , Paket 2 = [1][0] , Paket 3 = [2][0]...
	i=0
	while i<=packet_count:
		#print "i=",i
		log = extract(syslog_packet[i][0], 1, "NULL")
		log["datetime"] = "\""+str(syslog_packet[i][1])+"\""
		
		if savedns and not nodns: log_PTR = get_hostnames(log["dst"], syslog_packet[i][1], True) #erwartet datetime object keinen string!
		else: log_PTR = "NULL"
		sql_cmd="INSERT INTO "+lila_table+" (id, timestamp, datetime, chain, src, spt, dst, dpt, proto, type, code, ptr) VALUES "
		sql_cmd += "("
		sql_cmd += str(lila_id_start+i)
		sql_cmd += ", "+log["timestamp"]
		sql_cmd += ", "+log["datetime"]
		sql_cmd += ", "+log["chain"]
		sql_cmd += ", "+log["src"]
		sql_cmd += ", "+log["spt"]
		sql_cmd += ", "+log["dst"]
		sql_cmd += ", "+log["dpt"]
		sql_cmd += ", "+log["proto"]
		sql_cmd += ", "+log["type"]
		sql_cmd += ", "+log["code"]
		#sql_cmd += ", NULL" 
		sql_cmd += ", "+log_PTR
		#if backup_startid:
		#	sql_cmd += ", "+str(line_start)
		#	line_start += 1
		sql_cmd += ")"
		#print sql_cmd
		cursor.execute(sql_cmd)
		i=i+1
		if print_progress:
			sys.stdout.write("\b"*(2*max_len+1))
			sys.stdout.write(str(i).rjust(max_len)+"/"+str(packet_count+1))
			sys.stdout.flush()
				
	if print_progress: sys.stdout.write("\b"*(2*max_len+1)+"Done!"+" "*(2*max_len-4)+"\n\n")
	

def get_syslog_count():

	cursor2.execute("SELECT COUNT(*) FROM "+syslog_table)
	syslog_count = cursor2.fetchone()[0]
	return syslog_count

		
def set_dbvars():
	
	global lila_last_packet, lila_maxid, syslog_packet, syslog_minid, syslog_maxid, syslog_curid, new_entries_count
	
	#cursor2.execute("SELECT COUNT(*) FROM "+syslog_table)
	#syslog_datacount = int(cursor2.fetchone()[0])
	#syslog_maxid und syslog_datacount sind nicht identisch falls die syslogtable mit einer id>1 beginnt
	#das ist dann der fall wenn z.B. am Anfang etwas rausgelöscht wurde (warum auch immer) 
	#In der Mitte löschen ist problematisch da sql_write von min bis max id schreibt und zwischen drin ids fehlen würden0
		
	try:
		cursor.execute("SELECT id,timestamp,datetime FROM "+lila_table+" ORDER BY id DESC LIMIT 1")
		lila_last_packet = cursor.fetchone()
		lila_maxid = lila_last_packet[0]
	except:
		lila_last_packet = None
		lila_maxid = 0
	try:
		cursor2.execute("SELECT id FROM "+syslog_table+" ORDER BY id ASC LIMIT 1")
		syslog_minid = cursor2.fetchone()[0]
		cursor2.execute("SELECT id FROM "+syslog_table+" ORDER BY id DESC LIMIT 1")
		syslog_maxid = cursor2.fetchone()[0]
	except:
		syslog_minid = 0
		syslog_maxid = -1 #da entries anzahl = maxid-minid+1
	try:
		cursor2.execute("SELECT id,msg,datetime FROM "+syslog_table+" WHERE msg LIKE \"%"+str(lila_last_packet[1])+"%\" AND datetime=\""+str(lila_last_packet[2])+"\"")
		syslog_packet = cursor2.fetchone()
		syslog_curid = syslog_packet[0]
	except:
		syslog_packet = None
		syslog_curid = 0
	
	new_entries_count = syslog_maxid-syslog_curid #syslog_maxid kann -1 werden
	if new_entries_count == -1: new_entries_count = 0

def print_notice(string):
	sys.stdout.write(" "+color("green")+"* "+color("normal")+string)
	sys.stdout.flush()
	
def print_debug(level, function, text):
	if debug_level < level: return
	print " "+color("brown")+"* "+color("normal")+function+": "+text
	
	
	
def init_lila_db():
		
	set_dbvars() #global lila_last_packet, lila_maxid, syslog_packet, syslog_minid, syslog_maxid, syslog_curid, new_entries_count
					
	if lila_last_packet is not None: 	# Lila DB ist nicht leer und enthält mindestens einen Datensatz
		
		if syslog_packet is not None:	# Syslog DB enthält den letzen Datensatz von LILA
			print_notice("Existing table found ("+str(lila_maxid)+" packets).\n\n")
			if new_entries_count: 
				print_notice("Adding "+str(new_entries_count)+" new syslog entries..." )
				sql_write(lila_table, lila_maxid+1, syslog_curid+1, syslog_maxid, 1)
		else:							# letztes LILA Paket existiert nicht in der Syslog DB
			print_notice("New syslog detected.\n\n")
			print_notice("Creating new LILA table and adding all "+str(syslog_maxid-syslog_minid+1)+" syslog entries...")
			cursor.execute("TRUNCATE "+lila_table)
			sql_write(lila_table, 1, syslog_minid, syslog_maxid, 1)
	
	else: 								# LILA Datenbank leer
		
		if syslog_minid:				# LILA leer, Syslog hat inhalt (prinzipell auch mit maxid, geht aber nicht, da maxid -1 werden kann)
			print_notice("LILA table is empty.\n\n")
			print_notice("Adding all "+str(syslog_maxid-syslog_minid+1)+" syslog entries...")
			sql_write(lila_table, 1, syslog_minid, syslog_maxid, 1)
		else: 							# LILA leer, Syslog auch leer
			print_notice("LILA and SYSLOG tables are empty.\n\n")
					

def manage_tables():

	show_header()
	#print_notice("Showing tables in database "+lila_dbname+"...\n\n")
	cursor.execute("SELECT table_name, table_rows, create_time FROM information_schema.tables WHERE table_schema=\""+lila_dbname+"\" ORDER BY create_time ASC")
	data = cursor.fetchall()
	if not len(data):
		print "No tables found! Goodbye."
		exit(0)
	print color("white")+" No. Tablename                                Entries  Date of creation"+color("normal")
	print
	for i in range(0,len(data)):
		print color("white")+(str(i).rjust(3)).ljust(5)+color("normal")+data[i][0].ljust(39),"  ",(str(data[i][1]).rjust(5))+" ",data[i][2]
	print
	eingabe = raw_input("Enter number(s) of tables to delete separated by comma : ")
	print
	tables_input = (eingabe.replace(" ","").split(",")) #delete whitespaces
	#tables_input = ({}.fromkeys(tables_input)).keys() #delete dupes (unsorted)
	print color("white")+" No. Tablename                                Entries  Date of creation"+color("normal")
	print
	tables_todelete = []
	for i in range(0,len(data)):
		if str(i) in tables_input:
			outcol = color("red")
			tables_todelete.append(i)			
		else:
			outcol = color("normal")
	
		print outcol+(str(i).rjust(3)).ljust(5),data[i][0].ljust(39),"  ",(str(data[i][1]).rjust(5)).ljust(3),"    ",data[i][2]

	print color("normal")
	eingabe = raw_input("The red colored tables will be deleted! Type y or yes to continue : ")
	print

	if eingabe.lower()=="y" or eingabe.lower()=="yes":

		for i in tables_todelete:

			try:
				sql_cmd="DROP TABLE "+lila_dbname+"."+data[i][0]
				cursor.execute(sql_cmd)
			except:
				print_debug(1, "manage_tables", "command executed : "+sql_cmd+";")
				print_notice("SQL-Error: Aborting!\n\n") #should not happen
				exit(4)

		print_notice(str(len(tables_todelete))+" tables have been dropped.\n\n")
	else:
		print_notice("Aborted! No changes were made to the database.\n\n")

	exit(0)	


	
def sql_find(table, query):

	#pruefen ob table existiert
	global date_from, date_till
	
	if query[:7] == " WHERE ": adv_find = 1
	else: adv_find = 0
	
	blacklist(table)
	cursor.execute("UPDATE "+table+" SET viewed=0")

	if adv_find:
		sql_cmd = "SELECT id FROM "+table+query
	else:
		query="\"%"+query+"%\""
		sql_cmd = "SELECT id FROM "+table+" WHERE (dst LIKE "+query+" OR ptr LIKE "+query+" OR src LIKE "+query+")"

	cursor.execute("SELECT id FROM "+table+" ORDER BY id DESC LIMIT 1")
	ab_id = cursor.fetchone()[0] - print_last

	if ab_id and print_last and date_from=="": # -n ALL ==> ab_id = 0 , gar kein n angegeben ==> print_last  = 0, wenn date im adv_query angegeben ignoriere n
		if ab_id<0: ab_id = 0
		sql_cmd += " AND id>"+str(ab_id)
		cursor.execute("SELECT date FROM "+table+" WHERE id="+str(ab_id))
		date_from = str(cursor.fetchone()[0])
	try:
		cursor.execute(sql_cmd)
	except: 
		print color("white")+"Invalid search query!"+color("normal")
		if debug>=1: print sql_cmd
		return

	if not adv_find: print "Searching table \""+table+"\"...\n"
	data = cursor.fetchall()
	dupecount2 = 0
	for i in data:
		sql_output(table, i[0], i[0])
		if dupecount: dupecount2 += 1
	if len(data): print

	if date_from=="":
		cursor.execute("SELECT date FROM "+table+" WHERE id=1")
		date_from = str(cursor.fetchone()[0])
	if date_till=="":
		cursor.execute("SELECT date FROM "+table+" ORDER BY id DESC LIMIT 1")
		date_till = str(cursor.fetchone()[0])
	
	if len(data):
		print color("white")+"Showing",len(data)-dupecount2,"of",len(data),"matching",
		if len(data)==1: print "packet",
		else: print "packets",
	else:
		print color("white")+"No matching packets found",

	print "between "+date_from+" and "+date_till+color("normal")
	
	if not adv_find:

		print
		print "Searching the DNS table...",
		print
		print
		sql_cmd = "SELECT * FROM dns WHERE ip LIKE "+query+" OR ptr LIKE "+query+" OR ptr2 LIKE "+query
		cursor.execute(sql_cmd)
		data = cursor.fetchall()
		cursor.execute("SELECT date FROM dns WHERE id=1")
		first_date = str(cursor.fetchone()[0])
		cursor.execute("SELECT date FROM dns ORDER BY id DESC LIMIT 1")
		last_date = str(cursor.fetchone()[0])

		if len(data):
			print color("white")+"  ID          DATE                IP           PTR1, PTR2"+color("normal")
			for i in range(0,len(data)):
				print (str(data[i][0]).rjust(4)).ljust(7)+str(data[i][4])+"   "+str(data[i][1]).ljust(18)+str(data[i][2])+", "+str(data[i][3])

		print
		if len(data): print color("white")+"Found",len(data),"matching entries",
		else: print color("white")+"No matching entries found",
		print "between "+first_date+" and "+last_date	
				
def show_header():


	print color("magenta")+"==========================================================="
	print "   "+color("lila")+"LILA"+color("normal")+" - "+color("lila")+"L"+color("normal")+"ive "+color("lila")+"I"+color("normal")+"ptables "+color("lila")+"L"+color("normal")+"og "+color("lila")+"A"+color("normal")+"nalyzer - version 1.0"
	print color("magenta")+"==========================================================="+color("normal")
	print

def usage():

	show_header()
	print "Usage: lila [OPTIONS]"
	print
	print "Option             GNU long option              Meaning"
	print
	#print "-a                 --archive                    Create / update archive (backup mode). To analyze backup logs use -f BACKUP (implies -q)."
	print "-b                 --disableblacklist           Ignore the blacklist rules."
	print "-c                 --listnew                    List newly added entries since the last start of LILA (overrides -n)." 
	print "-d                 --showdupes                  Always show every entry, except blacklisted ones (overrides -D)."
	print "-D                 --nodupes                    Never show dupe entries. Print only one packet per destination IP and chain."
	#print "-f <staticfile>    --staticfile=<staticfile>    Print the contents of <staticfile> and save them to a table using the file's MD5 hash."
	#print "                                                Use -f BACKUP to print the backup table (implies -q)."
	#print "                                                Use -f <IP> to analyze a monitored IP."
	print "-g <level>         --debuglevel=<level>         Print some debug messages."
	#print "-i                 --stats                      Show information and TOP 10 statistics screen."
	#print "-k                 --killogs                    Delete the entire logfile on the hard disk before starting LILA (sudoers entry must exist)."
	print "-k <target>        --killtables=<target>        Drop lila table and truncate syslog table. Targets: lila, dns, syslog, all"
	print "                                                Note: Target all drops lila and truncates syslog table. DNS table is unaffected."
	#print "                                                sudoers example entry: "+username+"     "+hostname+"=NOPASSWD: /bin/dd if=/dev/null of="+logfile
	#print "-l <logfile>       --livelogfile=<logfile>      Use <logfile> as live log file."
	print "-m                 --managetables               View and delete tables created by LILA (implies -q)."
	print "-n <# of lines>    --print_last=<# of lines>    Print the last n lines and then start live log monitoring."
	print "                                                Use -n ALL to display every entry. Blacklist rules are nevertheless obeyed."
	#print "                                                In live log mode use -n ALL to display every entry. For staticfiles you don't need -n ALL."
	print "-o                 --nodns                      Don't resolve IPs to hostnames and don't output hostnames."
	print "-q                 --quit                       Quit immediately after first action (useful with -n)."
	print "-r <host>          --remotehost=<remotehost>    Use SSH to resolve hostnames on a remote computer."
	print "                                                Note: Please use ssh-agent (cmd: ssh-add) to cache your login credentials."
	#print "                                                Note2: You might want to mount the logfile dir as a network (ssh) filesystem. Read lila.cfg."
	#print "-s <keyword>       --search=<keyword>           Search the current and the DNS table by keyword (host, ip...) (implies -q)."
	#print "                                                Combine with -f BACKUP to search the archive / backup table."
	#print "-S                 --advsearch                  Start the advanced search mode. When active type help [ENTER] for more info."
	print "-t <sec>           --dupetime=<sec>             Set the time interval for determining dupe entries. Default: "+config.get("CMDLINE_OPTIONS","dupetime")
	print "                                                You can also specify days, hours, minutes and seconds. Example: -t 1d2h5m1s"
	print "                                                Note: -t 0 is not the same as -d! To display everything you have to specify -d"
	#print "-x <IP>            --examine=<IP>               Examine an IP address: Toggles monitor mode on/off for a certain IP."
	print "-?                 --help                       Print this help screen (implies -q ;-))."
	print
	print "Author: Joachim Fix (jfix@lavabit.com)"


def wait_till_change():
	
	#updatetime_old = get_updatetime()
	#updatetime_new = updatetime_old
	syslog_count_old = get_syslog_count()
	syslog_count_new = syslog_count_old
	
	while syslog_count_new == syslog_count_old:
		time.sleep(mysql_poll_interval)
		syslog_count_new = get_syslog_count()
def exit(exitcode):

	print_notice(color("normal")+"Exiting "+color("lila")+"LILA"+color("normal")+"... Goodbye!"+color("normal"))
	if exitcode: print " Exitcode "+str(exitcode)+".\n"
	else: print "\n"
	cursor.close() # kann warnung geben wenn z.b. waehrend -n ALL beendet wird
	mysql.commit() # ...
	mysql.close()
	cursor2.close() 
	mysql2.commit() 
	mysql2.close()
	sys.exit(exitcode)
	
def sql_output(table, id_start, id_end):
	
	global dupecount
	print_debug(2, "sql_output", "sql_output("+table+", "+str(id_start)+", "+str(id_end)+")")
	print_debug(2, "sql_output", "max_chainlen = "+str(max_chainlen))
	blacklist(table)
	entries_count = id_end-id_start+1
	cur_id = id_start-1 
		
	################### MARK DUPE ENTRIES - start #################
	if not showdupes:
		while cur_id < id_end: #mark new dupe entries
			sql_cmd = "SELECT id, chain, dst, datetime FROM %s WHERE black=0 AND id>%s ORDER BY id ASC LIMIT 1" % (lila_table, cur_id)
			print_debug(3, "sql_output", sql_cmd)
			cursor.execute(sql_cmd)
			cur_packet = cursor.fetchone()
			if cur_packet is None: 
				print_debug(2,"sql_output", "No packet found! --> break()")
				break
			cur_id = cur_packet[0]
			print_debug(3, "sql_output", "cur_packet: "+str(cur_packet))
			if id_start != id_end:
				sql_cmd = "UPDATE %s SET black=1 WHERE id>%s AND chain=\"%s\" AND dst=\"%s\" AND datetime <= DATE_ADD(\"%s\", INTERVAL %s SECOND)" % (
					lila_table, cur_id, cur_packet[1], cur_packet[2], cur_packet[3], dupetime)
				print_debug(2, "sql_output", sql_cmd)
				cursor.execute(sql_cmd)
			if not print_last: #== if live_mode (in live mode "older" logs than specified in the function args have to be analyzed)
				sql_cmd = "SELECT id, chain, dst, datetime, viewed FROM %s WHERE viewed=1 AND id<%s AND chain=\"%s\" AND dst=\"%s\" ORDER by id DESC LIMIT 1" % (lila_table, id_start, cur_packet[1], cur_packet[2]) #check for old dupes and mark (for live mode)
				print_debug(2, "sql_output (live mode detected)", sql_cmd)
				cursor.execute(sql_cmd)
				old_packet = cursor.fetchone()
				if old_packet is not None: 
					sql_cmd = "UPDATE %s SET black=1 WHERE id>=%s AND chain=\"%s\" AND dst=\"%s\" AND datetime <= DATE_ADD(\"%s\", INTERVAL %s SECOND)" % (lila_table, id_start, old_packet[1], old_packet[2], old_packet[3], dupetime)
					print_debug(2, "sql_output", sql_cmd)
					cursor.execute(sql_cmd)
	################### MARK DUPE ENTRIES - end ##-#################
	
	cursor.execute("SELECT COUNT(*) FROM %s WHERE black=1 AND id>=%s AND id<=%s" % (lila_table, id_start, id_end))
	dupecount = cursor.fetchone()[0]
	print_debug(1, "sql_output", "%s of %s packets with ids from %s to %s are dupes." % (dupecount, entries_count, id_start, id_end))
	#                  0      1          2       3      4   5    6    7     8     9     10    11    12     13
	sql_cmd = "SELECT id, timestamp, datetime, chain, src, spt, dst, dpt, proto, type, code, ptr, viewed, black \
		FROM %s WHERE id>=%s AND id <=%s AND black = 0 ORDER BY id ASC" % (lila_table, id_start, id_end)
	cursor.execute(sql_cmd)
	output_packets = cursor.fetchall()
	print_debug(3, "sql_output", "entries: \n"+str(output_packets))
		
	########## Actual Output - start ######-####
	
	for packet in output_packets:
		
		outstr = color(output_col["datetime"])+time.strftime(datetime_format, time.strptime(str(packet[2]), "%Y-%m-%d %H:%M:%S"))+" "
		outstr += color(chain_col.get(packet[3].lower(), "normal"))+packet[3].ljust(max_chainlen+1)
			
		if src_format == "hostname": 
			try: 
				src_str = socket.gethostbyaddr(packet[4])[0]
				src_spt_ljust = 7
			except: 
				src_str = packet[4]
				src_spt_ljust = 21-len(src_str) 
		elif src_format == "ip":
			src_str = packet[4]
			src_spt_ljust = 21-len(src_str) #XXX.XXX.XXX.XXX:YYYYY 21=15+6 zeichen! ,d.h. 21-len(src_str) STANDARD annahme: variable länge da untersch. src ips
		else:
			src_str = src_format
			src_spt_ljust = 7 # hostname immer gleich lang
						
		spt=""
		if packet[5] is not None and show_spt: spt=":"+str(packet[5])
		outstr += color(output_col["source_ip"])+src_str+color(output_col["source_port"])+spt.ljust(src_spt_ljust) 
		if packet[8] is not None:
			if packet[8].lower() == "icmp":
				outstr += color(output_col["protocol"])+"--icmp"+str(packet[9])+"-->"
			elif packet[8].lower() == "igmp":
				outstr += color(output_col["protocol"])+"---igmp-->"
			else:
				outstr += color(output_col["protocol"])+"---"+packet[8].lower()+"--->"
		else:
			outstr += color("red")+"--!NONE!->"
		dpt=""
		if packet[7] is not None and show_dpt: dpt=":"+str(packet[7])
		outstr += " "+color(output_col["destination_ip"])+packet[6]+color(output_col["destination_port"])+dpt.ljust(21-len(packet[6]))
		outstr += color(output_col["hostnames"])
		if not nodns: outstr += get_hostnames(packet[6], packet[2], False)["log"]
		outstr += color("normal")
		print outstr
		if not print_last: cursor.execute("UPDATE "+lila_table+" SET viewed=1 WHERE id=\""+str(packet[0])+"\"")
		
	########## Actual Output - end ############
		
def main():
	
	global print_last, dupecount, list_new, resolver_pdnsd # showdupes kein global?
	
	init()
	show_header()
	if resolver_pdnsd: resolver_pdnsd = pdnsd_test() #disable pdnsd if test fails
	if debug_level > 0: print_notice("Debug message level "+str(debug_level)+" enabled.\n\n")
	init_lila_db()
	if showdupes: print_notice("Show all packets, including dupes.\n\n")
	elif ignoredupes and not showdupes: print_notice("Hide duplicate packets completely.\n\n")
	else: print_notice("Hide duplicate packets. Dupe time interval set to "+str(dupetime)+" seconds.\n\n")
		
	######### list_new packets code - start ########## 
	if list_new:
		print_last = 0 #list_new option overrides print_last
		if new_entries_count:
			print_notice("Analyzing packets since the last start...\n\n")
			print_debug(1,"main", "Start SQL output from id "+str(lila_maxid-new_entries_count+1)+" to "+str(lila_maxid))
			sql_output(lila_table, lila_maxid-new_entries_count+1, lila_maxid)
			print
			print_notice(color("normal")+str(new_entries_count-dupecount)+" of "+str(new_entries_count)+" packets shown ("+str(dupecount)+" hidden).\n\n"+color("normal"))	
		else:
			print_notice("There are no new packets since the last start.\n\n")
	######### list_new packets code - end ############ 
	
	########## print_last code - start ########## 
	if print_last == -1 or print_last>lila_maxid: print_last = lila_maxid
	if print_last:
		cursor.execute("UPDATE "+lila_table+" SET viewed=0") #sonst beim 2. mal kein output mit -n / if table_exists...
		print_notice("Analyzing last "+str(print_last)+" packets...\n\n")
		print_debug(1,"main", "Start SQL output from id "+str(lila_maxid-print_last+1)+" to "+str(lila_maxid))
		sql_output(lila_table, lila_maxid-print_last+1, lila_maxid)
		print
		print_notice(color("normal")+str(print_last-dupecount)+" of "+str(print_last)+" packets shown ("+str(dupecount)+" hidden).\n\n"+color("normal"))
	########## print_last code - end ############ 
	
	if (print_last or list_new) and quit: exit(0)
	print_last = 0 #für live mode detection von sql_output und gethostname
	print_notice("Starting live log monitoring... Done! (Press CTRL + C to exit LILA)\n\n")
	
	########## main loop - START ##########
	while 1:
		count = -1
		while count != get_syslog_count():
			count = get_syslog_count()
			set_dbvars() #global lila_last_packet, lila_maxid, syslog_packet, syslog_minid, syslog_maxid, syslog_curid, new_entries_count
			sql_write(lila_table, lila_maxid+1, syslog_curid+1, syslog_maxid, 0) #Write changes to the LILA database
			time.sleep(mysql_poll_interval)
			if new_entries_count: 
				print_debug(1,"main","Changes detected. Added "+str(new_entries_count)+" packets.")
				sql_output(lila_table, lila_maxid+1, lila_maxid+new_entries_count)
				if quit: exit(0)
	########## main loop - END ##########
				
		
	

if __name__ == "__main__":
	try: main()
	except KeyboardInterrupt: exit(0)
	




			
