module(..., package.seeall) -- Load libraries require("modelfunctions") require("fs") require("format") require("validator") require("luasql.postgres") require("date") local DatabaseName = "weblog" 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/weblog.conf" local configcontent = fs.read_file(configfile) or "" local config = format.parse_ini_file(configcontent, "") or {} local goodwordslist = "/etc/weblog/goodwords" local badwordslist = "/etc/weblog/badwords" local baseurl = "/etc/weblog/" local database_creation_script = { "CREATE TABLE dbhistlog (logdatetime timestamp(3) without time zone NOT NULL, msgtext text)", "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)", "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 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)", "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)", "GRANT SELECT ON dbhistlog TO "..DatabaseUser, "GRANT SELECT ON pubweblog TO "..DatabaseUser, "GRANT SELECT ON pubweblog_history TO "..DatabaseUser, "GRANT SELECT, UPDATE, INSERT, DELETE ON source TO "..DatabaseUser, "GRANT SELECT ON usagestat TO "..DatabaseUser, "GRANT SELECT, UPDATE, INSERT, DELETE ON watchlist TO "..DatabaseUser, } -- ################################################################################ -- 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 and truncate to length local escape = function(sql, length) sql = sql or "" if length then sql = string.sub(sql, 1, length) end sql = string.gsub(sql, "'", "''") 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 weblog > 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 ('now', '%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 ORDER BY logdatetime") 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(entry, sourcename) if entry then local sql = string.format("INSERT INTO pubweblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')", escape(sourcename), escape(entry.clientip), escape(entry.clientuserid, 64):lower(), escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score), escape(entry.shortreason), escape(entry.badyesno), escape(entry.deniedyesno), escape(entry.bypassyesno), escape(entry.wordloc), escape(entry.goodwordloc)) local res = assert (con:execute(sql)) end end local importsquarklog = function(entry, sourcename) if entry then local sql = string.format("INSERT INTO pubweblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')", escape(sourcename), escape(entry.clientip), escape(entry.clientuserid, 64):lower(), escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score), escape(entry.shortreason), escape(entry.badyesno), escape(entry.deniedyesno), escape(entry.bypassyesno), escape(entry.wordloc), escape(entry.gwordloc)) local res = assert (con:execute(sql)) end end local importdglog = function(entry, sourcename) if entry then local sql = string.format("INSERT INTO pubweblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')", escape(sourcename), escape(entry.clientip), escape(entry.clientuserid, 64):lower(), escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score), escape(entry.shortreason), escape(entry.badyesno), escape(entry.deniedyesno), escape(entry.bypassyesno), escape(entry.wordloc), escape(entry.gwordloc)) local res = assert (con:execute(sql)) end end local importdumplog = function(entry, sourcename) if entry then local sql = string.format("INSERT INTO pubweblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s')", escape(sourcename), escape(entry.clientip), escape(entry.clientuserid, 64):lower(), escape(entry.logdatetime), escape(entry.URL), escape(entry.bytes), escape(entry.reason), escape(entry.score), escape(entry.shortreason), escape(entry.badyesno), escape(entry.deniedyesno), escape(entry.bypassyesno), escape(entry.wordloc), escape(entry.gwordloc)) 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 -- 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 pubweblog where bypassyesno > '0' group by clientuserid" local res1 = assert (con:execute(sql)) sql = "delete from watchlist where exists " .. "(select * from watchlist w where w.clientuserid = watchlist.clientuserid " .. "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 pubweblog.sourcename, " .. "date_trunc('hour', pubweblog.logdatetime) as date, " .. "count(*), SUM(deniedyesno) from pubweblog group by sourcename,date" local res = assert (con:execute(sql)) 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 in pubweblog beyond purgedate.") -- Move flagged records to histoy and then purge anything older than purgedate sql = "Insert into pubweblog_history select * from pubweblog where logdatetime < '" .. temp .."' and (badyesno > 0 or deniedyesno > 0 or bypassyesno > 0 or selected = 'true')" res = assert (con:execute(sql)) logme("Moved " .. res .. " old records to history") sql = "Delete from pubweblog where logdatetime < '" .. temp .."'" res = assert (con:execute(sql)) logme("Deleted " .. res .. " old records to from pubweblog") -- 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") -- 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, badyesno, deniedyesno, bypassyesno, score, urisearch, selected) local sql = "" local where = {} if clientuserid and clientuserid ~= "" then where[#where+1] = "clientuserid LIKE '%"..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 badyesno and badyesno ~= "" then where[#where+1] = "badyesno = '"..escape(badyesno).."'" end if deniedyesno and deniedyesno ~= "" then where[#where+1] = "deniedyesno = '"..escape(deniedyesno).."'" end if bypassyesno and bypassyesno ~= "" then where[#where+1] = "bypassyesno = '"..escape(bypassyesno).."'" end if score and score ~= "" then where[#where+1] = "score >= '"..escape(score).."'" end if urisearch and urisearch ~= "" then where[#where+1] = "lower(uri) LIKE '%"..escape(urisearch).."%'" end if selected and selected == "true" then where[#where+1] = "selected = 'true'" end if #where > 0 then sql = " WHERE " .. table.concat(where, " AND ") end return sql end local listlogentries = function(activelog, clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, sortby, selected) local entries = {} -- retrieve a cursor local sql = "SELECT * FROM "..activelog sql = sql .. generatewhereclause(clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, selected) sql = sql .. " ORDER BY "..sortby cur = assert (con:execute(sql)) row = cur:fetch ({}, "a") while row do if config.shorturi == "true" then shorturi=string.gsub(row.uri, "[;?].*", "...") end 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 } if (config.shortreason ~= "true") then entries[#entries].shortreason = nil end row = cur:fetch (row, "a") end -- close everything cur:close() return entries end local listpubweblogentries = function(...) return listlogentries(...) 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 date, sourcename" 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 testdatabaseentry = function(datatype, value) local success = true local errtxt local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." DEFAULT '"..escape(value).."' ) ON COMMIT DROP" 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 local convertdatabaseentry = function(datatype, value) local success = true local errtxt local result = value local res, err = pcall(function() local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." )" assert (con:execute(sql)) sql = "INSERT INTO testing VALUES ('"..value.."')" 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 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 function parsesquidlog(line) -- 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 goodwordloc="" local badwordloc="" -- logme("value of word7 is "..words[7]) local wrdcnt=0 local isbad=0 local isdenied=0 local isbypass=0 local ignoreme=false --check for ignored records first for thisline in io.lines("/etc/weblog/ignorewords") do if not thisline then break end _,instcnt = string.lower(words[7]):gsub(thisline, " ") if instcnt ~= 0 then ignoreme = true break end end if ignoreme ~= true then --proceed with record analysis for thisline in io.lines("/etc/weblog/badwords") do if not thisline then break end _,instcnt = string.lower(words[7]):gsub(thisline, " ") --if string.find(words[7],thisline) ~= nil then if instcnt ~= 0 then -- logme("instcnt = "..instcnt) isbad=1 wrdcnt= wrdcnt + instcnt if badwordloc ~= "" then badwordloc = badwordloc.."|"..thisline else badwordloc=thisline end end if string.find(words[7],"*DENIED*") then logme("*Denied*") isdenied=1 elseif string.find(words[7],"GBYPASS") then logme("GBYPASS") isbypass=1 elseif string.find(words[7],"*OVERRIDE*") then logme("*OVERRIDE*") isbypass=1 end end for goodline in io.lines("/etc/weblog/goodwords") do if not goodline then break end _,instcnt = string.lower(words[7]):gsub(goodline, " ") --if string.find(words[7],goodline) then if instcnt ~= 0 then if wrdcnt ~= 0 then wrdcnt = wrdcnt - instcnt if goodwordloc ~= "" then goodwordloc = goodwordloc.."|"..goodline else goodwordloc = goodline end end end end end -- Reset bad to reduce number of bad hits if score is zero -- if wrdcnt == 0 then -- isbad=0 -- end local logentry = {logdatetime=words[1], elapsed=words[2], clientip=words[3], code=string.match(words[4] or "", "^[^/]*"), status=string.match(words[4] or "", "[^/]*$"), bytes=words[5], method=words[6], URL=words[7], clientuserid=words[8], peerstatus=string.match(words[9] or "", "^[^/]*"), peerhost=string.match(words[9] or "", "[^/]*$"), score=wrdcnt, badyesno=isbad, deniedyesno=isdenied, bypassyesno=isbypass, wordloc=badwordloc, gwordloc=goodwordloc} -- 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 if logentry.clientuserid and logentry.clientuserid ~= "-" then logentry.logdatetime = os.date("%Y-%m-%d %H:%M:%S", logentry.logdatetime)..string.match(logentry.logdatetime, "%..*") return logentry end end return nil end local function parsesquarklog(line) -- 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 goodwordloc="" local badwordloc="" local wrdcnt=0 local isbad=0 local isdenied=0 local isbypass=0 local ignoreme=false --check for ignored records first for thisline in io.lines("/etc/weblog/ignorewords") do if not thisline then break end _,instcnt = string.lower(words[5]):gsub(thisline, " ") if instcnt ~= 0 then ignoreme = true break end end if ignoreme ~= true then for thisline in io.lines(baseurl.."badwords") do if not thisline then -- logme("This line is apparently empty...") break end _,instcnt = string.lower(words[5]):gsub(thisline, " ") -- if string.find(words[5],thisline) ~= nil then --logme("checking "..thisline.." against "..words[5]) if instcnt ~= 0 then isbad=1 wrdcnt = wrdcnt + instcnt if badwordloc ~= "" then badwordloc = badwordloc.."|"..thisline else badwordloc=thisline end -- logme("bad "..badwordloc) end if string.find(words[6],"*DENIED*") then isdenied=1 end if string.find(words[6],"*OVERRIDE*") then isbypass=1 end end for goodline in io.lines(baseurl.."goodwords") do if not goodline then -- logme("This line is apparently empty...") break end _,instcnt = string.lower(words[5]):gsub(goodline, " ") --if string.find(words[4],goodline) then if instcnt ~= 0 then if wrdcnt ~= 0 then wrdcnt = wrdcnt - instcnt if goodwordloc ~= "" then goodwordloc = goodwordloc.."|"..goodline else goodwordloc = goodline end end end end end local words = format.string_to_table(line, "\t") local logentry = {logdatetime=words[1], clientuserid=words[2], clientip=words[3], URL=words[4], reason=words[5], method=words[6], bytes=words[7], shortreason=words[9], score=wrdcnt, badyesno=isbad, deniedyesno=isdenied, bypassyesno=isbypass, wordloc=badwordloc, gwordloc=goodwordloc} if logentry.reason and logentry.reason ~= "" then if logentry.shortreason == "" then logentry.shortreason = logentry.reason end return logentry end return nil end local function parsedglog(line) -- 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 goodwordloc="" local badwordloc="" -- logme("value of word4 is "..words[4]) local wrdcnt=0 local isbad=0 local isdenied=0 local isbypass=0 local ignoreme=false --check for ignored records first for thisline in io.lines("/etc/weblog/ignorewords") do if not thisline then break end _,instcnt = string.lower(words[4]):gsub(thisline, " ") if instcnt ~= 0 then ignoreme = true break end end if ignoreme ~= true then for thisline in io.lines("/etc/weblog/badwords") do if not thisline then -- logme("This line is apparently empty...") break end _,instcnt = string.lower(words[4]):gsub(thisline, " ") --if string.find(words[4],thisline) ~= nil then if instcnt ~= 0 then if wrdcnt ~= 0 then isbad=1 wrdcnt= wrdcnt + instcnt if badwordloc ~= "" then badwordloc = badwordloc.."|"..thisline else badwordloc=thisline end -- logme("bad "..badwordloc) end if string.find(words[5],"*DENIED*") then isdenied=1 elseif string.find(words[5],"GBYPASS") then isdenied=1 elseif string.find(words[5],"*OVERRIDE*") then isbypass=1 end end for goodline in io.lines("/etc/weblog/goodwords") do if not goodline then -- logme("This line is apparently empty...") break end _,instcnt = string.lower(words[4]):gsub(goodline, " ") --if string.find(words[4],goodline) then if instcnt ~= 0 then wrdcnt = wrdcnt - instcnt if goodwordloc ~= "" then goodwordloc = goodwordloc.."|"..goodline else goodwordloc = goodline end end end end end local words = format.string_to_table(line, "\t") local logentry = {logdatetime=words[1], clientuserid=words[2], clientip=words[3], URL=words[4], reason=words[5], method=words[6], bytes=words[7], shortreason=words[9], score=wrdcnt, badyesno=isbad, deniedyesno=isdenied, bypassyesno=isbypass, wordloc=badwordloc, gwordloc=goodwordloc} if logentry.reason and logentry.reason ~= "" then if logentry.shortreason == "" then logentry.shortreason = logentry.reason end return logentry end return nil end local function parsedumplog(line) -- 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 goodwordloc="" badwordloc="" wrdcnt=0 isbad=0 isdenied=0 isbypass=0 for thisline in io.lines("/etc/weblog/badwords") do if not thisline then logme("This line is apparently empty...") break end _,instcnt = string.lower(words[5]):gsub(thisline, " ") if instcnt ~= 0 then isbad=1 wrdcnt = wrdcnt + instcnt if badwordloc ~= "" then badwordloc = badwordloc.."|"..thisline else badwordloc=thisline end -- logme("bad "..badwordloc) end if string.find(words[6],"*DENIED*") then isdenied=1 end if string.find(words[5],"GBYPASS") then isbypass=1 elseif string.find(words[6],"*OVERRIDE*") then isbypass=1 end end for goodline in io.lines("/etc/weblog/goodwords") do if not goodline then -- logme("This line is apparently empty...") break end _,instcnt = string.lower(words[4]):gsub(goodline, " ") --if string.find(words[4],goodline) then if instcnt ~= 0 then if wrdcnt ~= 0 then wrdcnt = wrdcnt - instcnt if goodwordloc ~= "" then goodwordloc = goodwordloc.."|"..goodline else goodwordloc = goodline end end end end local words = format.string_to_table(line, "\t") local logentry = {logdatetime=words[1], clientuserid=words[2], clientip=words[3], URL=words[4], reason=words[6], method=words[5], bytes=words[7], shortreason=words[9], score=wrdcnt, badyesno=isbad, deniedyesno=isdenied, bypassyesno=isbypass, wordloc=badwordloc, gwordloc=goodwordloc} if logentry.reason and logentry.reason ~= "" then if logentry.shortreason == "" then logentry.shortreason = logentry.reason end return logentry end return nil end --local function parsedglog(line) -- local words = format.string_to_table(line, "\t") -- local logentry = { logdatetime=words[1], clientuserid=words[2], clientip=words[3], -- URL=words[4], reason=words[5], method=words[6], bytes=words[7], -- shortreason=words[9]} -- if logentry.reason and logentry.reason ~= "" then -- if logentry.shortreason == "" then -- logentry.shortreason = logentry.reason -- end -- logentry.score = string.match(logentry.reason, "^.*: ([0-9]+) ") -- logentry.logdatetime = string.gsub(logentry.logdatetime, "%.", "-") -- return logentry -- end -- return nil --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 fs.write_file(cookiesfile, "password="..source.passwd.."&userid="..source.userid.."&Logon=Logon") local cmd = "wget -O - --no-check-certificate --save-cookies "..cookiesfile.." --keep-session-cookies --post-file '"..cookiesfile.."' '"..source.method.."://"..format.escapespecialcharacters(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 elseif source.method == "local" then success = true 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 elseif source.method == "local" then candidates = fs.find_files_as_array(nil, source.source) end return candidates end local openlogfile = function(source, cookiesfile, logfile) local handle 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" if string.find(logfile, "%.gz$") then cmd = cmd.." | gunzip -c" end handle = io.popen(cmd) elseif source.method == "local" then if string.find(logfile, "%.gz$") then local cmd = "gunzip -c "..logfile handle = io.popen(cmd) else handle = io.open(logfile) end end return handle 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() elseif source.method == "local" then os.remove(logfile) 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) 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 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="local", label="Method", option={"http", "https", "local"} }) source.userid = cfe({ label="UserID" }) source.passwd = cfe({ label="Password" }) source.source = cfe({ value="/var/log", label="Source Location / 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 -- remove spaces from sourcename source.sourcename = string.gsub(source.sourcename, "%s+$", "") 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 -- import either squid or dg log file. -- delete logfile after local function importlogfile(source, cookiesfile, file, parselog_func, importlog_func) logme("Getting " .. file ) local loghandle = openlogfile(source, cookiesfile, file) logme("Processing " .. file ) local res, err = pcall(function() con:execute("START TRANSACTION") for line in loghandle:lines() do assert(con:execute("SAVEPOINT before_line")) local res2, err2 = pcall(function() local logentry = parselog_func(line) importlog_func(logentry, source.sourcename) end) if not res2 then if (config.stoponerror == "true") then pcall(function() con:execute("ROLLBACK") end) else assert(con:execute("ROLLBACK TO before_line")) con:execute("COMMIT") end pcall(function() logme("Exception on line:"..line) end) if err2 then pcall(function() logme("err2 "..err2) end) end if (config.stoponerror == "true") then assert(res2, "Import halted on exception") else con:execute("START TRANSACTION") end else assert(con:execute("RELEASE SAVEPOINT before_line")) end end con:execute("COMMIT") end) if not res then pcall(function() con:execute("ROLLBACK") end) if err then pcall(function() logme(err) end) end end loghandle:close() if res then logme("Deleting " .. file ) deletelogfile(source, cookiesfile, file) end return res end function importlogs() local result = cfe({ label="Weblog Import Logs Result" }) local count = 0 local success = true local res, err = pcall(function() databaseconnect(DatabaseOwner, config.password) -- Download, parse, and import the logs logme("Executing importlogs") logme("Analyzing...") local sql = "ANALYZE" res = assert (con:execute(sql)) -- Determine sources local sources = listsourceentries(sourcename) 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 success = importlogfile(source, cookiesfile, file, parsedglog, importdglog) and success end if string.match(file, "squark/access%.log[%.%-]") then count = count + 1 success = importlogfile(source, cookiesfile, file, parsesquarklog, importsquarklog) and success end if string.match(file, "squid/access%.log[%.%-]") then count = count + 1 success = importlogfile(source, cookiesfile, file, parsesquidlog, importsquidlog) and success end if string.match(file, "dump/access%.log[%.%-]") then count = count + 1 success = importlogfile(source, cookiesfile, file, parsedumplog, importdumplog) and success end end end os.remove(cookiesfile) end end -- Process the logs if success then addtowatchlist() updateusagestats() groomwatchlist() end -- Purge old database entries groomusagestat() groomdbhistlog() groompublogs() databasedisconnect() end) if not res or not success then result.errtxt = "Import Logs Failure" if err then pcall(function() logme(err) end) result.errtxt = result.errtxt .. "\n" .. err end pcall(function() databasedisconnect() 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 local function validatewatchlistentry(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 if not watch.value.expiredatetime.errtxt then local res, err = pcall(function() databaseconnect(DatabaseUser) local s s,watch.value.expiredatetime.errtxt = testdatabaseentry("TIMESTAMP", watch.value.expiredatetime.value) databasedisconnect() success = success and s end) if not res and err then watch.value.expiredatetime.errtxt = err success = false end end return success end function createwatchlistentry(watch) watch.value.clientuserid.value = watch.value.clientuserid.value:lower() watch.errtxt = "Failed to create watchlist entry" local success = validatewatchlistentry(watch) if success then 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 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 validateparameters = function(params) local success = true if params.clientip.value ~= "" and string.find(params.clientip.value, "[^%d%.]") then params.clientip.errtxt = "Invalid IP Address" success = false end if params.window.value ~= "" and not validator.is_integer(params.window.value) then params.window.errtxt = "Must be an integer" success = false end local res, err = pcall(function() databaseconnect(DatabaseUser) local s if params.starttime.value ~= "" then s,params.starttime.errtxt,params.starttime.value = convertdatabaseentry("TIMESTAMP", params.starttime.value) success = success and s end if params.endtime.value ~= "" then s,params.endtime.errtxt,params.endtime.value = convertdatabaseentry("TIMESTAMP", params.endtime.value) success = success and s end if params.focus.value ~= "" then s,params.focus.errtxt,params.focus.value = convertdatabaseentry("TIMESTAMP", params.focus.value) success = success and s end databasedisconnect() end) if not res and err then params.starttime.errtxt = err params.endtime.errtxt = err params.focus.errtxt = err success = false end return success end local handleparameters = function(activelog, clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, sortby, selected, focus) local result = {} result.activelog = cfe({ value=activelog or "pubweblog", label="Active Weblog" }) 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.badyesno = cfe({ value=badyesno, label="Show Dodgy Records", descr="Limit search to Dodgy records"}) result.deniedyesno = cfe({ value=deniedyesno, label="Show Denied Records", descr="Limit search to Denied uri"}) result.bypassyesno = cfe({ value=bypassyesno, label="Show Bypass Actions", descr="Limit search to Bypass attempts"}) result.score = cfe({ value=score, label="Minimum Score", descr="Minimum score to search on"}) result.urisearch = cfe({ value=urisearch or "", label="URI Contains", descr="Retrieve records where the URI contains this word"}) result.sortby = cfe({ value=sortby, label="Sort By field", descr="Sort by this field when displaying records"}) result.selected = cfe({ value=selected, label="Show Selected Records", descr="Show only records that have been selected"}) result.window = cfe({ value=config.window or "5", label="Time Window" }) result.focus = cfe({ value=focus or "", label="Focus Time" }) return result end function getselected(csvdata) local result = {} result.id = cfe({ value=csvdata or "", label="Record ID", descr="Id of Record"}) result.log = cfe({ type="list", value={}, label="Weblog Access Log" }) local res, err = pcall(function() databaseconnect(DatabaseUser) local entries = {} -- retrieve a cursor local sql = "SELECT * FROM pubweblog where selected = true and logdatetime >= '" .. starttime .. "' and logdatetime <= '" .. endtime .. "'" local idcnt = 0 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, shorturi=shorturi, bytes=row.bytes, reason=row.reason, core=row.score, shortreason=row.shortreason, badyesno=row.badyesno, deniedyesno=row.deniedyesno, bypassyesno=row.bypassyesno, wordloc=row.wordloc, selected=row.selected } row = cur:fetch (row, "a") end -- close everything cur:close() result.log.value = entries or {} --result.log.value = sql databasedisconnect() end) return cfe({ type="group", value=result, errtxt=err, label="Weblog Selected" }) end function editselected(chkdata) keycnt = 0 sql = "UPDATE pubweblog SET selected = " databaseconnect(DatabaseOwner) idarray = format.string_to_table(chkdata, "|") for key,x in pairs(idarray) do keycnt = keycnt + 1 if keycnt == 1 then sql = sql..x.." WHERE id = " else sql = sql..x end end assert (con:execute(sql)) databasedisconnect() end function clearselected() local retval = cfe({ label="Clear selected records", errtxt = "Failed to clear selected records - who knows why" }) sql = "UPDATE pubweblog SET selected = false WHERE selected = true" databaseconnect(DatabaseOwner) assert (con:execute(sql)) databasedisconnect() retval.value = "Cleared" retval.errtxt = nil return retval end function getweblog(activelog, clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, sortby, selected, focus ) if (not activelog or activelog=="") then activelog = "pubweblog" end if (not starttime or starttime=="") and (not endtime or endtime=="") and config.auditstart~="" and config.auditend~="" then starttime = config.auditstart endtime = config.auditend if config.badyesno=="true" then badyesno = '1' end end if (not score or score=="") and config.minimumscore~="" then score = config.minimumscore end if (not sortby or sortby=="") and config.sortby~="" then sortby = config.sortby end local result = handleparameters(activelog, clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, sortby, selected, focus) result.log = cfe({ type="list", value={}, label="Weblog Access Log" }) local success = validateparameters(result) if success then local res, err = pcall(function() databaseconnect(DatabaseUser) result.log.value = listpubweblogentries(activelog, clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, sortby, selected ) or {} databasedisconnect() end) else err = "Invalid search parameters" end return cfe({ type="group", value=result, errtxt=err, label="Weblog Access 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 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.badyesno = cfe({ type="boolean", value=(config.badyesno == "1"), label="Display Suspect Records", descr="Show only records flagged as suspect on initial display" }) result.minimumscore = cfe({ value=config.minimumscore or "0", label="Minimum Score", descr="Minimum Score to search for" }) result.sortby = cfe({ type="select", value=config.sortby or "logdatetime", label="Sort By field", option={"logdatetime", "logdatetime DESC", "clientuserid", "clientuserid DESC", "clientip", "clientip DESC", "bytes", "bytes DESC", "score", "score DESC", "reason"} }) 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" }) result.shorturi = cfe({ type="boolean", value=(config.shorturi == "true"), label="Truncate URLs", descr="You can limit the length of displayed URLs by enabling this option"}) result.shortreason = cfe({ type="boolean", value=(config.shortreason == "true"), label="Short Reason", descr="Display a short reason (dansguardian only)"}) result.stoponerror = cfe({ type="boolean", value=(config.stoponerror == "true"), label="Stop on Error", descr="Stop import of logs if an error is encountered"}) return cfe({ type="group", value=result, label="Weblog Config" }) end local function validateconfig(newconfig) local success = modelfunctions.validateselect(newconfig.value.sortby) 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 local res, err = pcall(function() databaseconnect(DatabaseUser) local s if newconfig.value.auditstart.value ~= "" then s,newconfig.value.auditstart.errtxt,newconfig.value.auditstart.value = convertdatabaseentry("TIMESTAMP", newconfig.value.auditstart.value) success = success and s end if newconfig.value.auditend.value ~= "" then s,newconfig.value.auditend.errtxt,newconfig.value.auditend.value = convertdatabaseentry("TIMESTAMP", newconfig.value.auditend.value) success = success and s end databasedisconnect() end) if not res and err then newconfig.value.auditstart.errtxt = err newconfig.value.auditend.errtxt = err success = false end return success, newconfig end function updateconfig(newconfig) local success = validateconfig(newconfig) 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) --configcontent = format.update_ini_file(configcontent, "", "groupby", newconfig.value.groupby.value) configcontent = format.update_ini_file(configcontent, "", "shorturi", tostring(newconfig.value.shorturi.value)) configcontent = format.update_ini_file(configcontent, "", "shortreason", tostring(newconfig.value.shortreason.value)) configcontent = format.update_ini_file(configcontent, "", "stoponerror", tostring(newconfig.value.stoponerror.value)) configcontent = format.update_ini_file(configcontent, "", "badyesno", tostring(newconfig.value.badyesno.value)) configcontent = format.update_ini_file(configcontent, "", "minimumscore", tostring(newconfig.value.minimumscore.value)) configcontent = format.update_ini_file(configcontent, "", "sortby", tostring(newconfig.value.sortby.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 end row = cur:fetch (row, "a") 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 function listfiles() local retval = cfe({ type="list", value={}, label="Weblog Files" }) if not fs.is_dir(baseurl) then fs.create_directory(baseurl) end for file in posix.files(baseurl) do file = baseurl..file if fs.is_file(file) then table.insert(retval.value, file) end end table.sort(retval.value) return retval end function getnewfile() local filename = cfe({ label="File Name", descr="Must be in "..baseurl }) return cfe({ type="group", value={filename=filename}, label="Weblog File" }) end function readfile(filename) return modelfunctions.getfiledetails(filename, listfiles().value) end function updatefile(filedetails) return modelfunctions.setfiledetails(filedetails, listfiles().value) end function deletefile(filename) local retval = cfe({ label="Delete Weblog File result", errtxt = "Failed to delete Weblog File - invalid filename" }) for i,file in ipairs(listfiles().value) do if filename == file then retval.value = "Deleted File" retval.errtxt = nil os.remove(filename) break end end return retval end