extract mysensor data

Python and python framework

Moderator: leecollings

Post Reply
ebioz
Posts: 8
Joined: Tuesday 07 February 2023 21:43
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

extract mysensor data

Post by ebioz »

Hi,
i am using mysensors on 3 arduinos that are collecting data with various sensors and managing the watering of my garden. Data is collected by domoticz on a RPI.
I am trying to get the data from domoticz to send it to another system that will allow to store it for a long time and external to my home as i don't want to open domoticz itself on internet.
I choose to pull the DB every minute to get last updated data. (bad idea ? better idea ?)

And domoticz database is far to be simple ... or logic.
I started with the table deviceStatus that have all the collected metrics. But i can't find the link between this table and the table my sensorChilds : how can i know that a device in deviceStatus is link to a node and child ?
i spent hours looking for the link and was not able to find how domoticz link it in the DB....

Thanks for your help.
willemd
Posts: 650
Joined: Saturday 21 September 2019 17:55
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.1
Location: The Netherlands
Contact:

Re: extract mysensor data

Post by willemd »

Just sharing some thoughts:

I don't know about mysensorchilds table. In my case it is empty, although I have quite a number of devices.

I assume you really want 1 minute data? Otherwise you might just take values from meter, multimeter, percentage etc tables because domoticz fills those tables every 5 minutes with the latest values.

Did you consider influxdb data push? You can choose whether to push only changed values or all values at regular intervals.
Your one minute data collection from domoticz dbase might result in a lot of same values, depending on what you are measuring.

I am currently just copying daily backup files (created by auto backups) to a save place and renaming them with a date-timestamp. If I really want 5 minute data in the future (for a full year for example) then I can just load the relevant periods from those backups into a new database.

The best method depends on how much detail you need.
ebioz
Posts: 8
Joined: Tuesday 07 February 2023 21:43
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: extract mysensor data

Post by ebioz »

thanks for your reply.
In my case i pool domoticz db every minute but i only take data that is older less than one minute. And it works well.
Here my issue is to have the link between deviceStatus and mysensor tables.
I started to read the source code and it seems that something id encoded in the deviceID acording to a rule....
Is there somewhere technical doc that describe this encoding from nodeID to deviceID ?
User avatar
waltervl
Posts: 5859
Joined: Monday 28 January 2019 18:48
Target OS: Linux
Domoticz version: 2024.7
Location: NL
Contact:

Re: extract mysensor data

Post by waltervl »

For pushing real time data from Domoticz to another database for further processing use the influxdb push function: https://www.domoticz.com/wiki/Influxdb Select the Domoticz devices you want to push and the trigger, and the data will be sent to the other database.

Use Google how to install and use influxdb. Mostly Grafana is used for making extra graphs based on influxdb data.
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
ebioz
Posts: 8
Joined: Tuesday 07 February 2023 21:43
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: extract mysensor data

Post by ebioz »

i didn't know this domoticz capabilities to send data. Interesting i will explorate the http later.
But i am still looking for the rules used to populate the devicestate table. Because influx just relay the contents of this table.
Sometimes childId will be in unit column, sometimes it is encoded in deviceId column, sometimes elseswhere.... there is no logical rule... it is per device type and i can't find any documentation.
User avatar
waltervl
Posts: 5859
Joined: Monday 28 January 2019 18:48
Target OS: Linux
Domoticz version: 2024.7
Location: NL
Contact:

Re: extract mysensor data

Post by waltervl »

There is no documentation on the database tables as this is normally not touched by users. Perhaps it is documented in the source code of mysensors hardware in Domoticz github?
ebioz
Posts: 8
Joined: Tuesday 07 February 2023 21:43
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: extract mysensor data

Post by ebioz »

indeed code is available... i had a look on it.
Code is very poor regarding comments.... and is there that a i found that the nodeid information is spread in various columns depending on device type without any visible logic...
And yes you are right i am not suppose to get infos from DB but from APIs. But APIs are more or less a dump of the raw tables that don't provide the logic link from a probe > device Arduino > controller > domoticz.
Thks for your help.
User avatar
waltervl
Posts: 5859
Joined: Monday 28 January 2019 18:48
Target OS: Linux
Domoticz version: 2024.7
Location: NL
Contact:

