ZWave Routing Report

oliver9 · August 24, 2020, 11:43pm

I created this little spreadsheet that converts the Z-Wave Radio Devices table

into a more readable report:

You need to have at least firmware 2.2.3.119 installed as the routing information isn't listed in the Z-Wave Radio Devices page in prior firmwares.

If you are using "Hub Login Security", you need to turn it temporarily off to create the report. Otherwise the spreadsheet can't read the information needed.

I tried to use Excel's "Basic Authentication", but it appears that Hubitat is using a different method of securing the hub. If anybody has some insight to this, feel free to contact me and I will try to implement it.

The current version of my spreadsheet is limited to 3 hops as that is what I have on my network, but you can expand it to more if needed. See below for instructions.

After you download the spreadsheet you will need to update the IP address of where to pull the report from. Here is how:

Select "Data" | "Show Queries".
If you don't see this tab on your Ribbon, then you need to install Power Query from Microsoft.

Double click "IP"

Update the IP address with the one of your hub

You can find it by looking at the IP address when you access your hub using your browser

Select "Close & Load" to generate your report

You can sort the resulting report by select "Data" | "Sort". Just make sure your cursor is located within the data to be sorted.

The report will automatically refresh when you first open the spreadsheet, but you can also refresh it manually, by selecting "Data" | "Refresh All"

If you have more than 3 hops than you'll need additional columns in the spreadsheet. Here is how:

Copy I2 to J2 (using copy/paste)

In J2, change "ID3" in the formula
=IFNA(VLOOKUP([@ID3],Table_0[[ID]:[Device]],2,FALSE),"")
to match the name of the column that has been created for the additional hop (column F).

My apologies for the lacking screenshots for the last steps. Also not quite sure what happens if you have less than 3 hops. Feel free to send me your "Z-Wave Details.html" file (select "Save As ..." using your browser) and I'll try to figure out how to deal with this variable. If someone has more experience dealing with these dynamic data sources in Excel and knows a solution, I'll be more than happy to implement them.

BPKH · August 25, 2020, 1:42am

Amazing write up! Ran into issues at first due to having security enabled; so disable login security if you want to try it out!

mark.amber · August 25, 2020, 4:29am

I'd love to try this out, but I don't see "Show Queries" under the Data Menu. Where else can I find the place to edit the IP source address for the query?

njanda · August 25, 2020, 4:44am

Very interesting stuff man.
2 questions, if I may ?

What do you mean by "legs" ? (is that the number of repeaters or something ?)
Will this only work with the C7 Hubs ?

Cheers,

oliver9 · August 25, 2020, 5:22am

It looks like you are missing the "Get & Transform" tab on your Ribbon

You should be able to get it back by right clicking the Ribbon and then select "Customize the Ribbon"

From there select the "Data" Tab, then select "Reset" | "Reset Only Selected Ribbon Tab". Hopefully that will put the missing tab into your Ribbon.

I am using Excel 2016. If you are using a different version, the options may be slightly different.

oliver9 · August 25, 2020, 5:30am

That's the number of connections a device has to make to reach the hub.

If you look at your "Settings" | "Z-Wave Details" you can see those "connections" by looking at the "Route" column and see how many "->" you have listed there for the different devices. In this example there are 3 "legs" or "connections between my device "WiFi" and my hub.

I have no idea. All I ever owned was a C7. If your "Z-Wave Details" page looks like my example above, then it should work.

njanda · August 25, 2020, 5:35am

Ok, thanks, I get it.
This will only work for C7 hubs as the earlier releases do not have this latest Z wave data, available.
FYI: I've not seen "legs' used before and have been more used to seeing "hops" or "repeaters" used so it's good to have that clear now.
Cheers,

oliver9 · August 25, 2020, 5:39am

Thanks for the insight. I have updated my post accordingly.

mark.amber · August 25, 2020, 5:49am

I'm using Excel 2013 and it doesn't appear to have "Get & Transform". I wonder if I need to add it as an optional feature? (modify/reinstall)

UPDATE: a little Googling reveled that Get & Transform is called "Power Query" in Excel 2010 and 2013. Its a downloadable Add-In from Microsoft. After intstalling Power Query, there is a new Ribbon Menu that let me change the Data Source settings. I updated that with my Hubitat IP address and seems to be working now

oliver9 · August 25, 2020, 6:12am

I'm assuming you did try the "Reset Only Selected Ribbon Tab" option

There should be a way to edit the query even in Excel 2013.

Try to select "Connections" | "Properties" | "Definition" | "Edit Query"

The "Edit Query" is grayed out on mine, but maybe it's an option on yours.

mark.amber · August 25, 2020, 6:20am

See the update to my post right before yours (I must have been updating it while you were typing your reply)

Excel 2010 and 2013 need the free Add-In called Power Query. Slightly different menus but it seems to be working. Thanks for helping and sharing

Mr.Olsen · August 25, 2020, 12:32pm

Great work, thank you!

oliver9 · August 25, 2020, 5:30pm

I simplified the editing of the IP address by making it a parameter. I updated the original instructions accordingly and also added information shared by other users. Thanks to @BPKH, @mark.amber, @njanda for the input.

Panda · September 24, 2020, 6:19am

It’s very cool. Does anyone know if Google Sheets would support this?

Panda · October 3, 2020, 3:02pm

In looking at it more I don’t think Google Sheets will work the same since it’s in the cloud and not on the same subnet. That means the auto import will fail but I was thinking I could save a copy of the zwave html page to Google Drive and then update the sheet.

More importantly, I’m trying to visualize the hops these devices take since I have a mix of Zwave and Zwave plus devices. You can tell if the switch is a zwave plus from the cluster so was thinking to decode the route column from this:

01 -> 0B -> 20 -> 1E -> 14 40kbps

To something more readable by using the device name and color coding the name to the type of repeater it’s hitting.

Probably beyond my hack skills so if anyone has seen this done I’d appreciate the lead.

oliver9 · October 3, 2020, 5:03pm

I was always wondering what the "Clusters" is. If you can give me some more information, I'll try to implement that information into the report.

Panda · October 3, 2020, 6:07pm

If it starts with 0x5E it’s a zwave plus device.

I’m on a Mac though and I can’t figure out how to get to your IP address parameter to use it directly with my version of excel. Power query requires windows from what I could tell.

oliver9 · October 3, 2020, 8:33pm

I don't think I can use the "Conditional Formatting" in these type of queries. That's what would be necessary to change the color of the text (or cell).

I could add another column that indicates if the device is a repeater or not.

Are you aware of any documentation of that "Clusters" information?

Unfortunately, I have zero experience with Mac.

Panda · October 3, 2020, 9:13pm

On one of my other posts I was sent some detailed specs so let me check that.

Understand the Mac issue. That’s why I was looking to make a platform agnostic one via google sheets.

Mr.Olsen · October 5, 2020, 12:34pm

It's the Z-Wave Command Classes.
Looking for the term"List-of-defined-Z-Wave-Command-Classes" with your search engine of choice will let you find what you need.