Logic Machine Forum
Logger All Datagrams KNX BUS to mysql - 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: Logger All Datagrams KNX BUS to mysql (/showthread.php?tid=2620)

Logger All Datagrams KNX BUS to mysql - Hyxion14 - 29.04.2020

Hello I have a project in which I need to save in a bbdd mysql all the states of all KNX groups in real time.

In other words, I need to listen to the KNX BUS and send all the datagrams that are written to the BUS to a mysql DB.

Can LM do this?

Can somebody help me?.

Thank you.

RE: Logger All Datagrams KNX BUS to mysql - admin - 30.04.2020

See this thread on a basic example of bus monitoring. It can be easily extended to send data to mysql or any other database:
https://forum.logicmachine.net/showthread.php?tid=273

RE: Logger All Datagrams KNX BUS to mysql - Hyxion14 - 30.04.2020

Maybe an idea would be, to mark all the objects with "LOG" and every hour, to synchronize the objects of the LM bbdd with the DB of mysql?

RE: Logger All Datagrams KNX BUS to mysql - admin - 30.04.2020

You can modify CSV example to export to mysql: https://openrb.com/example-export-last-hour-csv-object-log-file-to-external-ftp-server-from-lm2/

RE: Logger All Datagrams KNX BUS to mysql - gjniewenhuijse - 01.05.2020

This is my code, maybe not perfect but it works

Code:
--[[
* table:
[datalen]
  * number: 4
[datahex]
  * string: 453FE000
[data]
  * number: 3070
[srcraw]
  * number: 8448
[dstraw]
  * number: 17930
[type]
  * string: groupwrite
[src]
  * string: 2.1.0
[dst]
  * string: 8/6/10
--]]

if not client then
  require('genohm-scada.eibdgm')

  objects = grp.all()
  datatypes = {}

  for _, object in ipairs(objects) do
    datatypes[ object.id ] = {object.name, object.datatype}
  end
 
  function writemysql(I_datalen,I_datahex,I_data,I_srcraw,I_dstraw,I_type,I_src,I_dst,I_name)
      require('luasql.mysql')
    env = luasql.mysql()
    dbcon = env:connect('xxxxxxxxx', 'xxxxxxx', 'xxxxxxxx', '192.168.12.25', '3306')
    if dbcon then
      if I_type == 'groupread' then
        cursor = dbcon:execute( 'insert into datalog (datetime,datalen,srcraw,dstraw,type,src,dst,dstname) values (now(),'..I_datalen..','..I_srcraw..','..I_dstraw..',"'..I_type..'","'..I_src..'","'..I_dst..'","'..I_name..'") ' )
      else
          cursor = dbcon:execute( 'insert into datalog (datetime,datalen,datahex,data,srcraw,dstraw,type,src,dst,dstname) values (now(),'..I_datalen..',"'..I_datahex..'",'..tostring(I_data)..','..I_srcraw..','..I_dstraw..',"'..I_type..'","'..I_src..'","'..I_dst..'","'..I_name..'") ' ) 
      end
      --log(cursor)
      dbcon:close()
      env:close()
    end
  end
 
  function writehandler(event)
    if datatypes[ event.dstraw ] then
      name = datatypes[ event.dstraw ][1]
        dpt = datatypes[ event.dstraw ][2]
    else
      name = ''
    end
    if dpt then
      event.data = knxdatatype.decode(event.datahex, dpt)
    else
        event.data = nil 
    end
    --log(event)
    writemysql(event.datalen,event.datahex,event.data,event.srcraw,event.dstraw,event.type,event.src,event.dst,name)
  end

  function readhandler(event)
    if datatypes[ event.dstraw ] then
        name = datatypes[ event.dstraw ][1]
    else
      name = ''
    end
    --log(event)
    writemysql(event.datalen,event.datahex,'',event.srcraw,event.dstraw,event.type,event.src,event.dst,name)
  end

  -- knx connection
  client = eibdgm:new({ timeout = 1 })
  client:sethandler('groupwrite', writehandler)
  client:sethandler('groupresponse', writehandler)
  client:sethandler('groupread', readhandler)
end

-- handle knx
client:step()