weblog-model.lua 52.5 KB
Newer Older
1
local mymodule = {}
Ted Trask's avatar
Ted Trask committed
2 3

-- Load libraries
4
modelfunctions = require("modelfunctions")
5 6 7
fs = require("acf.fs")
format = require("acf.format")
validator = require("acf.validator")
8 9 10
luasql = require("luasql.postgres")
posix = require("posix")
subprocess = require("subprocess")
Ted Trask's avatar
Ted Trask committed
11

12
local DatabaseName = "webproxylog"
Ted Trask's avatar
Ted Trask committed
13 14 15 16 17 18
local DatabaseOwner = "weblogowner"
local DatabaseUser = "webloguser"

local path = "PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin "
local env
local con
19
local configfile = "/etc/weblog/weblog.conf"
Ted Trask's avatar
Ted Trask committed
20 21
local configcontent = fs.read_file(configfile) or ""
local config = format.parse_ini_file(configcontent, "") or {}
22
local goodwordslist = "/etc/weblog/goodwords"
23
local goodwords
24
local badwordslist = "/etc/weblog/badwords"
25
local badwords
Ted Trask's avatar
Ted Trask committed
26
local ignorewordslist = "/etc/weblog/ignorewords"
27
local ignorewords
Ted Trask's avatar
Ted Trask committed
28
local files = {badwordslist, goodwordslist, ignorewordslist, configfile}
Ted Trask's avatar
Ted Trask committed
29 30

local database_creation_script = {
31
	"CREATE TABLE dbhistlog (logdatetime timestamp(3) without time zone NOT NULL, msgtext text)",
32 33
	"CREATE TABLE pubweblog(sourcename character varying(40), clientip inet NOT NULL, clientuserid character varying(64) NOT NULL, logdatetime timestamp(3) without time zone NOT NULL, uri text NOT NULL, bytes bigint NOT NULL, reason text, score integer, shortreason text, badyesno int, deniedyesno int, bypassyesno int, wordloc text, goodwordloc text, selected boolean, id serial)",
	"CREATE TABLE pubweblog_history(sourcename character varying(40), clientip inet NOT NULL, clientuserid character varying(64) NOT NULL, logdatetime timestamp(3) without time zone NOT NULL, uri text NOT NULL, bytes bigint NOT NULL, reason text, score integer, shortreason text, badyesno int, deniedyesno int, bypassyesno int, wordloc text, goodwordloc text, selected boolean, id int)",
34
	"CREATE TABLE weblog(sourcename character varying(40), clientip inet NOT NULL, clientuserid character varying(64) NOT NULL, logdatetime timestamp(3) without time zone NOT NULL, uri text NOT NULL, bytes bigint NOT NULL, reason text, score integer, shortreason text, badyesno int, deniedyesno int, bypassyesno int, wordloc text, goodwordloc text)",
Ted Trask's avatar
Ted Trask committed
35
	"CREATE TABLE source (sourcename character varying(40) NOT NULL, method character varying(100) NOT NULL, userid character varying(32), passwd character varying(255), source character varying(255) NOT NULL, tzislocal boolean, enabled boolean)",
36
	"CREATE TABLE usagestat (sourcename character varying(40) NOT NULL, date timestamp(0) without time zone NOT NULL, numrequest integer, numblock integer)",
Ted Trask's avatar
Ted Trask committed
37 38 39 40 41
	"ALTER TABLE ONLY source ADD CONSTRAINT source_pkey PRIMARY KEY (sourcename)",
	"CREATE INDEX dbhistlogdatetimeidx ON dbhistlog USING btree (logdatetime)",
	"CREATE INDEX pubweblogclientdateidx ON pubweblog USING btree (logdatetime, clientuserid)",
	"CREATE INDEX pubweblogclientuserididx ON pubweblog USING btree (clientuserid)",
	"CREATE INDEX pubwebloglogdatetimeidx ON pubweblog USING btree (logdatetime)",
42 43 44
	"CREATE INDEX pubweblog_historyclientdateidx ON pubweblog_history USING btree (logdatetime, clientuserid)",
	"CREATE INDEX pubweblog_historyclientuserididx ON pubweblog_history USING btree (clientuserid)",
	"CREATE INDEX pubweblog_historylogdatetimeidx ON pubweblog_history USING btree (logdatetime)",
45 46
	"GRANT SELECT ON dbhistlog TO "..DatabaseUser,
	"GRANT SELECT ON pubweblog TO "..DatabaseUser,
47
	"GRANT SELECT ON pubweblog_history TO "..DatabaseUser,
48 49
	"GRANT SELECT, UPDATE, INSERT, DELETE ON source TO "..DatabaseUser,
	"GRANT SELECT ON usagestat TO "..DatabaseUser,
Ted Trask's avatar
Ted Trask committed
50 51 52 53 54 55 56 57 58 59 60 61 62
}

-- ################################################################################
-- DATABASE FUNCTIONS

local function assert (v, m)
	if not v then
		m = m or "Assertion failed!"
		error(m, 0)
	end
	return v, m
end

63 64
-- Escape special characters in sql statements and truncate to length
local escape = function(sql, length)
Ted Trask's avatar
Ted Trask committed
65
	sql = sql or ""
66
	if length then sql = string.sub(sql, 1, length) end
67
	return con:escape(sql)
Ted Trask's avatar
Ted Trask committed
68 69 70 71 72
end

-- List the postgres databases on this system
local listdatabases = function()
	local dbs = {}
73
	local result = modelfunctions.run_executable({"psql", "-U", "postgres", "-tl"})
Ted Trask's avatar
Ted Trask committed
74 75 76 77 78 79 80 81 82 83 84
	for line in string.gmatch(result, "[^\n]+") do
		dbs[#dbs+1] = string.match(line, "^ (%S+)")
	end
	return dbs
end

-- Create the necessary database
local createdatabase = function(password)
	local result = {}

	-- First, create the users
85 86 87 88 89 90 91 92 93
	local cmd = "CREATE USER "..DatabaseOwner.." WITH PASSWORD '"..password.."'"
	local cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)

	cmd = "CREATE USER "..DatabaseUser
	cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)
Timo Teräs's avatar
Timo Teräs committed
94

Ted Trask's avatar
Ted Trask committed
95
	-- Create the database
96 97 98 99
	cmd = "CREATE DATABASE "..DatabaseName.." WITH OWNER "..DatabaseOwner
	cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)
Timo Teräs's avatar
Timo Teräs committed
100

Ted Trask's avatar
Ted Trask committed
101 102 103 104 105 106 107
	return table.concat(result, "\n")
end

-- Delete the database and roles
local deletedatabase = function()
	local result = {}

108 109 110 111 112 113 114 115 116 117 118 119 120 121
	local cmd = "DROP DATABASE "..DatabaseName
	local cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)
	
	cmd = "DROP ROLE "..DatabaseUser
	cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)
	
	cmd = "DROP ROLE "..DatabaseOwner
	cmdresult, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-c", cmd}, true)
	table.insert(result, errtxt)
	table.insert(result, cmdresult)
