filter alarm table

[Frank68]

Run this script to log alerts for the current month and post the result:

Code:

123456789101112131415161718192021222324252627

date = os.date('*t') date.month = date.month + 1 date.day, date.hour, date.min, date.sec = 1, 0, 0, 0 ts_end = os.time(date) date.month = date.month - 1 ts_start = os.time(date) alerts_table = db:getall('SELECT * FROM alerts where alerttime BETWEEN ? AND ? ORDER BY id DESC', ts_start, ts_end) -- csv buffer buffer = {} -- format csv row csv = string.format('%q,%q,%q', "ID", "Messaggio", "Data e Ora") -- add to buffer table.insert(buffer, csv) -- add empty line to buffer table.insert(buffer, "") -- Loop through alerts_table for _, alerts in ipairs(alerts_table) do   -- format csv row   csv = string.format('%q,%q,%q', alerts.id, alerts.alert, os.date("%d.%m.%Y %X", alerts.alerttime))   -- add to buffer   table.insert(buffer, csv) end res = table.concat(buffer, '\n') log(res)

this is the result

[admin]

This means that the export from DB to CSV part is working correctly. There must be something else wrong with your script if data is not populated.

[Frank68]

This means that the export from DB to CSV part is working correctly. There must be something else wrong with your script if data is not populated.

But if I use

alerts_table = db:getall('SELECT * FROM alerts ORDER BY id DESC')

the script working fine.



filtering is probably the problem

BR

[admin]

Post the latest version of your script that should export current month data instead of previous

[Frank68]

Post the latest version of your script that should export current month data instead of previous

This i scomplete scritp for currento mounth

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116

