Read values in scheduled script store in ftp

[Rauschentofft]

Hi.

I need help with a scheduled script.
I want to read 4 byte floating point values throu tags or miltiple tags
and store the values to local lm ftp in a csv file.

The value from each object should contain: Date, Time, Name, Value and unit/suffix.

Is this possible?

[admin]

You can use this example as a starting point: http://openrb.com/example-export-last-ho...-from-lm2/

[Rauschentofft]

I was thinking a script like this.

But this script is only working in an event.

-- Write data to a file in the local FTP

value = event.getvalue()  


logdate = os.date('%Y.%m.%d %H:%M:%S', os.time())
csv = string.format('%q,%q,%q,%q\r\n', logdate, event.dst, grp.alias(event.dst), tostring(value))

log(csv)  
  

file, error = io.open("home/ftp/outdoor_temp1.txt","a")
log(file,error)
file:write(csv)
file:close()

[admin]

You need to provide an absolute path:

Code:

1

file, error = io.open("/home/ftp/outdoor_temp1.txt","a")

[Rauschentofft]

Hi. 

I got this script to work. But i have two qiestions.

It is possible to skip past hour data and get the acctual time instead?

How read the unit/suffix and add it to the result.

Result:
--Date, Time, Adress, Name, Value
"2018.10.04 07:34:10","33/1/3","Meter 2","1495"

--------Script write to FTP----------

-- get past hour data (3600 seconds)
logtime = os.time() - 10 * 1

-- list of objects by id
objects = {'Mätare'}

-- 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 = { }

