Flukso Energy meter monitoring pack: Part 3: Get data into SCOM
This blog post is part of a series check out the other posts in this series:
- Part 1: Intro on the device used
- Part 2: Get data into MySQL
- Part 3: Get data into SCOM
- Part 4: Seeing it in action
So after we have successfully set up the connection between the flukso and our mysql dbase with basically following the same route as the nest thermostat data is pouring into our own dbase on the same device..
The only thing left to do is get this data in SCOM as well. I’ve created a separate management pack and PowerShell script for this to give people the ability to install it separate from each other but the goal is to create one big management pack in the end.
This blog post 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
We basically need the same requirements as for the NEST thermostat monitoring as we ar using the same route:
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\Flukso\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 MySQL using 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 preferred to use different scripts to get the different parameters out of the dbase.
The script has some prep work for water consumption in there as well but this is not yet fully operational as I need to convert the pulses to l/min so more on that later.
The dbase is filled with data every minute so I run the PowerShell script below every 120 sec to get data in. The data is measured in watt.
The script used:
It can be downloaded here: http://scug.be/dieter/files/2014/02/perfdatafrommysqlelectricity.rar
[xml]
Param($energysort)
[void][system.reflection.Assembly]::LoadFrom(“C:\Program Files (x86)\MySQL\MySQL Connector Net 6.8.3\Assemblies\v2.0\MySQL.Data.dll”)
#Create a variable to hold the connection:
$myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection
#Set the connection string:
$myconnection.ConnectionString = "database=flukso;server=<fill in ip of server>;user id=<user>;pwd=<password>"
#Call the Connection object’s Open() method:
$myconnection.Open()
#uncomment this to print connection properties to the console
#echo $myconnection
$API = New-Object -ComObject "MOM.ScriptAPI"
$PropertyBag = $API.CreatePropertyBag()
#The dataset must be created before it can be used in the script:
$dataSet = New-Object System.Data.DataSet
$command = $myconnection.CreateCommand()
#$command.CommandText = "select date, time, sensor_1 from fluksodata";
$command.CommandText = "SELECT Sensor_1 FROM fluksodata ORDER BY IDTimestamp DESC LIMIT 1";
$reader = $command.ExecuteReader()
#echo $reader
#The data reader will now contain the results from the database query.
#Processing the Contents of a Data Reader
#The contents of a data reader is processes row by row:
while ($reader.Read()) {
#And then field by field:
for ($i= 0; $i -lt $reader.FieldCount; $i++) {
$value = $reader.GetValue($i).ToString()
}
}
echo $value
$myconnection.Close()
$PropertyBag.AddValue("energysort", $energysort)
$PropertyBag.AddValue("electricity", $value)
[/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 $energysort to identify the flukso sensor.
Now to get all the different parameters as mentioned above the only things you need to change are:
- The name of the script itself
- Command.CommandText = “SELECT Sensor_1 FROM fluksodata ORDER BY IDTimestamp DESC LIMIT 1”;
- The property bag value: $PropertyBag.AddValue(“electricity”, $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.
Now that we have the PowerShell in place. Check out this blog post to make a management pack for it: http://scug.be/dieter/2014/02/19/nest-thermostat-monitoring-pack-part-3-create-the-mp/
Download the MP here: http://scug.be/dieter/files/2014/02/flukso.energymeter.rar