Hey all, I have been looking for a way to log device changes to an external database so I could then visualize the data. I couldn't find anything ready-made so I put together a script using parts I found on these forums as a starting point. I settled on using InfluxDB as the database mostly because I couldn't get SQLExpress to work. I think it is a better solution anyway.
Why do this?
I wanted pretty dashboards with lots of cool graphs and stuff. Grafana is what I'm using to generate my dashboards.
OK, how then?
I am using Windows, so these instructions reflect that. Install InfluxDB. Install Grafana. Create a database in InfluxDB where your measurements will go. I called mine "homeauto". Configuration is beyond the scope of this post but I can try to answer questions if you have them. Both InfluxDB and Grafana run on Windows and don't have any other dependencies.
Copy this script and paste it into a new text file, then rename it L2DB-influxdb.vb. Place that in your HomeSeer Scripts folder. Make sure you modify the variables at the top to fit your system.
I recommend running InfluxDB from the command line so that you can watch the output and see your measurements being added. Once you're satisfied with how it is working, you can use NSSM to run it as a service. I am using Telegraf to send info about my Homeseer server itself to InfluxDB. Installation is straightforward.
Why do this?
I wanted pretty dashboards with lots of cool graphs and stuff. Grafana is what I'm using to generate my dashboards.
OK, how then?
I am using Windows, so these instructions reflect that. Install InfluxDB. Install Grafana. Create a database in InfluxDB where your measurements will go. I called mine "homeauto". Configuration is beyond the scope of this post but I can try to answer questions if you have them. Both InfluxDB and Grafana run on Windows and don't have any other dependencies.
Copy this script and paste it into a new text file, then rename it L2DB-influxdb.vb. Place that in your HomeSeer Scripts folder. Make sure you modify the variables at the top to fit your system.
Code:
'VB.Net script to write Homeseer 3 values to InfluxDB 'Created by Brian based on code found on the Homeseer forums. No warranty. Use at your own risk. 'Uncomment the log statements if you're having problems to try to track down the error. 'Installation Instrustions: ' 0: Install InfluxDB & get it running ' 1: Modify the variables below to fit your system. ' 2: Add this line to Homeseer\Scripts\Startup.vb ' hs.RegisterStatusChangeCB("L2DB-influxdb.vb","Main") ' 3: Restart Homeseer Imports System.Web Imports System.Net Imports System.IO Imports System.Text Sub Main(ByVal Parms As Object) '========================================================== 'Modify these to fit your system dim INFLUX_DB_SERVER_IP = "localhost" dim INFLUX_DB_SERVER_PORT = "8086" dim INFLUX_DB_DATABASE_NAME = "homeauto" dim SKIP_LIST = "183" 'comma separated list of device references to skip logging '========================================================== dim device_name, device_location, device_location2, deviceObj, device_type, problem dim dev_address as string Dim device_value As Double Dim devRef As Integer 'hs.WriteLog("L2DB-InfluxDB", "Script running") 'Get device info from Homeseer dev_address = Parms(1) 'address of device. device_value = Parms(2) 'new value of device devRef = Parms(4) 'Device reference of the device deviceObj = hs.GetDeviceByRef(devRef) device_name = deviceObj.name(hs) device_location = deviceObj.location(hs) device_location2 = deviceObj.location2(hs) device_type = deviceObj.Device_Type_String(hs) If device_type = "Timer" Then 'Don't log timers Exit Sub End If If SKIP_LIST <> "" Then Dim skips = SKIP_LIST.split(New Char() {","c}) Dim skip as String For Each skip in Skips If devref = skip Exit Sub End If Next End If 'hs.WriteLog("L2DB-InfluxDB", "Logging to Database: Reference: " & devref & " Address: " & dev_address & ", Device Name: " & device_name & ", New Value: " & device_value) 'Build the post data string Dim postdata as String = devref If device_name <> "" Then postdata = postdata & ",name=" & device_name End If If device_location <> "" Then postdata = postdata & ",location=" & device_location End If If device_location2 <> "" Then postdata = postdata & ",location2=" & device_location2 End If If device_type <> "" Then 'Types are optional and not all devices have them postdata = postdata & ",type=" & device_type End If If dev_address <> "" Then 'Addresses are optional and not all devices have them postdata = postdata & ",address=" & dev_address End If postdata = postdata.Replace(" ", "\ ") postdata = postdata & " value=" & device_value 'hs.WriteLog("L2DB-InfluxDB", postdata) 'Set up the Webrequest Dim url = "http://" & INFLUX_DB_SERVER_IP & ":" & INFLUX_DB_SERVER_PORT & "/write?db=" & INFLUX_DB_DATABASE_NAME Dim httpWebRequest = DirectCast(WebRequest.Create(url), HttpWebRequest) 'Dim httpWebRequest = DirectCast(WebRequest.Create("https://requestb.in/XXXXXXX"), HttpWebRequest) 'For testing- to see what the request contains httpWebRequest.ContentType = "application/x-www-form-urlencoded" httpWebRequest.Method = "POST" Dim encoding As New System.Text.UTF8Encoding 'Make the request to the database Try Dim data As Byte() = encoding.GetBytes(postdata) httpWebRequest.ContentLength = data.Length Dim myStream As Stream = httpWebRequest.GetRequestStream() If data.Length > 0 Then myStream.Write(data, 0, data.Length) myStream.Close() End If Catch ex As Exception hs.WriteLog("L2DB-InfluxDB", "Error: " & ex.ToString()) End Try Try Dim httpResponse = DirectCast(HttpWebRequest.GetResponse(), HttpWebResponse) Using streamReader = New StreamReader(httpResponse.GetResponseStream().ToString) Dim responseText = StreamReader.ReadToEnd() 'hs.WriteLog("L2DB-InfluxDB", "Response: " & responseText) End Using Catch ex As Exception hs.WriteLog("L2DB-InfluxDB", "Error: " & ex.ToString()) hs.WriteLog("L2DB-InfluxDB", "Request was: " & postdata) End Try End Sub
Comment