Automatic export all logs and send by mail

[domotiqa]

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)

Hi, thanks for the script,

is there an easy way to separate in different csv file the log object and send them in one email?