diff options
Diffstat (limited to 'weblog-model.lua')
| -rw-r--r-- | weblog-model.lua | 1196 |
1 files changed, 1196 insertions, 0 deletions
diff --git a/weblog-model.lua b/weblog-model.lua new file mode 100644 index 0000000..aeb401e --- /dev/null +++ b/weblog-model.lua @@ -0,0 +1,1196 @@ +module(..., package.seeall) + +-- Load libraries +require("modelfunctions") +require("fs") +require("format") +require("validator") +require("luasql.postgres") + +local DatabaseName = "webproxylog" +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 +local configfile = "/etc/weblog.conf" +local configcontent = fs.read_file(configfile) or "" +local config = format.parse_ini_file(configcontent, "") or {} + +local database_creation_script = { + "CREATE TABLE dbhistlog (logdatetime timestamp(0) without time zone NOT NULL, msgtext text)", + "CREATE TABLE pubblocklog(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 integer NOT NULL, reason text, score integer)", + "CREATE TABLE blocklog(sourcename character varying(40), clientip inet NOT NULL, clientuserid character varying(64) NOT NULL, logdatetime timestamp(0) without time zone NOT NULL, uri text NOT NULL, bytes integer NOT NULL, reason text, score integer)", + "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 integer NOT NULL, reason text, score integer)", + "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 integer NOT NULL, reason text, score integer)", + "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)", + "CREATE TABLE usagestat (sourcename character varying(40) NOT NULL, date timestamp(0) without time zone NOT NULL, numrequest integer, numblock integer)", + "CREATE TABLE watchlist (clientuserid character varying(64) NOT NULL, expiredatetime timestamp(0) without time zone NOT NULL)", + "ALTER TABLE ONLY source ADD CONSTRAINT source_pkey PRIMARY KEY (sourcename)", + "CREATE INDEX blocklogclientididx ON blocklog USING btree (clientuserid)", + "CREATE INDEX blocklogclientidx ON blocklog USING btree (clientip, clientuserid)", + "CREATE INDEX dbhistlogdatetimeidx ON dbhistlog USING btree (logdatetime)", + "CREATE INDEX pubblocklogclientididx ON pubblocklog USING btree (clientuserid)", + "CREATE INDEX pubblocklogclientidx ON pubblocklog USING btree (clientip, clientuserid)", + "CREATE INDEX pubweblogclientdateidx ON pubweblog USING btree (logdatetime, clientuserid)", + "CREATE INDEX pubweblogclientidx ON pubweblog USING btree (clientip, clientuserid)", + "CREATE INDEX pubweblogclientipidx ON pubweblog USING btree (clientip)", + "CREATE INDEX pubweblogclientuserididx ON pubweblog USING btree (clientuserid)", + "CREATE INDEX pubwebloglogdatetimeidx ON pubweblog USING btree (logdatetime)", + "CREATE INDEX weblogclientidx ON weblog USING btree (clientip, clientuserid)", + "CREATE INDEX weblogclientipidx ON weblog USING btree (clientip)", + "CREATE INDEX weblogclientuserididx ON weblog USING btree (clientuserid)", + "GRANT SELECT ON dbhistlog TO webloguser", + "GRANT SELECT ON pubblocklog TO webloguser", + "GRANT SELECT ON pubweblog TO webloguser", + "GRANT SELECT, UPDATE, INSERT, DELETE ON source TO webloguser", + "GRANT SELECT ON usagestat TO webloguser", + "GRANT SELECT, UPDATE, INSERT, DELETE ON watchlist TO webloguser", +} + +-- ################################################################################ +-- DATABASE FUNCTIONS + +local function assert (v, m) + if not v then + m = m or "Assertion failed!" + error(m, 0) + end + return v, m +end + +-- Escape special characters in sql statements +local escape = function(sql) + sql = sql or "" + return string.gsub(sql, "'", "''") +end + +-- List the postgres databases on this system +local listdatabases = function() + local dbs = {} + local cmd = path.."psql -U postgres -tl 2>&1" + local f = io.popen(cmd) + local result = f:read("*a") or "" + f:close() + 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 + local cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseOwner..' WITH PASSWORD \''..password..'\'" 2>&1' + local f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseUser..'" 2>&1' + f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + + -- Create the database + cmd = path..'psql -U postgres -c "CREATE DATABASE '..DatabaseName..' WITH OWNER '..DatabaseOwner..'" 2>&1' + f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + + return table.concat(result, "\n") +end + +-- Delete the database and roles +local deletedatabase = function() + local result = {} + + local cmd = path..'psql -U postgres -c "DROP DATABASE '..DatabaseName..'" 2>&1' + local f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseUser..'" 2>&1' + f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseOwner..'" 2>&1' + f = io.popen(cmd) + table.insert(result, f:read("*a")) + f:close() + + return table.concat(result, "\n") +end + +-- Run an SQL script +local runSQLscript = function(filename) + -- Create the database + local cmd = path..'psql -U postgres -f "'..filename..'" '..DatabaseName..' 2>&1' + local f = io.popen(cmd) + local result = f:read("*a") or "" + f:close() + -- Create the tables + print (result) + return result +end + +-- Create the database and tables +-- pg_dump -U postgres -c webproxylog > makeweblog.postgres +--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) + local sql = string.format("INSERT INTO dbhistlog VALUES ('%s', '%s')", + os.date("%Y-%m-%d %H:%M:%S"), escape(message)) + local res = assert (con:execute(sql)) +end + +local listhistorylogentries = function() + local entries = {} + -- retrieve a cursor + cur = assert (con:execute"SELECT logdatetime, msgtext from dbhistlog") + 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 + +local importsquidlog = function(logentries, sourcename) + for i,entry in pairs(logentries) do + local sql = string.format("INSERT INTO weblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s')", + escape(sourcename), escape(entry.clientip), escape(entry.clientuserid:lower()), + escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes)) + local res = assert (con:execute(sql)) + end +end + +local importdglog = function(logentries, sourcename) + for i,entry in pairs(logentries) do + local sql = string.format("INSERT INTO blocklog VALUES ('%s', '0.0.0.0', '%s', '%s', '%s', '%s', '%s', '%s')", + escape(sourcename), escape(entry.clientuserid:lower()), escape(entry.logdatetime), + escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score or "0")) + local res = assert (con:execute(sql)) + end +end + +local listsourceentries = function(sourcename) + local sources = {} + -- retrieve a cursor +-- cur = assert (con:execute"SELECT sourcename, userid, passwd, source, tzislocal FROM source WHERE method='http' and enabled=true") + 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 + +-- Add blocked users (from blocklog) to the watchlist, removing old entries if updated. +local addtowatchlist = function() + local watchdays = config.watchdays or 14 + local sql = "insert into watchlist select clientuserid, " .. + "(max(logdatetime) + INTERVAL '"..watchdays.." days') as expiredatetime " .. + "from blocklog group by clientuserid" + local res1 = assert (con:execute(sql)) + sql = "delete from watchlist where exists " .. + "(select * from watchlist w where w.clientuserid = watchlist.clientuserid " .. + "and w.expiredatetime > watchlist.expiredatetime)" + local res2 = assert (con:execute(sql)) + logme("There are now " .. (res1-res2) .. " new names in the watchlist") +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) + local sql = "insert into usagestat select weblog.sourcename, " .. + "date_trunc('hour', weblog.logdatetime) as date, " .. + "count(*), 0 from weblog group by sourcename,date" + local res = assert (con:execute(sql)) + + -- update the usagestat table from blocklog + -- (will result in multiple rows where logs rotated on partial hours) + -- (also, numrequest and numblock end up in different rows) + sql= "insert into usagestat select blocklog.sourcename, " .. + "date_trunc('hour', blocklog.logdatetime) as date, " .. + "0, count(*) from blocklog group by sourcename,date" + res = assert (con:execute(sql)) +end + +-- Process weblog and blocklog, combine into pubweblog and pubblocklog +-- empties weblog and blocklog +local importpubweblog = function() + -- Merge equal blocks into weblog + sql = "update weblog set reason=blocklog.reason, " .. + "score=blocklog.score from blocklog where " .. + "blocklog.logdatetime >= date_trunc('second', weblog.logdatetime) and " .. + "blocklog.logdatetime < (weblog.logdatetime + INTERVAL '45' second) and " .. + "blocklog.sourcename=weblog.sourcename and ".. + "blocklog.clientuserid=weblog.clientuserid and ".. + "weblog.uri like (blocklog.uri || '%')" + res = assert (con:execute(sql)) + logme("importpubweblog merged " .. res .. " blocks with matching timestamps.") + + sql = "insert into weblog select * from blocklog where " .. + " NOT EXISTS (select * from weblog where " .. + "blocklog.logdatetime >= date_trunc('second', weblog.logdatetime) and " .. + "blocklog.logdatetime < (weblog.logdatetime + INTERVAL '45' second) and " .. + "blocklog.sourcename=weblog.sourcename and ".. + "blocklog.clientuserid=weblog.clientuserid and ".. + "weblog.uri like (blocklog.uri || '%') )" + res = assert (con:execute(sql)) + if ( res > 0 ) then + logme("NOTE: importpubweblog found and merged " .. res .. " unmatched blocks (source IP will not match)") + end + + -- Move weblog to pubweblog + sql= "insert into pubweblog select * from weblog" + res = assert (con:execute(sql)) + logme("importpubweblog imported " .. res .. " new rows into database.") + + -- update pubblocklog table + sql = "insert into pubblocklog select * from weblog where reason is not null" + res = assert (con:execute(sql)) + logme("importpubweblog imported " .. res .. " new blocks into block table.") + + -- grab the bypass info + sql = "insert into pubblocklog select * from weblog where uri like '%?GBYPASS%'" + res = assert (con:execute(sql)) + logme("importpubweblog imported " .. res .. " dg bybass events into block table.") + + -- Truncate the staging tables + assert (con:execute("truncate weblog")) + assert (con:execute("truncate blocklog")) + logme("truncated staging tables") +end + +-- Delete people from the watchlist when they have expired out +local groomwatchlist = function() + local res = assert (con:execute("delete from watchlist where expiredatetime < now()")) + logme("removed " .. res .. " names from watchlist") +end + +-- Delete useage stats from more than a year ago +local groomusagestat = function() + local res = assert (con:execute("delete from usagestat where " .. + "date < (now() - INTERVAL '1 year')")) + logme("removed " .. res .. " old usage status lines") +end + +-- Delete history log information from more than a month ago +local groomdbhistlog = function() + local res = assert (con:execute("delete from dbhistlog where " .. + "logdatetime < (now() - INTERVAL '1 month')")) + logme("removed " .. res .. " old dbhistlog lines") +end + +-- Delete old junk from pub tables +local groompublogs = function() + local purgedays = config.purgedays or 30 + local watchdays = config.watchdays or 14 + local historydays = config.historydays or 14 + local now = os.time() + + local temp = os.date("%Y-%m-%d %H:%M:%S", now - purgedays*86400) + logme("Purgedate is " .. temp .. ". Nothing will exist beyond purgedate.") + + -- purge anything older than purgedate + sql = "delete from pubweblog where logdatetime < '" .. temp .."'" + res = assert (con:execute(sql)) + logme("Purged " .. res .. " old records from pubweblog") + + sql = "delete from pubblocklog where logdatetime < '" .. temp .. "'" + res = assert (con:execute(sql)) + logme("Purged " .. res .. " old records from pubblocklog") + + -- 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") + + sql = "delete from pubblocklog where logdatetime < (timestamp '"..temp.."' - INTERVAL '"..tostring(watchdays+historydays).." days')" + res = assert (con:execute(sql)) + logme("removed " .. res .. " old pubblocklog 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() + local entries = {} + -- retrieve a cursor + cur = assert (con:execute("SELECT * from watchlist ORDER BY clientuserid")) + row = cur:fetch ({}, "a") + while row do + entries[#entries+1] = {clientuserid = row.clientuserid, expiredatetime= row.expiredatetime} + row = cur:fetch (row, "a") + end + -- close everything + cur:close() + return entries +end + +local importwatchlistentry = function(clientuserid, expiredatetime) + local sql = string.format("INSERT INTO watchlist VALUES ('%s', '%s')", + escape(clientuserid), escape(expiredatetime)) + local res = assert (con:execute(sql)) + return res +end + +local deletewatchlistentry = function(clientuserid) + local sql = string.format("DELETE FROM watchlist WHERE clientuserid='%s'", escape(clientuserid)) + local res = assert (con:execute(sql)) + return res +end + +local generatewhereclause = function(clientuserid, starttime, endtime, clientip) + local sql = "" + local where = {} + if clientuserid and clientuserid ~= "" then + where[#where+1] = "clientuserid = '"..escape(clientuserid).."'" + 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 + if #where > 0 then + sql = " WHERE " .. table.concat(where, " AND ") + end + return sql +end + +local listlogentries = function(logname, clientuserid, starttime, endtime, clientip) + local entries = {} + -- retrieve a cursor + local sql = "SELECT * from "..logname + sql = sql .. generatewhereclause(clientuserid, starttime, endtime, clientip) + sql = sql .. " ORDER BY logdatetime" + cur = assert (con:execute(sql)) + row = cur:fetch ({}, "a") + while row do + entries[#entries+1] = {sourcename=row.sourcename, clientip=row.clientip, clientuserid=row.clientuserid, logdatetime=row.logdatetime, uri=row.uri, bytes=row.bytes, reason=row.reason, score=row.score} + row = cur:fetch (row, "a") + end + -- close everything + cur:close() + return entries +end + +local listpubblocklogentries = function(...) + return listlogentries("pubblocklog", ...) +end + +local listpubweblogentries = function(...) + return listlogentries("pubweblog", ...) +end + +local grouppubblocklogentries = function(starttime, endtime) + local entries = {} + -- retrieve a cursor + local sql = "SELECT clientuserid, count(*) AS numblock, max(score) AS maxscore FROM pubblocklog" + sql = sql .. generatewhereclause(nil, starttime, endtime) + sql = sql .. " GROUP BY clientuserid ORDER BY numblock DESC" + cur = assert (con:execute(sql)) + row = cur:fetch ({}, "a") + while row do + entries[#entries+1] = {clientuserid=row.clientuserid, numblock=row.numblock, maxscore=row.maxscore} + row = cur:fetch (row, "a") + end + -- close everything + cur:close() + return entries +end + +local listusagestats = function() + local entries = {} + -- retrieve a cursor + local sql = "SELECT sourcename, date, sum(numrequest) AS numrequest, sum(numblock) AS numblock " .. + "FROM usagestat GROUP BY sourcename, date ORDER BY sourcename, date" + 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 + +local printtableentries = function(tablename) + -- retrieve a cursor + local count = 0 + cur = assert (con:execute("SELECT * from "..tablename)) + -- 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 + APP.logevent(name.." = "..val..", ") + end + row = cur:fetch (row, "a") + end + -- close everything + cur:close() + APP.logevent("Table "..tablename.." contains "..count.." rows") +end + +-- ################################################################################ +-- LOG FILE FUNCTIONS + +local parsesquidlog = function(logdata) + local logentries = {} + for line in string.gmatch(logdata, "[^\n]+") do + -- 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 logentry = {logdatetime=words[1], elapsed=words[2], clientip=words[3], code=string.match(words[4], "^[^/]*"), status=string.match(words[4], "[^/]*$"), bytes=words[5], method=words[6], URL=words[7], clientuserid=words[8], peerstatus=string.match(words[9], "^[^/]*"), peerhost=string.match(words[9], "[^/]*$")} + logentry.logdatetime = os.date("%Y-%m-%d %H:%M:%S", logentry.logdatetime)..string.match(logentry.logdatetime, "%..*") + -- Don't care about local requests (from DG) + if logentry.clientip ~= "127.0.0.1" then + logentries[#logentries+1] = logentry + end + end + return logentries +end + +local parsedglog = function(logdata) + local logentries = {} + for line in string.gmatch(logdata, "[^\n]+") do + local words = {} + for word in string.gmatch(line, "[^\t]+") do + words[#words+1] = word + end + local logentry = {logdatetime=words[1], clientuserid=words[2], clientip=words[3], URL=words[4], reason=words[5], method=words[6], bytes=words[7]} + logentry.score = string.match(logentry.reason, "^.*: ([0-9]*) ") + logentry.logdatetime = string.gsub(logentry.logdatetime, "%.", "-") + + logentries[#logentries+1] = logentry + end + return logentries +end + +-- ################################################################################ +-- 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 + local cmd = "wget -O - --no-check-certificate --save-cookies "..cookiesfile.." --keep-session-cookies --post-data 'password="..source.passwd.."&userid="..source.userid.."&Logon=Logon' '"..source.method.."://"..source.source.."/cgi-bin/acf/acf-util/logon/logon' 2>/dev/null" + local f = io.popen(cmd) + local resultpage = f:read("*a") + f:close() + if resultpage == "" then + logme("Failed to connect to "..source.sourcename) + elseif string.find(resultpage, "Log in") then + logme("Failed to log in to "..source.sourcename) + else + success = true + end + end + return success +end + +local getlogcandidates = function(source, cookiesfile) + local candidates = {} + if source.method == "http" or source.method == "https" then + local cmd = "wget -O - --no-check-certificate --load-cookies "..cookiesfile.." '"..source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/status' 2>/dev/null" + local f = io.popen(cmd) + local resultpage = f:read("*a") + f:close() + for file in string.gmatch(resultpage, "download%?name=([^\"]+)") do + candidates[#candidates+1] = file + end + end + return candidates +end + +local getlogfile = function(source, cookiesfile, logfile) + local filecontent + if source.method == "http" or source.method == "https" then + local cmd = "wget -O - --no-check-certificate --load-cookies "..cookiesfile.." --post-data 'name="..logfile.."' '"..source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/download' 2>/dev/null" + local f = io.popen(cmd) + filecontent = f:read("*a") + f:close() + end + return filecontent +end + +local deletelogfile = function(source, cookiesfile, logfile) + if source.method == "http" or source.method == "https" then + local cmd = "wget -O - --no-check-certificate --load-cookies "..cookiesfile.." --post-data 'name="..logfile.."' '"..source.method.."://"..source.source.."/cgi-bin/acf/alpine-baselayout/logfiles/delete' 2>/dev/null" + local f = io.popen(cmd) + f:close() + end +end + +-- ################################################################################ +-- PUBLIC FUNCTIONS + +function getsourcelist() + 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 + +function getsource(sourcename) + local sourcedata = getnewsource() + sourcedata.value.sourcename.value = sourcename + sourcedata.value.sourcename.errtxt = "Source name does not exist" + + 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) + for i,name in ipairs({"sourcename", "userid", "passwd", "source"}) do + if sourcedata.value[name].value == "" then + sourcedata.value[name].errtxt = "Cannot be empty" + success = false + end + end + + return success +end + +function updatesource(sourcedata) + 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 + +function getnewsource() + local source = {} + source.sourcename = cfe({ label="Source Name" }) + source.method = cfe({ type="select", value="https", label="Method", option={"http", "https"} }) + source.userid = cfe({ label="UserID" }) + source.passwd = cfe({ label="Password" }) + source.source = cfe({ label="Source Address" }) + source.tzislocal = cfe({ type="boolean", value=false, label="Using local timezone" }) + source.enabled = cfe({ type="boolean", value=false, label="Enabled" }) + return cfe({ type="group", value=source, label="Source" }) +end + +function createsource(sourcedata) + 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 + + 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 + else + end + + return sourcedata +end + +function deletesource(sourcename) + local result = cfe({ errtxt="Failed to delete source", label="Delete source result" }) + local res, err = pcall(function() + databaseconnect(DatabaseUser) + local number = deletesourceentry(sourcename) + databasedisconnect() + if number > 0 then + result.errtxt = nil + result.value = "Source Deleted" + end + end) + if not res and err then + result.errtxt = result.errtxt .. "\n" .. err + end + return result +end + +function testsource(sourcename) + local result = cfe({ label="Test source result" }) + + -- temporary override of logme function to capture messages to result.value + result.value = {} + local temp = logme + logme = function(message) table.insert(result.value, message) end + + local cookiesfile = "/tmp/cookies-"..tostring(os.time()) + local res, err = pcall(function() + databaseconnect(DatabaseUser) + local sources = listsourceentries(sourcename) + databasedisconnect() + if #sources < 1 then + result.errtxt = "Failed to find source" + else + local source = sources[1] + -- run the test + if connecttosource(source, cookiesfile) then + local files = getlogcandidates(source, cookiesfile) + 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) + if not res then + result.errtxt = "Failed to connect to source" + if err then + result.errtxt = result.errtxt .. "\n" .. err + end + end + os.remove(cookiesfile) + + -- fix the result + result.value = table.concat(result.value, "\n") or "" + logme = temp + + return result +end + +function importlogs() + local result = cfe({ label="Weblog Import Logs Result" }) + local count = 0 + + local res, err = pcall(function() + databaseconnect(DatabaseOwner, config.password) + -- Determine sources + local sources = listsourceentries(sourcename) + + -- Download, parse, and import the logs + 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 + if string.match(file, "dansguardian/access%.log%.") then + count = count + 1 + logme("Processing " .. file ) + logme("Getting " .. file ) + logcontent = getlogfile(source, cookiesfile, file) + logentries = parsedglog(logcontent) + importdglog(logentries, source.sourcename) + logme("Deleting " .. file ) + deletelogfile(source, cookiesfile, file) + elseif string.match(file, "squid/access%.log%.") then + count = count + 1 + logme("Processing " .. file ) + logme("Getting " .. file ) + local logcontent = getlogfile(source, cookiesfile, file) + local logentries = parsesquidlog(logcontent) + importsquidlog(logentries, source.sourcename) + logme("Deleting " .. file ) + deletelogfile(source, cookiesfile, file) + end + end + end + os.remove(cookiesfile) + end + end + + -- Process the logs + addtowatchlist() + updateusagestats() + importpubweblog() + -- Purge old database entries + groomwatchlist() + groomusagestat() + groomdbhistlog() + groompublogs() + + databasedisconnect() + end) + if not res then + result.errtxt = "Import Logs Failure" + if err then + pcall(function() logme(err) end) + result.errtxt = result.errtxt .. "\n" .. err + end + end + + result.value = "Imported "..tostring(count).." logs" + + return result +end + +function getactivitylog() + 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 + +function getwatchlist() + local retval = cfe({ type="list", value={}, label="Weblog Watchlist" }) + local res, err = pcall(function() + databaseconnect(DatabaseUser) + retval.value = listwatchlistentries() or {} + databasedisconnect() + end) + if not res then + retval.errtxt = err + end + + return retval +end + +function getnewwatchlistentry() + local watch = {} + watch.clientuserid = cfe({ label="User ID" }) + local watchdays = config.watchdays or 14 + watch.expiredatetime = cfe({ value=os.date("%Y-%m-%d %H:%M:%S", os.time() + watchdays*86400), label="Expiration Date" }) + + return cfe({ type="group", value=watch, label="Watchlist Entry" }) +end + +function createwatchlistentry(watch) + local success = true + for i,name in ipairs({"clientuserid", "expiredatetime"}) do + if watch.value[name].value == "" then + watch.value[name].errtxt = "Cannot be empty" + success = false + end + end + watch.value.clientuserid.value = watch.value.clientuserid.value:lower() + watch.errtxt = "Failed to create watchlist entry" + local res, err = pcall(function() + databaseconnect(DatabaseUser) + local watchlist = listwatchlistentries() or {} + for i,w in ipairs(watchlist) do + if w.clientuserid == watch.value.clientuserid.value then + watch.value.clientuserid.errtxt = "User ID already exists" + success = false + break + end + end + if success then + local count = importwatchlistentry(watch.value.clientuserid.value, watch.value.expiredatetime.value) + if count > 0 then + watch.errtxt = nil + end + end + databasedisconnect() + end) + if not res and err then + watch.errtxt = watch.errtxt .. "\n" .. err + end + + return watch +end + +function deletewatchlistent(clientuserid) + local result = cfe({ errtxt="Failed to delete watchlist entry", label="Delete watchlist entry result" }) + local res, err = pcall(function() + databaseconnect(DatabaseUser) + local number = deletewatchlistentry(clientuserid) + databasedisconnect() + if number > 0 then + result.value = "Watchlist entry deleted" + result.errtxt = nil + end + end) + if not res and err then + result.errtxt = result.errtxt .. "\n" .. err + end + + return result +end + +local handleparameters = function(clientuserid, starttime, endtime, clientip, focus) + local result = {} + result.clientuserid = cfe({ value=clientuserid or "", label="User ID" }) + result.starttime = cfe({ value=starttime or "", label="Start Time" }) + result.endtime = cfe({ value=endtime or "", label="End Time" }) + result.clientip = cfe({ value=clientip or "", label="Client IP" }) + result.window = cfe({ value=config.window or "5", label="Time Window" }) + result.focus = cfe({ value=focus or "", label="Time Window" }) + return result +end + +function getweblog(clientuserid, starttime, endtime, clientip, focus) + if (not starttime or starttime=="") and (not endtime or endtime=="") and config.auditstart~="" and config.auditend~="" then + starttime = config.auditstart + endtime = config.auditend + end + local result = handleparameters(clientuserid, starttime, endtime, clientip, focus) + result.log = cfe({ type="list", value={}, label="Weblog Access Log" }) + local res, err = pcall(function() + databaseconnect(DatabaseUser) + result.log.value = listpubweblogentries(clientuserid, starttime, endtime, clientip) or {} + databasedisconnect() + end) + return cfe({ type="group", value=result, errtxt=err, label="Weblog Access Log" }) +end + +function getblocklog(clientuserid, starttime, endtime, clientip, focus) + if (not starttime or starttime=="") and (not endtime or endtime=="") and config.auditstart~="" and config.auditend~="" then + starttime = config.auditstart + endtime = config.auditend + end + local result = handleparameters(clientuserid, starttime, endtime, clientip, focus) + result.log = cfe({ type="list", value={}, label="Weblog Block Log" }) + local res, err = pcall(function() + databaseconnect(DatabaseUser) + result.log.value = listpubblocklogentries(clientuserid, starttime, endtime, clientip) or {} + databasedisconnect() + end) + return cfe({ type="group", value=result, errtxt=err, label="Weblog Block Log" }) +end + +function getusagestats() + 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 + +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.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 = grouppubblocklogentries(config.auditstart, config.auditend) 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 = {} + result.auditstart = cfe({ value=config.auditstart or "", label="Audit Start Time" }) + result.auditend = cfe({ value=config.auditend or "", label="Audit End Time" }) + result.window = cfe({ value=config.window or "5", label="Time Window", descr="Minutes of activity to display before and after selected block" }) + result.watchdays = cfe({ value=config.watchdays or "14", label="Days to Watch", descr="Number of additional days to keep history for users in watchlist" }) + result.purgedays = cfe({ value=config.purgedays or "30", label="Days before Purge", descr="Days to keep history, regardless of audit" }) + result.historydays = cfe({ value=config.historydays or "14", label="Days to keep History", descr="Days beyond Audit Start Time to keep complete log history" }) + return cfe({ type="group", value=result, label="Weblog Config" }) +end + +function updateconfig(newconfig) + local success = true + -- Validating a timestamp is going to be tricky, how about using postgres? + if newconfig.value.window.value == "" then + newconfig.value.window.errtxt = "Cannot be blank" + success = false + elseif not validator.is_integer(newconfig.value.window.value) then + newconfig.value.window.errtxt = "Must be a number" + success = false + end + if not validator.is_integer(newconfig.value.watchdays.value) then + newconfig.value.watchdays.errtxt = "Must be a number" + success = false + end + if not validator.is_integer(newconfig.value.purgedays.value) then + newconfig.value.purgedays.errtxt = "Must be a number" + success = false + end + if not validator.is_integer(newconfig.value.historydays.value) then + newconfig.value.historydays.errtxt = "Must be a number" + success = false + end + if success then + configcontent = format.update_ini_file(configcontent, "", "auditstart", newconfig.value.auditstart.value) + configcontent = format.update_ini_file(configcontent, "", "auditend", newconfig.value.auditend.value) + configcontent = format.update_ini_file(configcontent, "", "window", newconfig.value.window.value) + configcontent = format.update_ini_file(configcontent, "", "watchdays", newconfig.value.watchdays.value) + configcontent = format.update_ini_file(configcontent, "", "purgedays", newconfig.value.purgedays.value) + configcontent = format.update_ini_file(configcontent, "", "historydays", newconfig.value.historydays.value) + + fs.write_file(configfile, configcontent) + config = format.parse_ini_file(configcontent, "") or {} + else + newconfig.errtxt = "Failed to update config" + end + + return newconfig +end + +function getnewadhocquery() + local query = {} + query.query = cfe({ label="Query select statement" }) + return cfe({ type="group", value=query, label="Ad-hoc Query" }) +end + +function adhocquery(query) + local success = true + query.value.query.value = query.value.query.value:lower() + if query.value.query.value == "" then + query.value.query.errtxt = "Empty select statement" + success = false + elseif not string.find(query.value.query.value, "^%s*select%s") then + query.value.query.errtxt = "Must be a select statement" + success = false + end + + if success then + local cur + local res, err = pcall(function() + databaseconnect(DatabaseUser) + cur = assert (con:execute(query.value.query.value)) + databasedisconnect() + end) + if not res or not cur then + query.value.query.errtxt = err or "Select failed" + query.errtxt = "Query failed" + else + query.value.result = cfe({ type="list", value={}, label="Select result" }) + local result = query.value.result.value + local row = cur:fetch ({}, "a") + while row do + result[#result+1] = {} + for name,val in pairs(row) do + result[#result][name] = val + row = cur:fetch (row, "a") + end + end + -- close everything + cur:close() + end + else + query.errtxt = "Query failed" + end + + return query +end + +function testdatabase() + local retval = cfe({ type="boolean", value=false, label="Database present" }) + local dbs = listdatabases() + for i,db in ipairs(dbs) do + if db == DatabaseName then + retval.value = true + break + end + end + return retval +end + +function getnewdatabase() + local database = {} + local errtxt + database.password = cfe({ label="Password" }) + database.password_confirm = cfe({ label="Password (confirm)" }) + local test = testdatabase() + if test.value then + errtxt = "Database already exists!" + success = false + end + return cfe({ type="group", value=database, label="Create Database", errtxt=errtxt }) +end + +function create_database(database) + local success = true + local errtxt + + if database.value.password.value == "" or string.match(database.value.password.value, "'%s") then + database.value.password.errtxt = "Invalid password" + success = false + end + if database.value.password.value ~= database.value.password_confirm.value then + database.value.password_confirm.errtxt = "Password does not match" + success = false + end + local test = testdatabase() + if test.value then + errtxt = "Database already exists!" + success = false + end + + if success then + errtxt = createdatabase(database.value.password.value) + test = testdatabase() + if not test.value then + success = false + else + local res, err = pcall(function() + databaseconnect(DatabaseOwner, database.value.password.value) + for i,scr in ipairs(database_creation_script) do + assert (con:execute(scr)) + end + databasedisconnect() + -- put the password in the config file for future use + configcontent = format.update_ini_file(configcontent, "", "password", database.value.password.value) + fs.write_file(configfile, configcontent) + config = format.parse_ini_file(configcontent, "") or {} + end) + if not res then + errtxt = err + success = false + end + end + if not success then + deletedatabase() + end + end + + if not success then + database.errtxt = "Failed to create database" + if errtxt then + database.errtxt = database.errtxt.."\n"..errtxt + end + end + + return database +end |