Timo Teräs's avatar
Timo Teräs committed
122

Ted Trask's avatar
Ted Trask committed
123 124 125 126 127
	return table.concat(result, "\n")
end

-- Run an SQL script
local runSQLscript = function(filename)
128 129
	local result, errtxt = modelfunctions.run_executable({"psql", "-U", "postgres", "-f", filename, DatabaseName}, true)
	return errtxt or result
Ted Trask's avatar
Ted Trask committed
130 131 132
end

-- Create the database and tables
133
-- pg_dump -U postgres -c webproxylog > makeweblog.postgres
Ted Trask's avatar
Ted Trask committed
134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156
--runSQLscript("/root/work/weblog/makeweblog.postgres")

local databaseconnect = function(username, password)
	if not con then
		-- create environment object
		env = assert (luasql.postgres())
		-- connect to data source
		con = assert (env:connect(DatabaseName, username, password))
	end
end

local databasedisconnect = function()
	if env then
		env:close()
		env = nil
	end
	if con then
		con:close()
		con = nil
	end
end

local logme = function(message)
157
	local sql = string.format("INSERT INTO dbhistlog VALUES ('now', '%s')", escape(message))
Ted Trask's avatar
Ted Trask committed
158 159 160 161 162 163
	local res = assert (con:execute(sql))
end

local listhistorylogentries = function()
	local entries = {}
	-- retrieve a cursor
Timo Teräs's avatar
Timo Teräs committed
164
	cur = assert (con:execute"SELECT logdatetime, msgtext from dbhistlog ORDER BY logdatetime")
Ted Trask's avatar
Ted Trask committed
165 166 167 168 169 170 171 172 173 174
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = {logdatetime = row.logdatetime, msgtext = row.msgtext}
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

175
local importlogentry = function(entry, sourcename)
176
	if entry then
177
		local sql = string.format("INSERT INTO weblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')",
178
			escape(sourcename), escape(entry.clientip), escape(entry.clientuserid, 64):lower(),
179
			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))
Ted Trask's avatar
Ted Trask committed
180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230
		local res = assert (con:execute(sql))
	end
end

