DXP (Data Export) Plug-in for Homeseer HS3

This Plug-in for HomeSeer is designed to export device data (On Change or Interval) to a wide range of databases and systems. We use the standard OLE Connection String format to determine where to write the data. The plug-in leverages a multi-threaded architecture to ensure loading of the HS3 instance is kept to a minimum and writes to the database in the background. For this reason there may be a small delay between device state changes and the record arriving at the database, but this should be limited to seconds. The shortest interval we can sample data is 1 second although I'm not sure why you'd want that. Common times are something like 900 seconds for environmental, and On Change (0 seconds) for zones but its up to you to set it up as you see fit. 

The market for this plug-in is long term and/or large volume data analysis from Homeseer HS3 systems. Using a reasonable Server data can be selected, analysed and charted using tools like Microsoft Excel, Jasper, Crystal etc. In my case the plug-in exports power usage data hourly, environmental data (Temperature and Humidity) every 15 minutes and a few key zones such as movement as and when they change.  

Configuration of devices to be monitored is stored in the plug-ins .INI file which resides in the Config folder under the HS3 directory, OR you can add devices via the Config Tab. Be sure to SAVE the config after adding devices or your changes will be lost when the plug-in restarts. The format of the INI file in the [Devices] section is Monitor_xxx=yyy where xxx is the HS3 Device Reference and is always an integer and yyy is the sample period or 0 for on change and is in seconds. That is, if the sample period is zero, then the plug-in will write to the database every time the given device changes, whereas for non zero sample period the plug-in will write to the database every sample seconds. Example would be: 

[Devices]
Monitor_100=900     <- Write Device 100's state every 900 seconds (15 minutes)
Monitor_200=0       <- Write Device 200's state every time it changes. 
Monitor_300,3600   <- Write Device 300's state every hour (3600 seconds)

The Plug-in, using the OLE connection string establishes connection to a database (local or remote) and writes to a Table called DXP (Table name can be changed in the .ini file) and you need to make sure you've created this table and given the user INSERT privileges before running the plug-in. 

Table Format

You will need to create the table in this format:

CREATE TABLE [dbo].[DXP] (
[RID] bigint IDENTITY NOT NULL,
[TIMESTAMP] datetime,
[DeviceReference] int,
[DeviceName] varchar(64),
[DeviceValue] decimal(10,4),
[DeviceState] varchar(64),
[DeviceControl] varchar(64),
[DeviceIsOn] bit
)

The above SQL is for MSSQL, MySQL is slightly different as is Oracle, MariaDB, Interbase, JET and so on. If your familiar with the database then the format of the CREATE TABLE command shouldn't be that hard. In this example above I've created an identity Column named RID. This isn't INSERTed so you can leave it off or create your own name and format.

If your using Microsoft Access (JET 4.0) Then the create table would be slightly different:

CREATE TABLE DXP (
  [RID] COUNTER,
  [TIMESTAMP] DATETIME,
  [DeviceReference] INTEGER,
  [DeviceName] VARCHAR(64),
  [DeviceValue] SINGLE,
  [DeviceState] VARCHAR(64),
  [DeviceControl] VARCHAR(64),
  [DeviceIsOn] BIT
)

Its worth having an identity so there's a primary key on the table, at least in MSSQL anyways and you'd create this with ALTER TABLE [dbo].[DXP] ADD PRIMARY KEY CLUSTERED ([RID]). Again the syntax varies between database systems.

You can further enhance the table with an index such as

CREATE NONCLUSTERED INDEX [dIndex]
ON [dbo].[DXP] (
[TIMESTAMP],
[DeviceReference]
)

to Speed up analysis when the table grows large enough that runtime memory indexes are no longer sufficient. 

If you wish to use MariaDB (or MySQL) then you'll need to create the table using

CREATE TABLE `DXP` (
`RID` bigint(20) NOT NULL AUTO_INCREMENT,
`TIMESTAMP` datetime DEFAULT NULL,
`DeviceReference` int(11) DEFAULT NULL,
`DeviceName` varchar(64) DEFAULT NULL,
`DeviceValue` decimal(10,4) DEFAULT NULL,
`DeviceState` varchar(64) DEFAULT NULL,
`DeviceControl` varchar(64) DEFAULT NULL,
`DeviceIsOn` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`RID`),
KEY `dIndex` (`TIMESTAMP`,`DeviceReference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But remember to install the MariaDB ODBC Connector (or MySQL equivilent)

OLEDB Connection String

There are many resources online for OLE Connection strings. An example here would be MS SQL Server which looks like

Provider=SQLNCLI11;Server=192.168.1.99;Database=HSData;Uid=HomeSeer;Pwd=Genius1234;

or

Provider=sqloledb;Data Source=192.168.1.99;Initial Catalog=HSData;User Id=HomeSeer;Password=Genius1234;

If Your using Microsoft JET (Access) then the connection string would be something like:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files (x86)\HomeSeer HS3\Data\DXP\DXP.mdb;

Depending on what version of the driver you are using

Plugin Example (Status Page)

This is an actual screenshot of the plug-in in action, You'll notice the sample rate for the devices as well as last update together with the four metrics (Device Value, Device State and Device Control shown and IsOn additionally) that are sampled and exported. 

Miscellaneous Notes

You will need to give the User that you've assigned to the Connection String at least UPDATE permission on the table so make sure you GRANT it (or whatever the applicable command is on your chosen DB)

Be aware that some databases (e.g.MySQL) use Case Sensitive Collumn names so make sure you keep the case correct or it will break. 

The format of DeviceValue is your responsibility, above I selected Decimal(10,4) but you can use whatever precision you wish.

There are more advanced settings in the INI file that can be changed to alter various low level parameters, but please don't go messing with these unless you know what your doing and always take a backup of the .INI so you can rollback.

Scripting

There are two functions exposed for scripting use

DXPWriteDevice: This function like the name suggests writes the data for a given device (by Device Reference) to the database. A typical example of this to write data for device 487 would be:

&nhs.PluginFunction("DXP","","DXPWriteDevice",New Object(){487})

DXPSQLDataQuery: This function is a far more advanced and allows you to throw an SQL Query to the database and have it return JSON data. A typical implementation of this would be something like:

Dim JSONData as string=hs.PluginFunction("DXP","","DXPSQLDataQuery",New Object(){"SELECT * FROM DXP WHERE DEVICEREFERENCE=487"})

In this example the string variable JSONData would receive the data from the query and can then be parsed into whatever you want. In my local implementation of this I use the JSON data to build charts hourly and store them as PNG files for loading into another screen, but you can use it for anything you wish. Remember these are SQL Queries and you'll need to use the language native to the database your connecting to and in some cases queries are case sensitive so watch out for this. Even in an error condition the returned string will still be JSON but with a key value pair of Error and the actual error. You can of course check for this, and an error will be sent to the HS3 event log also containing this information. 

Bug Reporting

The BugTracker for Reporting Issues is available Here

Please make sure when submitting bug reports you fully describe the issue and include a LOGFILE by setting up a logifle and uploading the log and attach the INI File. Please don't upload the entire log just the section that shows the issue you're reporting. 

For some background and an overview of Design and Development Follow this link