Nest Thermostat monitoring pack: Part 2: Get data into SCOM from MySQL
This blog post is part of a series check out the other posts in this series:
- Part 1: How did I get data
- Part 2: Get data into SCOM from MySQL
- Part 3: Create the MP
- Part 4: Seeing it in action
- Part 5: Limit the max temperature
So after we have successfully set up the connection between the nest and our mysql dbase data is pouring into our own dbase. So how do we get that data into SCOM so we can graph it and monitor it.
This blogpost will explain how to retrieve the data with PowerShell (of course) and dump it into a property bag which is readable by SCOM. This is the second phase in our schematic example:
Requirements
What do we need to retrieve the data out of the MySQL dbase.
- A watchernode which has PowerShell V2.0 installed (can be a server or a desktop laying somewhere)
- a reg key to identify this watcher node. I’m using “HKLM\SOFTWARE\NEST\Watchernode” for this
- The mysql connector installed: http://dev.mysql.com/downloads/connector/net/ (note in this example I’m using version 6.8.3)
- Scom agent installed on the machine to be able to discover it as a class
There’s no additional install required on the mysql server although you will need the following to connect:
- Location
- User which has access to the mysql dbase (I use Root but this is not the safest way)
- password
I’m using this on a virtual Win2012 machine without any issues.
Retrieve the data from MySQLusing a PowerShell script
This is the script I created to get the data out of MySQL.
Note that this script only is retrieving one value. It’s possible to retrieve multiple values all at once but I prefered to use different scripts to get the different parameters out of the dbase.
The Nest parameters I read in:
- Current temperature: The current temperature measured by the Nest device
- Target temperature: The target temperature set for the Nest device at that time
- Humidity: The Humidity measured by the Nest device.
- Heating status: Whether the heating is on (1) or off ( 0 )
The script used:
It can be downloaded here: http://gallery.technet.microsoft.com/SCOM-Retrieve-performance-507293f1
[xml]<br />#===================================================================================================<br /># AUTHOR: Dieter Wijckmans<br /># DATE: 18/02/2014<br /># Name: Nest_humidity.PS1<br /># Version: 1.0<br /># COMMENT: Get the current humidity value from the nest device from the mysql dbase<br />#<br /># Usage: .\Nest_humidity.ps1<br />#<br />#===================================================================================================<br />param($location)<br />#load the connector but make sure to check the path if you are using a different version<br />[void][system.reflection.Assembly]::LoadFrom(“C:\Program Files (x86)\MySQL\MySQL Connector Net 6.8.3\Assemblies\v2.0\MySQL.Data.dll”)<br />#Create a variable to hold the connection:<br />$myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection<br />#Set the connection string:<br />$myconnection.ConnectionString = "database=<Fill in your dbase name>;server=<Fill in your server ip>;user id=<fill in user id>;pwd=<not 1234 right?>"<br />#Call the Connection object’s Open() method:<br />$myconnection.Open()<br />#Prepare the property bag<br />$API = New-Object -ComObject "MOM.ScriptAPI"<br />$PropertyBag = $API.CreatePropertyBag()<br />#The dataset must be created before it can be used in the script:<br />$dataSet = New-Object System.Data.DataSet<br />#Run the actual query<br />$command = $myconnection.CreateCommand()<br />$command.CommandText = "SELECT humidity FROM data ORDER BY timestamp DESC LIMIT 1";<br />$reader = $command.ExecuteReader()<br />#Processing the Contents of a Data Reader we only want the last value<br />while ($reader.Read()) {<br /><%%KEEPWHITESPACE%%> for ($i= 0; $i -lt $reader.FieldCount; $i++) {<br /><%%KEEPWHITESPACE%%> $value = $reader.GetValue($i).ToString()<br /><%%KEEPWHITESPACE%%> }<br />}<br />$myconnection.Close()<br />$PropertyBag.AddValue("location", $location)<br />$PropertyBag.AddValue("humidity", $value)<br />$PropertyBag<br /><br />[/xml]
This script will basically do the following.
- Prepare the environment
- Open the connection to MySQL
- Get the data in the data reader
- Read out the last line because we are only interested in the most recent value
- Fill it in the property bag
Note: I’m also using a variable $location to identify the Nest Thermostat if you have more than one.
Now to get all the different parameters as mentioned above the only things you need to change are:
- The name of the script itself
- The Select statement: SELECT humidity FROM data ORDER BY timestamp DESC LIMIT 1 with the column name of the desired value
- The property bag value: $PropertyBag.AddValue(“heating”, $value)
If everything goes well you have your data in your MySQL dbase now and can retrieve it remotely via PowerShell to pass it on to SCOM.
Next blog post we’ll get everything in SCOM.
Stay Tuned.