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.

Automatic export all logs and send by mail
#1
Hi, 

I need to constantly monitor about 80 objects and I thought the best way was to automatically export logs every month and send them by email.

The questions are:

is it possible to do it and if so how?

Is there enough memory in LM to hold the 30-day logs of 80 objects or is it better to use an external USB memory? Here, too, how can I do?

Thanks for your kind response


Peppe
Reply
#2
(18.02.2019, 09:46)gdimaria Wrote: Hi, 

I need to constantly monitor about 80 objects and I thought the best way was to automatically export logs every month and send them by email.

The questions are:

is it possible to do it and if so how?

Is there enough memory in LM to hold the 30-day logs of 80 objects or is it better to use an external USB memory? Here, too, how can I do?

Thanks for your kind response


Peppe
Hi, use this example http://openrb.com/example-export-last-ho...-from-lm2/

and file in attach

cant attach file

see code below (it has russian comments)
Code:
-- temp файл


filepath = os.tmpname()
-- получаем время  начала  записи за последний час (3600 секунд)
logtime = os.time() - 60 * 60

-- Список  объектов по id
objects = {}

-- получаем объекты по которым ведется запись действий
query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
for _, object in ipairs(db:getall(query)) do
 objects[ tonumber(object.address) ] = {
   datatype = tonumber(object.datatype),
   name = tostring(object.name or ''),
 }
end

-- csv буфер
buffer = {'"date","address","name","value"'}

-- получаем список действий по объектам
query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
for _, row in ipairs(db:getall(query, logtime)) do
 object = objects[ tonumber(row.address) ]

 -- находим соответствие между объектом и  типом события – запись по групповому адресу
 if object and row.eventtype == 'write' then
   datatype = object.datatype

   -- проверяем что тип данных установлен
   if datatype then
     -- раскодируем данные
     data = knxdatatype.decode(row.datahex, datatype)

     -- удаляем null символы из char/string типов
     if datatype == dt.char or datatype == dt.string then
       data = data:gsub('%z+', '')
     -- конвертируем date в формат DD.MM.YYYY
     elseif datatype == dt.date then
       data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
     -- time в HH:MM:SS
     elseif datatype == dt.time then
       data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
     end
   else
     data = ''
   end

   -- форматируем csv колонки
   logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
   csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))

   -- добавляем в  buffer данные
   table.insert(buffer, csv)
 end
end


-- пишем в файл только если есть данные в буфере buffer
if #buffer > 1 then
 file = io.open(filepath, 'w')
 log(filepath)
 file:write(table.concat(buffer, '\r\n'))
 file:close()
end
log(1, filepath)
to = 'ok@evika.ru'
require('socket.smtp')
require('mime')

require('ltn12')
local escape = function(v)
   return '<' .. tostring(v) .. '>'
 end
    local settings = {
   -- "from" field, only e-mail must be specified here
   from = '@gmail.com',
   -- smtp username
   user = '@gmail.com',
   -- smtp password
   password = '',
   -- smtp server
   server = 'smtp.gmail.com',
   -- smtp server port
   port = 465,
   -- enable ssl, required for gmail smtp
   secure = 'sslv23',
 }

 local smtp = require('socket.smtp')
log(2)
 
-- message headers and body
mesgt = {
     headers = {
        to = email_address,
        ["content-type"] = 'text/html',
        ["content-disposition"] = 'attachment; filename="logs"',
        ["content-description"] ='yourattachment',
        ["content-transfer-encoding"] = "BASE64",
        subject = "subject line"
    },
     body = ltn12.source.chain(
       ltn12.source.file(io.open(filepath, "r")),
       ltn12.filter.chain(
 mime.encode("base64"),
 mime.wrap("base64")
)
       )
 }
settings.source = smtp.message(mesgt)
 -- fixup from field
log(4)    
 settings.from = escape(settings.from)

 settings.rcpt = { to }
    smtp.send(settings)
    
file = io.open (filepath, 'r')
log (io.readfile (filepath))
log(file)



os.remove(filepath)
Reply
#3
(18.02.2019, 10:48)AEK Wrote:
(18.02.2019, 09:46)gdimaria Wrote: Hi, 

I need to constantly monitor about 80 objects and I thought the best way was to automatically export logs every month and send them by email.

The questions are:

is it possible to do it and if so how?

Is there enough memory in LM to hold the 30-day logs of 80 objects or is it better to use an external USB memory? Here, too, how can I do?

Thanks for your kind response


Peppe
Hi, use this example http://openrb.com/example-export-last-ho...-from-lm2/

and file in attach

cant attach file

see code below (it has russian comments)
Code:
-- temp файл


filepath = os.tmpname()
-- получаем время  начала  записи за последний час (3600 секунд)
logtime = os.time() - 60 * 60

-- Список  объектов по id
objects = {}

-- получаем объекты по которым ведется запись действий
query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
for _, object in ipairs(db:getall(query)) do
 objects[ tonumber(object.address) ] = {
   datatype = tonumber(object.datatype),
   name = tostring(object.name or ''),
 }
end

-- csv буфер
buffer = {'"date","address","name","value"'}

-- получаем список действий по объектам
query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
for _, row in ipairs(db:getall(query, logtime)) do
 object = objects[ tonumber(row.address) ]

 -- находим соответствие между объектом и  типом события – запись по групповому адресу
 if object and row.eventtype == 'write' then
   datatype = object.datatype

   -- проверяем что тип данных установлен
   if datatype then
     -- раскодируем данные
     data = knxdatatype.decode(row.datahex, datatype)

     -- удаляем null символы из char/string типов
     if datatype == dt.char or datatype == dt.string then
       data = data:gsub('%z+', '')
     -- конвертируем date в формат DD.MM.YYYY
     elseif datatype == dt.date then
       data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
     -- time в HH:MM:SS
     elseif datatype == dt.time then
       data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
     end
   else
     data = ''
   end

   -- форматируем csv колонки
   logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
   csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))

   -- добавляем в  buffer данные
   table.insert(buffer, csv)
 end
