Automatic export all logs and send by mail

[manos@dynamitec]

Hello Admin,

I can understand that but we have a LM5Lp2 and using it on various sites as a logger when required. It is not that it is running for very long periods. I just want to overcome the 50k data points limit. In the projects we have (not logging everything of course), I would guess that 50K telegrams will be enough for one day. If the LM is logging for 7-15 days then this file will be 15 time written or so. When installed on a new site for example we can delete all files from the FTP server and start over with the logging.

Is this possible to do?

Regards,

[admin]

See this example: https://openrb.com/example-export-last-h...-from-lm2/
It has both local file and remote FTP options.

[manos@dynamitec]

See this example: https://openrb.com/example-export-last-h...-from-lm2/
It has both local file and remote FTP options.

Hello Admin,

I have applied the solution from the example on the link, although unfortunately I just noticed that not all fields are included on the csv file. I would like to export all data that can be seen on the object logs utility. For the moment only timestamp, address, name and value is included in the csv export...

I am using the following script:

Code:

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162

   --ftp file    ftpfile = string.format('/home/ftp/%s.csv', os.date('%Y-%m-%d_%H-%M'))    -- get past 12 hours data (43200 seconds)    logtime = os.time() - 12 * 60 * 60          -- list of objects by id     objects = {}          -- objects with logging enabled     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     buffer = { '"date","address","name","value"' }          -- get object logs     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) ]            -- found matching object and event type is group write       if object and row.eventtype == 'write' then         datatype = object.datatype              -- check that object datatype is set         if datatype then           -- decode data           data = knxdatatype.decode(row.datahex, datatype)                -- remove null chars from char/string datatype           if datatype == dt.char or datatype == dt.string then             data = data:gsub('%z+', '')           -- date to DD.MM.YYYY           elseif datatype == dt.date then             data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)           -- time to HH:MM:SS           elseif datatype == dt.time then             data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)           end         else           data = ''         end              -- format csv row         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))              -- add to buffer         table.insert(buffer, csv)       end     end              -- upload to ftp only when there's data in buffer     if #buffer > 1 then       data = table.concat(buffer, '\r\n')       io.writefile(ftpfile, data)     end

How can this be modified to include the type (read/write) and source address fields?

Kind Regards,

[admin]

See this updated example: https://kb.logicmachine.net/scripting/ex...-hour-csv/

[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:

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273

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?

[domotiqa]

I test it but I think the smtp output of my customer is blocked (result 1, nil) for the log. I will try on my test server

Code:

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465

smtp = require('socket.smtp') mime = require('mime') ltn12 = require('ltn12') to = 'xxx@gmail.com' subject = 'CSV logs' message='export log' logtime = os.time() - 60 * 60--*24*7 -- last hour (3600 seconds) -------------- cherche les cpt query = [[   SELECT ol2.name   FROM objects ol2   WHERE ol2.disablelog = 0 ]] csv={} nombre=0 nom={} for _, objectlog in ipairs(db:getall(query, logtime)) do     nombre=nombre+1       nom[nombre]=objectlog.name   ----------------   --buffer = {'"date","address","name","value"'}   buffer = {'"date","name","value"'}   query2 = [[     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(query2, logtime)) do     if row.datatype and row.eventtype == 'write' and row.name == nom[nombre] 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',         --logdate, knxlib.decodega(row.id), row.name, tostring(data))         logdate, string.split(row.name,'-')[5], tostring(data))     end   end   csv[nombre] = table.concat(buffer, '\r\n')      end --pour chaque object ayant log attachement = {} nombre=0 for _, nomCpt in ipairs(nom) do   nombre=nombre+1   -- create file   table.insert(attachement, {filename=nomCpt .. '.csv', filedata=csv[nombre], mimetype='text/csv'}) end mailattach(to, subject, message, attachement)

and function in common

Code:

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768

--******************************** -- send an e-mail with attachments function mailattach(to, subject, message, files)   local settings = {     -- "from" field, only e-mail must be specified here     from = 'notification@xxx.com',     -- smtp username     user = 'notification@xxx.com',     -- smtp password     password = 'xxx',     -- smtp server     server = 'xxx.net',     --server = 'xxxx.net',         -- smtp server port     port = 465,     --port = 587,     -- enable ssl, required for gmail smtp     secure = 'sslv23',     --secure = 'tlsv1_2',   } -- ssl0.ovh.net sur port 465 -- ns0.ovh.net sur ports 25, 5025, 587, enfin tout ce que j'essaye...   local smtp = require('socket.smtp')   if type(to) ~= 'table' then     to = { to }   end   for index, email in ipairs(to) do     to[ index ] = '<' .. tostring(email) .. '>'   end     -- escape double quotes in file name   for _, object in ipairs(table) do     --files[_].filename = files[_].filename:gsub('"', '\\"')     end      -- fixup from field     local from = '<' .. tostring(settings.from) .. '>'     -- message headers and body   email = {headers = {}, body = {}}   email.headers = {         to = table.concat(to, ', '),         subject = subject,         ['From'] = from,         --['Content-type'] = 'text/html; charset=utf-8',       }   email.body[1] = { headers = { ['Content-Type'] = 'text/html; charset=utf-8', }, body = mime.eol(0, message) }   -- adding attachements   for _, object in ipairs(files) do   email.body[_+1] = {       headers = { ['Content-Type'] = files[_].mimetype or 'text/plain', ['Content-Disposition'] = 'attachment; filename="' .. files[_].filename ..'"', ['Content-Transfer-Encoding'] = 'BASE64', },       body = ltn12.source.chain( ltn12.source.string(files[_].filedata), ltn12.filter.chain(mime.encode('base64'), mime.wrap() ) )     }   end     settings.source = smtp.message(email)   settings.from = from   settings.rcpt = to   return smtp.send(settings) end