After a few months of extensive system expansion... My WAF


#1

Spent the last few months, staying on the same firmware version to keep the system working 100%. All applications and rules were solid , while I pursued significant system expansion. All is working well afterwards.

Built a few extremely configurable setups, that took fairly little time using Node Red and SQL Server.

National Weather Broadcast repeater system that echos alerts via TTS announcement on the Sonos system, and Pushover Notifications to my devices. TTS Alerts are minimized to new alerts, and alerts that were updated by status or severity. The system self clears, and stored all Alerts in historical records if desired.

Fully graphed WeatherStation data using Weatherflow. All data stored in SQL database. (real-time and historical) Tablet monitoring of historical plots using Grafana.

IQ Air Visual Pro and API to ascertain Air Quality Index (indoors and outdoors)
System makes suggestions about using HVAC or whole house fan to maximize comfort and save $

All Data WorkFlows: Hubitat -> Maker API -> Node Red -> API -> Maker Api -> Hubitat

Implemented using : Hubitat, NodeRed, MS SQL Server and a LOT of open API.

If anyone is interested, I am willing to share everything.

Matt


#2

Where's the WAF in all that?


#3

I am writing it up now... LOL


#4

Never got to finish my previous post, had to take the dogs for a walk.

As usual the WIFE has never really been excited any of the home automation projects. She is always making comments ... "It was just, nice, when we used light switches" "can your electronic girlfriend talk less?" LOL. :stuck_out_tongue: "do you remember when the house was just quiet..?"
"your electronic girlfriend was just complaining about something".

To even further reduce the WAF. She cannot tell when she is instructing Hubitat or Alexa to do something, so unfortunately Hubitat usually is at fault in her mind. When I try and politely remind her of which services are handled by device, she is less than interested to hear the facts. LOL
Usually Alexa can't decipher her request, or the wife uses the wrong verbiage. :wink:

She was appreciative of the washer and dryer completion cycle announcement today.

Maybe they WAF is flattening out to a mild dislike. :sunny:


#5

Oh man! You're in for it if that ever breaks. Better CYA. Hope you have that all on a UPS.


#6

I hear what you are saying etarding the WAF and I recognize a similar situation in my home :slight_smile:

But I would surely like to know more about your Grafana setup. From what I have read in other threads this usually causes instability so I haven’t wanted to go that route yet. I do however want to have some logging.. would be interested to hear more about your setup.


#7

FWIW, my WAF is high for automation, and low for TTS, and neither the Mrs nor I can stand Voice Control, so the WAF for that is effectively Zero.

Best bang for the WAF buck in my house is automated stairwell lighting, and Luminance based lighting in our Living areas.

She's also a big fan of ceiling fan automations, and the door contact based closet lighting was a big hit.

Conceptually, shes also a fan of water sensors for basement flooding alerts, but until I get around to setting up the Push alerts for that ....

Best of luck in raising the WAF!


#8

WAF was sky-high on the laundry announcer. It was boosted by the driveway sensor here