end


-- пишем в файл только если есть данные в буфере buffer
if #buffer > 1 then
 file = io.open(filepath, 'w')
 log(filepath)
 file:write(table.concat(buffer, '\r\n'))
 file:close()
end
log(1, filepath)
to = 'ok@evika.ru'
require('socket.smtp')
require('mime')

require('ltn12')
local escape = function(v)
   return '<' .. tostring(v) .. '>'
 end
    local settings = {
   -- "from" field, only e-mail must be specified here
   from = '@gmail.com',
   -- smtp username
   user = '@gmail.com',
   -- smtp password
   password = '',
   -- smtp server
   server = 'smtp.gmail.com',
   -- smtp server port
   port = 465,
   -- enable ssl, required for gmail smtp
   secure = 'sslv23',
 }

 local smtp = require('socket.smtp')
log(2)
 
-- message headers and body
mesgt = {
     headers = {
        to = email_address,
        ["content-type"] = 'text/html',
        ["content-disposition"] = 'attachment; filename="logs"',
        ["content-description"] ='yourattachment',
        ["content-transfer-encoding"] = "BASE64",
        subject = "subject line"
    },
     body = ltn12.source.chain(
       ltn12.source.file(io.open(filepath, "r")),
       ltn12.filter.chain(
 mime.encode("base64"),
 mime.wrap("base64")
)
       )
 }
settings.source = smtp.message(mesgt)
 -- fixup from field
log(4)    
 settings.from = escape(settings.from)

 settings.rcpt = { to }
    smtp.send(settings)
    
file = io.open (filepath, 'r')
log (io.readfile (filepath))
log(file)



os.remove(filepath)
Thank you so much!
Reply
#4
Hi,

have anyone this code working?

I tried, but it looks like the mail will not sent.

when i open the pop-up logs, i see the record of my history

and the message
Code:
log by mail 17.01.2022 05:22:05
* file (0x01f6b5a0)
what looks good for me.


i also tried to use, just for sure if mail is working
E-mail backup file once a month from LM

when i run this script i got the error
Code:
test mail bakup 17.01.2022 05:24:44
* string: 555 5.5.2 Syntax error. w19sm3726342ejb.140 - gsmtp

i use a gmail account without 2fa and i use it on another server, without problems.

maybe a wrong version of ssl?

(18.02.2019, 10:48)AEK Wrote: see code below (it has russian comments)
Code:
-- temp файл


filepath = os.tmpname()
-- получаем время  начала  записи за последний час (3600 секунд)
logtime = os.time() - 60 * 60

-- Список  объектов по id
objects = {}

-- получаем объекты по которым ведется запись действий
query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
for _, object in ipairs(db:getall(query)) do
 objects[ tonumber(object.address) ] = {
   datatype = tonumber(object.datatype),
   name = tostring(object.name or ''),
 }
end

-- csv буфер
buffer = {'"date","address","name","value"'}

-- получаем список действий по объектам
query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
for _, row in ipairs(db:getall(query, logtime)) do
 object = objects[ tonumber(row.address) ]

 -- находим соответствие между объектом и  типом события – запись по групповому адресу
 if object and row.eventtype == 'write' then
   datatype = object.datatype

   -- проверяем что тип данных установлен
   if datatype then
     -- раскодируем данные
     data = knxdatatype.decode(row.datahex, datatype)

     -- удаляем null символы из char/string типов
     if datatype == dt.char or datatype == dt.string then
       data = data:gsub('%z+', '')
     -- конвертируем date в формат DD.MM.YYYY
     elseif datatype == dt.date then
       data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
     -- time в HH:MM:SS
     elseif datatype == dt.time then
       data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
     end
   else
     data = ''
   end

   -- форматируем csv колонки
   logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
   csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))

   -- добавляем в  buffer данные
   table.insert(buffer, csv)
 end
end


-- пишем в файл только если есть данные в буфере buffer
if #buffer > 1 then
 file = io.open(filepath, 'w')
 log(filepath)
 file:write(table.concat(buffer, '\r\n'))
 file:close()
end
log(1, filepath)
to = 'ok@evika.ru'
require('socket.smtp')
require('mime')

require('ltn12')
local escape = function(v)
   return '<' .. tostring(v) .. '>'
 end
local settings = {
   -- "from" field, only e-mail must be specified here
   from = '@gmail.com',
   -- smtp username
   user = '@gmail.com',
   -- smtp password
   password = '',
   -- smtp server
   server = 'smtp.gmail.com',
   -- smtp server port
   port = 465,
   -- enable ssl, required for gmail smtp
   secure = 'sslv23',
 }

 local smtp = require('socket.smtp')
log(2)
 
-- message headers and body
mesgt = {
     headers = {
        to = email_address,
        ["content-type"] = 'text/html',
        ["content-disposition"] = 'attachment; filename="logs"',
        ["content-description"] ='yourattachment',
        ["content-transfer-encoding"] = "BASE64",
        subject = "subject line"
    },
     body = ltn12.source.chain(
       ltn12.source.file(io.open(filepath, "r")),
       ltn12.filter.chain(
 mime.encode("base64"),
 mime.wrap("base64")
)
       )
 }
settings.source = smtp.message(mesgt)
 -- fixup from field
log(4)
 settings.from = escape(settings.from)

 settings.rcpt = { to }
smtp.send(settings)

file = io.open (filepath, 'r')
log (io.readfile (filepath))
log(file)



os.remove(filepath)
Reply
#5
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)
Reply


Forum Jump: