Added audit functions back in and cleaned up importing

3 files changed, 75 insertions, 40 deletions

diff --git a/weblog-model.lua b/weblog-model.lua
index ba4df26..d8d28ee 100644
--- a/weblog-model.lua
+++ b/weblog-model.lua
@@ -183,8 +183,7 @@ local importlogentry = function(entry, sourcename)
 	if entry then
 		local sql = string.format("INSERT INTO weblog 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))
-
+			escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score or "0"), escape(entry.shortreason), escape(entry.badyesno or "0"), escape(entry.deniedyesno or "0"), escape(entry.bypassyesno or "0"), escape(entry.wordloc), escape(entry.goodwordloc))
 		local res = assert (con:execute(sql))
 	end
 end
@@ -237,7 +236,7 @@ local addtowatchlist = function()
 	local watchdays = config.watchdays or 14
 	local sql = "insert into watchlist select clientuserid, " ..
 		"(max(logdatetime) + INTERVAL '"..watchdays.." days') as expiredatetime " ..
-		"from weblog where bypassyesno > '0' group by clientuserid"
+		"from weblog where deniedyesno > '0' group by clientuserid"
 	local res1 = assert (con:execute(sql))
 	sql = "delete from watchlist where exists " ..
 		"(select * from watchlist w where w.clientuserid = watchlist.clientuserid " ..
@@ -425,6 +424,25 @@ local listpubweblogentries = function(...)
 	return listlogentries(...)
 end
 
