Logic Machine Forum
Automatic export all logs and send by mail - Printable Version

+- Logic Machine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: Automatic export all logs and send by mail (/showthread.php?tid=1918)

Pages: 1 2


Automatic export all logs and send by mail - gdimaria - 18.02.2019

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


RE: Automatic export all logs and send by mail - AEK - 18.02.2019

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



RE: Automatic export all logs and send by mail - gdimaria - 18.02.2019

(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)
Thank you so much!


RE: Automatic export all logs and send by mail - Dré - 17.01.2022

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)



RE: Automatic export all logs and send by mail - admin - 17.01.2022

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)



RE: Automatic export all logs and send by mail - Dré - 16.09.2022

The script is working fine,
but is it also possible to send the results to two email addresses?


RE: Automatic export all logs and send by mail - admin - 16.09.2022

Try this:
Code:
to = {
  escape('user1@example.com'),
  escape('user2@example.com'),
}

msgt = {
  headers = {
    to = table.concat(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



RE: Automatic export all logs and send by mail - Dré - 16.09.2022

Thanks is it working, except there was a little mistake:
at line 10 there is missing a ']' after '['content-disposition''

another question.
I also want to make a export from just 1 object, is that also possible?
group adres 4/0/22 with the name: 'Inbraak In=0 / Uit=1-geschakeld'


RE: Automatic export all logs and send by mail - admin - 16.09.2022

If you want to send a generic alert via email then use an event script like in this example: https://forum.logicmachine.net/showthread.php?tid=3330


RE: Automatic export all logs and send by mail - Dré - 16.09.2022

Actually, no, this is not what I'm looking for.

The customer has an alarm system, and many times there is a discussion if it was on at night or not.
So he will look at the log if the alarm system was activated, so he would look at the log to see if it is on.
At the moment he gets a mail of the last 24 hours with all the events. (only when he pressed a button for sending the mail).
I would do almost the same but just for the event of the alarm system was turned on, with a larger time period.
so mostly he dont have to know, only when there is a discussion about it.
so i would make a second button, to sent a mail with this log.


RE: Automatic export all logs and send by mail - admin - 16.09.2022

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)



RE: Automatic export all logs and send by mail - Dré - 16.09.2022

Yes thanks this is working


RE: Automatic export all logs and send by mail - Dré - 02.01.2023

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

I have a customer who want to know what user did sent the command on visualization,
Is it possible to add on the export the user (who was logged in).

I have different users who all have his own panel to control the lights/gates.


RE: Automatic export all logs and send by mail - admin - 02.01.2023

Try this:
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","metadata"'}

query = [[
  SELECT o.id, o.datatype, o.name, ol.datahex, ol.logtime, ol.eventtype, ol.meta
  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,%q',
      logdate, knxlib.decodega(row.id), row.name, tostring(data), row.meta or '')
  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)



RE: Automatic export all logs and send by mail - Dré - 02.01.2023

Great thanks again, it is working


RE: Automatic export all logs and send by mail - Dré - 21.03.2023

I have a question,
I have a client panel, sometimes it has problems and need to reboot.
The panel login automatic and it always show the visualisation page.

My question, is it possible to record when someone login and who?
I don't mean when someone executes a command, but really logging in to the server on a client pc.

And when this is possible, is it also possible to sent it by mail (on request or automatic)?


RE: Automatic export all logs and send by mail - admin - 21.03.2023

See these threads:
https://forum.logicmachine.net/showthread.php?tid=4028
https://forum.logicmachine.net/showthread.php?tid=4025


RE: Automatic export all logs and send by mail - Dré - 26.05.2023

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

I will use this script to notify me, when something happened.
Is it possible to use this script not by a adress but with a tag?


RE: Automatic export all logs and send by mail - manos@dynamitec - 04.10.2023

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

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


RE: Automatic export all logs and send by mail - admin - 04.10.2023

Constantly writing to LM SD card will kill it sooner or later. If you want to analyze large batches of data then you should export it to an external database like influxdb. See this thread for examples: https://forum.logicmachine.net/showthread.php?tid=1531