I recently took a chance and opened up a SCOM management pack xml definition file and I was elated at what I found. The default management packs from Microsoft come chock full of very nice goodies. I am talking about lines and lines of VB script code that do everything from report drive space to identifying instances of SQL Server running on your network. These packs are meant to be run from SCOM on the individual machines but if you're like me you love to tinker and know just how something works. I took some of this VB script code and easily converted a few interesting methods to VB.Net methods and quickly created some very quick fun stuff to report on some of the important things on my servers in a UI that I control.
For example, I opened the Microsoft.SQLServer.2005.Discovery.xml file and found some great goodies for poking around a 2005 instance. I now think of these management pack files as my own personal code snippet library. There is a host of methods for reading registry values that are specific to SQL Server, no more fumbling around with searching countless posts to find this stuff simply open the pack! There are even clips in there for reading information using WMI, what could be easier?
For example there is method found in the Microsoft.SQLServer.2005.Discovery.xml pack that simply gets the running instances - GetRunningInstances(ByVal aInstances, ByRef aNonRunningInstances) - now you would have to create the connection object, create the objects needed to support this method, then use this method to see if the passed in instance is running using WMI. What I am trying to show is that just about everything you need is present and can really give you some nice ideas for creating a custom toolbox.
Simply go to Microsoft and download the free trial of SCOM, install it (and I HIGHLY recommend installing it on a virtual machine!), and poke into the management packs as much as you want.
I found the code in the packs amazing and they sparked my curiosity when it comes to peering into SQL Server instances and discovering some valuable information. Of course you need certain levels of privileges to report on some of these attributes but that is nothing a good service account can't take care of. The code is easily converted to your language of choice and is far easier than hunting and pecking around forums for the equivalent. It's all in one place and really has the opportunity for some useful automation and reporting capabilities in your own custom solution. The beauty of it is this is all code that can be found spread out on the web all found in very simple format in one place.
I use it to complete my ideas and validate that some useful monitoring or reporting task within SQL Server can be done and help me just start the code to completing this task.
Stick around I will try and post something in the future that shows some of this code in action and some of the interesting things you can pull out of these packs to put these code snippets to work for you.