filter alarm table

[Frank68]

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

[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:

--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:

--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:

--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