--My (smtps) username !IMPORTANT! user = '...' --My (smtps) password !IMPORTANT! password = '...' --Sender for e-mail from = '<' .. user .. '>' alias_from = 'Do not Reply ' --Recipient for e-mail to = '<claudio@ciemme.com>' alias_to = to --Subject for e-mail subjectpart1 = 'Lista Allarmi Mese' subjectpart2 = 'E-Mail inviata in automatico dal WebServer' --Message on bottom of email (will only be showed when client don't understand attachment) epilogue = 'End of message' date = os.date('*t') -- recupera data e ora dalla tabella del db date.day, date.hour, date.min, date.sec = 1, 0, 0, 0 ts_end = os.time(date) date.month = date.month + 1 ts_start = os.time(date) alerts_table = db:getall('SELECT * FROM alerts where alerttime BETWEEN ? AND ? ORDER BY id DESC', ts_start, ts_end) --alerts_table = db:getall('SELECT * FROM alerts ORDER BY id DESC') --------------------------------------------------------------------------------- log('Dal - ' .. os.date("%F",ts_start) .. ' Al - ' .. os.date("%F",ts_end)) --------------------------------------------------------------------------------- -- csv buffer buffer = {} -- format csv row csv = string.format('%q,%q,%q', "ID", "Messaggio", "Data e Ora") -- add to buffer table.insert(buffer, csv) -- add empty line to buffer table.insert(buffer, "") -- Loop through alerts_table for _, alerts in ipairs(alerts_table) do   -- format csv row   csv = string.format('%q,%q,%q', alerts.id, alerts.alert, os.date("%d.%m.%Y %X", alerts.alerttime))   -- add to buffer   table.insert(buffer, csv) end    --Create table to include mail settings   local settings = {       from = from,       rcpt = to,       user = user,       password = password,       server = 'smtps.aruba.it',       port = 465,       secure = 'sslv23',   }   --Create attachment inside FTP server   src = os.date('%Y-%m') .. '.csv'   dst = '/home/ftp/' .. src   io.writefile(dst, buffer)   --Create subject   subject = subjectpart1 .. ": " .. src .. " " .. subjectpart2   --Load required modules to send email with attachment   local smtp = require("socket.smtp")   local mime = require("mime")   local ltn12 = require("ltn12")   --Create e-mail header   settings.source = smtp.message{   headers = {             from = '' .. alias_from .. ' ' .. from .. '',             to = '' .. alias_to .. ' ' .. to .. '',             subject = subject   },   --Load attachment inside body        body = {   preamble = "",    [1] = {            headers = {              ["content-type"] = 'text/plain',              ["content-disposition"] = 'attachment; filename="'..src..'"',              ["content-description"] = '.. src ..',              ["content-transfer-encoding"] = "BASE64",           },           body = ltn12.source.chain(             ltn12.source.file(io.open(dst, "rb")),             ltn12.filter.chain(             mime.encode("base64"),             mime.wrap()           )         )       },         epilogue = epilogue     }   }   --Send the email   r, e = smtp.send(settings)   --Create alert when sending gives an error with error message   if (e) then     log (e)     log (r)     alert("Could not send email: ", e, "\n")   end --Delete created file from ftp folder inside HL os.remove(dst)

BR

[admin]

You've made the change incorrectly, now filter starting date is after the ending date. This should produce correct filter period:

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116

--My (smtps) username !IMPORTANT! user = '...' --My (smtps) password !IMPORTANT! password = '...' --Sender for e-mail from = '<' .. user .. '>' alias_from = 'Do not Reply ' --Recipient for e-mail to = '<claudio@ciemme.com>' alias_to = to --Subject for e-mail subjectpart1 = 'Lista Allarmi Mese' subjectpart2 = 'E-Mail inviata in automatico dal WebServer' --Message on bottom of email (will only be showed when client don't understand attachment) epilogue = 'End of message' date = os.date('*t') -- recupera data e ora dalla tabella del db date.day, date.hour, date.min, date.sec = 1, 0, 0, 0 ts_start = os.time(date) date.month = date.month + 1 ts_end = os.time(date) alerts_table = db:getall('SELECT * FROM alerts where alerttime BETWEEN ? AND ? ORDER BY id DESC', ts_start, ts_end) --alerts_table = db:getall('SELECT * FROM alerts ORDER BY id DESC') --------------------------------------------------------------------------------- log('Dal - ' .. os.date("%F",ts_start) .. ' Al - ' .. os.date("%F",ts_end)) --------------------------------------------------------------------------------- -- csv buffer buffer = {} -- format csv row csv = string.format('%q,%q,%q', "ID", "Messaggio", "Data e Ora") -- add to buffer table.insert(buffer, csv) -- add empty line to buffer table.insert(buffer, "") -- Loop through alerts_table for _, alerts in ipairs(alerts_table) do   -- format csv row   csv = string.format('%q,%q,%q', alerts.id, alerts.alert, os.date("%d.%m.%Y %X", alerts.alerttime))   -- add to buffer   table.insert(buffer, csv) end    --Create table to include mail settings   local settings = {       from = from,       rcpt = to,       user = user,       password = password,       server = 'smtps.aruba.it',       port = 465,       secure = 'sslv23',   }   --Create attachment inside FTP server   src = os.date('%Y-%m') .. '.csv'   dst = '/home/ftp/' .. src   io.writefile(dst, buffer)   --Create subject   subject = subjectpart1 .. ": " .. src .. " " .. subjectpart2   --Load required modules to send email with attachment   local smtp = require("socket.smtp")   local mime = require("mime")   local ltn12 = require("ltn12")   --Create e-mail header   settings.source = smtp.message{   headers = {             from = '' .. alias_from .. ' ' .. from .. '',             to = '' .. alias_to .. ' ' .. to .. '',             subject = subject   },   --Load attachment inside body       body = {   preamble = "",    [1] = {             headers = {              ["content-type"] = 'text/plain',              ["content-disposition"] = 'attachment; filename="'..src..'"',              ["content-description"] = '.. src ..',              ["content-transfer-encoding"] = "BASE64",           },           body = ltn12.source.chain(             ltn12.source.file(io.open(dst, "rb")),             ltn12.filter.chain(             mime.encode("base64"),             mime.wrap()           )         )       },         epilogue = epilogue     }   }   --Send the email   r, e = smtp.send(settings)   --Create alert when sending gives an error with error message   if (e) then     log (e)     log (r)     alert("Could not send email: ", e, "\n")   end --Delete created file from ftp folder inside HL os.remove(dst)

[Frank68]

You've made the change incorrectly, now filter starting date is after the ending date. This should produce correct filter period:

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116

--My (smtps) username !IMPORTANT! user = '...' --My (smtps) password !IMPORTANT! password = '...' --Sender for e-mail from = '<' .. user .. '>' alias_from = 'Do not Reply ' --Recipient for e-mail to = '<claudio@ciemme.com>' alias_to = to --Subject for e-mail subjectpart1 = 'Lista Allarmi Mese' subjectpart2 = 'E-Mail inviata in automatico dal WebServer' --Message on bottom of email (will only be showed when client don't understand attachment) epilogue = 'End of message' date = os.date('*t') -- recupera data e ora dalla tabella del db date.day, date.hour, date.min, date.sec = 1, 0, 0, 0 ts_start = os.time(date) date.month = date.month + 1 ts_end = os.time(date) alerts_table = db:getall('SELECT * FROM alerts where alerttime BETWEEN ? AND ? ORDER BY id DESC', ts_start, ts_end) --alerts_table = db:getall('SELECT * FROM alerts ORDER BY id DESC') --------------------------------------------------------------------------------- log('Dal - ' .. os.date("%F",ts_start) .. ' Al - ' .. os.date("%F",ts_end)) --------------------------------------------------------------------------------- -- csv buffer buffer = {} -- format csv row csv = string.format('%q,%q,%q', "ID", "Messaggio", "Data e Ora") -- add to buffer table.insert(buffer, csv) -- add empty line to buffer table.insert(buffer, "") -- Loop through alerts_table for _, alerts in ipairs(alerts_table) do   -- format csv row   csv = string.format('%q,%q,%q', alerts.id, alerts.alert, os.date("%d.%m.%Y %X", alerts.alerttime))   -- add to buffer   table.insert(buffer, csv) end    --Create table to include mail settings   local settings = {       from = from,       rcpt = to,       user = user,       password = password,       server = 'smtps.aruba.it',       port = 465,       secure = 'sslv23',   }   --Create attachment inside FTP server   src = os.date('%Y-%m') .. '.csv'   dst = '/home/ftp/' .. src   io.writefile(dst, buffer)   --Create subject   subject = subjectpart1 .. ": " .. src .. " " .. subjectpart2   --Load required modules to send email with attachment   local smtp = require("socket.smtp")   local mime = require("mime")   local ltn12 = require("ltn12")   --Create e-mail header   settings.source = smtp.message{   headers = {             from = '' .. alias_from .. ' ' .. from .. '',             to = '' .. alias_to .. ' ' .. to .. '',             subject = subject   },   --Load attachment inside body        body = {   preamble = "",    [1] = {            headers = {              ["content-type"] = 'text/plain',              ["content-disposition"] = 'attachment; filename="'..src..'"',              ["content-description"] = '.. src ..',              ["content-transfer-encoding"] = "BASE64",           },           body = ltn12.source.chain(             ltn12.source.file(io.open(dst, "rb")),             ltn12.filter.chain(             mime.encode("base64"),             mime.wrap()           )         )       },         epilogue = epilogue     }   }   --Send the email   r, e = smtp.send(settings)   --Create alert when sending gives an error with error message   if (e) then     log (e)     log (r)     alert("Could not send email: ", e, "\n")   end --Delete created file from ftp folder inside HL os.remove(dst)

perfect thank you very much