(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

(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-hour-csv-object-log-file-to-external-ftp-server-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)

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

(17.01.2022, 08:17)admin Wrote:

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)

(16.09.2022, 10:09)admin Wrote: Use this for a single group address. CSV is rather pointless in this case. This script simply shows dates and values in the message body.

Code:

id = buslib.encodega('0/0/1')
to = { 'to1@example.com', 'to2@example.com' }
subject = 'Alarm logs'

logtime = os.time() - 24 * 60 * 60 -- last 24 hours

buffer = {}

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

for _, row in ipairs(db:getall(query, id, 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 ] = logdate .. ' ' .. tostring(data)
  end
end

message = table.concat(buffer, '\r\n')
mail(to, subject, message)

(17.01.2022, 08:17)admin Wrote: 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)