HS3 MQTT Plugin

[tominthevan]

Geert-Jan

That was quick! I had the wrong plug-in installed. I'll fix that and see how it goes.

Thanks

Tom

[tominthevan]

Hi

Thanks that worked. I should have read the forum more closely. That suggestion moved me forward a lot. Now I need to install a broker somewhere on my network.

Would it be asking too much of the little Pi to have the broker also running on it beside HS3?

Tom

[geert-jan]

Hereby an updated version, but not thoroughly tested yet.

Changelog:
- Added filter on the 'Publish' page, which will make selecting a device more easy and reduces page loading time.

Note that per default ALL check marks are off, meaning that NO devices will be displayed until you select the Room and Type (aka location and location2).

Regards,
Geert-Jan

[raymonvdm]

Hi,

the MQTT plugin writes the MQTT data in a Homeseer device, the device values are available in events and scripts.
I use 'location2' as device type; Electra, Water, Gas.

The script below is called from a Homeseer event every hour, and writes the actual device values for each device where the type is 'Electra', 'Gas' or 'Water' to a MySQL database. The types can easily be extended, with Temperature, Humidity, etc.

Code: Select all

Imports MySql.Data.MySqlClient

Sub main(Optional ByVal pParms As String = "")
  Dim en
  Dim dv

  en = hs.GetDeviceEnumerator

  if IsReference(en) Then
    do while not en.Finished
      dv = en.GetNext
      if not dv is nothing then
          select case dv.location2(hs)
          case "Electra" 
            writeDB("electra", dv, 1000)
          case "Gas"
            writeDB("gas", dv, 1000)
          case "Water"
            writeDB("water", dv, 1)
          end select
      end if
    loop
  end if


End Sub


Sub writeDB(ByVal dbTable as String, ByVal dvRef as Object, devFactor as Integer)
  Dim conn As MySqlConnection
  Dim myCommand As New MySqlCommand
  Dim timestamp As String
  Dim devValue As Integer

  timestamp = Datepart("yyyy",Now()) & "-" & Right("0" & DatePart("m",Now()),2) & "-" & Right("0" & DatePart("d",Now()),2) & " " & Right("0" & DatePart("h",Now()),2) & ":" & "00:00"

  if NOT dbTable = Nothing
    conn = New MySqlConnection()
    conn.ConnectionString = "server=<ip address>; user id=<user id>; password=<password>; database=<database>"

    devValue = Convert.Toint64(dvRef.devValue(hs) * devFactor)

    Try
          conn.Open()

          myCommand.CommandText = "INSERT INTO " _
		& dbTable _
		&  " (deviceName, timestamp, deviceValue, deviceType, deviceId)" _
		& " VALUES ('" _
                  & dvRef.Name(hs) & "','" _
                  & timestamp & "','" _
                  & devValue  & "','" _
                  & dvRef.location2(hs) & "','" _
                  & dvRef.Code(hs) & "')"

      myCommand.Connection = conn

      myCommand.ExecuteNonQuery()

      conn.Close()
   Catch myerror As MySqlException
      hs.writelog ("datalog", "Error Connecting to Database: " & myerror.Message)
   End Try
 end If

End Sub

Sub updateCounter(Optional ByVal pParms As String = "")
    dim electraTotal as double
    dim dev1 as integer
    dim dev2 as integer
    dim dev3 as integer
    Dim arrParm() As String

    ' Extract parameters
    arrParm = pParms .ToString.Split(";")
    dev1= arrParm(0)
    dev2= arrParm(1)
    dev3= arrParm(2)

    electraTotal = hs.devicevalueex(dev1) + hs.devicevalueex(dev2)
    hs.setdevicevaluebyref(dev3, electraTotal, true)
    hs.setdevicestring(dev3, electraTotal & " KWh", true)

end sub

Adapt the fields between'<>' in conn.ConnectionString = "server=<ip address>; user id=<user id>; password=<password>; database=<database>" to your configuration.


Regards,
Geert-Jan

I have installed http://dev.mysql.com/downloads/connector/net/ but the script is not yet working

Code: Select all

Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlException' is not defined.
Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlConnection' is not defined.

[Sparkman]

Great, thanks very much. Trying it right now.

Cheers
Al

[geert-jan]

@raymonvdm:

I got VB and MySql working by downloading the MySql connector from Microsoft:
- http://dev.mysql.com/downloads/connector/net/1.0.html
- Select platform '.Net and Mono'
- Download the zip file
- Extract the .dll files to your Homeseer folder

Regards,
Geert-Jan

[raymonvdm]

Which version did you select? I tried 1.0.10 and 6.8.7 but stil no go

Code: Select all

Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlException' is not defined.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlConnection' is not defined.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlCommand' is not defined.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlConnection' is not defined.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Namespace or type specified in the Imports 'MySql.Data.MySqlClient' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Namespace or type specified in the Imports 'System.Core' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

Note: My HS3 is running on Windows 7

[geert-jan]

I am using MySql connector version 6.7.4, date 19-06-2013, on a Windows XP system (running in a VirtualBox).

Regards,
Geert-Jan

[raymonvdm]

Is it also possible to add the Reference ID to the list of devices visable under MQTT -> Publish ?

I have found a way to export the MQTT data to file to create my cacti graphs but i need to manually lookup the reference id for each device which is quite time consuming

[geert-jan]

Adding the reference ID is no issue. I will add it to the overview.

Regards,
Geert-Jan

[raymonvdm]

I did some extra testing and it looks like some value updates are not MQTTed out. I have an OpenThermGateway script and the HomeSeer device status seems to be updated but the values are never send out by MQTT

[geert-jan]

Hereby an updated version.

Changelog v0.6.4:
- Added device Id to 'Publish' page
- Publish value on value change (/homeseer/ + <dvRef> + /value)
- Publish string on string change (/homeseer/ + <dvRef> + /string)

This may lead to 2 publish messages per value update, since mostly also the string value is updated.

Regards,
Geert-Jan

[raymonvdm]

Is it possible to select the value or sting to publish to prevent dual MQTT messages. Usually the value is sufficient but sometimes you indeed need a string instead of a value

[geert-jan]

Hi,

I will add a feature to select if you want to publish the value on value change, or publish the string on string change.

Regards,
Geert-Jan

[raymonvdm]

Hi,

I will add a feature to select if you want to publish the value on value change, or publish the string on string change.

Regards,
Geert-Jan

Or both perhaps