I have to keep reminding her that it's the background stuff where HE really pulls its weight --water heater timers, motion detection, leak detection. But she'd buy it just for the laundry notification. (But we need a mesh, so I'm home free on all the other devices.)


#9

My hubby is all about automation as long as he doesn't have to do anything with it. So .. I made that happen. We ask Alexa to do absolutely nothing. Nada. Zilch. Everything is controlled with motion, illuminance, presence, diy smart phone docks and harmony integration. And paying attention to our habits. I can't get that Alexa woman to do anything for me, but he can ask her for a light from time to time and she woo's him. I'm the one that set it up! I think she resents me. Oh well. It works for us.


#10

This is only the case for some using the Influxdb logger app. Many including myself have been using Node Red to tap into the HE websocket with no I'll effect for months. It does require another device (raspberry pi or pc) to run node red.

See this thread for details.


#11

I have not had any system instability using Node Red with Grafana or Maker API

Node Red collects all Weather data from WeatherFlow (UDP port monitoring)
Node Red calls Maker API and pushes all data into Hubitat (all measurements / 1x minute)
Node Red pushes all weather data into SQL database for Grafana and historical record


After implementing this: Hubitat > NodeRed > MySQL > Grafana (LONG READ)

I extended the functionality to measure the response times of Input to Output devices (motion sensor -> light) , which is then plotted using Grafana. It's not perfect, but pretty good. I defined all of the rules by input to output device relationships (using MakerAPI Device IDs) that I want to monitor via event websocket data in the database tables.

At this time I measure response times of the output devices when the inputs are triggered

All websocket event data if immediately pushed into SQL
Approx once / minute, SQL stored procedure is triggered to analyze the data.
When an input device trigger is found in the event traffic, I find the corresponding output device defined by the relationship tables.

The measured response time is a bit tricky to calculate when multiple input to output devices exist for a single rule, but can be accomplished using a Full Outer Join

     e.g.  Motion Sensor #1,2 and 3  trigger 4 bulbs in my den.

USE [hubitat]
GO
/****** Object:  StoredProcedure [dbo].[usp_MeasureDevicePairResponseTimeSeconds]    Script Date: 09/10/2019 09:28:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Matthew Klueppel
-- Create date: 8/25/2019
-- Description:	Calculates device trigger to reponse times(seconds)
-- =============================================
--	History
--  V1.0	MattK	8/25/2019	Original Release
--  Query All Loaded Rule Pairs
--  Query ALL Input Devices linked to rule pairs4
--  Query ALL Output Devices linked to rule pairs
--  Calculate ALL response times witnessed and store

ALTER PROCEDURE [dbo].[usp_MeasureDevicePairResponseTimeSeconds]
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	--declare temp variables 
		DECLARE @RuleCursor_PKID INT
		DECLARE @PairCusor_PKID INT
		DECLARE @5MinutesAgo datetime2
		DECLARE @InputDevice_ID int
		DECLARE @OutputDevice_ID int
		DECLARE @InputDeviceTriggeredAt datetime2
		DECLARE @OuputDeviceResponsedAt datetime2
		DECLARE @RuleID INT
		DECLARE @FirstActiveTime datetime2
		DECLARE @FirstResponseTime datetime2
		DECLARE @FirstActiveTimeDeviceID int
		DECLARE @FirsResponseTimeDeviceID INT
		DECLARE @NumberFirstActiveTimeRows int
		DECLARE @NumberFirstResponseTimeRows int
		DECLARE @SampleTime int 
		
		
	--inits
		SET @SampleTime  = -2
	
	--create temp tables for all measurement calculations
	
			CREATE TABLE #UniqueRules
			(	pkid INT IDENTITY(1,1) NOT NULL,
				fk_RuleID int
						PRIMARY KEY CLUSTERED ( pkid ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]	
	
	
			CREATE TABLE #TimingPairs
			(	pkid INT IDENTITY(1,1) NOT NULL,
				fk_RuleID int,
				fk_InputDeviceID int,
				fk_OutputDeviceID int,
				TriggeredOn datetime,
				RespondedOn datetime,
				ResponseLag decimal(18,3)
						PRIMARY KEY CLUSTERED ( pkid ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]	
		
	
	
			CREATE TABLE #InputDeviceTriggers
			(	pkid INT IDENTITY(1,1) NOT NULL,
				fk_RuleID int,
				fk_DeviceID int,
				value nvarchar(2500),
				TriggeredOn datetime
						PRIMARY KEY CLUSTERED ( pkid ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]	
	
	
			CREATE TABLE #OutputDeviceResponses
			
			(	pkid INT IDENTITY(1,1) NOT NULL,
				fk_RuleID int,
				fk_DeviceID int,
				value nvarchar(2500),
				RespondedOn datetime
						PRIMARY KEY CLUSTERED ( pkid ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]	
							
	
	
			CREATE TABLE #RulesPairedDevices
			
			(	pkid INT IDENTITY(1,1) NOT NULL,
			
				fk_RuleID int,
				fk_InputDeviceID int,
				InputDeviceDescr nvarchar(100),
				fk_OutputDeviceID int,
				OutputDeviceDescr nvarchar(100),
				fk_RuleType int,
				RuleTtpeDescr nvarchar(50),
				RuleName nvarchar(100),
				RuleDesc nvarchar(250),
				TriggeredOn datetime,
				RespondedOn DATETIME,
				MeasuredDeviceResponseTimeSeconds decimal(18,2)
				
						PRIMARY KEY CLUSTERED ( pkid ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
						) ON [PRIMARY]
						
						
						
						
						
			
		
		
		INSERT INTO #RulesPairedDevices
			(fk_RuleID ,
			fk_InputDeviceID ,
			InputDeviceDescr ,
			fk_OutputDeviceID ,
			OutputDeviceDescr ,
			fk_RuleType ,
			RuleTtpeDescr ,
			RuleName ,
			RuleDesc )

				--Query all registered device pairs
					SELECT 
						R.pkid,				--RuleID,
						ID.DeviceID,		--Input Device ID,
						ID.DeviceDescr,		-- Input Device Desc
						OD.DeviceID,		--Output Device ID
						OD.DeviceDescr,		--Output Device Desc
						RT.pkid,			--RuleType ID
						RT.RuleDescription,	--RuleType Description
						R.ruleName,			--Rule Name
						R.ruleDesc			--Rule Description
						
						
					FROM tbl_Rules R
					INNER JOIN tbl_RuleTypes RT on RT.pkid = R.ruletypeID
					INNER JOIN tbl_InputDevices ID on ID.RuleID = R.pkid
					INNER JOIN tbl_OutputDevices OD on OD.RuleID= R.pkid





		--Retrieve input for all input/outputs devices for registered rules
		INSERT INTO #UniqueRules
			(fk_RuleID)

			SELECT DISTINCT(pkid)
			FROM tbl_Rules




select 'All Unique Rules#'
Select * 
	FROM #UniqueRules U
	INNER JOIN tbl_Rules R on R.pkid = U.fk_RuleID
	

	--Iterate across all rules

		SELECT TOP 1 @RuleCursor_PKID   = fk_RuleID
		FROM #UniqueRules
		ORDER BY fk_RuleID

		--set Capture Window time
		set @5MinutesAgo = DATEADD(MINUTE,@SampleTime,(GETDATE()))	
		
			--select @5MinutesAgo		


		WHILE (@@ROWCOUNT <> 0)
		BEGIN
							--Clear the table
								--DELETE  FROM #RulesPairedDevices

									SELECT TOP 1 @PairCusor_PKID = pKID
									FROM #RulesPairedDevices
									WHERE fk_RuleID = @RuleCursor_PKID
									ORDER BY pKID



									WHILE (@@ROWCOUNT <> 0)
									BEGIN
									

													  --TODO:   Calculate Something

															SELECT @RuleID =fk_RuleID,
																	@InputDevice_ID = RP.fk_InputDeviceID,
																	@OutputDevice_ID =RP.fk_OutputDeviceID
															
															FROM #RulesPairedDevices RP
															WHERE	
																	pkid = @PairCusor_PKID


													--Clear the table
															DELETE	FROM #InputDeviceTriggers

															--All Active/Inactive Events records for (ALL input devices on this rule)
																INSERT INTO #InputDeviceTriggers
																	(fk_RuleID,fk_DeviceID ,value ,TriggeredOn )
																	
																	SELECT 
																	
																		@RuleID ,
																		[deviceId],
																		[value],
																		[created]
																	
																	FROM [hubitat].[dbo].[events]
																	
																	WHERE 
																	
																		[deviceId] = @InputDevice_ID  and 
																		[value] = 'active'  and
																		Created >= @5MinutesAgo
																		
																	ORDER BY row_id desc
																	

																	
													
															
															
															
										  --TODO:   Calculate Something
											
											  SELECT TOP 1 @PairCusor_PKID  = pKID
											  FROM #RulesPairedDevices
											  WHERE fk_RuleID = @RuleCursor_PKID and pKID > @PairCusor_PKID 
											  ORDER BY pKID

										END


													--Clear the table
															DELETE	 FROM #OutputDeviceResponses

																INSERT INTO #OutputDeviceResponses
																	(fk_RuleID,fk_DeviceID ,value ,RespondedOn )
																	
																	SELECT 
																		@RuleID ,
																		[deviceId],
																		[value],
																		[created]
																	FROM [hubitat].[dbo].[events]
																	WHERE 
																		[deviceId] = @OutputDevice_ID  and 
																		[value] = 'on'  AND 
																		created > @5MinutesAgo
																	ORDER BY row_id desc



															
																SELECT 'Input Device Table'
																	select 
																		fk_RuleID, 
																		fk_DeviceID,
																		value,
																		TriggeredOn
																	From #InputDeviceTriggers
																	ORDER by TriggeredOn DESC
																	
																SELECT 'Output Device Table'
																	select 
																		fk_RuleID, 
																		fk_DeviceID,
																		value,
																		RespondedOn
																	FROM #OutputDeviceResponses
																	ORDER by RespondedOn DESC
																	

																						

																--Capture the Count for Number of Input and Output devices
																	SET @NumberFirstActiveTimeRows = (SELECT COUNT(*)FROM #InputDeviceTriggers) 
																	SET @NumberFirstResponseTimeRows = (SELECT COUNT(*) FROM #OutputDeviceResponses)

																

																if ((@NumberFirstActiveTimeRows = 0 OR @NumberFirstResponseTimeRows = 0))
																	BEGIN
																		SELECT 'No Matched Timing Pairs Found!'
																	END
																else
																
																	BEGIN
																	
																			DELETE FROM #TimingPairs
																			
																	
																			INSERT INTO #TimingPairs	
																				 (fk_RuleID,fk_InputDeviceID ,fk_OutputDeviceID ,TriggeredOn ,RespondedOn ,ResponseLag  )
																	
																	
																				--Fully Calculate all input to output timing paths.  This will alwas find the FASTEST Devices					
																				SELECT @RuleID, I.fk_DeviceID,O.fk_DeviceID,I.TriggeredOn,O.RespondedOn, DATEDIFF(microsecond,I.TriggeredOn ,O.RespondedOn)/1000000.000 as ResponseLagTime
																				FROM #InputDeviceTriggers I
																				FULL OUTER JOIN #OutputDeviceResponses O on I.fk_RuleID = O.fk_RuleID
															
																		SELECT 'Show all timing pairs'
																		SELECT *
																		FROM #TimingPairs

																	
																		SELECT 'Found Timing Pair'
																		SELECT TOP 1 *
																		FROM #TimingPairs
																		WHERE 
																			ResponseLag   =(SELECT MIN(TP.ResponseLag) 
																						    FROM #TimingPairs TP
																						    WHERE ResponseLag>=0)
																		
																		
																		
																				
																		
																		INSERT INTO tbl_RuleResponseTimes
																			(RuleID, ResponseTimeMeasured,TriggeredTime,ResponseTime,Trigger_DeviceID,Response_DeviceID)
																		
																		SELECT @RuleID,
																				TP.ResponseLag,
																				Tp.TriggeredOn,
																				Tp.RespondedOn,
																				Tp.fk_InputDeviceID,
																				Tp.fk_OutputDeviceID
																				
																		FROM #TimingPairs TP
																		WHERE 
																			TP.ResponseLag   =(SELECT MIN(TP1.ResponseLag) 
																						    FROM #TimingPairs TP1
																						    WHERE TP1.ResponseLag>=0)		
																				
	
																		
																	END
																



					--Move to next registered pair/rule

			  --TODO:   Calculate Something
				
				  SELECT TOP 1 @RuleCursor_PKID  = fk_RuleID
				  FROM #UniqueRules
				  WHERE fk_RuleID > @RuleCursor_PKID 
				  ORDER BY fk_RuleID

			END



END