This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm that you accept these cookies being set.

filter alarm table
#21
(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

Reply
#22
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.
Reply
#23
(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
Reply
#24
Post the latest version of your script that should export current month data instead of previous
Reply
#25
(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
Reply
#26
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)
Reply
#27
(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
Reply


Forum Jump: