Added audit fields for total requests, flagged, blocks, overrides, max score, also checking squid access.log for squark annotations

1 files changed, 27 insertions, 7 deletions

diff --git a/weblog-model.lua b/weblog-model.lua
index 87b2618..d5fa4da 100644
--- a/weblog-model.lua
+++ b/weblog-model.lua
@@ -407,17 +407,21 @@ local listpubweblogentries = function(...)
 	return listlogentries(...)
 end
 
-local groupdeniedlogentries = function(starttime, endtime, groupby)
+local groupflaggedlogentries = 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"
+	--local sql = "SELECT "..groupby..", count(*) AS numblock, max(score) AS maxscore FROM pubweblog"
+	local sql = "SELECT "..groupby..", COUNT(*) as numrecords, SUM(CASE WHEN (bypassyesno > '0' OR deniedyesno > '0' OR badyesno > '0') THEN 1 ELSE 0 END) as numflagged, sum(score) AS numhits, sum(CASE WHEN deniedyesno > '0' THEN 1 ELSE 0 END) AS numdenied, sum(CASE WHEN bypassyesno > '0' THEN 1 ELSE 0 END) AS numbypassed, max(score) as maxscore from pubweblog"
+	--sql = sql .. generatewhereclause(nil, starttime, endtime) .. " AND deniedyesno > '0'"
+	sql = sql .. generatewhereclause(nil, starttime, endtime)
+	--sql = sql .. " GROUP BY "..groupby.. " ORDER BY numblock DESC"
+	sql = sql .. " GROUP BY " ..groupby.. " ORDER BY numflagged DESC"
 	cur = assert (con:execute(sql))
 	row = cur:fetch ({}, "a")
 	while row do
-		entries[#entries+1] = {numblock=row.numblock, maxscore=row.maxscore}
+		--entries[#entries+1] = {numblock=row.numblock, maxscore=row.maxscore}
+		entries[#entries+1] = {numrecords=row.numrecords, numflagged=row.numflagged, numhits=row.numhits, numdenied=row.numdenied, numbypassed=row.numbypassed, maxscore=row.maxscore}
 		entries[#entries][groupby] = row[groupby]
 		row = cur:fetch (row, "a")
 	end
@@ -537,6 +541,8 @@ local function checkwords(logentry)
 				badwordloc[#badwordloc+1] = thisline
 			end
 		end
+		
+		--check for DansGuardian actions
 		if string.find(logentry.URL,"*DENIED*") then
 			-- logme("*Denied*")
 			logentry.deniedyesno=1
@@ -547,6 +553,17 @@ local function checkwords(logentry)
 			-- logme("*OVERRIDE*")
 			logentry.bypassyesno=1
 		end
+
+		--check for Squark actions
+		if (logentry.squarkaction and logentry.squarkaction ~= "") then
+		logme("squarkaction="..logentry.squarkcategory)
+		   if string.find(logentry.squarkaction, "blocked") then
+		   	logentry.deniedyesno=1
+		   elseif string.find(logentry.squarkaction,"overridden") then
+		   	logentry.bypassyesno=1
+		   end
+		end
+
 		for i,goodline in ipairs(goodwords) do
 			if not goodline then
 				break
@@ -593,7 +610,10 @@ local function parsesquidlog(line)
 		URL=words[7],
 		clientuserid=words[8],
 		peerstatus=string.match(words[9] or "", "^[^/]*"),
-		peerhost=string.match(words[9] or "", "[^/]*$")}
+		peerhost=string.match(words[9] or "", "[^/]*$"),
+		squarkcategory=string.match(words[11] or "", "^[^,]*"),
+		squarkaction=string.match(words[11] or "", "[^,]*$")}
+
 
 	checkwords(logentry)
 
@@ -1290,7 +1310,7 @@ function getauditstats()
 	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 {}
+			result.stats.value = groupflaggedlogentries(config.auditstart, config.auditend, result.groupby.value) or {}
 			databasedisconnect()
 		end
 	end)