-- 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('\r%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))

   -- add to buffer
   table.insert(buffer, csv)
 end
end

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

if #buffer > 1 then
 data = table.concat(buffer, '\r\n')
 io.writefile(ftpfile, data)
end
log(data)

[admin]

What do you mean by the actual time?

You can get units like this, then use object.units when generating CSV:

Code:

12345678

query = 'SELECT address, datatype, name, units 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 ''),    units = tostring(object.units or ''), } end

[Rauschentofft]

Could i get this script to work with a residential script?

--Write data to a file in the local FTP

value = event.getvalue()


logdate = os.date('%Y.%m.%d %H:%M:%S', os.time())
csv = string.format('%q,%q,%q,%q\r\n', logdate, event.dst, grp.alias(event.dst), tostring(value))

log(csv)


file, error = io.open("home/ftp/outdoor_temp1.txt","a")
log(file,error)
file:write(csv)
file:close()

[Daniel]

Yes only you need to change value = event.getvalue() to value = grp.getvalue('1/1/1')
This will work but if you will be writing to FTP which is on SD card every 60s you will kill it quite fast.

[admin]

The path is incorrect, it must start with a "/", so it should be /home/ftp/outdoor_temp1.txt
Another issue is that you are constantly appending data to a file. If this is done very fast then the file can grow in size quickly and fill the whole partition. And as Daniel mentioned this can degrade the SD card due to constant writes.
If you want to send data to external sources look into MQTT. If you want to log data externally then InfluxDB can be used (it has very simple integration with LM).

[Rauschentofft]

I am going use scheduled script. Every 6th hour it should read the meters and store it ftp and then send an mail.
Then i want to delete the file after the email is sent.

---

I tried with this script and made the changes but i got this error:

Error log:
test 11.01.2021 17:16:00
User script:7: attempt to index global 'event' (a nil value)
stack traceback:
User script:7: in main chunk

--Write data to a file in the local FTP

value = grp.getvalue('16/1/1')


logdate = os.date('%Y.%m.%d %H:%M:%S', os.time())
csv = string.format('%q,%q,%q,%q\r\n', logdate, event.dst, grp.alias(event.dst), tostring(value))

log(csv)


file, error = io.open("/home/ftp/outdoor_temp1.txt","a")
log(file,error)
file:write(csv)
file:close()

[admin]

Replace event.dst in your script with '16/1/1', event variable is not defined in resident and scheduled scripts.
If you want to send data as an attachment then you don't need to write it to file at all. See this example: https://forum.logicmachine.net/showthread.php?tid=394
If you need to write some temporary data to a file then use tmp storage (for example "/tmp/myfile.txt"). tmp storage uses RAM so it does not degrade the SD card.

[Rauschentofft]

Instead of get past hour data, just read every adress with tag energy.

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116

-- Mail Attachment subject = 'Energy Values' --to = 'mail@mail.com'' to = mail@mail.com' -- get past hour data (3600 seconds) logtime = os.time() - 60 * 60 -- list of objects by id objects = {'Energy'} -- 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 -- transform table to string for csv file data1 = table.concat(buffer, '\r\n')   -- make sure these settings are correctat   local settings = {     -- "from" field, only e-mail must be specified here   from = 'mail@mail.com'',     -- smtp username   user = 'mail@mail.com'',     -- smtp password   password = 'Password',     -- smtp server     server = 'smtp.gmail.com',     -- smtp server port     port = 465,     -- enable ssl, required for gmail smtp     secure = 'sslv23',   }   mime = require("mime")   ltn12 = require("ltn12")   local smtp = require('socket.smtp')   local escape = function(v)   return '<' .. tostring(v) .. '>'   end settings.from = escape(settings.from) settings.rcpt = { escape(to) } settings.source = smtp.message{    headers = {     to = to,       subject = subject,     },    body = {       preable = "This email contains 1 attachment.",       [1] = {       body = mime.eol(0, [["This message is automaticly send, Do not reply on this e-mail."]])       },       [2] = {      headers = {       ["content-type"] = 'text/plain; name="Objectlog.csv"',       ["content-disposition"] = 'attachment; filename="Objectlog.csv"',      },       body = data1       },     epilogue = "End of mail"    } } r, e = smtp.send(settings) if (e) then   io.stderr:write("Could not send email: ", e, "\n") end

[Rauschentofft]

I have the function now. It is possible to skip the adress in the csv file.

I just want Date, Time, Name and Value

2021.01.13 18:38:17,"16/1/3","Mät 3","3400502"
2021.01.13 18:38:12,"16/1/2","Mät 2","200302"
2021.01.13 18:38:08,"16/1/1","Mät 1","10202"
Br

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110

-- get past hour data (3600 seconds) logtime = os.time() - 60 * 60 -- list of objects by id objects = {'Mätare'} -- 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","name","adress","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 -- transform table to string for csv file data1 = table.concat(buffer, '\r\n')   -- make sure these settings are correctat   local settings = {     -- "from" field, only e-mail must be specified here   from = 'gmail@gmail.com',     -- smtp username   user = 'gmail@gmail.com',     -- smtp password   password = 'Password!',     -- smtp server     server = 'smtp.gmail.com',     -- smtp server port     port = 465,     -- enable ssl, required for gmail smtp     secure = 'sslv23',   }   mime = require("mime")   ltn12 = require("ltn12")   local smtp = require('socket.smtp')   local escape = function(v)   return '<' .. tostring(v) .. '>'   end settings.from = escape(settings.from) settings.rcpt = { escape(to) } settings.source = smtp.message{    headers = {     to = to,       subject = subject,     },    body = {       preable = "This email contains 1 attachment.",       [1] = {       body = mime.eol(0, [["This message is automaticly send, Do not reply on this e-mail."]])       },       [2] = {      headers = {       ["content-type"] = 'text/plain; name="Objectlog.csv"',       ["content-disposition"] = 'attachment; filename="Objectlog.csv"',      },       body = data1       },     epilogue = "End of mail"    } } r, e = smtp.send(settings) if (e) then   io.stderr:write("Could not send email: ", e, "\n") end

[admin]

Use this to generate CSV for a certain tag and a certain time:

Code:

12345678910111213141516171819202122232425262728293031

tagname = 'log' logtime = os.time() - 60 * 60 query = [[ SELECT ol.*, o.name, o.datatype FROM objectlog ol JOIN objects o ON ol.address=o.id JOIN objecttags t ON o.id=t.object WHERE t.tag=? AND logtime>=? ORDER BY id DESC ]] buffer = { '"date","name","address","value"' } items = db:getall(query, tagname, logtime) for _, item in ipairs(items) do   id = tonumber(item.address) or 0   logdate = os.date('%Y.%m.%d %H:%M:%S', math.floor(item.logtime))   etype = item.eventtype   if (etype == 'write' or etype == 'response') and item.datatype then     value = grp.decodevalue(item.datahex, item.datatype)   else     value = ''   end   buffer[ #buffer + 1 ] = string.format('%q,%q,%q,%q',     logdate, item.name or '', buslib.decodega(id), tostring(value)) end csv = table.concat(buffer, '\n')

[Rauschentofft]

HI.

I still got the groupadress in the csv file.

[admin]

Like this:

Code:

12345678910111213141516171819202122232425262728293031

tagname = 'log' logtime = os.time() - 60 * 60 query = [[ SELECT ol.*, o.name, o.datatype FROM objectlog ol JOIN objects o ON ol.address=o.id JOIN objecttags t ON o.id=t.object WHERE t.tag=? AND logtime>=? ORDER BY id DESC ]] buffer = { '"date","name","value"' } items = db:getall(query, tagname, logtime) for _, item in ipairs(items) do   id = tonumber(item.address) or 0   logdate = os.date('%Y.%m.%d %H:%M:%S', math.floor(item.logtime))   etype = item.eventtype   if (etype == 'write' or etype == 'response') and item.datatype then     value = grp.decodevalue(item.datahex, item.datatype)   else     value = ''   end   buffer[ #buffer + 1 ] = string.format('%q,%q,%q',     logdate, item.name or '', tostring(value)) end csv = table.concat(buffer, '\n')

[Rauschentofft]

Hi.

And if i want to email this csv file?

[admin]

See this thread: https://forum.logicmachine.net/showthread.php?tid=394

[Rauschentofft]

Hi.

In the csv file it looks like this:

"date","address","name","value"
"2021.02.02 05:00:07","1/6/17","N13A33","11491"
"2021.02.02 05:00:07","1/6/16","N10A25","12894"

Could i change it so it looks like this instead?

date;name;value
2021-02-02;4400A1FS6B;591616,24
2021-02-02;4400A1FS6C;290030,96

[admin]

This should produce what you need. Are you sure that you don't need the time to be included?

Code:

12345678910111213141516171819202122232425262728293031323334

tagname = 'log' logtime = os.time() - 60 * 60 query = [[ SELECT ol.*, o.name, o.datatype FROM objectlog ol JOIN objects o ON ol.address=o.id JOIN objecttags t ON o.id=t.object WHERE t.tag=? AND logtime>=? ORDER BY id DESC ]] buffer = { 'date;name;value' } items = db:getall(query, tagname, logtime) for _, item in ipairs(items) do   id = tonumber(item.address) or 0   logdate = os.date('%Y-%m-%d', math.floor(item.logtime))   etype = item.eventtype   if (etype == 'write' or etype == 'response') and item.datatype then     value = grp.decodevalue(item.datahex, item.datatype)     if type(value) == 'number' then       value = tostring(value):gsub('%.', ',')     end   else     value = ''   end   buffer[ #buffer + 1 ] = string.format('%s;%s;%s',     logdate, item.name or '', tostring(value)) end csv = table.concat(buffer, '\n')