Started cleanup, not complete

1 files changed, 281 insertions, 68 deletions

diff --git a/weblog-model.lua b/weblog-model.lua
index be90d3d..3ee6601 100644
--- a/weblog-model.lua
+++ b/weblog-model.lua
@@ -8,7 +8,7 @@ require("validator")
 require("luasql.postgres")
 require("date")
 
-local DatabaseName = "webproxylog"
+local DatabaseName = "weblog"
 local DatabaseOwner = "weblogowner"
 local DatabaseUser = "webloguser"
 
@@ -133,7 +133,7 @@ local runSQLscript = function(filename)
 end
 
 -- Create the database and tables
--- pg_dump -U postgres -c webproxylog > makeweblog.postgres
+-- pg_dump -U postgres -c weblog > makeweblog.postgres
 --runSQLscript("/root/work/weblog/makeweblog.postgres")
 
 local databaseconnect = function(username, password)
@@ -180,10 +180,20 @@ local importsquidlog = function(entry, sourcename)
 		local sql = string.format("INSERT INTO pubweblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')",
 			escape(sourcename), escape(entry.clientip), escape(entry.clientuserid, 64):lower(),
 			escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score), escape(entry.shortreason), escape(entry.badyesno), escape(entry.deniedyesno), escape(entry.bypassyesno), escape(entry.wordloc), escape(entry.goodwordloc))
+			
 		local res = assert (con:execute(sql))
 	end
 end
 
+local importsquarklog = function(entry, sourcename)
+        if entry then
+           local sql = string.format("INSERT INTO pubweblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')",
+	          escape(sourcename), escape(entry.clientip), escape(entry.clientuserid, 64):lower(),
+                  escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score), escape(entry.shortreason), escape(entry.badyesno), escape(entry.deniedyesno), escape(entry.bypassyesno), escape(entry.wordloc), escape(entry.gwordloc))
+           local res = assert (con:execute(sql))
+        end
+end
+
 local importdglog = function(entry, sourcename)
 	if entry then
            local sql = string.format("INSERT INTO pubweblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')",
@@ -192,6 +202,14 @@ local importdglog = function(entry, sourcename)
            local res = assert (con:execute(sql))
 	end
 end 
+local importdumplog = function(entry, sourcename)
+	if entry then
+           local sql = string.format("INSERT INTO pubweblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')",
+	          escape(sourcename), escape(entry.clientip), escape(entry.clientuserid, 64):lower(),
+                  escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score), escape(entry.shortreason), escape(entry.badyesno), escape(entry.deniedyesno), escape(entry.bypassyesno), escape(entry.wordloc), escape(entry.gwordloc))
+           local res = assert (con:execute(sql))
+	end
+end 
 
 local listsourceentries = function(sourcename)
 	local sources = {}
@@ -301,6 +319,21 @@ local groompublogs = function()
 	res = assert (con:execute(sql))
 	logme("Deleted " .. res .. " old records to from pubweblog")
 
+	-- purge anything older than startddate+historydays+watchdays
+	--local temp = config.auditstart
+	--if not temp or temp == "" then temp = os.date("%Y-%m-%d %H:%M:%S") end
+	--logme("Purge date since last audit is " .. tostring(watchdays+historydays) .. " days before " .. temp .. ".")
+
+	--sql = "delete from pubweblog where logdatetime < (timestamp '"..temp.."' - INTERVAL '"..tostring(watchdays+historydays).." days')"
+	--res = assert (con:execute(sql))
+        --logme("removed " .. res .. " old pubweblog records that are older than history+watchdays")
+
+	-- purge good people after historydays
+	--logme("The delete date for non-watchlist users is " .. tostring(historydays) .. " days before " .. temp .. ".")
+
+	--sql = "delete from pubweblog where logdatetime < (timestamp '".. temp.."' - INTERVAL '"..tostring(historydays).." days') and clientuserid NOT IN (select clientuserid from watchlist)"
+	--res = assert (con:execute(sql))
+	--logme("removed " .. res .. " records for users not on the watchlist.")
 end
 
 local listwatchlistentries = function()
@@ -475,7 +508,6 @@ local printtableentries = function(tablename)
 end
 
 -- ################################################################################
-
 -- LOG FILE FUNCTIONS
 
 local function parsesquidlog(line)
@@ -501,8 +533,7 @@ local function parsesquidlog(line)
 		if not thisline then
 			break
 		end
-		_,instcnt = string.lower(words[7]):gsub(format.escapespecialcharacters(thisline), " ")
-		--if string.find(words[7],thisline) ~= nil then
+		_,instcnt = string.lower(words[7]):gsub(thisline, " ")
 		if instcnt ~= 0 then
 		   ignoreme = true
 		   break
