SCOM: Agentpostinstall.ps1 PowerShell demo script Webcast 01042014

April 3, 2014 at 10:29 am in LiveMeeting, SCOM, SCOM 2012 by Dieter Wijckmans

On april 1st 2014 (a day I will remember for a long time due to various reasons) I held a webcast for Microsoft Technet Belux regarding automation of admin tasks in SCOM.

I went over the basics to get started, the pitfalls and gave some tips and tricks to get you going. This session was recorded and together with the slide deck it’s made available here:

http://www.slideshare.net/technetbelux/make-scom-work-for-you-and-not-the-other-way-around

In this demo I created a small PowerShell script that could save you some time when agents are installed in your environment through an image. In this particular scenario the agents are automatically in the “pending approval” list in SCOM.

Running this PowerShell will add them to the environment, make them remotely manageable, point them all to a management server of your choice and put agent proxying on true.

Feel free to adapt the script for your needs.

The script in question:


#=====================================================================================================
# AUTHOR:    Dieter Wijckmans
# DATE:        01/04/2014
# Name:        agentpostinstall.PS1
# Version:    1.0
# COMMENT:    Approve agents after install, make remotely manageable, assign to 1 management server
#           and enable agent proxying.
#
# Usage:    .\postinstallagenttasks.ps1 mgserverfrom mgserverto sqlserverinstance dbase
# Parameters: mgserverfrom: the primary server at this point
#             mgserverto: The new primary server
#             sqlserverinstance: the sql server where the opsdb resides + instance
#             dbase: name of the opsdb
#
#=====================================================================================================

param ([string]$mgserverfrom,[string]$mgserverto,[string]$sqlserverinstance,[string]$dbase)
###Prepare environment for run###

####
# Start Ops Mgr snapin
###

##Read out the Management server name
$objCompSys = Get-WmiObject win32_computersystem
$inputScomMS = $objCompSys.name

#Initializing the Ops Mgr 2012 Powershell provider#
Import-Module -Name "OperationsManager"
New-SCManagementGroupConnection -ComputerName $inputScomMS

#Get all agents which are in pending mode and approve
$pending = Get-SCOMPendingManagement | Group AgentPendingActionType
$Count = $pending.count
echo $count

If ($count -eq $null)
{
echo "No agents to approve"
Exit
}
Else
{
Get-SCOMPendingManagement | where {$_.AgentPendingActionType -eq "ManualApproval"} | Sort AgentName | Approve-SCOMPendingManagement
}

#Let all servers report to 1 primary management server

$serverfrom = Get-SCOMManagementServer | ? {$_.name -eq "$mgserverfrom"}
$agents = Get-SCOMAgent -ManagementServer $serverfrom
$serverto = Get-SCOMManagementServer | ? {$_.name -eq "$mgserverto"}
Set-SCOMParentManagementServer -Agent:$agents -FailoverServer:$null
Set-SCOMParentManagementServer -Agent:$agents -PrimaryServer:$serverto
Set-SCOMParentManagementServer -Agent:$agents -FailoverServer:$serverfrom

#Set all servers to remotely manageable in SQL

$ServerName = "$sqlserverinstance"
$DatabaseName = "$dbase"
$Query = "UPDATE MT_HealthService SET IsManuallyInstalled=0 WHERE IsManuallyInstalled=1"

#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30

#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

#Set all servers to agent proxy enabled

Get-SCOMAgent | where {$_.ProxyingEnabled.Value -eq $False} | Enable-SCOMAgentProxy

It can be downloaded here

download-button-fertig11

Note

  • that you need to give the proper parameters for it to work as stated in the description.
  • that perhaps you will have to check the SQL connection string on-line 68 with your SQL dba and adapt accordingly.

Received MVP 2014 award

April 2, 2014 at 10:02 am in Uncategorized by Dieter Wijckmans

 

Yesterday I have received the news that I am awarded with the Microsoft Most Valuable Professional award 2014 in Cloud and Datacenter Management.

SNAG-0229

I can’t describe how thrilled I am to be a part of this community to share even more knowledge with true experts in the field to gain even more insight in the System Center products.

