diff options
Diffstat (limited to 'weblog-model.lua')
| -rw-r--r-- | weblog-model.lua | 349 |
1 files changed, 281 insertions, 68 deletions
diff --git a/weblog-model.lua b/weblog-model.lua index be90d3d..3ee6601 100644 --- a/weblog-model.lua +++ b/weblog-model.lua @@ -8,7 +8,7 @@ require("validator") require("luasql.postgres") require("date") -local DatabaseName = "webproxylog" +local DatabaseName = "weblog" local DatabaseOwner = "weblogowner" local DatabaseUser = "webloguser" @@ -133,7 +133,7 @@ local runSQLscript = function(filename) end -- Create the database and tables --- pg_dump -U postgres -c webproxylog > makeweblog.postgres +-- pg_dump -U postgres -c weblog > makeweblog.postgres --runSQLscript("/root/work/weblog/makeweblog.postgres") local databaseconnect = function(username, password) @@ -180,10 +180,20 @@ local importsquidlog = function(entry, sourcename) 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')", @@ -192,6 +202,14 @@ local importdglog = function(entry, sourcename) 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 = {} @@ -301,6 +319,21 @@ local groompublogs = function() 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() @@ -475,7 +508,6 @@ local printtableentries = function(tablename) end -- ################################################################################ - -- LOG FILE FUNCTIONS local function parsesquidlog(line) @@ -501,8 +533,7 @@ local function parsesquidlog(line) if not thisline then break end - _,instcnt = string.lower(words[7]):gsub(format.escapespecialcharacters(thisline), " ") - --if string.find(words[7],thisline) ~= nil then + _,instcnt = string.lower(words[7]):gsub(thisline, " ") if instcnt ~= 0 then ignoreme = true break @@ -510,14 +541,16 @@ local function parsesquidlog(line) end if ignoreme ~= true then - --proceed with record analysis, badwords first + --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(format.escapespecialcharacters(thisline), " ") + _,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 @@ -528,25 +561,22 @@ local function parsesquidlog(line) end - if (words[11] and words[11]~= nil and string.find(words[11],"," )) then - --logme("squid says "..words[11]) - prxarray = split(words[11],",") - for r,s in pairs(prxarray) do - if string.find(s,"blocked") then - isdenied=1 - elseif string.find(s,"overridden") then - isbypass=1 - end - 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 - - --and now a good words search in mitigation of severity for goodline in io.lines("/etc/weblog/goodwords") do if not goodline then break end - _,instcnt = string.lower(words[7]):gsub(format.escapespecialcharacters(goodline), " ") + _,instcnt = string.lower(words[7]):gsub(goodline, " ") --if string.find(words[7],goodline) then if instcnt ~= 0 then if wrdcnt ~= 0 then @@ -560,19 +590,18 @@ local function parsesquidlog(line) end end end - - if (r and r~=nil) then - reason=r - else - reason=words[6] - 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=reason, + method=words[6], URL=words[7], clientuserid=words[8], peerstatus=string.match(words[9] or "", "^[^/]*"), @@ -594,6 +623,109 @@ local function parsesquidlog(line) 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 @@ -617,7 +749,7 @@ local function parsedglog(line) if not thisline then break end - _,instcnt = string.lower(words[4]):gsub(format.escapespecialcharacters(thisline), " ") + _,instcnt = string.lower(words[4]):gsub(thisline, " ") if instcnt ~= 0 then ignoreme = true break @@ -700,6 +832,107 @@ local function parsedglog(line) 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 @@ -960,9 +1193,9 @@ end -- import either squid or dg log file. -- delete logfile after local function importlogfile(source, cookiesfile, file, parselog_func, importlog_func) - --logme("Getting " .. file ) + logme("Getting " .. file ) local loghandle = openlogfile(source, cookiesfile, file) - --logme("Processing " .. file ) + logme("Processing " .. file ) local res, err = pcall(function() con:execute("START TRANSACTION") for line in loghandle:lines() do @@ -1035,10 +1268,18 @@ function importlogs() 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) @@ -1267,15 +1508,15 @@ function editselected(chkdata) keycnt = 0 sql = "UPDATE pubweblog SET selected = " databaseconnect(DatabaseOwner) - idarray = split(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 + 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 @@ -1557,7 +1798,6 @@ function create_database(database) 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 @@ -1570,12 +1810,10 @@ function listfiles() 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 @@ -1597,28 +1835,3 @@ function deletefile(filename) return retval end - --- Split a string to an array by delimiter or pattern -function split(str, pat) - if string.find(str, pat) == nil then - return str - end - local t = {} - local fpat = "(.-)" .. pat - local last_end = 1 - local s, e, cap = str:find(fpat, 1) - while s do - if s ~= 1 or cap ~= "" then - table.insert(t,cap) - end - last_end = e+1 - s, e, cap = str:find(fpat, last_end) - end - if last_end <= #str then - cap = str:sub(last_end) - table.insert(t, cap) - end - return t -end - - |