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