This couldn’t have been possible without the help and support of a lot of people who guided me into the world of System Center. However there’s a small problem with name dropping: You are always forgetting some people. But hey I’m happy to take the risk.

First of all I would like to go back to 2010. While I was working at a client I came across Kurt Van Hoecke (who’s an MVP now as well) who introduced me to the System Center Suite. I did have an ITIL background but never heard of System Center as such. I agreed to join him to MMS2010 and barely got there due to the ash cloud. During that MMS I already met the people of System Center User Group and other System Center engineers who became good friends afterwards.

Time went by and I started to experiment with SCOM and other Sysctr products. I changed employer specifically to start working with Sysctr products and from then on it started rolling.

I officially joined SCUG Belgium in 2011 and have blogged ever since. Started speaking at events as well with already recently a couple of highlights (Expertslive, SystemCenteruniverse US,…) and hopefully many more to come.

During the past years I enjoyed sharing my knowledge, findings regarding the Sysctr products, helping out people with issues and just meeting new people with the same passion. I can’t count the hours I’ve spend on these activities but I enjoy doing it otherwise you would not continue right?

So what now? Well euhm basically nothing. I will continue blogging, speaking, helping out and hopefully meet even more people with the same passion. As a board member of SCUG I can say that we will continue to provide a platform for System Center content in Belgium and throughout the world. If you would like to start blogging / speaking / contributing here just drop me a line.

So finally I would like to start name dropping… The dangerous stuff right?

First of all thanks to Arlindo Alves and Sigrid VandenWeghe: As Microsoft Belux community leads they provide us (and me) with a solid platform to build and grow our community platform.

Second I would like to thank the members of the SCUG who helped me in the beginning of my wanders through the System Center world.

Third I would like to shout out to some specific people who had a significant impact on my journey II ‘ve travelled so far. Thanks Maarten Goet, Kenny Buntinx, Tim de Keukelaere, Cameron Fuller, Kurt van Hoecke, Kevin Greene, Marnix Wolf, Mike Resseler and so many more I’m forgetting to mention right now.

It’s because of these individuals and much more due to the buzz in the Sysctr community  that I really like sharing my knowledge and meeting new people while I’m speaking

Last I would like to express a special thanks to the Sysctr Community members who provided good content in the past, now and in the future. It’s their blogs, effort and guidance who helped me in the beginning to gain a good insight in the Sysctr world.

Some blogs that really helped me in the beginning (and still are helping me today)

Last but not least I want to encourage you to share your knowledge as well in the community. Every bit of effort even the smallest ones really contribute in keeping this community alive and helping others to fully understand the potential of the system center suite. Hopefully see you at one of the events in the near future!

Connect with me on

Webcast 01/04: Make SCOM work for you and not the other way around!

March 6, 2014 at 9:23 am in LiveMeeting, SCOM, SCOM 2012 by Dieter Wijckmans

 

Face it… Sometimes keeping SCOM alive can be a real challenge especially when there are a lot of people who have access to the console… Sometimes it feels like monitoring SCOM itself can be a full time job!

scom-manager

I came across a lot of environments and one of the things I’ve learned is in fact if you automate a lot of tasks you already gain a lot of time. Well there’s PowerShell for that!

It gives you the opportunity to go from a SCOM zero to a SCOM hero by freeing up more time to do the cool stuff… Because let’s face it nobody likes scrolling through alerts, approving agents, checking the status of agents,…

In this session I’ll walk you trough some of the scripts I always implement when running a SCOM project to give me control, flexibility and save me some time in the end.

Apart from these scripts I’ll showcase also the awesome power the vast System Center community can bring you to even more facilitate the troubleshooting, configuration and manageability of your environment. The community is out there… No need to build something which is already out in the open. I’ll give you some pointers to get the community layer in your environment and will encourage you to start sharing in the process…

So if you want to save some time, see some cool pointers + tips and tricks to automate some of those tedious tasks?

Make sure to sign up below for my online webcast!

Hurry up because seats are unlimited!

Register here:

https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032579216&Culture=en-BE&community=0

Flukso Energy Meter Monitoring Pack: Part 4: Seeing it all in action