Re: extract mysensor data

Post by waltervl »

I am not using mysensors but do you want to extract specific mysensor data or just the sensor/switch data that is stored into the various domoticz devices managed by the Domoticz mysensor hardware gateway?

You could also use Dzvents scripting to export that device data out of Domoticz (next to the HTTP or influxdb data push).
Edit: see example viewtopic.php?t=38973
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
ebioz
Posts: 8
Joined: Tuesday 07 February 2023 21:43
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: extract mysensor data

Post by ebioz »

To sump up what i am looking for is :
- if i have a deviceStatus.id value, how can i do the link with the nodeid in mysensor.id
- how to group the domoticz devices by nodeId

The nodeId refers to the "arduino" that have the sensors and collect the data.

If a solution by API exists i whould be very happy...
User avatar
waltervl
Posts: 5859
Joined: Monday 28 January 2019 18:48
Target OS: Linux
Domoticz version: 2024.7
Location: NL
Contact:

Re: extract mysensor data

Post by waltervl »

This is all abacadabra for me. Seems the mysensor implementation is a little bit different. You could check the mysensors forum.
Did you look at the device list (menu setup devices)? See also wiki https://www.domoticz.com/wiki/Setup_Devices
That should link the devices to the Mysensors gateway and could indicate mysensors deviceids

Else post some screenshots of the domoticz user interface to show what data you would like to see exported.
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
User avatar
sincze
Posts: 1302
Joined: Monday 02 June 2014 22:46
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.4
Location: Netherlands / Breda Area
Contact:

Re: extract mysensor data

Post by sincze »

