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

2 files changed, 39 insertions, 11 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)
diff --git a/weblog-viewauditstats-html.lsp b/weblog-viewauditstats-html.lsp
index 81485b5..04e48ac 100644
--- a/weblog-viewauditstats-html.lsp
+++ b/weblog-viewauditstats-html.lsp
@@ -22,13 +22,21 @@
 <TABLE id="audit" class="tablesorter"><THEAD>
 	<TR style="font-weight:bold;">
 		<TH><% if data.value.groupby.value == "clientip" then %>Client IP<% else %>User ID<% end %></TH>
-		<TH>Blocks</TH>
+		<TH>Total Requests</TH>
+		<TH>Flagged Requests</TH>
+		<TH>Total Word Hits</TH>
+		<TH>Blocked</TH>
+		<TH>Overridden</TH>
 		<TH>Maximum Score</TH>
 	</TR>
 </THEAD><TBODY>
 <% for i,stat in ipairs(data.value.stats.value) do %>
-	<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>
+	<TR><TD><%= html.link{value = "viewweblog?"..data.value.groupby.value.."="..stat[data.value.groupby.value], label=stat[data.value.groupby.value]} %></TD>
+	<TD><%= html.html_escape(stat.numrecords) %></TD>
+	<TD><%= html.html_escape(stat.numflagged) %></TD>
+	<TD><%= html.html_escape(stat.numhits) %></TD>
+	<TD><%= html.html_escape(stat.numdenied) %></TD>
+	<TD><%= html.html_escape(stat.numbypassed) %></TD>
 	<TD><%= html.html_escape(stat.maxscore) %></TD></TR>
 <% end %>
 </TBODY></TABLE>
@@ -37,7 +45,7 @@
 <p class='error'><%= html.html_escape(data.errtxt) %></p>
 <% end %>
 <% if #data.value.stats.value == 0 then %>
-<p>No blocks, try adjusting the audit dates</p>
+<p>No flagged records, try adjusting the audit dates</p>
 <% end %>
 
 <form action="<%= html.html_escape(page_info.script .. page_info.prefix .. page_info.controller .. "/completeaudit") %>">