February 25, 2014 at 12:57 am in Flukso, Monitor your home, operations manager, SCOM, SCOM 2007, SCOM 2012 by Dieter Wijckmans

This blog post is part of a series check out the other posts in this series:

So after all this hard work. To get the data into my MySQL dbase and into SCOM. What can I actually do with it?

This is the second part of  a far greater monitoring project I’m building to basically monitor my house but now I have control over the temperature and heating in my house using the Nest Thermostat monitoring pack AND can check on my power consumption and basically control my electrical bill.

I’ve created the views in the flukso monitoring view for electricity:

printscreen-0162

Nothing much we can do with this view as this is actually giving me a good reading. It’s in fact what we can do with the data which get into SCOM. Because this data is now into SCOM we can use this data to generate alerts when sudden peaks occur.

A cool one I have setup is the peak right around supper. We have an electrical furnace so when someone starts cooking at around 18h (6PM) I now get an alert becaus the total power consumption is above 4000 watt at that time…

So I know now perfectly well when I need to rush home to get in on time for dinner…

Now that I have this data in I can move forward and build a cool demo to show the added value of having this data in.

This is the second part of the puzzle of monitoring my house. In fact this process can also be used when having a solar power installation to see the generated energy on the graph.

In short notice I will be adding Water readings to the graph as well and have another few things I would like to add to the management pack to be able to patrol my house but more on that later.

Flukso Energy meter monitoring pack: Part 3: Get data into SCOM

February 25, 2014 at 12:51 am in Monitor your home, Nest, operations manager, SCOM, SCOM 2012 by Dieter Wijckmans

This blog post is part of a series check out the other posts in this series:

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:

clip_image001

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:

clip_image002

It can be downloaded here: http://scug.be/dieter/files/2014/02/perfdatafrommysqlelectricity.rar

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)

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

clip_image002.jpg

 

Flukso Energy meter monitoring pack: Part 2: Get data into MySQL

February 25, 2014 at 12:51 am in Flukso, Monitor your home, operations manager, SCOM 2012 by Dieter Wijckmans

This blog post is part of a series check out the other posts in this series:

So after we have successfully installed the device and data is flowing to the flukso website we get a nice graph on our dashboard which is available by logging into the website:

clip_image001

Cool… So now we get a clear overview of our energy consumption. But there’s nothing we can do with it basically. We can look at it. Make some adjustments but no alerts, no long term reports nothing…

So as I discussed in the first post there’s an open API which makes the data available locally. This is great. No need to retrieve the data from an external website. It stays inside my own network.

The setup was very similar to my Nest Thermostat approach because I had that framework already in place I planned to use it the same way and get the data in SCOM via the same process.

Again the heart of my setup is my trusty Synology DS412+ hosting my linux distro and my MySQL dbase instance:

clip_image002

How did I get data in?

The setup is very similar to my Nest Thermostat approach. To get the data queried out of the Flukso device I have used the script written by a fellow fluksonian PeterJ (yep that’s the official name of users of flukso): https://docs.google.com/file/d/13wB85cPx_5nykBq3ZShnClHa1rpkRE5edNEluMqxrFaCRlvJrD8Bn_6UDCs9/edit?pli=1

He uses a set of PHP scripts to get the data in.

A high level overview of the install:

  • Connect to your Synology box with Winscp
  • Copy the content of the files to /volume1/web/flukso (Make sure to follow the exact same paths as described on the google drive).
  • Open settings.php and fill in the parameters requested:

<?php
// Rename to settings.inc.php