mm I try to follow the steps here.
Can you please explain in normal scenario terms what you want to achieve. (not the technical part with id's).

As I use mysensors for years already and data (plenty of nodes) is collected in Domoticz via the Gateway and from there I can push data pretty much everywhere.

For example "temp data" or "CO2 values" to an external DB and create a graph with another application.
Pass2php
LAN: RFLink, P1, OTGW, MySensors
USB: RFXCom, ZWave, Sonoff 3
MQTT: ZIgbee2MQTT,
ZWAVE: Zwave-JS-UI
WIFI: Mi-light, Tasmota, Xiaomi Shelly
Solar: Omnik, PVOutput
Video: Kodi, Harmony HUB, Chromecast
Sensors: You name it I got 1.
ebioz
Posts: 8
Joined: Tuesday 07 February 2023 21:43
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: extract mysensor data

Post by ebioz »

Hi,

What i want to get from domoticz every minute is the following information for each device that is a domoticz device ( probe ) :
- domoticz device id
- my Sensor node id
- my sensor child id
- type-subtype of value
- value of the measurement
- date
And my script with it will insert the data in an historical db with a specific schema where everthing is a a real probe from a node collected by a GW.
Location > GW (an RPI) > Node (an arduino) > probe
And desagregate TEMP-HUM-BARO that is stored in a single device, and it is 3 probes seperated on the arduino...

Node id and child id is the tricky part because depending on type of data, it is not stored in the same way. And for V_LIGHT_LEVEL, i can't find child_id value....

Thanks.

PS : i play this morning with chatGPT to get the solution... the funny thing is that it told me that the solution is in the source code of the file https://github.com/domoticz/domoticz/bl ... re/neo.cpp.... that i was not able to find... not so smart mister GPT ;)
User avatar
sincze
Posts: 1302
Joined: Monday 02 June 2014 22:46
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.4
Location: Netherlands / Breda Area
Contact:

Re: extract mysensor data

Post by sincze »

ebioz wrote: Wednesday 15 February 2023 14:43 Hi,

What i want to get from domoticz every minute is the following information for each device that is a domoticz device ( probe ) :
- domoticz device id
- my Sensor node id
- my sensor child id
- type-subtype of value
- value of the measurement
- date
And my script with it will insert the data in an historical db with a specific schema where everthing is a a real probe from a node collected by a GW.
Location > GW (an RPI) > Node (an arduino) > probe
And desagregate TEMP-HUM-BARO that is stored in a single device, and it is 3 probes seperated on the arduino...

Node id and child id is the tricky part because depending on type of data, it is not stored in the same way. And for V_LIGHT_LEVEL, i can't find child_id value....

Thanks.

PS : i play this morning with chatGPT to get the solution... the funny thing is that it told me that the solution is in the source code of the file https://github.com/domoticz/domoticz/bl ... re/neo.cpp.... that i was not able to find... not so smart mister GPT ;)
I assume just the device and it's value not sufficient for an historical DB ??
I have no clue what the usage of a node and child Id would be to store if the device already can have a unique name.
I can not figure that out honestly.

Did you configure your nodes with a fixed child ID as well during flashing ? Hence these can change if a sensor is replaced for example. That is why I use the device name.
Pass2php
LAN: RFLink, P1, OTGW, MySensors
USB: RFXCom, ZWave, Sonoff 3
MQTT: ZIgbee2MQTT,
ZWAVE: Zwave-JS-UI
WIFI: Mi-light, Tasmota, Xiaomi Shelly
Solar: Omnik, PVOutput
Video: Kodi, Harmony HUB, Chromecast
Sensors: You name it I got 1.
ebioz
Posts: 8
Joined: Tuesday 07 February 2023 21:43
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: extract mysensor data

Post by ebioz »

for example i have 3 weather stations with 4 probes : (temp-hum-baro-lux)
The code is the same, some same sketch name, same childs ids, but node id is not the same.
Examples of the db domoticz complexity
For temp, hum and baro : in devicestatus.deviceid we have something stored in int format to recode to hexa to get node and childId
For voltage, soil moisture, etc : in devicestatus.deviceid we have something like 00XXYY : where XX is the nodeId and YY the childID in hexa.
For switches : in devicestatus.deviceid we have something like 0000YY : where XX is the nodeId in hexa, and childID is stored in "unit" column

And the worst : for devices percentage type (like V_LIGHT_LEVEL) : nodeid is in deviceid like 0000YY, but childID no where.... i seek the code, and for this devices domo assumes that you can only have a child type percentage per node mysensor... (bug ?)

The implementation of mysensors is nor logic nor consistent because depends on the type of probe, like if they had a developer per type of probe...
User avatar
waltervl
Posts: 5859
Joined: Monday 28 January 2019 18:48
Target OS: Linux
Domoticz version: 2024.7
Location: NL
Contact:

Re: extract mysensor data

Post by waltervl »

But why do you need mysensor sensor- and child node-id in your export? Is the Domoticz device ID not enough? And then you can use the existing export tools or a lua/dzvents scripts that runs every minute to export the requested data.
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
User avatar
sincze
Posts: 1302
Joined: Monday 02 June 2014 22:46
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.4
Location: Netherlands / Breda Area
Contact:

Re: extract mysensor data

Post by sincze »

waltervl wrote: Wednesday 15 February 2023 17:10 But why do you need mysensor sensor- and child node-id in your export? Is the Domoticz device ID not enough? And then you can use the existing export tools or a lua/dzvents scripts that runs every minute to export the requested data.
Exactly what I was thinking indeed. My DB does not store the V_LIGHT_LEVEL and value, just the value. As I know the device is in LUX.
Pass2php
LAN: RFLink, P1, OTGW, MySensors
USB: RFXCom, ZWave, Sonoff 3
MQTT: ZIgbee2MQTT,
ZWAVE: Zwave-JS-UI
WIFI: Mi-light, Tasmota, Xiaomi Shelly
Solar: Omnik, PVOutput
Video: Kodi, Harmony HUB, Chromecast
Sensors: You name it I got 1.
ebioz
Posts: 8
Joined: Tuesday 07 February 2023 21:43
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: extract mysensor data

Post by ebioz »

the final goal of the export is to display on an external website the data by location, by node or by type of sensor.
So i need to know of witch node belongs each probe.
So need relation between device_id and node-child....
So my message to domo devs is : use the same rules of data mapping for all the probes.... thanks :)
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest