DataYours/EventWatcher Data Warehouse to Azure SQL Server for Data Mining

AgileHumor · March 12, 2014, 6:04pm

I wanted a method to store my home automation history for future analyses in developing patterns.

Much to my delight, @akbooer made the awesome solution (which is also an in progress replacement to DataMine) called DataYours.
http://forum.micasaverde.com/index.php/topic,23109.msg157687.html

He also developed a simple logging app EventWatcher:
http://forum.micasaverde.com/index.php/topic,16984.0.html

This project extends the the EventWatcher and DataWatcher data collectors and store that data in the cloud. It does that by using the EventWatcher/DataWatcher Syslog feature to take the single string of data and and parse it into a structured SQL table that will be easy to analyze historical data trends using tools like MS Excel. This allows me to store an unlimited amount of data, off the Vera, for large term data mining trends. I get about 1,500 to 2,500 data points an hour which is about 5MB a day in SQL uncompressed database size.

Technically, it works by taking any Syslog server that can store the data in a backend SQL database, in my case, it’s stored in the SysLogD table and the MsgText column…and parsing the single string it to the structured data table layouts shown below:

Table EventWatcher
Syslog: Mar 11 18:31:45 Vera-30010199 EventWatcher: K [046] Thermostat, CurrentTemperature = 70
To Columns:

HA Unit (new colum): Vera-30010199
DeviceId: 46
DeviceName: Thermostat
Variable: CurrentTemperature
Value: 70
Class: K
Category: Table Lookup match EventWatcher.Class to CategoryKey.Class
Room: Table Lookup match EventWatcher.Class to DeviceKey.CategoryKey

Table DataWatcher
Syslog: Mar 13 13:30:14 Vera-30010199 DataWatcher: Vera-30010099.449.urn:micasaverde-com:serviceId:EnergyMetering1.Watts 65 1394742614
To Columns:

HA Unit (new colum): Vera-30010099
DeviceID: 449
ServiceURL (new colum): micasaverde-com
ServiceType (new colum): EnergyMetering1
Variable: Watts
Value: 65
Device Name: Table Lookup match DataWatcher.DeviceID to DeviceKey.Device Name
Device Type: Table Lookup match DataWatcher.DeviceID to DeviceKey.Device Name
Room: Lookup match DataWatcher.DeviceID to DeviceKey.Room

Table Device Lookup

DeviceID
Parent - %Create but Ignore
Device Name
Room
Battery - %Create but Ignore
Device Type
Alt ID - %Create but Ignore

Table CategoryKey Lookup

Class: K
Device Type: TemperatureSensor

To do this, I has a copy of Kiwi SysLog Enterprise ($295) which allows logging to a SQL database that we use at work this is the major downside of this solution. If anyone knows a free Syslog server that can log to SQL, please do share. My original goal was to write a small Azure web service to accept the Alternate Event Server HTTPS request and parse it directly in to Azure SQL. Since I had this a Syslog to SQL server, I chose the path of least resistance.

Key Steps:
Important, you must get DataWatcher or Eventwatcher configured first, and ensure the data is SysLog’d.

Event Watcher is available in the Vera App Store
DataYours includes many other pieces other than the 2 datawatcher files, however are files are focused on storing data locally in databases on the Vera (of limited size and resources). DataWatcher can be installed by downloading 2 data files from the DataYours project:
http://forum.micasaverde.com/index.php/topic,23109.msg164196.html#msg164196

Steps once you have DataWatcher or EventWatchers installed and Syslog’ing:

Sign up for an Azure Web Database trial. A small database will run $5/mo
http://www.windowsazure.com/en-us/pricing/details/sql-database/#web-and-business
Create a SQL database on Azure. Note the data you enter for the Database name, username, and password. You’ll also need the IP address of SQL server which will get displayed in the Azure dashboard after your database gets created in about a minute.

Note - Make sure you add your home’s public IP to the access/firewall rules of the Azure SQL Instance. There is a “add this PC” button which makes it simple, Azure will figure out your home’s public IP for you and create the firewall rule.

On your Windows PC (which I also use a the TTS Engine, HTPC, and Camera Server) at home, install Kiwi Log Server (or any tool that can take a Syslog string and dump it to a SQL database.
Run the SQL Query below (using Web based SQL Manager from the “Manage Button” of the Azure SQL instance) to create the database table ‘SysLogD’ (used to store your syslog messages), as well as the key lookup tables Device and CategoryKey.

-- Core objects
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Syslogd]') AND type in (N'U'))
BEGIN
       CREATE TABLE dbo.Syslogd (MsgDate VARCHAR(10),
                                 MsgTime VARCHAR(8),
                                 MsgText VARCHAR(1024)

       CREATE CLUSTERED INDEX Syslogd_Index ON Syslogd (MsgDate)
END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitMessage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))		
DROP FUNCTION dbo.SplitMessage
GO

CREATE FUNCTION dbo.SplitMessage ( @MessageToSplit VARCHAR(MAX), @Delimiter varchar(1))
RETURNS
 @ReturnList TABLE (OrderNum INT, [Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT
 DECLARE @OrderNum INT = 1

 WHILE CHARINDEX(@Delimiter, @MessageToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(@Delimiter, @MessageToSplit)  
  SELECT @name = SUBSTRING(@MessageToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @OrderNum, @name

  SELECT @MessageToSplit = SUBSTRING(@MessageToSplit, @pos+1, LEN(@MessageToSplit)-@pos)
  SELECT @OrderNum = @OrderNum + 1 
 END

 INSERT INTO @returnList
 SELECT @OrderNum, @MessageToSplit

 RETURN
END

GO




SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Device]') AND type in (N'U'))
BEGIN 

	CREATE TABLE [dbo].[Device](
		[DeviceId] [int] NOT NULL,
		[Parent] [int] NULL,
		[DeviceName] [nvarchar](255) NULL,
		[Room] [nvarchar](255) NULL,
		[BatteryPercentage] [float] NULL,
		[DeviceType] [nvarchar](255) NULL,
		[AltId] [INT] NULL
	) ON [PRIMARY]

	CREATE CLUSTERED INDEX Device_Index ON dbo.Device (DeviceId)


END
GO

IF NOT EXISTS (SELECT * FROM Device)
BEGIN

INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (1, 0, N'ZWave', NULL, NULL, N'ZWaveNetwork', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (2, 1, N'_Scene Controller', NULL, NULL, N'SceneController', 1)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (5, 0, N'Philips Hue Controller', N'X - Media Rack', NULL, N'HueController', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (6, 5, N'LR - TV LightStrip', N'US - Living Room - LR', NULL, N'DimmableLight', 19)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (38, 5, N'DBR - Top Lamp', N'DS - Bedroom - DBR', NULL, N'DimmableLight', 12)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (39, 5, N'DBR - Top Lamp Hue', N'DS - Bedroom - DBR', NULL, N'HueLamp', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (40, 5, N'ENT - Front Porch Light', N'US - Front Entry - ENT', NULL, N'DimmableLight', 11)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (41, 5, N'ENT - Front Porch Light Hue', N'US - Front Entry - ENT', NULL, N'HueLamp', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (42, 5, N'KIT - Pendant Light', N'US - Kitchen - KIT', NULL, N'DimmableLight', 10)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (43, 5, N'KIT - Pendant Hue', N'US - Kitchen - KIT', NULL, N'HueLamp', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (44, 0, N'Nest', N'X - Media Rack', NULL, N'Nest', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (45, 44, N'Activated - Away Mode', NULL, NULL, N'NestStructure', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (46, 44, N'Thermostat', NULL, N'100', NULL, NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (47, 44, N'Inside Humidity', NULL, NULL, N'NestHumidistat', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (48, 0, N'MiOS Update Utility', NULL, NULL, N'MiosUpdater', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (49, 0, N'Russound RNET Controller', N'X - Media Rack', NULL, N'RNETController', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (50, 0, N'Sprinkler', N'Backyard - BY', NULL, N'OpenSprinkler', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (51, 49, N'UPA - Audio', N'US - Patio - UPA', NULL, N'RNETZone', 1)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (52, 49, N'MBR - Audio', N'DS -Master Bedroom - MBR', NULL, N'RNETZone', 2)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (53, 50, N'Sprinkler Front(01)', N'Backyard - BY', NULL, N'BinarySprinkler', 1)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (54, 50, N'Sprinkler Back(02)', N'Backyard - BY', NULL, N'BinarySprinkler', 2)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (57, 0, N'Outside Weather', N'Backyard - BY', NULL, N'weather', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (58, 57, N'Outside Temperature', N'Backyard - BY', NULL, N'TemperatureSensor', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (59, 57, N'Outside Low Temp', N'Backyard - BY', NULL, N'TemperatureSensor', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (60, 57, N'Outside High Temp', N'Backyard - BY', NULL, N'TemperatureSensor', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (61, 57, N'Outside Humidity', N'Backyard - BY', NULL, N'HumiditySensor', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (63, 0, N'GRG - Overhead Door', N'US - Garage - GRG', NULL, N'DoorLock', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (64, 49, N'KIT - Audio', N'US - Kitchen - KIT', NULL, N'RNETZone', 3)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (65, 49, N'MBA - Audio', N'DS - Master Bath - MBA', NULL, N'RNETZone', 4)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (66, 49, N'DPA - Audio', N'DS - Patio - DPA', NULL, N'RNETZone', 5)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (67, 49, N'LR - Audio', N'US - Living Room - LR', NULL, N'RNETZone', 6)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (68, 50, N'Sprinkler Mulch(03)', N'Backyard - BY', NULL, N'BinarySprinkler', 3)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (69, 0, N'GC100', N'X - Media Rack', NULL, N'MultiIO', NULL)
INSERT [dbo].[Device] ([DeviceId], [Parent], [DeviceName], [Room], [BatteryPercentage], [DeviceType], [AltId]) VALUES (70, 69, N'GC100 I/R Port', N'X - Media Rack', NULL, N'IrTransmitter', 1)



END

GO




SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CategoryKey]') AND type in (N'U'))
BEGIN 

	CREATE TABLE [dbo].[CategoryKey](
		[Class] VARCHAR(20) NOT NULL,		
		[Category] [nvarchar](255) NOT NULL
	) ON [PRIMARY]

	CREATE CLUSTERED INDEX CategoryKey_Index ON dbo.CategoryKey (Class)

END
GO

IF NOT EXISTS(SELECT * FROM CategoryKey)
BEGIN
	INSERT dbo.CategoryKey (Class, Category) VALUES ('X', 'Light Switch')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('S', 'Sensor')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('K', 'HVAC')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('C', 'Camera')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('D', 'Door Lock')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('W', 'Window Covering')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('R', 'Remote Control')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('I', 'Infrared')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('O', 'Generic IO')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('G', 'Generic Sensor')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('H', 'Humidity')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('T', 'Temperature')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('L', 'Light Level')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('M', 'Energy')
	INSERT dbo.CategoryKey (Class, Category) VALUES ('Y', 'Scene Controller')
END

GO

Edit the default Kiwi log rules and add an ‘Action’ of ‘Log to database’. , database name, username, and password. It defaults to ‘display’ and ‘log to file’ actions. Personally, I deleted the “log to file” option so I wouldn’t fill up the disk.
- Run this query below to create the EventWatcher and DataWatcher tables, as well as the SQL Stored Procedure to parse the incoming data any time a record is updated on the SysLogD table. If you update your device table or device data table above (like change a display name or room the device is assigned), you can also re-run this command to reparse all master Syslog data.




Use Microsoft Excel > External Data and connect to your SQL Database.  Now you can use Excel to filter, chart, and view the data from your home.


PS - Screenshots of data attached of difference of data before and after the stored procedure used to parse the single syslog message text.

Weeves · March 14, 2014, 10:44pm

Rsyslog is free and logs to MySQL.
I have an older version of it running on my NAS, (but not yet to a database)

AgileHumor · March 19, 2014, 4:29pm

Code for EventWatcher/DataWatcher stored procedure and tables from step #6 above:

-- STEP 1: Check if table already exist 


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Syslogd]') AND type in (N'U'))
BEGIN
        CREATE TABLE dbo.Syslogd (MsgDate VARCHAR(10),
                                                MsgTime VARCHAR(8),
                                                MsgText VARCHAR(1024))


        CREATE CLUSTERED INDEX Syslogd_Index ON Syslogd (MsgDate)

END

GO


















IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventWatcher]') AND type in (N'U'))
DROP TABLE dbo.EventWatcher 
GO


	CREATE TABLE dbo.EventWatcher 
	(	
		Id int not null identity(1,1),				
		HAUnit VARCHAR(100),				
		DeviceId int,	
		DeviceName varchar(100),				
		Variable varchar(100),
		Value varchar(100),		
		Class varchar(100),
		Category varchar(100),
		Room varchar(100)
	)


	CREATE CLUSTERED INDEX EventWatcher_Index ON dbo.EventWatcher (Id)


GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DataWatcher]') AND type in (N'U'))
DROP TABLE dbo.DataWatcher 
GO


	CREATE TABLE dbo.DataWatcher 
	(	
		Id int not null identity(1,1),				
		HAUnit VARCHAR(100),				
		DeviceId int,			
		ServiceUrl varchar(100),
		ServiceType varchar(100),
		Variable varchar(100),
		Value varchar(100),
		DeviceName varchar(100),
		DeviceType varchar(100),
		Room varchar(100)
	)

CREATE CLUSTERED INDEX DataWatcher_Index ON dbo.DataWatcher (Id)

	

GO


-- STEP 2: Create Trigger, delete obsolete objects

IF OBJECT_ID ('dbo.IO_Trig_DataEvent_Insert','TR') IS NOT NULL
   DROP TRIGGER dbo.IO_Trig_DataEvent_Insert
GO

IF OBJECT_ID ('dbo.IO_Trig_EventWatcher_Insert','TR') IS NOT NULL
   DROP TRIGGER dbo.IO_Trig_EventWatcher_Insert
GO

IF OBJECT_ID ('dbo.IO_Trig_DataWatcher_Insert','TR') IS NOT NULL
   DROP TRIGGER dbo.IO_Trig_DataWatcher_Insert
GO


IF OBJECT_ID ('dbo.IO_Trig_Syslogd_Insert','TR') IS NOT NULL
   DROP TRIGGER dbo.IO_Trig_Syslogd_Insert
GO

CREATE TRIGGER dbo.IO_Trig_Syslogd_Insert
	ON dbo.Syslogd
AFTER INSERT, UPDATE
AS

SET NOCOUNT ON 



Declare @MsgText varchar(1024), 
		@HAUnit VARCHAR(100),				
		@DeviceId int,					
		@Variable varchar(100),
		@Value varchar(100),
		@DeviceName varchar(100),
		@DeviceType varchar(100),
		@Class varchar(100),
		@Category varchar(100),
		@Room varchar(100),					
		@ServiceUrl varchar(100),
		@ServiceType varchar(100),
		
		@TempValue varchar(100)
		


Select @MsgText = MsgText
FROM Inserted i 


IF (ISNULL(@MsgText,'') Like '%EventWatcher%') 
BEGIN 
	IF (@MsgText LIKE '%[%' 
		AND  @MsgText LIKE '%]%'
		AND @MsgText LIKE '%,%'  
		AND @MsgText LIKE '%=%')
	BEGIN
		SELECT  @HAUnit = (SELECT Name from dbo.SplitMessage(@MsgText, ' ') WHERE OrderNum = 4)

		SELECT @TempValue = (SELECT Name from dbo.SplitMessage(@MsgText, ' ') WHERE OrderNum = 6)
		SELECT @Class =  substring(@MsgText,charindex('EventWatcher:', @MsgText) + Len('EventWatcher:') + 1, 1)
		SELECT @DeviceId = RTRIM(LTRIM(substring(@MsgText,charindex('[', @MsgText) + 1, (charindex(']', @MsgText) - charindex('[', @MsgText)-1))))
		SELECT @DeviceName = RTRIM(LTRIM(substring(@MsgText,charindex(']', @MsgText) + 1, (charindex(',', @MsgText) - charindex(']', @MsgText)-1))))
		SELECT @Variable = RTRIM(LTRIM(substring(@MsgText,charindex(',', @MsgText) + 1, (charindex('=', @MsgText) - charindex(',', @MsgText)-1))))
		SELECT @Value = CASE WHEN charindex(' ', RTRIM(LTRIM(substring(@MsgText,charindex('=', @MsgText) + 1, len(@MsgText))))) > 0
							THEN  RTRIM(LTRIM(LEFT(substring(@MsgText,charindex('=', @MsgText) + 2, len(@MsgText)), charindex(' ', substring(@MsgText,charindex('=', @MsgText) + 2, len(@MsgText))))))
						ELSE RTRIM(LTRIM(substring(@MsgText,charindex('=', @MsgText) + 1, len(@MsgText))))
				END


		SELECT	@DeviceType = DeviceType,
				@Room = Room		
			FROM dbo.Device
			WHERE DeviceID = @DeviceID

		SELECT @Category = Category
		FROM dbo.CategoryKey
		where Class = @Class 

		
		INSERT EventWatcher (HAUnit, 
							DeviceId, 
							DeviceName, 
							Variable, 
							Value, 
							Class, 
							Category, 
							Room)
		VALUES (@HAUnit,
				@DeviceID,
				@DeviceName, 
				@Variable,
				@Value,
				@Class,
				@Category,
				@Room)
	END	        
END
ELSE BEGIN 
	SELECT  @HAUnit = (SELECT Name from dbo.SplitMessage(@MsgText, ' ') WHERE OrderNum = 4)

	SELECT @TempValue = (SELECT Name from dbo.SplitMessage(@MsgText, ' ') WHERE OrderNum = 6)
	SELECT	@DeviceID  = (SELECT Name FROM dbo.SplitMessage(@TempValue, '.') WHERE OrderNum = 2) 

	SELECT @TempValue = substring(@MsgText,charindex('DataWatcher:', @MsgText) + Len('DataWatcher:') + 1, LEN(@MsgText) - charindex('DataWatcher:', @MsgText))
	SELECT	@ServiceUrl  = (SELECT Name FROM dbo.SplitMessage(@TempValue, ':') WHERE OrderNum = 2)  

	SELECT	@TempValue  = (SELECT Name FROM dbo.SplitMessage(@TempValue, ':') WHERE OrderNum = 4)  
	SELECT	@ServiceType  = (SELECT Name FROM dbo.SplitMessage(@TempValue, '.') WHERE OrderNum = 1)  

	SELECT @TempValue = (SELECT Name FROM dbo.SplitMessage(@TempValue, '.') WHERE OrderNum = 2)
	SELECT	@Variable  = (SELECT Name FROM dbo.SplitMessage(@TempValue, ' ') WHERE OrderNum = 1)  

	SELECT  @Value = (SELECT Name from dbo.SplitMessage(@MsgText, ' ') WHERE OrderNum = 7)

	SELECT @DeviceName = DeviceName,
			@DeviceType = DeviceType,
			@Room = Room		
		FROM dbo.Device
		WHERE DeviceID = @DeviceID


		   

		   INSERT DataWatcher(HAUnit, 
						DeviceId, 
						DeviceName, 
						DeviceType,
						ServiceUrl,
						ServiceType,
						Variable, 
						Value,  
						Room)
			VALUES (@HAUnit,
					@DeviceID,
					@DeviceName,
					@DeviceType,
					@ServiceUrl ,
					@ServiceType,
					@Variable,
					@Value,
					@Room )
	        
END



SET NOCOUNT OFF
GO


-- STEP 3: Parse existing data 

Declare @existingData TABLE (MsgText varchar(1024), Processed bit DEFAULT(0)) 

INSERT @existingData (MsgText)
SELECT MsgText
from Syslogd

DECLARE @Msg varchar(1024) 

WHILE EXISTS(SELECT 1 FROM @existingData WHERE Processed = 0)
BEGIN
	SELECT TOP 1 @Msg = MsgText
	FROM @existingData
	WHERE Processed = 0
	
	
	print 'Processing: ' + @Msg

	Update Syslogd
		Set MsgText = MsgText
	WHERE MsgText = @Msg 



	UPDATE @existingData
	Set processed = 1
	WHERE MsgText = @Msg
END



-- STEP 4: Usage

-- Create Sample data for testing purpose
/*
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES ('03-11-2014', '18:31:44','Mar 11 18:31:46 Vera-30010408 EventWatcher: K [046] Thermostat, CurrentTemperature = 70')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES (  '03-11-2014', '18:31:35','Mar 11 18:31:36 Vera-30010408 EventWatcher: M [477] Home Energy Monitor Clamp 1, Watts = 916')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES (  '03-11-2014', '18:31:05', 'Mar 11 18:31:06 Vera-30010408 EventWatcher: M [477] Home Energy Monitor Clamp 1, KWH = 65.1870')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES ('03-11-2014', '18:31:44','Mar 11 18:31:46 Vera-30010408 EventWatcher: K [046] Thermostat, CurrentTemperature = 70')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES (  '03-11-2014', '18:31:35','Mar 11 18:31:36 Vera-30010408 EventWatcher: M [477] Home Energy Monitor Clamp 1, Watts = 916')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES (  '03-13-2014', '18:31:05', 'Mar 13 17:31:18 Vera-30010408 EventWatcher: M [477] Home Energy Monitor Clamp 1, KWH = 85.0010 ()')

select * from EventWatcher



INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES ('03-11-2014', '18:31:44','Mar 13 20:52:02 Vera-30010408 Syslogd: Vera-30010408.449.urn:micasaverde-com:serviceId:EnergyMetering1.Watts 71 1394769122')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES (  '03-11-2014', '18:31:35','Mar 13 20:51:59 Vera-30010408 Syslogd: Vera-30010408.477.urn:micasaverde-com:serviceId:EnergyMetering1.Watts 926 1394769119')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES (  '03-11-2014', '18:31:05', 'Mar 13 20:51:50 Vera-30010408 Syslogd: Vera-30010408.349.urn:micasaverde-com:serviceId:LightSensor1.CurrentLevel 9 1394769110')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES ('03-11-2014', '18:31:44','Mar 13 20:50:54 Vera-30010408 Syslogd: Vera-30010408.265.urn:upnp-org:serviceId:Dimming1.LoadLevelStatus 0 1394769054')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES (  '03-11-2014', '18:31:35','Mar 13 20:04:44 Vera-30010408 Syslogd: Vera-30010408.304.urn:micasaverde-com:serviceId:SceneController1.sl_SceneActivated 2 1394766284')
INSERT Syslogd (MsgDate, MsgTime, MsgText)
VALUES (  '03-13-2014', '18:31:05', 'Mar 13 20:04:44 Vera-30010408 Syslogd: Vera-30010408.278.urn:micasaverde-com:serviceId:SecuritySensor1.Tripped 0 1394766284')


Select * from DataWatcher

	*/

akbooer · March 19, 2014, 5:52pm

You have been busy… nice to see that data going to a good home.

AgileHumor · March 27, 2014, 6:29pm

Adding some new components that should be ready soon:

Multi Device Support - Device table now supports multiple HAUnits
Health Monitoring - Keep the Device table updated with the latest date or battrey level anytime a new value is created in Datawatcher on a per device basis.
1. BatteryLevel (integer)
2. Last date LastCommFailure value updated
3. Last date LastPollNoReply value updated
4. Last date LastPollOKUpdate value updated
Error Table - Any data that can’t be parsed by to EventWatcher or DataWatcher gets copied to this table.

akbooer · March 27, 2014, 6:59pm

Can you expand on that? What sort of things are you seeing here?
ie. my fault, or yours

AgileHumor · March 27, 2014, 10:20pm

Not your fault, this is when we can’t parse the syslog text.

There is no delimiter, so the parsing engine is getting a little complex when spaces are in the variable value…and sometimes a line is skipped.

akbooer · March 27, 2014, 11:29pm

Yes, the format is terrible… the whole point of EventWatcher in the first place, and then syslog, though, was to make the logs easy for people to read, not machines. I could add a JSON option.

AgileHumor · March 30, 2014, 3:30am

I would eventually like to move to a web service where the alternative event server. That way no parsing will be required (or the Syslog server middlman).

Do you think your DataWatcher front end could be used to build the devices to watch?

Otherwise, I guess I can maintain in in code re:
http://forum.micasaverde.com/index.php/topic,16713.msg153469.html#msg153469

However, today SQL parsing is easier than web services.

akbooer · March 30, 2014, 11:52am

Think I might need a little more in the way of detailed requirements, but if you’re asking whether the existing DataWatcher can direct its output to something which sends an HTTPS request rather than a syslog UDP datagram, then yes. Very easy.

Aaron · July 13, 2015, 5:01pm

Agile,
Are you still using this? I’m thinking I might be able to convert this to MySQL/MariaDB - thoughts?

AgileHumor · July 28, 2015, 3:30am

No, I embarrassingly went back to DataMine so it would work with my home tablet + Imperihome.
http://forum.micasaverde.com/index.php/topic,22763.msg188456.html#msg188456

Finalist · June 16, 2017, 1:32pm

I used akbooker’s solution which I am still using today to direct events to an HTTPS listener hosted in Azure. From there I parse the incoming string and insert into a SQL Database for trend analysis and have an Azure Event Stream Service that is watching the incoming data to perform Windowing functions over time to detect when strange things happen. This also allows for directing the stream to Azure ML services for additional machine learning capabilities on the collected data.

I installed everything according to the instructions and had to make a few small tweaks. I modified the LUA script where his is logging to a syslog server. I enabled syslog logging but I replaced that code with a HTTP Rest Call to my endpoint that sends the formatted “e” parameter as a JSON string, you can search by my user name for more details. I will post that code and maybe some examples - just wanted to say this is still possible and I have had ZERO downtime / errors by doing it this way. I have over 112,000 events and it is very fun to dig for trends and outliers in the data. This also allows me to create dashboard in Power BI for viewing on almost any platform, mobile, tablets, etc.
And as far as the cost - I think I am still under $4 a month (I have an MSDN account which gives me some credits so the cost is $0)