// DB Settings
define('DB_HOST', 'localhost');
define('DB_NAME', 'flukso');
define('DB_USER', '<fill in a user with rights to create dbase on your Mysql>');
define('DB_PASS', '<fill in the password of that user>);

// Flukso settings
define('FL_ADDRESS', 'ipaddressofflukso:8080/sensor');
define('FL_PASSWORD', ''); //for future use
define('FL_SENSOR1', 'sensor ID');
define('FL_SENSOR2', '');
define('FL_SENSOR3', '');

// Meter settings
define('START_DAY', '070000');
define('END_DAY', '230000');
?>

  • Note the sensor ID can be retrieved from the website in the sensor section (make sure to use the ID and not the token)

clip_image003

  • Run the install.php script by accessing your synology via putty to gain a terminal access (more explanation check the Nest thermostat topic here)
  • At this point the dbase should be created an ready to go:

clip_image004

  • All left to do is create another line in the cron and restart it to get data flowing into our dbase and ready to get extracted by SCOM.
  • The crontab which needs to be changed is located in /etc and is named crontab. The line is in red.

Note: make sure to use TABS between the different columns otherwise the line will be deleted with the next reboot. On a Synology box it is… I don”t know on other linux distro’s but better safe than sorry right:

clip_image005

  • The line that needs to be added: */1    *    *    *    *    root    /usr/bin/php /volume1/web/flukso/cronjob.php

After this install the data should normally be coming in.

I’ve tried to create a brief overview on how to setup the Synology to get the data from the flukso into my own MySQL dbase using a community driven script. However this is a System Center blog so I’m not going to go further in detail here.

If you still have questions either check the flukso forum which has some really active members out there eager to help spread the word on this nifty device: https://www.flukso.net/forum

Or connect with me on twitter @dieterwijckmans so I can assist where needed.

Flukso Energy meter monitoring pack: Part 1: Intro on the device used

February 25, 2014 at 12:50 am in Flukso, Monitor your home, operations manager, SCOM, SCOM 2012 by Dieter Wijckmans

This blog post is part of a series check out the other posts in this series:

This post is part of an ongoing series on how to monitor my house with SCOM and build scenarios based on the data that comes into SCOM.

More info on the blog series here: http://scug.be/dieter/2014/02/19/monitor-your-home-with-scom/

After monitoring the temperature / humidity and heating in my house I now have turned my focus on the aspects that cost basically money. My electrical bill. To get this data in you need an energy meter. I actually have 2 at the moment so I can level them out to see which one is right… Boys and toys right.

clip_image001

They are both of Belgian companies but can be used on any power grid. The above device is called the smappee.

clip_image002

It’s a rather new device with a very spacy exterior and lighting. Indeed it’s connecting quite easy to your environment and it measures everything beautifully. The nice thing about this device is in fact it has a nice shiny app for iPhone and Android so you can get your data while on the road. The coolest thing is in fact that this device is capable of detecting certain patterns on your internal electrical grid to identify certain devices in your household so you can easily pinpoint what the big consumers of power are. This works quite well… The downside of this device however is that there’s up until now no way to get the data from the device towards your own device. This is not open source. Although there’s no additional fee for the website and the apps it’s kind of useless if you want to get the data out and play with it…

More info on the smappee can be found here: http://www.smappee.com/

clip_image003

The device just below is completely different. Although it serves the same purpose: monitoring your power consumption. This device is just a small box which holds a custom made device which was built from the ground up with the open source community in mind. The software is running on a linux distro, dd-wrt for the routing and you have the possibility to access it via a terminal to gain root access and play with the device. The data gathered is logged to the flukso server and nicely graphed on a custom dashboard protected by your user name and password. You get a nice overview of your consumption even in real time. Besides the electrical consumption you can also check water and gas consumption so an all-round device for a little bit less than the Smappee. The cool thing in fact is that you can access the data locally by checking the box in the admin dashboard. This opens up the local API which can be addressed by a simple CURL call.

More info on the Flukso can be found here: https://www.flukso.net/about

Installation?

The installation for both devices was straight forward. As soon as the device came online you needed to connect it to an account on the website and that was it… Now only to get the data into the device.

To use this you need to have a little background of electrical work. Both website come with a huge disclaimer if you are not confident with installing the metering device ask a professional.

What you need to do is clamp a power metering device over the hot wire of your electrical installation behind the meter and before the first fuse in your fuse box:

clip_image004

After connecting the clamp to the device you are good to go to get things monitored. Both devices use the same tech so if you have both just connect both of the clamps to the wire. No cutting is involved.

So this was a blog about System Center right?

True… But I’m also active in the flukso community and promised to give feedback to them as well how I cracked this box open to get all the data into a MySQL dbase. I’ve used a similar approach as the nest thermostat series which can be found here: http://scug.be/dieter/2014/02/19/nest-thermostat-monitoring-pack-part-i-how-did-i-get-data/

So how did I get data?

Still not much System center content but important for the people who are going to use this or try this at their home because face it… Monitoring is our profession and if we can save some money while we are at it… Check out the other parts to find out how I got data in.

Nest Thermostat monitoring pack: Part 4 seeing it in action

February 19, 2014 at 12:35 am in Monitor your home, Nest by Dieter Wijckmans

This blog post is part of a series check out the other posts in this series:

So after all this hard work. To get the data into my MySQL dbase and into SCOM. What can I actually do with it?

This is just the beginning of a far greater monitoring project I’m building to basically monitor my house but now I have control over the temperature and heating in my house.

I’ve created the views in the nest folder for humidity, Heating status and a separate view for target and current temperature.

Humidity view:

clip_image001

Nothing much we can do with this view as this is actually giving me a good reading. Everything between 30 and 60 is healthy condition so no complaints here.

Next in line is the Heating status:

clip_image002

This is basically a Boolean (on or off). The standard graph in Nest is also telling me this but I have to click through some views to get there. Now I can get this in a simple graph in my console wherever I want it.

Saving the best for last the temperature graph

clip_image003

The first 2 graphs are nice to have but this one is actually pretty cool. This is giving me the relationship between the target temp asked by my household at a given time and the actual temperature in my house. Here I can clearly see that it takes approx. 2 hours to get my home heated up (radiant floor) but the heat stays constant for a long time. This is due to the nature or radiant floor and because my house is well isolated. If I overlay the 2 graphs I can clearly see that the temp is rising as soon as my heating is working…

So now I have the data in there. Next step in the process is to create a API control console task to actually change the target temp. This is possible via the API I’ve mentioned so it will be added to the mp in a short while.

Nest Thermostat monitoring pack: Part 3: Create the mp

February 19, 2014 at 12:34 am in Monitor your home, Nest by Dieter Wijckmans

This blog post is part of a series check out the other posts in this series:

The Nest thermostat monitoring pack is in general part of a “monitor your home with scom” series which can be found here: http://scug.be/dieter/2014/02/19/monitor-your-home-with-scom/

downloadbuttonfertig11.jpg

Download the MP I’ve created here: http://scug.be/dieter/?p=1204

Now that we have discussed how to get the data from the Nest website via an api call into our dbase where we were able to get the data via PowerShell into a property bag. It’s now time to get SCOM working with this data.

I’m gathering a sample of the dbase every 5 minutes and the dbase itself is filled with data only when there was a call home from the device to the Nest website. As some of you will probably already know it’s not possible to use a PowerShell script to populate a performance rule in the console itself. You need to have a vbs. In fact it’s not a good idea at all to create a management pack in the console as it will be filled with GUID’s and such.

I’ve used the SCOM 2007 authoring console for quite a long time and am still using it but the biggest disadvantage is the fact it cannot interpret SCOM 2012 mp’s. If you create a MP with the authoring console it will work on both SCOM 2007 and SCOM 2012. But if you try to load a scom 2012 mp into the authoring console you’ll get a schema mismatch because it just can’t cope with the new schema. This makes it impossible to create the mp in the authoring console, load it in the SCOM 2012 management group, make minor modifications and then load it back into the console…

Well then you should use Visual Studio Authoring Extensions… True… But I don’t know Visual Studio. It’s still on my to do list but hey there are many things in there.

So for now I’ve used another great tool: Silect MP Author which is freely available. I made the core rules in there to get the PowerShell performance collection rule in there and then made view modifications and such in the console itself. Ok it’s not pretty but it’s just to showcase the possibilities of SCOM and I plan to integrate this bit into a larger “Monitor your home” mp which I probably rebuild from scratch using VSAE.

So enough chatting. Let’s create this performance rules to get the data we have in our dbase via PowerShell into our management group.

First things first. Download the free Silect MP author tool here: https://bridgeways.com/mp-author-landing-page

Install the tool and open MP Author

clip_image001

Create a new management pack:

clip_image002

Give it a proper name:

clip_image003

Save in a location (I’ll do it by default on my SkyDrive so I can work on my project anytime from anywhere)

clip_image004

Leave the references as is

clip_image005

Choose Empty

clip_image006

Create

clip_image007

Now create a target for our watcher node which is identified by reg key: HKLM\software\nest\watchernode

clip_image008

Right click target and choose group

clip_image009

Check for a server where the regkey is located on. It’s easier to browse than to type in the key.

clip_image010

Supply credentials.

clip_image011

and locate the key: HKLM\Software\nest\watchernode

clip_image012

Give the attribute a name

clip_image013

identify the discovery

clip_image014

and we only want to check whether it exists. We don’t care about the content.

clip_image015

We run this every day.

clip_image016

Create.

clip_image017

Next thing we need to do is create the group of watcher nodes

clip_image018

Fill in the desired expression and click next (note this changed in the final mp I’ve uploaded)

clip_image019

Create the group

clip_image020

So now we need to create the performance rule with our PowerShell we tested earlier on:

clip_image021

Copy paste the script in the script body window.

Make sure to change the credentials in the connection string as discussed in part 2 of this blog series.

clip_image022

Fill in the location variable

clip_image023

Identify the performance rule

clip_image024

Map the content of the property bags to instance and value which scom can use to create the performance dataset.

clip_image025

Leave the schedule as is (more on this later)

clip_image026

Create the script

clip_image027

So now we have a script to get humidity in our environment, the value that is… The same process needs to be followed to get current temperature, target temperature and heating status in as well.

I already did it in the mp I’ve uploaded but I really wanted to show you the ease of use of mpauthor. I plan to do a more thorough blog series on this great tool but this is not in scope of this blog series of course.

So now the scheduler part… We want to collect data more frequent than once a day of course. Turns out it’s not possible to change this in MP author nor in the console after you have loaded the mp. You need to change the xml code itself.

The collection rule is only configured in the mp as daily:

clip_image028

You need to change this to:

clip_image029

And you need to do this for all rules you have created of course.

Note: Like I said before this is just a small showcase of how the management pack is constructed. The management pack which is attached here is slightly different and has some config done in the console in there so it’s not as clean as ID’s concerned. Again I’m planning to rebuild a full mp when I have all my different monitoring aspects in place.

If you want to use the management pack I have created make sure to change the connection string values to your dbase location username and password

So all left to do now is load the MP in your management group and check whether everything is running.

Nest Thermostat monitoring pack: Part 2: Get data into SCOM from MySQL

February 19, 2014 at 12:31 am in Monitor your home, Nest by Dieter Wijckmans

This blog post is part of a series check out the other posts in this series:

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:

printscreen-0109_2

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:

downloadbuttonfertig11.jpg

It can be downloaded here: http://gallery.technet.microsoft.com/SCOM-Retrieve-performance-507293f1

#===================================================================================================
# AUTHOR: Dieter Wijckmans
# DATE: 18/02/2014
# Name: Nest_humidity.PS1
# Version: 1.0
# COMMENT: Get the current humidity value from the nest device from the mysql dbase
#
# Usage: .\Nest_humidity.ps1
#
#===================================================================================================
param($location)
#load the connector but make sure to check the path if you are using a different version
[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=<Fill in your dbase name>;server=<Fill in your server ip>;user id=<fill in user id>;pwd=<not 1234 right?>"
#Call the Connection object’s Open() method:
$myconnection.Open()
#Prepare the property bag
$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
#Run the actual query
$command = $myconnection.CreateCommand()
$command.CommandText = "SELECT humidity FROM data ORDER BY timestamp DESC LIMIT 1";
$reader = $command.ExecuteReader()
#Processing the Contents of a Data Reader we only want the last value
while ($reader.Read()) {
 for ($i= 0; $i -lt $reader.FieldCount; $i++) {
 $value = $reader.GetValue($i).ToString()
 }
}
$myconnection.Close()
$PropertyBag.AddValue("location", $location)
$PropertyBag.AddValue("humidity", $value)
$PropertyBag

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.