+local groupdeniedlogentries = function(starttime, endtime, groupby)
+	groupby = groupby or "clientuserid"
+	local entries = {}
+	-- retrieve a cursor
+	local sql = "SELECT "..groupby..", count(*) AS numblock, max(score) AS maxscore FROM pubweblog"
+	sql = sql .. generatewhereclause(nil, starttime, endtime) .. " AND deniedyesno > '0'"
+	sql = sql .. " GROUP BY "..groupby.. " ORDER BY numblock DESC"
+	cur = assert (con:execute(sql))
+	row = cur:fetch ({}, "a")
+	while row do
+		entries[#entries+1] = {numblock=row.numblock, maxscore=row.maxscore}
+		entries[#entries][groupby] = row[groupby]
+		row = cur:fetch (row, "a")
+	end
+	-- close everything
+	cur:close()
+	return entries
+end
+
 local listusagestats = function()
 	local entries = {}
 	-- retrieve a cursor
@@ -504,13 +522,10 @@ end
 -- LOG FILE FUNCTIONS
 
 local function checkwords(logentry)
-	local goodwordloc=""
-	local badwordloc=""
+	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 i,thisline in ipairs(ignorewords) do
@@ -519,12 +534,12 @@ local function checkwords(logentry)
 		end
 		_,instcnt = string.lower(logentry.URL):gsub(thisline, " ")
 		if instcnt ~= 0 then
-			ignoreme = true
+			logentry.ignoreme = true
 			break
 		end
 	end
 
-	if ignoreme ~= true then
+	if not logentry.ignoreme then
 		--proceed with record analysis
 		for i,thisline in ipairs(badwords) do
 			if not thisline then
@@ -532,29 +547,23 @@ local function checkwords(logentry)
 			end
 
 			_,instcnt = string.lower(logentry.URL):gsub(thisline, " ")
-			--if string.find(logentry.URL,thisline) ~= nil then
 			if instcnt ~= 0 then
 				-- logme("instcnt =  "..instcnt)
 				isbad=1
 				wrdcnt= wrdcnt + instcnt
-				if badwordloc ~= "" then
-					badwordloc = badwordloc.."|"..thisline
-				else
-					badwordloc=thisline
-				end
-			end
-
-			if string.find(logentry.URL,"*DENIED*") then
-				-- logme("*Denied*")
-				isdenied=1
-			elseif string.find(logentry.URL,"GBYPASS") then
-				-- logme("GBYPASS")
-				isbypass=1
-			elseif string.find(logentry.URL,"*OVERRIDE*") then
-				-- logme("*OVERRIDE*")
-				isbypass=1
+				badwordloc[#badwordloc+1] = thisline
 			end
 		end
+		if string.find(logentry.URL,"*DENIED*") then
+			-- logme("*Denied*")
+			logentry.deniedyesno=1
+		elseif string.find(logentry.URL,"GBYPASS") then
+			-- logme("GBYPASS")
+			logentry.bypassyesno=1
+		elseif string.find(logentry.URL,"*OVERRIDE*") then
+			-- logme("*OVERRIDE*")
+			logentry.bypassyesno=1
+		end
 		for i,goodline in ipairs(goodwords) do
 			if not goodline then
 				break
@@ -562,14 +571,12 @@ local function checkwords(logentry)
 			_,instcnt = string.lower(logentry.URL):gsub(goodline, " ")
 			--if string.find(logentry.URL,goodline) then
 			if instcnt ~= 0 then
-				if wrdcnt ~= 0 then
+				if wrdcnt >= instcnt then
 					wrdcnt = wrdcnt - instcnt
-					if goodwordloc ~= "" then
-						goodwordloc = goodwordloc.."|"..goodline
-					else
-						goodwordloc = goodline
-					end
+				else
+					wrdcnt = 0
 				end
+				goodwordloc[#goodwordloc+1] = goodline
 			end
 		end
 	end
@@ -580,10 +587,8 @@ local function checkwords(logentry)
 
 	logentry.score=wrdcnt
 	logentry.badyesno=isbad
-	logentry.deniedyesno=isdenied
-	logentry.bypassyesno=isbypass
-	logentry.wordloc=badwordloc
-	logentry.gwordloc=goodwordloc
+	logentry.wordloc=table.concat(badwordloc,"|")
+	logentry.gwordloc=table.concat(goodwordloc,"|")
 end
 
 local function parsesquidlog(line)
@@ -628,7 +633,8 @@ local function parsedglog(line)
 			reason=words[5],
 			method=words[6],
 			bytes=words[7],
-			shortreason=words[9]}
+			shortreason=words[9],
+			deniedyesno=1}
 
 	checkwords(logentry)
 
@@ -1292,6 +1298,35 @@ function getusagestats()
 	return retval
 end
 
+function getauditstats()
+	local result = {}
+	result.auditstart = cfe({ value=config.auditstart or "", label="Audit Start Time" })
+	result.auditend = cfe({ value=config.auditend or "", label="Audit End Time" })
+	result.groupby = cfe({ value=config.groupby or "clientuserid", label="Group By" })
+	result.stats = cfe({ type="list", value={}, label="Audit Block Statistics" })
+	local res, err = pcall(function()
+		if config.auditstart ~= "" and config.auditend ~= "" then
+			databaseconnect(DatabaseUser)
+			result.stats.value = groupdeniedlogentries(config.auditstart, config.auditend, result.groupby.value) or {}
+			databasedisconnect()
+		end
+	end)
+	return cfe({ type="group", value=result, errtxt=err, label="Weblog Audit Statistics" })
+end
+
+function completeaudit(timestamp)
+	local conf = getconfig()
+	conf.value.auditstart.value = conf.value.auditend.value
+	local now = os.time()
+	conf.value.auditend.value = timestamp or os.date("%Y-%m-%d %H:%M:%S", now - now%86400 - 86400)
+	conf = updateconfig(conf)
+	local retval = cfe({ value="Audit completed", label="Complete Audit Result" })
+	if conf.errtxt then
+		retval.value = ""
+		retval.errtxt = "Failed to complete audit\n"..conf.errtxt.."\n"..conf.value.auditend.errtxt
+	end
+	return retval
+end
 
 function getconfig()
 	local result = {}
diff --git a/weblog-viewauditstats-html.lsp b/weblog-viewauditstats-html.lsp
index efe02f3..81485b5 100644
--- a/weblog-viewauditstats-html.lsp
+++ b/weblog-viewauditstats-html.lsp
@@ -27,7 +27,7 @@
 	</TR>
 </THEAD><TBODY>
 <% for i,stat in ipairs(data.value.stats.value) do %>
-	<TR><TD><%= html.link{value = "viewblocklog?"..data.value.groupby.value.."="..stat[data.value.groupby.value], label=stat[data.value.groupby.value]} %></TD>
+	<TR><TD><%= html.link{value = "viewweblog?"..data.value.groupby.value.."="..stat[data.value.groupby.value].."&deniedyesno=1", label=stat[data.value.groupby.value]} %></TD>
 	<TD><%= html.html_escape(stat.numblock) %></TD>
 	<TD><%= html.html_escape(stat.maxscore) %></TD></TR>
 <% end %>
diff --git a/weblog.menu b/weblog.menu
index 30638e5..2a3c0fe 100644
--- a/weblog.menu
+++ b/weblog.menu
@@ -1,6 +1,6 @@
 #CAT  		GROUP/DESC		TAB		ACTION
 Applications 	41Weblog		Status		status
-#Applications 	41Weblog		Audit		viewauditstats
+Applications 	41Weblog		Audit		viewauditstats
 Applications	41Weblog		View_Log	viewweblog
 Applications 	41Weblog		Config		config
 Applications	41Weblog		File_List	listfiles