filter alarm table

[Frank68]

Hello

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.

Code:

--My (smtps) username !IMPORTANT!
user = 'report@test.com'
--My (smtps) password !IMPORTANT!
password = 'xxxxxx'

--Sender for e-mail
from = '<' .. user .. '>'
alias_from = 'Do not Reply '

--Recipient for e-mail
to = '<xxxx@xxxx.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'

-- vedere come filtrare mese se 1 prendere 12 e anno -1
----------------------------------------------------------------
if (os.date('%m')==1) then
    mp=12
    yr=os.date('%Y')-1
else 
    mp=os.date('%m')-1
    yr=os.date('%Y')
end
------------------------------------------------------------------

--log('MESE ' ..mp .. ' ANNO ' ..yr)

-- Get all alerts from DB
alerts_table = db:getall('SELECT * FROM alerts where alerttime like %'..mp..'.'..yr..'% ORDER BY id DESC')
-- 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("%x %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)

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)

grazie

[admin]

Date/time is stored as UNIX timestamp. Check for month number wrap is not needed, it is handled by the os.time() automatically.

Code:

date = os.date('*t')
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)

Keep in mind that alerts are deleted automatically, make sure that you've set the limit correctly in Utilities > General configuration.

[Frank68]

Date/time is stored as UNIX timestamp. Check for month number wrap is not needed, it is handled by the os.time() automatically.

Code:

date = os.date('*t')
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)

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

Thank's

[admin]

Replace os.date("%x %X" with os.date("%F %X"

[Frank68]

Replace os.date("%x %X" with os.date("%F %X"

I not have this entry in code

[admin]

   

[Frank68]

Hi

not work

Is possible to log  the return valu eof query fir control , or count number of row return ?

BR

[admin]

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.

[Frank68]

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.

This is my complete script

Code:

--My (smtps) username !IMPORTANT!
user = 'xxxx@xxx.com'
--My (smtps) password !IMPORTANT!
password = 'xxxx.'

--Sender for e-mail
from = '<' .. user .. '>'
alias_from = 'Do not Reply '

--Recipient for e-mail
to = '<xxx@xxx.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')
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)
---------------------------------------------------------------------------------
log('Dal - ' .. os.date("%x",ts_start) .. ' Al - ' .. os.date("%x",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, "")

i=0

-- Loop through alerts_table
for _, alerts in ipairs(alerts_table) do
  -- format csv row
  log(tostring(i+1))
  csv = string.format('%q,%q,%q', alerts.id, alerts.alert, os.date("%F %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]

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.

[Frank68]

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

[admin]

If you want this exact data time format then use os.date('%d.%m.%Y %X', alerts.alerttime)

[Frank68]

If you want this exact data time format then use os.date('%d.%m.%Y %X', alerts.alerttime)

in the query sintax ?

[admin]

Part of line 45. Don't change the query, it's already correct.

[Frank68]

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

Thank you

[admin]

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:

data = db:getall('SELECT COUNT(1) total, MIN(alerttime) tmin, MAX(alerttime) tmax FROM alerts')
log(data)

[Frank68]

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:

data = db:getall('SELECT COUNT(1) total, MIN(alerttime) tmin, MAX(alerttime) tmax FROM alerts')
log(data)

this is log

[admin]

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.

[Frank68]

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.

also use month+1 export nothing

[admin]

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)