@@ -510,14 +541,16 @@ local function parsesquidlog(line)
 	end
 
 	if ignoreme ~= true then
-	 --proceed with record analysis, badwords first
+	 --proceed with record analysis
 	  for thisline in io.lines("/etc/weblog/badwords") do
 		if not thisline then
 			break
 		end
 		
-		_,instcnt = string.lower(words[7]):gsub(format.escapespecialcharacters(thisline), " ")  
+		_,instcnt = string.lower(words[7]):gsub(thisline, " ")  
+		--if string.find(words[7],thisline) ~= nil then
 		if instcnt ~= 0 then
+		 -- logme("instcnt =  "..instcnt)
 		  isbad=1
 		  wrdcnt= wrdcnt + instcnt 
 		  if badwordloc ~= "" then 
@@ -528,25 +561,22 @@ local function parsesquidlog(line)
 		 
 	        end
 		
-		if (words[11] and words[11]~= nil and string.find(words[11],"," )) then
-		 --logme("squid says "..words[11])
-		 prxarray = split(words[11],",")
-	  	  for r,s in pairs(prxarray) do
-		     if string.find(s,"blocked") then
-		      isdenied=1
-		     elseif string.find(s,"overridden") then
-		      isbypass=1
-		    end
-		  end
-	        end
+		if string.find(words[7],"*DENIED*") then
+		   logme("*Denied*")
+		   isdenied=1
+		elseif string.find(words[7],"GBYPASS") then
+		   logme("GBYPASS")
+		   isbypass=1
+		elseif string.find(words[7],"*OVERRIDE*") then
+		   logme("*OVERRIDE*")
+		   isbypass=1
+		end
 	  end
-
-	--and now a good words search in mitigation of severity
 	  for goodline in io.lines("/etc/weblog/goodwords") do
 		if not goodline then
 		  break
 		end
-		_,instcnt = string.lower(words[7]):gsub(format.escapespecialcharacters(goodline), " ")
+		_,instcnt = string.lower(words[7]):gsub(goodline, " ")
 		--if string.find(words[7],goodline) then
 		if instcnt ~= 0 then
 		  if wrdcnt ~= 0 then
@@ -560,19 +590,18 @@ local function parsesquidlog(line)
 		end
 	  end	
 	end
