Automatic export all logs and send by mail

[manos@dynamitec]

Escaping of the "to" address was missing.
Here's a rewritten script with clearer structure. You only need to modify the to/settings/subject at the to top of the script. SSL settings do not matter anymore because newer SSL library automatically negotiates the highest supported SSL/TLS version between server/client.

Code:

to = 'to@example.com'

settings = {
  -- "from" field, only e-mail must be specified here
  from = 'sender@example.com',
  -- smtp username
  user = 'sender@example.com',
  -- smtp password
  password = '12345678',
  -- smtp server
  server = 'smtp.gmail.com',
  -- smtp server port
  port = 465,
  -- enable ssl, required for gmail smtp
  secure = 'sslv23',
}

subject = 'CSV logs'

logtime = os.time() - 60 * 60 -- last hour (3600 seconds)

buffer = {'"date","address","name","value"'}

query = [[
  SELECT o.id, o.datatype, o.name, ol.datahex, ol.logtime, ol.eventtype
  FROM objectlog ol
  JOIN objects o ON ol.address=o.id
  WHERE ol.logtime >= ?
  ORDER BY ol.id DESC
]]

for _, row in ipairs(db:getall(query, logtime)) do
  if row.datatype and row.eventtype == 'write' then
    data = grp.decodevalue(row.datahex, row.datatype)
    logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)

    buffer[ #buffer + 1 ] = string.format('%q,%q,%q,%q',
      logdate, knxlib.decodega(row.id), row.name, tostring(data))
  end
end

csv = table.concat(buffer, '\r\n')

smtp = require('socket.smtp')
mime = require('mime')
ltn12 = require('ltn12')

function escape(v)
  return '<' .. tostring(v) .. '>'
end

to = escape(to)

msgt = {
  headers = {
    to = to,
    ['content-type'] = 'text/csv',
    ['content-disposition'] = 'attachment; filename="logs.csv"',
    ['content-transfer-encoding'] = 'BASE64',
    subject = subject,
  },
  body = ltn12.source.chain(
    ltn12.source.string(csv),
    ltn12.filter.chain(mime.encode('base64'), mime.wrap('base64'))
  )
}

settings.source = smtp.message(msgt)
settings.from = escape(settings.from)
settings.rcpt = { to }

res, err = smtp.send(settings)
log(res, err)

Hello Admin,

I would like to have something similar but instead of sending the logs by mail just export per 24h all logs to the LM ftp. Or if possible if the database of 50k points is full then export to the internal ftp.
The task I need to achieve is a long period of telegrams recording and analyzing in a later phase. the period would be something about two weeks to one month.
Is this possible or not?

Regards Manos