local listsourceentries = function(sourcename)
	local sources = {}
	-- retrieve a cursor
	local sql = "SELECT sourcename, method, userid, passwd, source, tzislocal, enabled FROM source"
	if sourcename then
		sql = sql .. " WHERE sourcename='" .. escape(sourcename) .. "'"
	end
	sql = sql .. " ORDER BY sourcename"
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		row.tzislocal = (row.tzislocal == "t")
		row.enabled = (row.enabled == "t")
		sources[#sources+1] = row
		row = cur:fetch ({}, "a")
	end
	cur:close()
	return sources
end

local importsourceentry = function(source)
	local sql = string.format("INSERT INTO source VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')",
		escape(source.sourcename), escape(source.method), escape(source.userid), escape(source.passwd),
		escape(source.source), escape(tostring(source.tzislocal):upper()), escape(tostring(source.enabled):upper()))
	local res = assert (con:execute(sql))
	return res
end

local updatesourceentry = function(source)
	local sql = string.format("UPDATE source SET method='%s', userid='%s', passwd='%s', source='%s', tzislocal='%s', enabled='%s' WHERE sourcename='%s'",
		escape(source.method), escape(source.userid), escape(source.passwd), escape(source.source),
		escape(tostring(source.tzislocal):upper()), escape(tostring(source.enabled):upper()),
		escape(source.sourcename))
	local res = assert (con:execute(sql))
	return res
end

local deletesourceentry = function(sourcename)
	local sql = string.format("DELETE FROM source WHERE sourcename='%s'", escape(sourcename))
	local res = assert (con:execute(sql))
	return res
end

-- Generate usage statistics from weblog and blocklog
local updateusagestats = function()
	-- update the usagestat table from weblog
	-- (will result in multiple rows where logs rotated on partial hours)
231 232 233
	local sql = "insert into usagestat select weblog.sourcename, " ..
		"date_trunc('hour', weblog.logdatetime) as date, " ..
		"count(*), SUM(deniedyesno) from weblog group by sourcename,date"
Ted Trask's avatar
Ted Trask committed
234 235 236
	local res = assert (con:execute(sql))
end

237 238 239 240 241 242 243 244 245 246 247 248 249 250 251
-- Move weblog into pubweblog, and truncate weblog
local importpubweblog = function()
	local sql = "ANALYZE"
	res = assert (con:execute(sql))

	-- Move weblog to pubweblog
	sql= "insert into pubweblog select * from weblog"
	res = assert (con:execute(sql))
	logme("importpubweblog imported " .. res .. " new rows into database.")

	-- Truncate the staging table
	assert (con:execute("truncate weblog"))
	logme("truncated staging table")
end

Ted Trask's avatar
Ted Trask committed
252 253
-- Delete useage stats from more than a year ago
local groomusagestat = function()
Ted Trask's avatar
Ted Trask committed
254 255 256
--	local res = assert (con:execute("delete from usagestat where " ..
--		"date < (now() - INTERVAL '1 year')"))
--	logme("removed " .. res .. " old usage status lines")
Ted Trask's avatar
Ted Trask committed
257 258 259 260
end

-- Delete history log information from more than a month ago
local groomdbhistlog = function()
Ted Trask's avatar
Ted Trask committed
261 262 263
--	local res = assert (con:execute("delete from dbhistlog where " ..
--		"logdatetime < (now() - INTERVAL '1 month')"))
--	logme("removed " .. res .. " old dbhistlog lines")
Ted Trask's avatar
Ted Trask committed
264 265 266 267
end

-- Delete old junk from pub tables
local groompublogs = function()
Ted Trask's avatar
Ted Trask committed
268
--[[
Ted Trask's avatar
Ted Trask committed
269
	local purgedays = config.purgedays or 30
Ted Trask's avatar
Ted Trask committed
270

Ted Trask's avatar
Ted Trask committed
271 272 273
	local now = os.time()

	local temp = os.date("%Y-%m-%d %H:%M:%S", now - purgedays*86400)
274
	logme("Purgedate is " .. temp .. ". Nothing will exist in pubweblog from before purgedate.")
Ted Trask's avatar
Ted Trask committed
275

276
	-- Move flagged records to histoy and then purge anything older than purgedate
277
	sql = "Insert into pubweblog_history select * from pubweblog where logdatetime < '" .. escape(temp) .."' and (badyesno > 0 or deniedyesno > 0 or bypassyesno > 0 or selected = 'true')"
Ted Trask's avatar
Ted Trask committed
278
	res = assert (con:execute(sql))
279
	logme("Moved " .. res .. " old records to history")
Ted Trask's avatar
Ted Trask committed
280

281
	sql = "Delete from pubweblog where logdatetime < '" .. escape(temp) .."'"
Ted Trask's avatar
Ted Trask committed
282
	res = assert (con:execute(sql))
283 284 285 286 287
	logme("Deleted " .. res .. " old records from pubweblog")
	
	sql = "delete from pubweblog_history where logdatetime < (now() - INTERVAL '1 year')"
	res = assert (con:execute(sql))
	logme("Deleted " .. res .. " old records from pubweblog_history")
Ted Trask's avatar
Ted Trask committed
288
--]]
Ted Trask's avatar
Ted Trask committed
289 290
end

291
local generatewhereclause = function(clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, selected, sourcename)
Ted Trask's avatar
Ted Trask committed
292 293 294
	local sql = ""
	local where = {}
	if clientuserid and clientuserid ~= "" then
295
		where[#where+1] = "clientuserid LIKE '%"..escape(clientuserid).."%'"
Ted Trask's avatar
Ted Trask committed
296 297 298 299 300 301 302 303 304 305
	end
	if starttime and starttime ~= "" then
		where[#where+1] = "logdatetime >= '"..escape(starttime).."'"
	end
	if endtime and endtime ~= "" then
		where[#where+1] = "logdatetime <= '"..escape(endtime).."'"
	end
	if clientip and clientip ~= "" then
		where[#where+1] = "clientip = '"..escape(clientip).."'"
	end
306 307
	if badyesno then
		where[#where+1] = "badyesno = '1'"
308
	end
309 310
	if deniedyesno then
		where[#where+1] = "deniedyesno = '1'"
311
	end
312 313
	if bypassyesno then
		where[#where+1] = "bypassyesno = '1'"
314 315 316 317
	end
	if score and score ~= "" then
		where[#where+1] = "score >= '"..escape(score).."'"
	end
Ted Trask's avatar
Ted Trask committed
318
	if urisearch and urisearch ~= "" then
319 320
	    	where[#where+1] = "lower(uri) LIKE '%"..escape(urisearch).."%'"
	end
321
	if selected then
322 323
		where[#where+1] = "selected = 'true'"
	end
324 325 326 327 328 329 330
	if sourcename and #sourcename > 0 then
		tmp = {}
		for i,s in pairs(sourcename) do
			tmp[#tmp+1] = "sourcename = '"..escape(s).."'"
		end
		where[#where+1] = "("..table.concat(tmp, " OR ")..")"
	end
Ted Trask's avatar
Ted Trask committed
331

Ted Trask's avatar
Ted Trask committed
332 333 334
	if #where > 0 then
		sql = " WHERE " .. table.concat(where, " AND ")
	end
Ted Trask's avatar
Ted Trask committed
335

Ted Trask's avatar
Ted Trask committed
336 337 338
	return sql
end

339
local listlogentries = function(activelog, clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, sortby, selected, sourcename)
Ted Trask's avatar
Ted Trask committed
340 341
	local entries = {}
	-- retrieve a cursor
342
	local sql = "SELECT * FROM "..escape(activelog)
343
	sql = sql .. generatewhereclause(clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, selected, sourcename)
344
	sql = sql .. " ORDER BY "..escape(sortby)
Ted Trask's avatar
Ted Trask committed
345 346 347
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
Ted Trask's avatar
Ted Trask committed
348 349
		if config.shorturi == "true" then
			shorturi=string.gsub(row.uri, "[;?].*", "...")
350
		end
351
		entries[#entries+1] = {sourcename=row.sourcename, clientip=row.clientip, clientuserid=row.clientuserid, logdatetime=row.logdatetime, uri=row.uri, shorturi=shorturi, bytes=row.bytes, reason=row.reason, score=row.score, shortreason=row.shortreason, badyesno=row.badyesno, deniedyesno=row.deniedyesno, bypassyesno=row.bypassyesno, wordloc=row.wordloc, id=row.id, selected=row.selected }
352 353 354
		if (config.shortreason ~= "true") then
			entries[#entries].shortreason = nil
		end
Ted Trask's avatar
Ted Trask committed
355 356 357 358 359 360 361
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

362
local groupflaggedlogentries = function(starttime, endtime, groupby)
363 364 365
	groupby = groupby or "clientuserid"
	local entries = {}
	-- retrieve a cursor
366
	local sql = "SELECT "..escape(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"
367
	sql = sql .. generatewhereclause(nil, starttime, endtime)
368
	sql = sql .. " GROUP BY " ..escape(groupby).. " ORDER BY numflagged DESC"
369 370 371
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
372
		entries[#entries+1] = {numrecords=row.numrecords, numflagged=row.numflagged, numhits=row.numhits, numdenied=row.numdenied, numbypassed=row.numbypassed, maxscore=row.maxscore}
373 374 375 376 377 378 379 380
		entries[#entries][groupby] = row[groupby]
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

Ted Trask's avatar
Ted Trask committed
381 382 383 384
local listusagestats = function()
	local entries = {}
	-- retrieve a cursor
	local sql = "SELECT sourcename, date, sum(numrequest) AS numrequest, sum(numblock) AS numblock " ..
385
		"FROM usagestat GROUP BY sourcename, date ORDER BY date, sourcename"
Ted Trask's avatar
Ted Trask committed
386 387 388 389 390 391 392 393 394 395 396
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = {sourcename=row.sourcename, date=row.date, numrequest=row.numrequest, numblock=row.numblock}
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

397 398 399
local testdatabaseentry = function(datatype, value)
	local success = true
	local errtxt
400
	local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." DEFAULT '"..escape(value).."' ) ON COMMIT DROP"
401 402 403 404 405 406 407 408 409 410
	local res, err = pcall(function()
		assert (con:execute(sql))
	end)
	if not res then
		success = false
		errtxt = string.gsub(err or "", "\n.*", "")
	end
	return success, errtxt
end

411 412 413 414 415
local convertdatabaseentry = function(datatype, value)
	local success = true
	local errtxt
	local result = value
	local res, err = pcall(function()
416
		local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." )"
417
		assert (con:execute(sql))
418
		sql = "INSERT INTO testing VALUES ('"..escape(value).."')"
419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437
		assert (con:execute(sql))
		sql = "SELECT * FROM testing"
		local cur = assert (con:execute(sql))
		local row = cur:fetch ({}, "a")
		if row then
			result = row.test
		end
	end)
	if not res then
		success = false
		errtxt = string.gsub(err or "", "\n.*", "")
	end
	local res, err = pcall(function()
		local sql = "DROP TABLE testing"
		assert (con:execute(sql))
	end)
	return success, errtxt, result
end

Ted Trask's avatar
Ted Trask committed
438 439 440
local printtableentries = function(tablename)
	-- retrieve a cursor
	local count = 0
441
	cur = assert (con:execute("SELECT * from "..escape(tablename)))
Ted Trask's avatar
Ted Trask committed
442 443 444 445 446
	-- print all rows, the rows will be indexed by field names
	row = cur:fetch ({}, "a")
	while row do
		count = count + 1
		for name,val in pairs(row) do
447
			mymodule.logevent(name.." = "..val..", ")
Ted Trask's avatar
Ted Trask committed
448 449 450 451 452
		end
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
453
	mymodule.logevent("Table "..tablename.." contains "..count.." rows")
Ted Trask's avatar
Ted Trask committed
454 455 456 457 458
end

-- ################################################################################
-- LOG FILE FUNCTIONS

459
local function checkwords(logentry)
460 461
	local goodwordloc={}
	local badwordloc={}
462 463
	local wrdcnt=0
	local isbad=0
464

465
	--check for ignored records first
466
	for i,thisline in ipairs(ignorewords) do
467 468 469
		if not thisline then
			break
		end
470 471 472 473 474 475 476 477 478

		-- ignore blank lines
		if string.find(thisline, "%S") then
			_,instcnt = string.lower(logentry.URL):gsub(format.escapemagiccharacters(thisline), " ")
			if instcnt ~= 0 then
				logentry.ignoreme = true
				--logme("ignoring...")
				break
			end
479 480 481
		end
	end

482
	if not logentry.ignoreme then
483 484 485 486 487
		--proceed with record analysis
		for i,thisline in ipairs(badwords) do
			if not thisline then
				break
			end
Ted Trask's avatar
Ted Trask committed
488

489 490 491 492 493 494 495 496 497
			-- ignore blank lines
			if string.find(thisline, "%S") then
				_,instcnt = string.lower(logentry.URL):gsub(format.escapemagiccharacters(thisline), " ")
				if instcnt ~= 0 then
					-- logme("instcnt =  "..instcnt)
					isbad=1
					wrdcnt= wrdcnt + instcnt
					badwordloc[#badwordloc+1] = thisline
				end
498
			end
499
		end
Ted Trask's avatar
Ted Trask committed
500

501
		--check for DansGuardian actions
502
		if (logentry.reason and logentry.reason ~= "") then
Ted Trask's avatar
Ted Trask committed
503 504 505 506 507 508 509 510 511 512
			if string.find(logentry.reason,"DENIED") then
				-- logme("*Denied*")
				logentry.deniedyesno=1
			elseif string.find(logentry.URL,"GBYPASS") then
				-- logme("GBYPASS")
				logentry.bypassyesno=1
			elseif string.find(logentry.reason,"OVERRIDE") then
				-- logme("*OVERRIDE*")
				logentry.bypassyesno=1
			end
513
		end
514 515 516

		--check for Squark actions
		if (logentry.squarkaction and logentry.squarkaction ~= "") then
Ted Trask's avatar
Ted Trask committed
517 518 519 520 521 522
			--logme("squarkaction="..logentry.squarkaction)
			if string.find(logentry.squarkaction, "blocked") then
				logentry.deniedyesno=1
			elseif string.find(logentry.squarkaction,"overridden") then
				logentry.bypassyesno=1
			end
523 524
		end

525 526 527 528 529 530
		--check for Squark category
		if (logentry.squarkcategory and logentry.squarkcategory ~= "") then
			logentry.reason = logentry.squarkcategory
			logentry.shortreason = logentry.squarkcategory
		end

531 532 533 534
		for i,goodline in ipairs(goodwords) do
			if not goodline then
				break
			end
535 536
			
			-- ignore blank lines
537
			if string.find(goodline, "%S") then
538 539 540 541 542 543 544 545 546
				_,instcnt = string.lower(logentry.URL):gsub(format.escapemagiccharacters(goodline), " ")
				--if string.find(logentry.URL,goodline) then
				if instcnt ~= 0 then
					if wrdcnt >= instcnt then
						wrdcnt = wrdcnt - instcnt
					else
						wrdcnt = 0
					end
					goodwordloc[#goodwordloc+1] = goodline
547 548
				end
			end
549 550
		end
	end
Ted Trask's avatar
Ted Trask committed
551 552 553 554
	-- Reset bad to reduce number of bad hits if score is zero
	-- if wrdcnt == 0 then
	-- isbad=0
	-- end
555 556 557

	logentry.score=wrdcnt
	logentry.badyesno=isbad
558 559
	logentry.wordloc=table.concat(badwordloc,"|")
	logentry.gwordloc=table.concat(goodwordloc,"|")
560 561 562 563
end

local function parsesquidlog(line)
	-- Format of squid log (space separated):
564
	-- time elapsed remotehost code/status bytes method URL rfc931 peerstatus/peerhost ? squarkcategory/squarkaction
565 566 567 568 569 570
	local words = {}

	for word in string.gmatch(line, "%S+") do
		words[#words+1] = word
	end

571 572 573
	local logentry = {logdatetime=words[1],
		elapsed=words[2],
		clientip=words[3],
Ted Trask's avatar
Ted Trask committed
574 575
		code=string.match(words[4] or "", "^[^/]*"),
		status=string.match(words[4] or "", "[^/]*$"),
576
		bytes=words[5],
Ted Trask's avatar
Ted Trask committed
577
		method=words[6],
578
		URL=words[7],
579
		clientuserid=words[8],
Ted Trask's avatar
Ted Trask committed
580
		peerstatus=string.match(words[9] or "", "^[^/]*"),
581 582 583
		peerhost=string.match(words[9] or "", "[^/]*$"),
		squarkcategory=string.match(words[11] or "", "^[^,]*"),
		squarkaction=string.match(words[11] or "", "[^,]*$")}
Ted Trask's avatar
Ted Trask committed
584

585
	checkwords(logentry)
586

Natanael Copa's avatar
Natanael Copa committed
587 588 589 590 591 592
	-- Don't care about TCP_DENIED so apps like dropbox, nokia connection
	-- suite does not flood our logs.
	if logentry.code == nil or logentry.code == "TCP_DENIED" then
		return nil
	end

Ted Trask's avatar
Ted Trask committed
593 594
	-- Don't care about local requests (from DG) (this check also removes blank lines)
	if logentry.clientip and logentry.clientip ~= "127.0.0.1" then
595 596
		logentry.logdatetime = os.date("%Y-%m-%d %H:%M:%S", logentry.logdatetime)..string.match(logentry.logdatetime, "%..*")
		return logentry
Ted Trask's avatar
Ted Trask committed
597 598 599 600
	end
	return nil
end

601
local function parsedglog(line)
602
	local words = format.string_to_table(line, "\t")
603 604 605 606 607 608 609
	local logentry = {logdatetime=words[1],
			clientuserid=words[2],
			clientip=words[3],
			URL=words[4],
			reason=words[5],
			method=words[6],
			bytes=words[7],
610 611
			shortreason=words[9],
			deniedyesno=1}
Ted Trask's avatar
Ted Trask committed
612

613
	checkwords(logentry)
Ted Trask's avatar
Ted Trask committed
614 615 616 617 618

	if logentry.reason and logentry.reason ~= "" then
           	if logentry.shortreason == "" then
           		logentry.shortreason = logentry.reason
           	end
619
		--logentry.score = string.match(logentry.reason, "^.*: ([0-9]+) ")
620
		logentry.logdatetime = string.gsub(logentry.logdatetime, "%.", "-")
Ted Trask's avatar
Ted Trask committed
621 622 623 624 625
	   	return logentry
	end
	return nil
end

Ted Trask's avatar
Ted Trask committed
626 627 628 629 630 631 632 633 634
-- ################################################################################
-- DOWNLOAD FILE FUNCTIONS

-- must do apk_add wget first

local connecttosource = function(source, cookiesfile)
	local success = false
	logme("Connecting to source "..source.sourcename)
	if source.method == "http" or source.method == "https" then
635 636
		fs.write_file(cookiesfile, "password="..source.passwd.."&userid="..source.userid.."&Logon=Logon&submit=true")
		local resultpage, errtxt = modelfunctions.run_executable({"wget", "-O", "-", "--no-check-certificate", "--save-cookies", cookiesfile, "--keep-session-cookies", "--post-file", cookiesfile, source.method.."://"..source.source.."/cgi-bin/acf/acf-util/logon/logon"})
Ted Trask's avatar
Ted Trask committed
637 638
		if resultpage == "" then
			logme("Failed to connect to "..source.sourcename)
639
		elseif string.find(resultpage, "/acf%-util/logon/logon") then
640
			logme("Failed to log on to "..source.sourcename)
Ted Trask's avatar
Ted Trask committed
641 642 643
		else
			success = true
		end
644 645
	elseif source.method == "local" then
		success = true
Ted Trask's avatar
Ted Trask committed
646 647 648 649 650 651 652
	end
	return success
end

local getlogcandidates = function(source, cookiesfile)
	local candidates = {}
	if source.method == "http" or source.method == "https" then
653
		local resultpage = modelfunctions.run_executable({"wget", "-O", "-", "--no-check-certificate", "--load-cookies", cookiesfile, source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/status"})
654
		-- This method works for view prior to acf-alpine-baselayout-0.12.0
655
		for file in string.gmatch(resultpage, "download%?[^\"]*name=([^\"]+)") do
Ted Trask's avatar
Ted Trask committed
656 657
			candidates[#candidates+1] = file
		end
658 659 660 661 662 663 664 665
		-- This method works for view from acf-alpine-baselayout-0.12.0
		local reversetable = {}
		for file in string.gmatch(resultpage, 'name="filename"  value="([^\"]+)"') do
			if not reversetable[file] then
				candidates[#candidates+1] = file
				reversetable[file] = true
			end
		end
666 667
	elseif source.method == "local" then
		candidates = fs.find_files_as_array(nil, source.source)
Ted Trask's avatar
Ted Trask committed
668 669 670 671
	end
	return candidates
end

672
local openlogfile = function(source, cookiesfile, logfile)
673
	local handle, handle2, errtxt
Ted Trask's avatar
Ted Trask committed
674
	if source.method == "http" or source.method == "https" then
675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707
		local cmd = {"wget", "-O", "-", "--no-check-certificate", "--load-cookies", cookiesfile, "--post-data", "submit=true&viewtype=stream&name="..logfile.."&filename="..logfile, source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/download"}
		cmd.stdin = "/dev/null"
		cmd.stdout = subprocess.PIPE
		cmd.stderr = "/dev/null"
		local res, err = pcall(function()
	                -- For security, set the path
	                posix.setenv("PATH", "/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin")
	                local proc, errmsg, errno = subprocess.popen(cmd)
			if proc then
				if string.find(logfile, "%.gz$") then
					local cmd2 = {"gunzip", "-c"}
					-- Pipe the output of wget into gunzip
					cmd2.stdin = proc.stdout
					cmd2.stdout = subprocess.PIPE
					cmd2.stderr = "/dev/null"
	                		local proc2, errmsg2, errno2 = subprocess.popen(cmd2)
					if proc2 then
						handle = proc2.stdout
						handle2 = proc.stdout
					else
						proc.stdout:close()
						errtxt = errmsg2 or "Unknown failure"
					end
				else
					handle = proc.stdout
				end
				
			else
				errtxt = errmsg or "Unknown failure"
			end
		end)
		if not res or err then
			errtxt = err or "Unknown failure"
708
		end
709
	elseif source.method == "local" then
710
		if string.find(logfile, "%.gz$") then
711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727
			local res, err = pcall(function()
	                	-- For security, set the path
		                posix.setenv("PATH", "/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin")
				local cmd = {"gunzip", "-c", logfile}
				cmd.stdin = "/dev/null"
				cmd.stdout = subprocess.PIPE
				cmd.stderr = "/dev/null"
	               		local proc, errmsg, errno = subprocess.popen(cmd)
				if proc then
					handle = proc.stdout
				else
					errtxt = errmsg or "Unknown failure"
				end
			end)
			if not res or err then
				errtxt = err or "Unknown failure"
			end
728
		else
729
			handle = io.open(logfile)
730
		end
Ted Trask's avatar
Ted Trask committed
731
	end
732
	return handle, handle2
Ted Trask's avatar
Ted Trask committed
733 734 735 736
end

local deletelogfile = function(source, cookiesfile, logfile)
	if source.method == "http" or source.method == "https" then
737
		modelfunctions.run_executable({"wget", "-O", "-", "--no-check-certificate", "--load-cookies", cookiesfile, "--post-data", "submit=true&name="..logfile.."&filename="..logfile, source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/delete"})
738 739
	elseif source.method == "local" then
		os.remove(logfile)
Ted Trask's avatar
Ted Trask committed
740 741 742 743 744 745
	end
end

-- ################################################################################
-- PUBLIC FUNCTIONS

746
function mymodule.getsourcelist()
Ted Trask's avatar
Ted Trask committed
747 748 749 750 751 752 753 754 755 756 757 758 759
	local retval = cfe({ type="list", value={}, label="Weblog Source List" })
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		retval.value = listsourceentries()
		databasedisconnect()
	end)
	if not res then
		retval.errtxt = err
	end

	return retval
end

760 761
function mymodule.getsource(sourcename)
	local sourcedata = mymodule.getnewsource()
Ted Trask's avatar
Ted Trask committed
762 763
	sourcedata.value.sourcename.value = sourcename
	sourcedata.value.sourcename.errtxt = "Source name does not exist"
764
	sourcedata.value.sourcename.readonly = true
Ted Trask's avatar
Ted Trask committed
765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790

	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local sourcelist = listsourceentries()
		databasedisconnect()
		for i,source in ipairs(sourcelist) do
			if source.sourcename == sourcename then
				sourcedata.value.sourcename.errtxt = nil
				for name,val in pairs(source) do
					if sourcedata.value[name] then
						sourcedata.value[name].value = val
					end
				end
				break
			end
		end
	end)
	if not res then
		sourcedata.errtxt = err
	end

	return sourcedata
end

local validatesource = function(sourcedata)
	local success = modelfunctions.validateselect(sourcedata.value.method)
791 792 793 794 795 796
	local test = {"sourcename", "source"}
	if sourcedata.value.method.value ~= "local" then
		test[#test+1] = "userid"
		test[#test+1] = "passwd"
	end
	for i,name in ipairs(test) do
Ted Trask's avatar
Ted Trask committed
797 798 799 800 801 802 803 804 805
		if sourcedata.value[name].value == "" then
			sourcedata.value[name].errtxt = "Cannot be empty"
			success = false
		end
	end

	return success
end

806
function mymodule.updatesource(self, sourcedata)
Ted Trask's avatar
Ted Trask committed
807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828
	local success = validatesource(sourcedata)
	sourcedata.errtxt = "Failed to update source"
	if success then
		local source = {}
		for name,val in pairs(sourcedata.value) do
			source[name] = val.value
		end

		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			sourcedata.descr = updatesourceentry(source)
			databasedisconnect()
			sourcedata.errtxt = nil
		end)
		if not res and err then
			sourcedata.errtxt = sourcedata.errtxt .. "\n" .. err
		end
	end

	return sourcedata
end

829
function mymodule.getnewsource()
Ted Trask's avatar
Ted Trask committed
830
	local source = {}
831 832 833 834 835 836 837
	source.sourcename = cfe({ label="Source Name", seq=0 })
	source.method = cfe({ type="select", value="local", label="Method", option={"http", "https", "local"}, seq=3 })
	source.userid = cfe({ label="UserID", seq=4 })
	source.passwd = cfe({ type="password", label="Password", seq=5 })
	source.source = cfe({ value="/var/log", label="Source Location / Address", seq=2 })
	source.tzislocal = cfe({ type="boolean", value=false, label="Using local timezone", seq=6 })
	source.enabled = cfe({ type="boolean", value=false, label="Enabled", seq=1 })
Ted Trask's avatar
Ted Trask committed
838 839 840
	return cfe({ type="group", value=source, label="Source" })
end

841
function mymodule.createsource(self, sourcedata)
Ted Trask's avatar
Ted Trask committed
842 843 844 845 846 847 848
	local success = validatesource(sourcedata)
	sourcedata.errtxt = "Failed to create source"
	if success then
		local source = {}
		for name,val in pairs(sourcedata.value) do
			source[name] = val.value
		end
849 850
		-- remove spaces from sourcename
		source.sourcename = string.gsub(source.sourcename, "%s+$", "")
Ted Trask's avatar
Ted Trask committed
851 852 853 854 855 856 857 858 859 860 861 862 863 864 865

		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
			sourcedata.descr = importsourceentry(source)
			databasedisconnect()
			sourcedata.errtxt = nil
		end)
		if not res and err then
			sourcedata.errtxt = sourcedata.errtxt .. "\n" .. err
		end
	end

	return sourcedata
end

866
function mymodule.getdeletesource(self, clientdata)
867 868 869 870 871
	local retval = {}
	retval.sourcename = cfe({ value=clientdata.sourcename or "", label="Source Name" })
	return cfe({ type="group", value=retval, label="Delete Source" })
end

872
function mymodule.deletesource(self, delsource)
873
	delsource.errtxt="Failed to delete source"
Ted Trask's avatar
Ted Trask committed
874 875
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
876
		local number = deletesourceentry(delsource.value.sourcename.value)
Ted Trask's avatar
Ted Trask committed
877 878
		databasedisconnect()
		if number > 0 then
879 880 881
			delsource.errtxt = nil
		else
			delsource.value.sourcename.errtxt = "Failed to find source"
Ted Trask's avatar
Ted Trask committed
882 883 884
		end
	end)
	if not res and err then
885
		delsource.errtxt = delsource.errtxt .. "\n" .. err
Ted Trask's avatar
Ted Trask committed
886
	end
887
	return delsource
Ted Trask's avatar
Ted Trask committed
888 889
end

890
function mymodule.gettestsource(self, clientdata)
891 892 893 894
	local retval = {}
	retval.sourcename = cfe({ value=clientdata.sourcename or "", label="Source Name" })
	return cfe({ type="group", value=retval, label="Test Source" })
end
Ted Trask's avatar
Ted Trask committed
895

896
function mymodule.testsource(self, test)
Ted Trask's avatar
Ted Trask committed
897
	-- temporary override of logme function to capture messages to result.value
898
	test.descr = {}
Ted Trask's avatar
Ted Trask committed
899
	local temp = logme
Ted Trask's avatar
Ted Trask committed
900
	logme = function(message) table.insert(test.descr, message) end
901 902

	test.errtxt = "Test Failed"
Ted Trask's avatar
Ted Trask committed
903 904 905 906

	local cookiesfile = "/tmp/cookies-"..tostring(os.time())
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
907
		local sources = listsourceentries(test.value.sourcename.value)
Ted Trask's avatar
Ted Trask committed
908 909
		databasedisconnect()
		if #sources < 1 then
910
			test.value.sourcename.errtxt = "Failed to find source"
Ted Trask's avatar
Ted Trask committed
911 912 913 914 915
		else
			local source = sources[1]
			-- run the test
			if connecttosource(source, cookiesfile) then
				local files = getlogcandidates(source, cookiesfile)
916
				test.errtxt = nil
Ted Trask's avatar
Ted Trask committed
917 918 919 920 921 922 923 924 925 926
				if #files == 0 then
					logme("No log files found")
				else
					for i,file in ipairs(files) do
						logme("Found log file "..file)
					end
				end
			end
		end
	end)
927 928
	if err and not res then
		test.errtxt = test.errtxt .. "\n" .. err
Ted Trask's avatar
Ted Trask committed
929 930 931 932
	end
	os.remove(cookiesfile)

	-- fix the result
933
	test.descr = table.concat(test.descr, "\n") or ""
Ted Trask's avatar
Ted Trask committed
934 935
	logme = temp

936
	return test
Ted Trask's avatar
Ted Trask committed
937 938
end

939 940
-- import a logfile and delete logfile after
local function importlogfile(source, cookiesfile, file, parselog_func)
Ted Trask's avatar
Ted Trask committed
941
	logme("Getting " .. file )
942 943 944 945 946
	local loghandle, loghandle2 = openlogfile(source, cookiesfile, file)
	if not loghandle then
		logme("Failed to get " .. file )
		return
	end
Ted Trask's avatar
Ted Trask committed
947
	logme("Processing " .. file )
948 949 950
	local res, err = pcall(function()
		con:execute("START TRANSACTION")
		for line in loghandle:lines() do
951 952 953
			assert(con:execute("SAVEPOINT before_line"))
			local res2, err2 = pcall(function()
				local logentry = parselog_func(line)
954
				importlogentry(logentry, source.sourcename)
955 956 957 958 959 960
			end)
			if not res2 then
				if (config.stoponerror == "true") then
					pcall(function() con:execute("ROLLBACK") end)
				else
					assert(con:execute("ROLLBACK TO before_line"))
961
					con:execute("COMMIT")
962 963 964
				end
				pcall(function() logme("Exception on line:"..line) end)
				if err2 then
Ted Trask's avatar
Ted Trask committed
965
					pcall(function() logme(err2) end)
966 967 968
				end
				if (config.stoponerror == "true") then
					assert(res2, "Import halted on exception")
969 970
				else
					con:execute("START TRANSACTION")
971
				end
972 973
			else
				assert(con:execute("RELEASE SAVEPOINT before_line"))
974
			end
975 976 977 978 979 980 981 982
		end
		con:execute("COMMIT")
	end)
	if not res then
		pcall(function() con:execute("ROLLBACK") end)
		if err then
			pcall(function() logme(err) end)
		end
983
	end
984
	loghandle:close()
985 986 987
	if loghandle2 then
		loghandle2:close()
	end
988 989 990 991 992
	if res then
		logme("Deleting " .. file )
		deletelogfile(source, cookiesfile, file)
	end
	return res
993 994
end

995
function mymodule.getimportlogs(self, clientdata)
996 997 998 999
	local retval = {}
	return cfe({ type="group", value=retval, label="Import Logs" })
end

1000
function mymodule.importlogs(self, import)
Ted Trask's avatar
Ted Trask committed
1001
	local count = 0
1002
	local success = true
Ted Trask's avatar
Ted Trask committed
1003 1004 1005 1006 1007

	local res, err = pcall(function()
		databaseconnect(DatabaseOwner, config.password)

		-- Download, parse, and import the logs
1008 1009 1010 1011
		logme("Executing importlogs")
		logme("Analyzing...")
		local sql = "ANALYZE"
		res = assert (con:execute(sql))
1012 1013 1014 1015 1016 1017

		-- Get the word lists
		goodwords = fs.read_file_as_array(goodwordslist) or {}
		badwords = fs.read_file_as_array(badwordslist) or {}
		ignorewords = fs.read_file_as_array(ignorewordslist) or {}

1018 1019
		-- Determine sources
		local sources = listsourceentries(sourcename)
Ted Trask's avatar
Ted Trask committed
1020

Ted Trask's avatar
Ted Trask committed
1021 1022 1023 1024 1025 1026 1027
		for i,source in ipairs(sources) do
			if source.enabled then
				logme("Getting logs from source " .. source.sourcename)
				local cookiesfile = "/tmp/cookies-"..tostring(os.time())
				if connecttosource(source, cookiesfile) then
					local files = getlogcandidates(source, cookiesfile)
					for j,file in ipairs(files) do
1028
						if string.match(file, "dansguardian/access%.log[%.%-]") then
Ted Trask's avatar
Ted Trask committed
1029
							count = count + 1
1030
							success = importlogfile(source, cookiesfile, file, parsedglog) and success
Ted Trask's avatar
Ted Trask committed
1031
						elseif string.match(file, "squid/access%.log[%.%-]") then
Ted Trask's avatar
Ted Trask committed
1032
							count = count + 1
1033
							success = importlogfile(source, cookiesfile, file, parsesquidlog) and success
Ted Trask's avatar
Ted Trask committed
1034 1035 1036 1037 1038 1039 1040 1041
						end
					end
				end
				os.remove(cookiesfile)
			end
		end

		-- Process the logs
1042 1043
		if success then
			updateusagestats()
1044
			importpubweblog()
1045
		end
Ted Trask's avatar
Ted Trask committed
1046 1047 1048 1049 1050 1051 1052
		-- Purge old database entries
		groomusagestat()
		groomdbhistlog()
		groompublogs()

		databasedisconnect()
	end)
1053
	if not res or not success then
1054
		import.errtxt = "Import Logs Failure"
Ted Trask's avatar
Ted Trask committed
1055 1056
		if err then
			pcall(function() logme(err) end)
1057
			import.errtxt = import.errtxt .. "\n" .. err
Ted Trask's avatar
Ted Trask committed
1058
		end
1059
		pcall(function() databasedisconnect() end)
1060 1061
	else
		import.descr = "Imported "..tostring(count).." logs"
Ted Trask's avatar
Ted Trask committed
1062 1063
	end

1064
	return import
Ted Trask's avatar
Ted Trask committed
1065 1066
end

1067
function mymodule.getactivitylog()
Ted Trask's avatar
Ted Trask committed
1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080
	local retval = cfe({ type="list", value={}, label="Weblog Activity Log" })
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		retval.value = listhistorylogentries() or {}
		databasedisconnect()
	end)
	if not res then
		retval.errtxt = err
	end

	return retval
end

1081
function mymodule.geteditselected()
Ted Trask's avatar
Ted Trask committed
1082 1083 1084 1085 1086 1087
	local result = {}
	result.select = cfe({ type="list", value={}, label="Entries to mark as selected" })
	result.deselect = cfe({ type="list", value={}, label="Entries to mark as selected" })
	return cfe({ type="group", value=result, label="Select / Deselect log entries" })
end

1088
function mymodule.editselected(self, data)
Ted Trask's avatar
Ted Trask committed
1089 1090 1091 1092
	local res, err = pcall(function()
		databaseconnect(DatabaseOwner)
		con:execute("START TRANSACTION")
		for i,sel in ipairs(data.value.select.value) do
1093
			assert (con:execute("UPDATE pubweblog SET selected = true WHERE id = '"..escape(sel).."'"))
Ted Trask's avatar
Ted Trask committed
1094
		end
Ted Trask's avatar
Ted Trask committed
1095
		for i,sel in ipairs(data.value.deselect.value) do
1096
			assert (con:execute("UPDATE pubweblog SET selected = false WHERE id = '"..escape(sel).."'"))
Ted Trask's avatar
Ted Trask committed
1097 1098 1099 1100 1101 1102
		end
		con:execute("COMMIT")
		databasedisconnect()
	end)
	if not res then
		data.errtxt = err
Ted Trask's avatar
Ted Trask committed
1103
	end
Ted Trask's avatar
Ted Trask committed
1104
	return data
1105 1106
end

1107
function mymodule.getclearselected(self, clientdata)
1108 1109 1110 1111
	local retval = {}
	return cfe({ type="group", value=retval, label="Clear select fields" })
end

1112
function mymodule.clearselected(self, clear)
1113
	clear.errtxt = "Failed to clear select fields"
1114 1115 1116 1117 1118
	local res, err = pcall(function()
		sql = "UPDATE pubweblog SET selected = false WHERE selected = true"
		databaseconnect(DatabaseOwner)
		assert (con:execute(sql))
		databasedisconnect()
1119
		clear.errtxt = nil
1120 1121
	end)
	if not res then
1122
		clear.errtxt = clear.errtxt.."\n"..err
1123
	end
1124
	return clear
1125 1126
end

1127 1128 1129
local validateweblogparameters = function(params)
	local success = modelfunctions.validateselect(params.value.activelog)
	success = modelfunctions.validateselect(params.value.sortby) and success
1130
	success = modelfunctions.validatemulti(params.value.sourcename) and success
1131 1132 1133
	if params.value.clientip.value ~= "" and string.find(params.value.clientip.value, "[^%d%.]") then
		params.value.clientip.errtxt = "Invalid IP Address"
		success = false
Ted Trask's avatar
Ted Trask committed
1134
	end
1135 1136 1137 1138
	if not validator.is_integer(params.value.score.value) then
		params.value.score.errtxt = "Must be a number"
		success = false
	end
1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local s
		if params.value.starttime.value ~= "" then
			s,params.value.starttime.errtxt,params.value.starttime.value = convertdatabaseentry("TIMESTAMP", params.value.starttime.value)
			success = success and s
		end
		if params.value.endtime.value ~= "" then
			s,params.value.endtime.errtxt,params.value.endtime.value = convertdatabaseentry("TIMESTAMP", params.value.endtime.value)
			success = success and s
		end
		if params.value.focus.value ~= "" then
			s,params.value.focus.errtxt,params.value.focus.value = convertdatabaseentry("TIMESTAMP", params.value.focus.value)
			success = success and s
		end
		databasedisconnect()
	end)
	if not res and err then
		params.value.starttime.errtxt = err
		params.value.endtime.errtxt = err
		params.value.focus.errtxt = err
		success = false
1161
	end
1162 1163
	return success
end
Ted Trask's avatar
Ted Trask committed
1164

1165
function mymodule.getweblogparameters(self, clientdata)
1166
	local c = mymodule.getconfig()
1167 1168 1169 1170
	local result = {}
	result.activelog = cfe({ type="select", value="pubweblog", option={"pubweblog", "pubweblog_history"}, label="Active Weblog", seq=1 })
	result.starttime = cfe({ value=c.value.auditstart.value, label="Start Time", seq=2 })
	result.endtime = cfe({ value=c.value.auditend.value, label="End Time", seq=3 })
1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193
	result.sourcename = cfe({ type="multi", value={}, label="Source", option={}, seq=4 })
	result.clientuserid = cfe({ value=clientdata.clientuserid or "", label="User ID", seq=5 })
	result.clientip = cfe({ value=clientdata.clientip or "", label="Client IP", seq=6 })
	result.urisearch = cfe({ value="", label="URI Contains", descr="Retrieve records where the URI contains this word", seq=7 })
	result.score = cfe({ value=c.value.score.value, label="Minimum Score", descr="Minimum score to search on", seq=8 })
	result.sortby = cfe({ type="select", value=c.value.sortby.value, option=c.value.sortby.option, label="Sort By field", descr="Sort by this field when displaying records", seq=9 })
	result.badyesno = cfe({ type="boolean", value=c.value.badyesno.value, label="Show Suspect Records", descr="Limit search to records marked as suspect", seq=10 })
	result.deniedyesno = cfe({ type="boolean", value=c.value.deniedyesno.value, label="Show Denied Records", descr="Limit search to Denied URIs", seq=11 })
	result.bypassyesno = cfe({ type="boolean", value=c.value.bypassyesno.value, label="Show Bypass Records", descr="Limit search to Bypass attempts", seq=12 })
	result.selected = cfe({ type="boolean", value=false, label="Show Selected Records", descr="Limit search to records that have been selected", seq=13 })
	result.focus = cfe({ value="", label="Focus Time", seq=14 })

	-- Get the source options
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		local sources = listsourceentries()
		for i,s in ipairs(sources) do
			result.sourcename.value[#result.sourcename.value + 1] = s.sourcename
			result.sourcename.option[#result.sourcename.option + 1] = s.sourcename
		end
		databasedisconnect()
	end)
	if err and not res then
1194
		result.sourcename.errtxt = err
1195 1196
	end

1197 1198
	return cfe({ type="group", value=result, label="Weblog Access Log" })
end
Ted Trask's avatar
Ted Trask committed
1199

1200
function mymodule.getweblog(self, result)
1201 1202 1203 1204
	local success = validateweblogparameters(result)
	result.value.log = cfe({ type="list", value={}, label="Weblog Access Log" })
	result.value.window = cfe({ value=config.window or "5", label="Time Window" })
	local err
1205 1206 1207
	if success then
		local res, err = pcall(function()
			databaseconnect(DatabaseUser)
1208
			result.value.log.value = listlogentries(result.value.activelog.value, result.value.clientuserid.value, result.value.starttime.value, result.value.endtime.value, result.value.clientip.value, result.value.badyesno.value, result.value.deniedyesno.value, result.value.bypassyesno.value, result.value.score.value, result.value.urisearch.value, result.value.sortby.value, result.value.selected.value, result.value.sourcename.value ) or {}
1209 1210
			databasedisconnect()
		end)
1211 1212 1213
		if not res then
			result.errtxt = err
		end
1214
	else
1215
		result.errtxt = "Invalid search parameters"
1216
	end
1217
	return result
Ted Trask's avatar
Ted Trask committed
1218 1219
end

1220
function mymodule.getusagestats()
Ted Trask's avatar
Ted Trask committed
1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233
	local retval = cfe({ type="list", value={}, label="Weblog Usage Stats" })
	local res, err = pcall(function()
		databaseconnect(DatabaseUser)
		retval.value = listusagestats() or {}
		databasedisconnect()
	end)
	if not res then
		retval.errtxt = err
	end

	return retval
end

1234
function mymodule.getauditstats()
1235 1236 1237 1238 1239 1240 1241 1242
	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)
1243
			result.stats.value = groupflaggedlogentries(config.auditstart, config.auditend, result.groupby.value) or {}
1244 1245 1246 1247 1248 1249
			databasedisconnect()
		end
	end)
	return cfe({ type="group", value=result, errtxt=err, label="Weblog Audit Statistics" })
end

1250
function mymodule.getcompleteaudit(self, clientdata)
1251 1252 1253 1254 1255
	local retval = {}
	retval.timestamp = cfe({ value=clientdata.timestamp or "", label="New Audit End Time" })
	return cfe({ type="group", value=retval, label="Complete Audit" })
end

1256
function mymodule.completeaudit(self, complete)
1257 1258 1259 1260
	if "" == complete.value.timestamp.value then
		local now = os.time()
		complete.value.timestamp.value = os.date("%Y-%m-%d %H:%M:%S", now - now%86400 - 86400)
	end
1261
	local conf = mymodule.getconfig()
1262
	conf.value.auditstart.value = conf.value.auditend.value
1263
	conf.value.auditend.value = complete.value.timestamp.value
1264
	conf = mymodule.updateconfig(self, conf)
1265
	if conf.errtxt then
1266
		complete.errtxt = "Failed to complete audit\n"..conf.errtxt.."\n"..conf.value.auditend.errtxt
1267
	end
1268
	return complete
1269
end
Ted Trask's avatar
Ted Trask committed
1270