-	
-	if (r and r~=nil) then 
-	 reason=r
-	else 
-	 reason=words[6]
-	end
+	-- Reset bad to reduce number of bad hits if score is zero
+	-- if wrdcnt == 0 then
+	-- isbad=0
+	-- end
+		
 	local logentry = {logdatetime=words[1],
 		elapsed=words[2],
 		clientip=words[3],
 		code=string.match(words[4] or "", "^[^/]*"),
 		status=string.match(words[4] or "", "[^/]*$"),
 		bytes=words[5],
-		method=reason,
+		method=words[6],
 		URL=words[7],
 		clientuserid=words[8],
 		peerstatus=string.match(words[9] or "", "^[^/]*"),
@@ -594,6 +623,109 @@ local function parsesquidlog(line)
 	return nil
 end
 
+local function parsesquarklog(line)
+	-- Format of squid log (space separated):
+	-- time elapsed remotehost code/status bytes method URL rfc931 peerstatus/peerhost
+	local words = {}
+	
+	for word in string.gmatch(line, "%S+") do
+		words[#words+1] = word
+	end
+
+	local goodwordloc=""
+	local badwordloc=""
+	local wrdcnt=0
+	local isbad=0
+	local isdenied=0
+	local isbypass=0
+	local ignoreme=false
+	
+	--check for ignored records first
+	for thisline in io.lines("/etc/weblog/ignorewords") do
+		if not thisline then
+			break
+		end
+		_,instcnt = string.lower(words[5]):gsub(thisline, " ")
+		if instcnt ~= 0 then
+		   ignoreme = true
+		   break
+		end
+	end
+
+ 	if ignoreme ~= true then	
+	  for thisline in io.lines(baseurl.."badwords") do
+		if not thisline then
+			-- logme("This line is apparently empty...")
+			break
+		end
+		
+		_,instcnt = string.lower(words[5]):gsub(thisline, " ")
+	--	if string.find(words[5],thisline) ~= nil then
+		--logme("checking "..thisline.." against "..words[5])
+		if instcnt ~= 0 then
+		  isbad=1
+		  wrdcnt = wrdcnt + instcnt
+		if badwordloc ~= "" then 
+		    badwordloc = badwordloc.."|"..thisline
+		else
+		    badwordloc=thisline
+		end
+		 
+		  -- logme("bad "..badwordloc)
+	        end
+	
+		if string.find(words[6],"*DENIED*") then	
+		   isdenied=1
+		end
+		if string.find(words[6],"*OVERRIDE*") then
+		   isbypass=1
+		end
+	end
+	for goodline in io.lines(baseurl.."goodwords") do
+		if not goodline then
+		  -- logme("This line is apparently empty...")
+		  break
+		end
+		_,instcnt = string.lower(words[5]):gsub(goodline, " ")
+		--if string.find(words[4],goodline) then
+		  if instcnt ~= 0 then
+		   if wrdcnt ~= 0 then
+		     wrdcnt = wrdcnt - instcnt
+		     if goodwordloc ~= "" then
+		        goodwordloc = goodwordloc.."|"..goodline
+		     else 
+		        goodwordloc = goodline
+		     end
+		  end
+		end
+	  end
+	end
+	
+	local words = format.string_to_table(line, "\t")	
+	local logentry = {logdatetime=words[1],
+			clientuserid=words[2],
+			clientip=words[3],
+			URL=words[4],
+			reason=words[5],
+			method=words[6],
+			bytes=words[7],
+			shortreason=words[9],
+			score=wrdcnt,
+			badyesno=isbad,
+			deniedyesno=isdenied,
+			bypassyesno=isbypass,		
+			wordloc=badwordloc,
+			gwordloc=goodwordloc}	
+
+	if logentry.reason and logentry.reason ~= "" then
+           	if logentry.shortreason == "" then
+           		logentry.shortreason = logentry.reason
+           	end
+	   	return logentry
+	end
+	return nil
+end
+
 local function parsedglog(line)
 	-- Format of squid log (space separated):
 	-- time elapsed remotehost code/status bytes method URL rfc931 peerstatus/peerhost
@@ -617,7 +749,7 @@ local function parsedglog(line)
 		if not thisline then
 			break
 		end
-		_,instcnt = string.lower(words[4]):gsub(format.escapespecialcharacters(thisline), " ")
+		_,instcnt = string.lower(words[4]):gsub(thisline, " ")
 		if instcnt ~= 0 then
 		   ignoreme = true
 		   break
@@ -700,6 +832,107 @@ local function parsedglog(line)
 	return nil
 end
 
+local function parsedumplog(line)
+	-- Format of squid log (space separated):
+	-- time elapsed remotehost code/status bytes method URL rfc931 peerstatus/peerhost
+	local words = {}
+	
+	for word in string.gmatch(line, "%S+") do
+		words[#words+1] = word
+	end
+	goodwordloc=""
+	badwordloc=""
+	wrdcnt=0
+	isbad=0
+	isdenied=0
+	isbypass=0
+	for thisline in io.lines("/etc/weblog/badwords") do
+		if not thisline then
+			 logme("This line is apparently empty...")
+			break
+		end
+		_,instcnt = string.lower(words[5]):gsub(thisline, " ")
+		if instcnt ~= 0 then
+		  isbad=1
+		  wrdcnt = wrdcnt + instcnt
+		  if badwordloc ~= "" then 
+		    badwordloc = badwordloc.."|"..thisline
+		  else
+		    badwordloc=thisline
+		  end
+		 
+		   -- logme("bad "..badwordloc)
+	        end
+		if string.find(words[6],"*DENIED*") then	
+		   isdenied=1
+		end
+		if string.find(words[5],"GBYPASS") then
+		   isbypass=1
+		elseif string.find(words[6],"*OVERRIDE*") then
+		   isbypass=1
+		end
+	end
+	for goodline in io.lines("/etc/weblog/goodwords") do
+		if not goodline then
+		  -- logme("This line is apparently empty...")
+		  break
+		end
+		_,instcnt = string.lower(words[4]):gsub(goodline, " ")
+		--if string.find(words[4],goodline) then
+		  if instcnt ~= 0 then
+		   if wrdcnt ~= 0 then
+		     wrdcnt = wrdcnt - instcnt
+		     if goodwordloc ~= "" then
+		        goodwordloc = goodwordloc.."|"..goodline
+		     else 
+		        goodwordloc = goodline
+		     end
+		  end
+		end
+	end	
+	
+	local words = format.string_to_table(line, "\t")	
+	local logentry = {logdatetime=words[1],
+			clientuserid=words[2],
+			clientip=words[3],
+			URL=words[4],
+			reason=words[6],
+			method=words[5],
+			bytes=words[7],
+			shortreason=words[9],
+			score=wrdcnt,
+			badyesno=isbad,
+			deniedyesno=isdenied,
+			bypassyesno=isbypass,		
+			wordloc=badwordloc,
+			gwordloc=goodwordloc}	
+
+	if logentry.reason and logentry.reason ~= "" then
+           	if logentry.shortreason == "" then
+           		logentry.shortreason = logentry.reason
+           	end
+	   	return logentry
+	end
+	return nil
+end
+
+--local function parsedglog(line)
+--	local words = format.string_to_table(line, "\t")
+--	local logentry = { logdatetime=words[1], clientuserid=words[2], clientip=words[3],
+--		URL=words[4], reason=words[5], method=words[6], bytes=words[7],
+--		shortreason=words[9]}
+--	if logentry.reason and logentry.reason ~= "" then
+--		if logentry.shortreason == "" then
+--			logentry.shortreason = logentry.reason
+--		end
+--		logentry.score = string.match(logentry.reason, "^.*: ([0-9]+) ")
+--		logentry.logdatetime = string.gsub(logentry.logdatetime, "%.", "-")
+--		return logentry
+--	end
+--	return nil
+--end
+
+
 -- ################################################################################
 -- DOWNLOAD FILE FUNCTIONS
 
@@ -960,9 +1193,9 @@ end
 -- import either squid or dg log file.
 -- delete logfile after
 local function importlogfile(source, cookiesfile, file, parselog_func, importlog_func)
-	--logme("Getting " .. file )
+	logme("Getting " .. file )
 	local loghandle = openlogfile(source, cookiesfile, file)
-	--logme("Processing " .. file )
+	logme("Processing " .. file )
 	local res, err = pcall(function()
 		con:execute("START TRANSACTION")
 		for line in loghandle:lines() do
@@ -1035,10 +1268,18 @@ function importlogs()
 							count = count + 1
 							success = importlogfile(source, cookiesfile, file, parsedglog, importdglog) and success
 						end
+						if string.match(file, "squark/access%.log[%.%-]") then
+							count = count + 1
+							success = importlogfile(source, cookiesfile, file, parsesquarklog, importsquarklog) and success
+						end
 						if string.match(file, "squid/access%.log[%.%-]") then
 							count = count + 1
 							success = importlogfile(source, cookiesfile, file, parsesquidlog, importsquidlog) and success
 						end
+						if string.match(file, "dump/access%.log[%.%-]") then
+							count = count + 1
+							success = importlogfile(source, cookiesfile, file, parsedumplog, importdumplog) and success
+						end
 					end
 				end
 				os.remove(cookiesfile)
@@ -1267,15 +1508,15 @@ function editselected(chkdata)
 	keycnt = 0
 	sql = "UPDATE pubweblog SET selected = "
 	databaseconnect(DatabaseOwner)
-	idarray = split(chkdata,"|")
-	  for key,x in pairs(idarray) do
-	    keycnt = keycnt + 1
-	    if keycnt == 1 then 
-	      sql = sql..x.." WHERE id = "
-	    else 
-	      sql = sql..x
-	    end
-	  end
+	idarray = format.string_to_table(chkdata, "|")
+	for key,x in pairs(idarray) do
+		keycnt = keycnt + 1
+		if keycnt == 1 then 
+			sql = sql..x.." WHERE id = "
+		else 
+			sql = sql..x
+		end
+	end
 	assert (con:execute(sql))
 	databasedisconnect()
 end
@@ -1557,7 +1798,6 @@ function create_database(database)
 
 	return database
 end
-
 function listfiles()
         local retval = cfe({ type="list", value={}, label="Weblog Files" })
         if not fs.is_dir(baseurl) then fs.create_directory(baseurl) end
@@ -1570,12 +1810,10 @@ function listfiles()
         table.sort(retval.value)
         return retval
 end
-
 function getnewfile()
         local filename = cfe({ label="File Name", descr="Must be in "..baseurl })
         return cfe({ type="group", value={filename=filename}, label="Weblog File" })
 end
-
 function readfile(filename)
         return modelfunctions.getfiledetails(filename, listfiles().value)
 end
@@ -1597,28 +1835,3 @@ function deletefile(filename)
                                                                                                                                                                         
         return retval
 end
-
--- Split a string to an array by delimiter or pattern
-function split(str, pat)
-   if string.find(str, pat) == nil then
-      return str
-   end
-   local t = {}
-   local fpat = "(.-)" .. pat
-   local last_end = 1
-   local s, e, cap = str:find(fpat, 1)
-   while s do
-     if s ~= 1 or cap ~= "" then
-        table.insert(t,cap)
-     end
-     last_end = e+1
-     s, e, cap = str:find(fpat, last_end)
-   end
-   if last_end <= #str then
-      cap = str:sub(last_end)
-      table.insert(t, cap)
-   end
-   return t
-end
-
-