Confused about which device belong to which zwave-node Topic is solved
Moderator: leecollings
-
- Posts: 7
- Joined: Friday 20 May 2016 22:53
- Target OS: NAS (Synology & others)
- Domoticz version:
- Contact:
Confused about which device belong to which zwave-node
Hello,
I'm using domoticz 3.0.5292-7 on a synology NAS. It is connected with an aeotec z-stick and 2x aeotec smart switch. In the zwave screen I can see the switches as node-id 0x03 and 0x07
Now - as far as I understand - these nodes add multiple "devices", as can be seen in the following screen...
Now it's quite confusing in how this information needs to be interpreted... so some questions:
1. How can I see which node is responsible for which device ? I would assume that
- devices with index "0x00701" are from z-wave node 0x07 ( = Idx 23, 30, 21, 25, 26 )
- devices with index "0x00301" are from z-wave node 0x03 ( = Idx 16, 14, 15, 12, 9, 10 )
... so why would two physically identical aeotec smart switches report different numbers of devices ( 5 vs 6 ) ?
2. What are the "remaining devices?
- Idx 13 / ID 00000003
- Idx 24 / ID 00000007
- Idx 11 / ID 0x365
- Idx 17 / ID 0x401
- Idx 18 / ID 0x501
- Idx 19 / ID 0x601
- Idx 22 / ID 0x765
3. What are the devices with "old dates"
- Idx 13, last reported 2016/05/24
- Idx 11, last repored 2016/06/25
4. The device with Idx 16 is measuring kWh
- There must be a 2nd kWh meter - since there are 2 identical physical devices connected - but I don't see it... ?
Just a general thought... Maybe it would be an idea to - instead of showing "Aeon Labs Z-stick Gen 5" for every device, to instead show the Node-Name ( see the first screenshot, where I named them "Aeotec Smart Switch 6A" vs "... 6B" ) ? That would make it way more understandable...
I'm using domoticz 3.0.5292-7 on a synology NAS. It is connected with an aeotec z-stick and 2x aeotec smart switch. In the zwave screen I can see the switches as node-id 0x03 and 0x07
Now - as far as I understand - these nodes add multiple "devices", as can be seen in the following screen...
Now it's quite confusing in how this information needs to be interpreted... so some questions:
1. How can I see which node is responsible for which device ? I would assume that
- devices with index "0x00701" are from z-wave node 0x07 ( = Idx 23, 30, 21, 25, 26 )
- devices with index "0x00301" are from z-wave node 0x03 ( = Idx 16, 14, 15, 12, 9, 10 )
... so why would two physically identical aeotec smart switches report different numbers of devices ( 5 vs 6 ) ?
2. What are the "remaining devices?
- Idx 13 / ID 00000003
- Idx 24 / ID 00000007
- Idx 11 / ID 0x365
- Idx 17 / ID 0x401
- Idx 18 / ID 0x501
- Idx 19 / ID 0x601
- Idx 22 / ID 0x765
3. What are the devices with "old dates"
- Idx 13, last reported 2016/05/24
- Idx 11, last repored 2016/06/25
4. The device with Idx 16 is measuring kWh
- There must be a 2nd kWh meter - since there are 2 identical physical devices connected - but I don't see it... ?
Just a general thought... Maybe it would be an idea to - instead of showing "Aeon Labs Z-stick Gen 5" for every device, to instead show the Node-Name ( see the first screenshot, where I named them "Aeotec Smart Switch 6A" vs "... 6B" ) ? That would make it way more understandable...
Re: Confused about which device belong to which zwave-node
+1 vote for that. It is very confusing. I rather see names then numbers. Exposing internal (db) ids, is not really good practice. It leads to user confusion.
Computer use numbers for identification, human use names. We get confused with numbers.
It would be nice if in the hardware page of openzwave then when you click on a node it opens the device page already filtered with the right node devices. That would help also alot with renaming, configuring and anti-confusion.
To your questions:
Some devices will only be added when they send data and that can take a while. And it depends on the device when it sends data. For example a smokedetector which did not send any temperature reading until I put it outside, resulting in the temperature reading drop below the threshold configured on the device. And whalla! Resulting that the device sends a new temperature and it is added to domoticz. That is why sometimes devices are missing. (some devices you can force to send a reading usually by pressing a button on the device, look at the manual of your device)
Some z-wave devices give back more info then you need, you have to cherry pick which devices are usefull of a node. Like my smokedetector, it gives 10! new devices when added, I only use 3 of them. And I still do not know what the general and system sensor does.
My Powernode for gives back 2 devices with watt result in the first hour, then only 1 watt and 1 kwh. The second watt is never updated and used ever again after the first hour... so there are sometimes devices that are "old" and not used anymore.
Confusing eh?
Computer use numbers for identification, human use names. We get confused with numbers.
It would be nice if in the hardware page of openzwave then when you click on a node it opens the device page already filtered with the right node devices. That would help also alot with renaming, configuring and anti-confusion.
To your questions:
Some devices will only be added when they send data and that can take a while. And it depends on the device when it sends data. For example a smokedetector which did not send any temperature reading until I put it outside, resulting in the temperature reading drop below the threshold configured on the device. And whalla! Resulting that the device sends a new temperature and it is added to domoticz. That is why sometimes devices are missing. (some devices you can force to send a reading usually by pressing a button on the device, look at the manual of your device)
Some z-wave devices give back more info then you need, you have to cherry pick which devices are usefull of a node. Like my smokedetector, it gives 10! new devices when added, I only use 3 of them. And I still do not know what the general and system sensor does.
My Powernode for gives back 2 devices with watt result in the first hour, then only 1 watt and 1 kwh. The second watt is never updated and used ever again after the first hour... so there are sometimes devices that are "old" and not used anymore.
Confusing eh?
-
- Posts: 7
- Joined: Friday 20 May 2016 22:53
- Target OS: NAS (Synology & others)
- Domoticz version:
- Contact:
Re: Confused about which device belong to which zwave-node
Thanks for the info, I' pretty sure I got to the point where I renamed all devices to include "6A" and "6B" in the name.
There are 3 bogus devices ( from nodes which were added when I tried to get the 2nd switch added to the network ) - and which I later on deleted from the node-list. Apparently deleting nodes leaves the devices those nodes registered.
- Idx 17 / ID 0x401
- Idx 18 / ID 0x501
- Idx 19 / ID 0x601
There are 2 devices with 765 and 365 - which must also be related to 000007xx vs 00003xx ( even though all the other ones end on 701 vs 301 ). And then there is plain 3 and 7. So you basically need to ignore the last 2 digits and only look at the 3rd digit to figure out which node it is.
I then checked the log, noticed it was now reporting "general /kWh meter", went back to verify the devices screen and there it was.
So basically I got the expected devices... yay.
One minor issue would be if there is an easy way to get rid of the bogus devices?
There are 3 bogus devices ( from nodes which were added when I tried to get the 2nd switch added to the network ) - and which I later on deleted from the node-list. Apparently deleting nodes leaves the devices those nodes registered.
- Idx 17 / ID 0x401
- Idx 18 / ID 0x501
- Idx 19 / ID 0x601
There are 2 devices with 765 and 365 - which must also be related to 000007xx vs 00003xx ( even though all the other ones end on 701 vs 301 ). And then there is plain 3 and 7. So you basically need to ignore the last 2 digits and only look at the 3rd digit to figure out which node it is.
I then checked the log, noticed it was now reporting "general /kWh meter", went back to verify the devices screen and there it was.
So basically I got the expected devices... yay.
One minor issue would be if there is an easy way to get rid of the bogus devices?
Re: Confused about which device belong to which zwave-node
With the delete icon located is upper left corner, below the "Show 25 entries" on the devices page
- vasco
- Posts: 11
- Joined: Monday 14 August 2017 9:09
- Target OS: Linux
- Domoticz version: 4.10717
- Location: Netherlands
- Contact:
Re: Confused about which device belong to which zwave-node
I just started with Domoticz but have a lot of experience with other domotica software and are totally agree with this.
I have nodes added in the hardware interface and name them but going to devices I can't find anything back here. Just a mess of undefined devices. This is a really big showstopper and if no changes here quickly than Domoticz was a short testrun for me. How on earth do I need to config all those devices without knowing what everything is.
And I have experience but people without are not going anywhere here at all with there Zwave devices.
I have nodes added in the hardware interface and name them but going to devices I can't find anything back here. Just a mess of undefined devices. This is a really big showstopper and if no changes here quickly than Domoticz was a short testrun for me. How on earth do I need to config all those devices without knowing what everything is.
And I have experience but people without are not going anywhere here at all with there Zwave devices.
- gizmocuz
- Posts: 2350
- Joined: Thursday 11 July 2013 18:59
- Target OS: Raspberry Pi / ODroid
- Domoticz version: beta
- Location: Top of the world
- Contact:
Re: Confused about which device belong to which zwave-node
Yes this needs work, please help if you can, it's an open source project!
Imagine that someone started from scratch... he/she added one node... goes to the devices tab... then it's easy to spot
Then adds a second.. sees the new devices... so that's easy...
Yes, if you start with 20 existing devices... it's a total mess... but try this
- lets assume your zwave hardware name has the text 'zwave' in it
- you made a screenshot of your node list (what you see when you press setup next to the openzwave hardware)
go to the devices overview, in the filter type 'zwave 02' .. this should list all devices for node 0x02 (and probably some more, but the deviceid should start with 02), or type 'zwave 002'
the number is the hex id
and yes, it needs work, but at least you now know how to find your devices... and yes, it is not easy!
Imagine that someone started from scratch... he/she added one node... goes to the devices tab... then it's easy to spot
Then adds a second.. sees the new devices... so that's easy...
Yes, if you start with 20 existing devices... it's a total mess... but try this
- lets assume your zwave hardware name has the text 'zwave' in it
- you made a screenshot of your node list (what you see when you press setup next to the openzwave hardware)
go to the devices overview, in the filter type 'zwave 02' .. this should list all devices for node 0x02 (and probably some more, but the deviceid should start with 02), or type 'zwave 002'
the number is the hex id
and yes, it needs work, but at least you now know how to find your devices... and yes, it is not easy!
Quality outlives Quantity!
- vasco
- Posts: 11
- Joined: Monday 14 August 2017 9:09
- Target OS: Linux
- Domoticz version: 4.10717
- Location: Netherlands
- Contact:
Re: Confused about which device belong to which zwave-node
If I could help I will but I cannot program at all.
Is it so difficult to add the name we give in the node list to the device list?
Is it so difficult to add the name we give in the node list to the device list?
-
- Posts: 24
- Joined: Thursday 21 January 2016 19:54
- Target OS: Windows
- Domoticz version:
- Contact:
Re: Confused about which device belong to which zwave-node
Hi all,
as Gizmocus already pointed out it's quite a mess and it is not easy to figure out which devices relate to specific zwave nodes. As far as I can see there are at least two possible solutions:
1a. minimum change in Domoticz to have at least relation between node and devices (1:n). This should be quite easy and small amount of work for guys already contributing to this project (btw, respect and many thanks). It only takes to make additional column in DeviceStatus to hold zwave nodeID and populate that column when device is either first added or updated (like last seen field, etc.) - few lines of code. What is the reason that this was not already done? Simply lack of time/contributors or is there some technical reason for it?
1b. once 1a is done it would be also nice to have functions like node replace (analogous to device's replace function), filtering,...Although device filtering to specific zwave node id in devices list could be a bit tricky as this GUI should be hardware independent and once you introduce zwave node id to it you loose that.
2. to at least clearly and in deterministic way someone clearly explains how the heck does device get ID as they do. For example how one device gets ID of 00000E02 and other 000E004 and yet another 000000E and they all come from same zwave node. Is the ID constructed by Domoticz in some way (how?), does it already come from OZW....This would greatly help many people, not to say that this would also allow point 1b doable without even have to do 1a. Or even allow writing down SQL for "manual node replace" functionality.
And once again, many thanks to guys contributing to the project.
as Gizmocus already pointed out it's quite a mess and it is not easy to figure out which devices relate to specific zwave nodes. As far as I can see there are at least two possible solutions:
1a. minimum change in Domoticz to have at least relation between node and devices (1:n). This should be quite easy and small amount of work for guys already contributing to this project (btw, respect and many thanks). It only takes to make additional column in DeviceStatus to hold zwave nodeID and populate that column when device is either first added or updated (like last seen field, etc.) - few lines of code. What is the reason that this was not already done? Simply lack of time/contributors or is there some technical reason for it?
1b. once 1a is done it would be also nice to have functions like node replace (analogous to device's replace function), filtering,...Although device filtering to specific zwave node id in devices list could be a bit tricky as this GUI should be hardware independent and once you introduce zwave node id to it you loose that.
2. to at least clearly and in deterministic way someone clearly explains how the heck does device get ID as they do. For example how one device gets ID of 00000E02 and other 000E004 and yet another 000000E and they all come from same zwave node. Is the ID constructed by Domoticz in some way (how?), does it already come from OZW....This would greatly help many people, not to say that this would also allow point 1b doable without even have to do 1a. Or even allow writing down SQL for "manual node replace" functionality.
And once again, many thanks to guys contributing to the project.
-
- Posts: 73
- Joined: Friday 09 October 2015 19:14
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Contact:
Re: Confused about which device belong to which zwave-node
+1 for this
it's the *one* thing that pisses me off with Domoticz...it's OK if you have half a dozen devices, a mess when you have 50!
I'm wondering if theres something preventing this from being implemented of if I can help in any way...
it's the *one* thing that pisses me off with Domoticz...it's OK if you have half a dozen devices, a mess when you have 50!
I'm wondering if theres something preventing this from being implemented of if I can help in any way...
-
- Posts: 5
- Joined: Friday 20 October 2017 8:33
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Contact:
Re: Confused about which device belong to which zwave-node
An acceptable first step in this would just be a clear explanation of the rules for how IDs are created. Because now it's really hard and pretty much a guessing game. Some have zero padding, some not, some have numbers on the end, some do not, some have different length of the zero padding
-
- Posts: 24
- Joined: Thursday 21 January 2016 19:54
- Target OS: Windows
- Domoticz version:
- Contact:
Re: Confused about which device belong to which zwave-node
Hi to all,
I've looked at source code a little bit and found out why no one explains how devices get theirs deviceID - because it's so damn complicated.
Anyway, I looked further how to solve problem of retaining all device info (logs) and/or not producing tons of new devices in case your device's zwave node ID change for any reason (replacement of dead module, firmware upgrade with exclusion and inclusion,...) - I'm not sure how safe and complete it is, but this worked for me on test device. Here are the steps:
1. exclude existing device or dead node with nodeID A from network. For node number use 4 digit hex number, so if your node number is 25, then A=0019
2. shutdown Domoticz completely
3. open sqlite editor and check what devices relate to node A with following SQL: select * from DeviceStatus where DeviceID like '%<A>%'
If you think returned devices are those of node A, then proceed to step 4, otherwise change condition in SQL
4. execute SQL to change deviceID to correspond to the new node your device will get when re-included in network in latter steps. It should be latest currently included + 1. Let say that number is B:
UPDATE DeviceStatus SET DeviceID = replace(DeviceID, '<A>', '<B>' ) WHERE DeviceID LIKE '%<A>%' and <possibly modied condition from above select>
5. run Domoticz and wait until all nodes are queried by OZW
6. include device to network
If all goes well, everything in Domoticz should stay as it was with old node, including, logs, scripts, etc. As I'm not an expert for Domoticz, results from steps above could not be perfect so I welcome experts to review them and propose corrections and improvements .
I've looked at source code a little bit and found out why no one explains how devices get theirs deviceID - because it's so damn complicated.
Anyway, I looked further how to solve problem of retaining all device info (logs) and/or not producing tons of new devices in case your device's zwave node ID change for any reason (replacement of dead module, firmware upgrade with exclusion and inclusion,...) - I'm not sure how safe and complete it is, but this worked for me on test device. Here are the steps:
1. exclude existing device or dead node with nodeID A from network. For node number use 4 digit hex number, so if your node number is 25, then A=0019
2. shutdown Domoticz completely
3. open sqlite editor and check what devices relate to node A with following SQL: select * from DeviceStatus where DeviceID like '%<A>%'
If you think returned devices are those of node A, then proceed to step 4, otherwise change condition in SQL
4. execute SQL to change deviceID to correspond to the new node your device will get when re-included in network in latter steps. It should be latest currently included + 1. Let say that number is B:
UPDATE DeviceStatus SET DeviceID = replace(DeviceID, '<A>', '<B>' ) WHERE DeviceID LIKE '%<A>%' and <possibly modied condition from above select>
5. run Domoticz and wait until all nodes are queried by OZW
6. include device to network
If all goes well, everything in Domoticz should stay as it was with old node, including, logs, scripts, etc. As I'm not an expert for Domoticz, results from steps above could not be perfect so I welcome experts to review them and propose corrections and improvements .
-
- Posts: 73
- Joined: Friday 09 October 2015 19:14
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Contact:
Re: Confused about which device belong to which zwave-node
Cool.
That's actually another question I have, not sure why the "replacement" option isn't available for Z-Wave devices...
That's actually another question I have, not sure why the "replacement" option isn't available for Z-Wave devices...
- jvdz
- Posts: 2189
- Joined: Tuesday 30 December 2014 19:25
- Target OS: Raspberry Pi / ODroid
- Domoticz version: 4.107
- Location: Netherlands
- Contact:
Re: Confused about which device belong to which zwave-node
I have actually done the same yesterday and looked for some logic to generate 2 Views in SQLLIte Expert Personal.
What I did was create this SQL which you can run against a saves domoticz DB. Open this saved database in SQLLIte Expert Personal, just copy& past it into the SQL TAB when you selected the Database and hit "Execute SQL".
EDIT: NEXT POST CONTAINS AN UPDATED VERSION... SO YOU MIGHT WANT TO SKIP THIS PART:
A couple of views will be created:
- ZwaveHardwareId -> will contain the ZWAVE Hardwareid(s) used for selection of the ZWAVE devices
- ZwaveNodes_Temp -> Adapted view of ZwaveNodes also containing the HEX value for the NodeID
- ZwaveDevicesAll -> All Devices found from the Domoticz Devices table
- ZwaveDevicesOK -> All Devices found from the Domoticz Devices table ordered on ZWAVEID and ZWAVE Name
- ZwaveDevicesWrong -> All Devices found from the Domoticz Devices table That have a none existing ZWAVEID, so are likely the remainders from removed ZWAVE devices.
Code: Select all
-- create table that contains the HardwareID's for ZWAVE
DROP VIEW IF EXISTS ZwaveHardwareID;
CREATE VIEW ZwaveHardwareID
AS
SELECT DISTINCT [HardwareID]
FROM [ZwaveNodes];
-- create table that contains all ZWAVE devices and the hex version of the NodeID to use for matching
DROP VIEW IF EXISTS ZwaveNodes_Temp;
CREATE VIEW ZwaveNodes_Temp
AS
SELECT [NodeId],
PRINTF ('%02X', [NodeId]) AS "U2",
[Name],
[HardwareID]
FROM [ZwaveNodes];
-- create table that contains all Domoticz devices that below to the HardwareID's for ZWAVE
DROP VIEW IF EXISTS ZwaveDevicesAll;
CREATE VIEW ZwaveDevicesAll
AS
SELECT DISTINCT [DeviceStatus].[DeviceID],
[DeviceStatus].[Id],
[DeviceStatus].[Used],
[DeviceStatus].[HardwareID],
[DeviceStatus].[Name],
[DeviceStatus].[nValue],
[DeviceStatus].[sValue],
[DeviceStatus].[LastUpdate]
FROM [DeviceStatus]
INNER JOIN [ZwaveHardwareID] ON [ZwaveHardwareID].[HardwareID] = [DeviceStatus].[HardwareID];
-- create table with Domoticz devices ordered by ZWAVE device
DROP VIEW IF EXISTS ZwaveDevicesOK;
CREATE VIEW ZwaveDevicesOK
AS
SELECT SUBSTR ([DeviceID], - 4, 2) AS "ZW-NodeID",
[ZwaveNodes_Temp].[Name] AS "ZW-Name",
SUBSTR ([DeviceID], 1, 4) AS "Dev",
SUBSTR ([DeviceID], - 2, 2) AS "Dev1",
[ZwaveDevicesAll].[Name] AS "Dev-Name",
[ZwaveDevicesAll].[Id] AS IDX,
[ZwaveDevicesAll].[Used],
[Hardware].[Id],
[Hardware].[Name] AS "H-Name",
[ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[nValue],
[ZwaveDevicesAll].[sValue],
[ZwaveDevicesAll].[LastUpdate]
FROM [ZwaveDevicesAll]
INNER JOIN [Hardware] ON [Hardware].[Id] = [ZwaveDevicesAll].[HardwareID]
INNER JOIN [ZwaveNodes_Temp] ON [ZwaveNodes_Temp].[HardwareID] = [ZwaveDevicesAll].[HardwareID] AND [ZwaveNodes_Temp].[U2] = SUBSTR ([DeviceID], - 4, 2)
WHERE SUBSTR ([DeviceID], - 4, 2) <> "00"
AND length([DeviceID]) > 5
UNION ALL
SELECT SUBSTR ([DeviceID], - 2, 2) AS "ZW-NodeID",
[ZwaveNodes_Temp].[Name] AS "ZW-Name",
SUBSTR ([DeviceID], 1, 4) AS "Dev",
"xx" AS "Dev1",
[ZwaveDevicesAll].[Name] AS "Dev-Name",
[ZwaveDevicesAll].[Id] AS IDX,
[ZwaveDevicesAll].[Used],
[Hardware].[Id],
[Hardware].[Name] AS "H-Name",
[ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[nValue],
[ZwaveDevicesAll].[sValue],
[ZwaveDevicesAll].[LastUpdate]
FROM [ZwaveDevicesAll]
INNER JOIN [Hardware] ON [Hardware].[Id] = [ZwaveDevicesAll].[HardwareID]
INNER JOIN [ZwaveNodes_Temp] ON [ZwaveNodes_Temp].[HardwareID] = [ZwaveDevicesAll].[HardwareID] AND [ZwaveNodes_Temp].[U2] = SUBSTR ([DeviceID], - 2, 2)
WHERE [Hardware].[Name] = "ZWAVE"
AND SUBSTR ([DeviceID], - 4, 2) = "00"
AND length([DeviceID]) > 5
UNION ALL
SELECT SUBSTR (PRINTF ('%08X', [DeviceID]), - 4, 2) AS "ZW-NodeID",
[ZwaveDevicesAll].[Name] AS "Dev-Name",
SUBSTR (PRINTF ('%08X', [DeviceID]), 1, 4) AS "Dev",
"yy" AS "Dev1",
[ZwaveNodes_Temp].[Name] AS "ZW-Name",
[ZwaveDevicesAll].[Id] AS IDX,
[ZwaveDevicesAll].[Used],
[Hardware].[Id],
[Hardware].[Name] AS "H-Name",
[ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[nValue],
[ZwaveDevicesAll].[sValue],
[ZwaveDevicesAll].[LastUpdate]
FROM [ZwaveDevicesAll]
INNER JOIN [Hardware] ON [Hardware].[Id] = [ZwaveDevicesAll].[HardwareID]
INNER JOIN [ZwaveNodes_Temp] ON [ZwaveNodes_Temp].[HardwareID] = [ZwaveDevicesAll].[HardwareID] AND [ZwaveNodes_Temp].[U2] = SUBSTR (PRINTF ('%08X', [DeviceID]), - 4, 2)
WHERE length([DeviceID]) < 6
ORDER BY [ZW-NodeID],
[Dev];
-- create table with devices that don't below to any ZWAVE device anymore
DROP VIEW IF EXISTS ZwaveDevicesWrong;
CREATE VIEW ZwaveDevicesWrong
AS
SELECT [ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[Name] AS "Dev-Name",
[ZwaveDevicesAll].[Id] AS IDX,
[ZwaveDevicesOK].[Idx] AS OKIDX,
[ZwaveDevicesAll].[Used],
[ZwaveDevicesAll].[DeviceID],
[ZwaveDevicesAll].[nValue],
[ZwaveDevicesAll].[sValue],
[ZwaveDevicesAll].[LastUpdate]
FROM [ZwaveDevicesAll]
LEFT JOIN [ZwaveDevicesOK] ON [ZwaveDevicesOK].[Idx] = [ZwaveDevicesAll].[ID]
WHERE [ZwaveDevicesOK].[Idx] is null
ORDER BY [IDX]
Shoot when you have questions/comments/improvenents.
Jos
EDIT: Added nValue;sValue and LastUpdate to the Views
Last edited by jvdz on Sunday 24 December 2017 11:41, edited 1 time in total.
New Garbage collection scripts: https://github.com/jvanderzande/GarbageCalendar
- jvdz
- Posts: 2189
- Joined: Tuesday 30 December 2014 19:25
- Target OS: Raspberry Pi / ODroid
- Domoticz version: 4.107
- Location: Netherlands
- Contact:
Re: Confused about which device belong to which zwave-node
Have been playing some more with this idea and now have a SQL set of statements that create a single new table called "_ZwDevAll" which will contain all needed information in one table:
Enjoy,
Jos
New SQLCode which you can add to a BAckup copy of your domoticz.db like shown in the previous post:
Code: Select all
--------------------------------------------------------------------------------------------------------------------------
-- create temp table that contains all Domoticz devices that belong to the HardwareID's for ZWAVE
--------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS _ZWTempAll;
CREATE TABLE _ZWTempAll
AS
SELECT DISTINCT
"??" AS "ZW-ID",
"??" AS "ZW-Name",
[DeviceStatus].[Id] AS "D-IDX",
[DeviceStatus].[Name] AS "D-Name",
[DeviceStatus].[Used] AS "D-Used",
[DeviceStatus].[nValue],
[DeviceStatus].[sValue],
[DeviceStatus].[LastUpdate],
"?" AS "Sel",
[DeviceStatus].[DeviceID],
"?" AS "HexDevId",
[DeviceStatus].[HardwareID]
FROM [DeviceStatus]
INNER JOIN [ZwaveNodes] ON [ZwaveNodes].[HardwareID] = [DeviceStatus].[HardwareID]
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table with ZWAVE ID for DeviceIDs that contain the ZWAVE ID in a dec DeviceID which will be converted to Hex
--------------------------------------------------------------------------------------------------------------------------
UPDATE _ZWTempAll
SET [HexDevId] = PRINTF ('%08X', [DeviceID]),
[Sel] = "a"
WHERE length([DeviceID]) < 7
;
UPDATE _ZWTempAll
SET [HexDevId] = [DeviceID],
[Sel] = "b"
WHERE length([DeviceID]) > 6
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table with ZWAVE ID for DeviceIDs that contain the ZWAVE ID in the first 2 of the last 4 characters
--------------------------------------------------------------------------------------------------------------------------
UPDATE _ZWTempAll
SET [ZW-ID] = SUBSTR ([HexDevId], - 4, 2),
[Sel] = "1"
WHERE SUBSTR ([HexDevId], - 4, 2) <> "00"
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table with ZWAVE ID for DeviceIDs that contain the ZWAVE ID in the last 2 characters
--------------------------------------------------------------------------------------------------------------------------
UPDATE _ZWTempAll
SET [ZW-ID] = SUBSTR ([HexDevId], - 2, 2),
[Sel] = "2"
WHERE SUBSTR ([HexDevId], -4, 2) = "00"
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table with ZWAVE NAME for the ZWAVE device
--------------------------------------------------------------------------------------------------------------------------
With a AS
(
SELECT [ZwaveNodes].[Name] AS "ZW-Name",
[_ZWTempAll].[D-IDX]
FROM [_ZWTempAll]
INNER JOIN [ZwaveNodes] ON [ZwaveNodes].[HardwareID] = [_ZWTempAll].[HardwareID] AND PRINTF ('%02X', [ZwaveNodes].[NodeId]) = [ZW-ID]
)
UPDATE _ZWTempAll
SET [ZW-Name] = (SELECT "ZW-NAME" FROM a WHERE [a].[D-IDX] =[_ZWTempAll].[D-IDX] )
WHERE
EXISTS (
SELECT *
FROM a
WHERE [a].[D-IDX] = [_ZWTempAll].[D-IDX]
)
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table for OLD ZWAVE devices that aren't linked to an existing ZWAVE device anymore.
--------------------------------------------------------------------------------------------------------------------------
UPDATE _ZWTempAll
SET [ZW-Name] = "*** Not Found ***",
[Sel] = "*Err*"
WHERE [_ZWTempAll].[ZW-Name] = "??"
;
-- create sorted table which contains all ZWAVE devices and their ZWAVE device info sorted on ZW-ID and IDX
DROP TABLE IF EXISTS _ZWDevAll;
CREATE TABLE _ZWDevAll
AS
SELECT *
FROM [_ZWTempAll]
Order by [ZW-ID],[D-IDX]
;
-- remove temp table
DROP TABLE IF EXISTS _ZWTempAll;
Jos
New Garbage collection scripts: https://github.com/jvanderzande/GarbageCalendar
- jvdz
- Posts: 2189
- Joined: Tuesday 30 December 2014 19:25
- Target OS: Raspberry Pi / ODroid
- Domoticz version: 4.107
- Location: Netherlands
- Contact:
Re: Confused about which device belong to which zwave-node
Guess I have too much time on my hands.
...so was playing with file /www/app/DevicesController.js, which generates the Domoticz devices webpage.
Added a few lines of code to extract the ZWave DeviceID from the DeviceID and concatenated that to the Hardware name to allow for sorting in the Hardware name to group all devices by ZWave device. This is actually a pretty simple modification to accomplish what is wanted here.
This is the diff for the file in case you are interested to play and comment on this:
Jos
...so was playing with file /www/app/DevicesController.js, which generates the Domoticz devices webpage.
Added a few lines of code to extract the ZWave DeviceID from the DeviceID and concatenated that to the Hardware name to allow for sorting in the Hardware name to group all devices by ZWave device. This is actually a pretty simple modification to accomplish what is wanted here.
This is the diff for the file in case you are interested to play and comment on this:
Code: Select all
+++ b/www/app/DevicesController.js
@@ -384,6 +384,13 @@ define(['app'], function (app) {
itemSubIcons += ' <img src="images/empty16.png">';
}
var ID = item.ID;
+ if (item.HardwareTypeVal != undefined && item.HardwareTypeVal == 21) {
+ var ZWID = item.ID.substr(-4, 2);
+ if (ZWID == '00') {
+ ZWID = item.ID.substr(-2, 2);
+ }
+ item.HardwareName = item.HardwareName + ' ' + ZWID;
+ }
if (item.Type == "Lighting 1") {
ID = String.fromCharCode(item.ID);
}
Last edited by jvdz on Monday 01 January 2018 18:06, edited 2 times in total.
New Garbage collection scripts: https://github.com/jvanderzande/GarbageCalendar
-
- Posts: 4
- Joined: Friday 29 December 2017 22:21
- Target OS: Raspberry Pi / ODroid
- Domoticz version: 3.8777
- Location: Norway
- Contact:
Re: Confused about which device belong to which zwave-node
I did the mod on my installation, and it looks handy. I replaced the underscore with a whitespace because I think that looks cleaner.
You should send a pull request on this, it would be nice to have as a default feature.
Edit:
When using whitespace the search does not work, so the underscore must be there.
You should send a pull request on this, it would be nice to have as a default feature.
Edit:
When using whitespace the search does not work, so the underscore must be there.
- Attachments
-
- domoticz z-wave id.PNG (5.29 KiB) Viewed 7438 times
- jvdz
- Posts: 2189
- Joined: Tuesday 30 December 2014 19:25
- Target OS: Raspberry Pi / ODroid
- Domoticz version: 4.107
- Location: Netherlands
- Contact:
Re: Confused about which device belong to which zwave-node
Modified the code a little to test for "item.HardwareTypeVal == 21" in stead of text and created a Pull request.
Happy New Year,
Jos
Happy New Year,
Jos
New Garbage collection scripts: https://github.com/jvanderzande/GarbageCalendar
-
- Posts: 1355
- Joined: Friday 29 August 2014 11:26
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Location: Ermelo
- Contact:
Re: Confused about which device belong to which zwave-node
Maybe you can convert it to decimal as the zwave control panel and domoticz list also shows decimals?
Creator dzVents - RPi3, loads of zwave devices, esp8266, evohome.
- jvdz
- Posts: 2189
- Joined: Tuesday 30 December 2014 19:25
- Target OS: Raspberry Pi / ODroid
- Domoticz version: 4.107
- Location: Netherlands
- Contact:
Re: Confused about which device belong to which zwave-node
Thanks for your suggestion!
Reason I've put it on Hex was to easily relate to the DeviceID as that contains the hex code, but am actually fine either way.
Another option could also be to show it as the ZWAVE hardware panel does which is "Z-Wave 013 (0x0D)".
Let me know what the preferences are out there and can adapt it accordingly.
Jos
New Garbage collection scripts: https://github.com/jvanderzande/GarbageCalendar
-
- Posts: 1355
- Joined: Friday 29 August 2014 11:26
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Location: Ermelo
- Contact:
Re: Confused about which device belong to which zwave-node
Yeah, that's fine with me.
Creator dzVents - RPi3, loads of zwave devices, esp8266, evohome.
Who is online
Users browsing this forum: No registered users and 1 guest