I should only select alarms from the previous month and then export them to a csv file and send the whole thing via email.
But I thought about the query, I'm using the following code.
--My (smtps) username !IMPORTANT!user = 'report@test.com'--My (smtps) password !IMPORTANT!password = 'xxxxxx'--Sender for e-mailfrom = '<' .. user .. '>'alias_from = 'Do not Reply '--Recipient for e-mailto = '<xxxx@xxxx.com>'alias_to = to--Subject for e-mailsubjectpart1 = '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'-- vedere come filtrare mese se 1 prendere 12 e anno -1----------------------------------------------------------------if (os.date('%m')==1) thenmp=12yr=os.date('%Y')-1elsemp=os.date('%m')-1yr=os.date('%Y')
end--------------------------------------------------------------------log('MESE ' ..mp .. ' ANNO ' ..yr)-- Get all alerts from DBalerts_table = db:getall('SELECT * FROM alerts where alerttime like %'..mp..'.'..yr..'% ORDER BY id DESC')
-- csv bufferbuffer = {}
-- format csv rowcsv = string.format('%q,%q,%q', "ID", "Messaggio", "Data e Ora")
-- add to buffertable.insert(buffer, csv)
-- add empty line to buffertable.insert(buffer, "")
-- Loop through alerts_tablefor_, alertsinipairs(alerts_table) do-- format csv rowcsv = string.format('%q,%q,%q', alerts.id, alerts.alert, os.date("%x %X", alerts.alerttime))
-- add to buffertable.insert(buffer, csv)
end--Create table to include mail settingslocalsettings = {
from = from,
rcpt = to,
user = user,
password = password,
server = 'smtps.aruba.it',
port = 465,
secure = 'sslv23',
}
--Create attachment inside FTP serversrc = os.date('%Y-%m') .. '.csv'dst = '/home/ftp/' .. srcio.writefile(dst, buffer)
--Create subjectsubject = subjectpart1 .. ": " .. src .. " " .. subjectpart2--Load required modules to send email with attachmentlocalsmtp = require("socket.smtp")
localmime = require("mime")
localltn12 = require("ltn12")
--Create e-mail headersettings.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 emailr, e = smtp.send(settings)
--Create alert when sending gives an error with error messageif (e) thenlog (e)
log (r)
alert("Could not send email: ", e, "\n")
end--Delete created file from ftp folder inside HLos.remove(dst)
taking into account that if month = 1 I have to take the data of month 12 and previous year
my problem is the select query I don't know how to filter the column data whose name I don't know (alarm date)
Date/time is stored as UNIX timestamp. Check for month number wrap is not needed, it is handled by the os.time() automatically.
Code:
123456
date = os.date('*t')
date.day, date.hour, date.min, date.sec = 1, 0, 0, 0ts_end = os.time(date)
date.month = date.month - 1ts_start = os.time(date)
alerts_table = db:getall('SELECT * FROM alerts where alerttime BETWEEN ? AND ? ORDER BY id DESC', ts_start, ts_end)
Keep in mind that alerts are deleted automatically, make sure that you've set the limit correctly in Utilities > General configuration.
12.02.2021, 12:41 (This post was last modified: 12.02.2021, 13:01 by Frank68.)
(12.02.2021, 12:14)admin Wrote: Date/time is stored as UNIX timestamp. Check for month number wrap is not needed, it is handled by the os.time() automatically.
Code:
123456
date = os.date('*t')
date.day, date.hour, date.min, date.sec = 1, 0, 0, 0ts_end = os.time(date)
date.month = date.month - 1ts_start = os.time(date)
alerts_table = db:getall('SELECT * FROM alerts where alerttime BETWEEN ? AND ? ORDER BY id DESC', ts_start, ts_end)
Keep in mind that alerts are deleted automatically, make sure that you've set the limit correctly in Utilities > General configuration.
i'm trying the code but surely i'm wrong something, the code gives me the following values for the range
From 01/01/21 to - 02/01/21
I think there is a problem with the date format , swap day and month , i have a alarm inside but return nothing to export in csv
Post you full script listing otherwise it's impossible to find what's wrong with it. You can use log(any_variable_name) to log variable contents into Logs tab.
(12.02.2021, 13:45)admin Wrote: Post you full script listing otherwise it's impossible to find what's wrong with it. You can use log(any_variable_name) to log variable contents into Logs tab.
The script looks correct. What do you get in logs? Do you actually have alerts for the previous month in Alerts tab?
You can try getting alerts for the current month by adding date.month = date.month + 1 after date = os.date('*t') (line 21)
Also don't use %x date format because it is MONTH/DAY/YEAR, use %F which is YEAR-MONTH-DAY.
(12.02.2021, 14:00)admin Wrote: The script looks correct. What do you get in logs? Do you actually have alerts for the previous month in Alerts tab?
You can try getting alerts for the current month by adding date.month = date.month + 1 after date = os.date('*t') (line 21)
Also don't use %x date format because it is MONTH/DAY/YEAR, use %F which is YEAR-MONTH-DAY.
tried as recommended changed to% F, the result does not change, could it be an international setting problem?
If I look at the alerts they are merited in the format 12.02.2021 hh:mms
(12.02.2021, 14:25)admin Wrote: Part of line 45. Don't change the query, it's already correct.
done but nothing to do I always get back files with no line just headers.
The problem that with the query does not return anything, if instead I do a select * it returns the alarm, I'm afraid that the problem is the stored date format, how can I see the logicmachine db if possible?
in the previous post it was the format I see on the logicmachine
There's no date format in the database, UNIX timestamp (integer) is used to store date/time. Either the query is incorrect or you don't have alerts matching the requested time-frame.
Run this script once and post what got get in Logs tab:
Code:
12
data = db:getall('SELECT COUNT(1) total, MIN(alerttime) tmin, MAX(alerttime) tmax FROM alerts')
log(data)
(12.02.2021, 14:40)admin Wrote: There's no date format in the database, UNIX timestamp (integer) is used to store date/time. Either the query is incorrect or you don't have alerts matching the requested time-frame.
Run this script once and post what got get in Logs tab:
Code:
12
data = db:getall('SELECT COUNT(1) total, MIN(alerttime) tmin, MAX(alerttime) tmax FROM alerts')
log(data)
(12.02.2021, 15:14)admin Wrote: You only have 3 alerts in the database and the oldest one is from today. This is why you get an empty CSV when exporting previous month data.
s
