Posts: 325
Threads: 125
Joined: May 2020
Reputation:
0
(12.02.2021, 15:27)admin Wrote: Run this script to log alerts for the current month and post the result:
Code: 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
Posts: 7720
Threads: 42
Joined: Jun 2015
Reputation:
446
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.
Posts: 325
Threads: 125
Joined: May 2020
Reputation:
0
(12.02.2021, 15:41)admin Wrote: 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
Posts: 7720
Threads: 42
Joined: Jun 2015
Reputation:
446
Post the latest version of your script that should export current month data instead of previous
Posts: 325
Threads: 125
Joined: May 2020
Reputation:
0
(12.02.2021, 16:59)admin Wrote: Post the latest version of your script that should export current month data instead of previous
This i scomplete scritp for currento mounth
Code: --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
Posts: 7720
Threads: 42
Joined: Jun 2015
Reputation:
446
You've made the change incorrectly, now filter starting date is after the ending date. This should produce correct filter period:
Code: --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)
Posts: 325
Threads: 125
Joined: May 2020
Reputation:
0
(15.02.2021, 07:49)admin Wrote: You've made the change incorrectly, now filter starting date is after the ending date. This should produce correct filter period:
Code: --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
|