Export last hour CSV object log file to external or local FTP server from LM

Example: export last hour CSV object log file to external or internal FTP server from LogicMachine

Task

Once an hour, make CSV file with all objects logs and send to external FTP server with IP 192.168.1.11, login ‘ftplogin‘, password ‘ftppassword‘. In case you want to download the file manually from LM local FTP server, it is available in directory /home/ftp

Scheduled program

In Scripting -> Scheduled add the script which will run once an hour and add following code in Script editor.

Source code     
  1. require('socket.ftp')
  2.  
  3. -- ftp file
  4. ftpfile = string.format('ftp://ftplogin:ftppassword@192.168.1.11/%s.csv', os.date('%Y-%m-%d_%H-%M'))
  5. -- get past hour data (3600 seconds)
  6. logtime = os.time() - 60 * 60
  7.  
  8. -- list of objects by id
  9. objects = {}
  10.  
  11. -- objects with logging enabled
  12. query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
  13. for _, object in ipairs(db:getall(query)) do
  14.   objects[ tonumber(object.address) ] = {
  15.     datatype = tonumber(object.datatype),
  16.     name = tostring(object.name or ''),
  17.   }
  18. end
  19.  
  20. -- csv buffer
  21. buffer = { '"date","address","name","value"' }
  22.  
  23. -- get object logs
  24. query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
  25. for _, row in ipairs(db:getall(query, logtime)) do
  26.   object = objects[ tonumber(row.address) ]
  27.  
  28.   -- found matching object and event type is group write
  29.   if object and row.eventtype == 'write' then
  30.     datatype = object.datatype
  31.  
  32.     -- check that object datatype is set
  33.     if datatype then
  34.       -- decode data
  35.       data = knxdatatype.decode(row.datahex, datatype)
  36.  
  37.       -- remove null chars from char/string datatype
  38.       if datatype == dt.char or datatype == dt.string then
  39.         data = data:gsub('%z+', '')
  40.       -- date to DD.MM.YYYY
  41.       elseif datatype == dt.date then
  42.         data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
  43.       -- time to HH:MM:SS
  44.       elseif datatype == dt.time then
  45.         data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
  46.       end
  47.     else
  48.       data = ''
  49.     end
  50.  
  51.     -- format csv row
  52.     logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
  53.     csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))
  54.  
  55.     -- add to buffer
  56.     table.insert(buffer, csv)
  57.   end
  58. end
  59.  
  60. -- upload to ftp only when there's data in buffer
  61. if #buffer > 1 then
  62.   result, err = socket.ftp.put(ftpfile, table.concat(buffer, '\r\n'))
  63. end
  64.  
  65. -- error while uploading
  66. if err then
  67.   alert('FTP upload error: %s', tostring(err))
  68. end

Write to existing file on FTP

Here is example if you want to write to existing file log.csv which is located on FTP 192.168.1.40

Source code     
  1. require('socket.ftp')
  2.  
  3. logtime = os.time() - 60 * 60
  4. objects = {}
  5.  
  6. query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
  7. for _, object in ipairs(db:getall(query)) do
  8.   objects[ tonumber(object.address) ] = {
  9.     datatype = tonumber(object.datatype),
  10.     name = tostring(object.name or ''),
  11.   }
  12. end
  13.  
  14. buffer = {}
  15.  
  16. query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
  17. for _, row in ipairs(db:getall(query, logtime)) do
  18.   object = objects[ tonumber(row.address) ]
  19.  
  20.   if object and row.eventtype == 'write' then
  21.     datatype = object.datatype
  22.  
  23.     if datatype then
  24.       data = knxdatatype.decode(row.datahex, datatype)
  25.  
  26.       if datatype == dt.char or datatype == dt.string then
  27.         data = data:gsub('%z+', '')
  28.       elseif datatype == dt.date then
  29.         data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
  30.       elseif datatype == dt.time then
  31.         data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
  32.       end
  33.     else
  34.       data = ''
  35.     end
  36.  
  37.     logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
  38.     csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))
  39.     table.insert(buffer, csv)
  40.   end
  41. end
  42.  
  43. if #buffer > 1 then
  44.   data = table.concat(buffer, '\r\n')
  45.   res, err = socket.ftp.put({
  46.     host = '192.168.1.40',
  47.     user = 'ftp',
  48.     password = 'ftp',
  49.     command = 'appe',
  50.     argument = 'log.csv',
  51.     source = ltn12.source.string(data)
  52.   })
  53. end
  54.  
  55. if err then
  56.   alert('FTP upload error: %s', tostring(err))
  57. end

Write only specific object logs instead of full list

Create a scheduled script which runs each 10 minutes and fill the table with required object values:

Source code     
  1. data = storage.get('myobjectlogs', {})
  2.  
  3. table.insert(data, {
  4.   ['time'] = os.time(),
  5.   ['counter-1'] = grp.getvalue('1/1/1'),
  6.   ['counter-2'] = grp.getvalue('1/1/2'),
  7. })
  8.  
  9. storage.set('myobjectlogs', data)

Local FTP CSV storage

From the upper script do the following changes:

1. Replace this:

Source code     
  1. require('socket.ftp')
  2.  
  3. -- ftp file
  4. ftpfile = string.format('ftp://ftplogin:ftppassword@192.168.1.11/%s.csv', os.date('%Y-%m-%d_%H-%M'))

With this:

Source code     
  1. ftpfile = string.format('/home/ftp/%s.csv', os.date('%Y-%m-%d_%H-%M'))

2. Replace this:

Source code     
  1. -- upload to ftp only when there's data in buffer
  2. if #buffer > 1 then
  3.   result, err = socket.ftp.put(ftpfile, table.concat(buffer, '\r\n'))
  4. end
  5.  
  6. -- error while uploading
  7. if err then
  8.   alert('FTP upload error: %s', tostring(err))
  9. end

With this:

Source code     
  1. if #buffer > 1 then
  2.   data = table.concat(buffer, '\r\n')
  3.   io.writefile(ftpfile, data)
  4. end

3.Create a scheduled script that runs once a day to remove old logs (60 is a number of days to keep old logs):

Source code     
  1. function cleanup(dir, days)
  2.   local now, files, mtime, delta
  3.  
  4.   now = os.time()
  5.   files = io.ls(dir) or {}
  6.  
  7.   for _, file in ipairs(files) do
  8.     file = dir .. '/' .. file
  9.     _, mtime = io.stat(file)
  10.  
  11.     if mtime then
  12.       delta = (now - mtime) / 86400
  13.       if delta > days then
  14.         os.remove(file)
  15.       end
  16.     end
  17.   end
  18. end
  19.  
  20. cleanup('/home/ftp', 60)