Emre's OpsMgr Blog

SQL MP Extensions - MP Authoring Tutorial Part 1 - Getting Ready

2013-02-21T21:04:00Z

In the main post (here) I published the SQL MP Extensions Management Pack and within this post series I will be providing an Authoring Tutorial explaining the MP. This first post of the tutorial series is focusing how to get ready to start authoring.

To author an MP requires to be confident on the following bullets.

Knowing the Scenario; Learn the problem and scope your solution to an achievable goal.
Knowing the Target Technology; Colloborate with the SMEs of the techonolgy you are going to monitor. Try to understand
- How to monitor, What method?
- What are the cons of the method advised?
Knowing SCOM;
- With what MPElements will you achieve your goal?
  - Do I need a custom MonitorType? Custom DataSource?
  - Is cookdown needed?
- Can this be achievable via SCOM Console?

Knowing the Scenario

It was a common request from customers that they need to figure out unused databases in order to decomission and gain resources back (disk, cpu, memory). So the major idea here was return of unused resources.

Knowing the Target Technology

I queried my SQL colleagues Sibel (CTS) and Batuhan (PFE) on how to pull this information out of SQL. There was no perfect feature for this but we had one. SQL Index Usage Statistics (sys.dm_db_index_usage_stats - http://msdn.microsoft.com/en-us/library/ms188755.aspx)

Sth similar to this will do fine;

select DB_NAME(database_id) as dbname, datediff(dd,max(isnull(isnull(last_user_scan, last_user_update), isnull(last_user_seek, last_system_scan))),GETDATE()) as numofdays from sys.dm_db_index_usage_stats group by DB_NAME(database_id)

My colleagues also warned my that these statistics are reset once the SQL service restarted which is very important on my monitoring model. I was fine with this because my main goal was to figure out if a database was unused for "sometime" so it might reset the state to "green" when the service starts but if it is really unused it will go "red" after the threshold speciefied is reached. Kind of flip-flop but as rare as the service starts.

The only problem might be the databases which are idle for long and only used in long intervals (payrolls db once a month???) but if this is documented (Knowledge Articles) then the DBA and SCOM Admin knows how to override the threshold and the problem is solved.

Knowing SCOM

I now know that index usage tables has the information and I have the sql query that pulls the information as well as the problems I might hit with acceptable solutions. There was nothing stopping me (Really??? - Actually no - just be patient:) ) to use the existing Monitor type Timed Script Two State Monitor and Author the MP Through Operations Console (hell no!).

I would have easily jumped in the Operations Console and create a monitor based on the “Timed Script Two State Monitor” type (I didnt!!!)

But when I finish NEXTing this Wizard My Monitor will be targeted to the SQL 2008 Database Class since I want to affec the health of the Databases. What about if I have a SQL Server with 10 Instances and 50 databases on each instance??? the answer is easy if I use the OOB MonitorType 500 scripts will run at a time. I wouldnt want this to occur on my servers.

The existing MonitorType didnt help in this case (it only does if you have few instances of what you target) and I need to create my own MonitorType which does run the script once and collects data for all (this is named as cookdown).

Tools Required

Authoring a monitortype which has a datasource that supports cookdown is not avaiable in Operations Console. But is available via

MP authoring console – This is retired and we wont use it.
Visual Studio Authoring Extensions – This is the new tool with following major benefits.
- Great documentation on TechNet Wiki
- Management Pack Browser; makes it super easy to find out what you refer to
- Keeps you tidy with MP Fragments / folders

Supports advanced build options (seal the mp, edit mp header)
Solve the problem before you import (Visual Studio Build errors)

A favorite text editor – you always need one, choose your own, I have one : )

SUMMARY

I am now confident that I will need;

to Develop a vbscript that queries to figure out unused Databsaes through Index Usage Statistics of SQL
a custom MonitorType that runs this script but in a way that will not burn cpu with hundreds of vb scripts (cookdown support)
to document my cons so that I can help managing false positive alerts and noise
a way to Show the results (a custom report maybe)
to use Visual Studio Authoring Extensions as the development environment.

My Scope is done for now, on the next post I will be starting the Cookdown supporting datasaource development.

SQL MP Extensions – Unused Databases and More

2013-02-11T15:41:00Z

Figuring out (monitoring) unused SQL databases using SCOM was one of the common requests I got from DBAs of enterprise customers. I authored a management pack (SQL MP Extensions – download available) to address this feature and also took this an opportunity to provide a tutorial for MP authoring with a real life scenario.

I hopefully will be updating this MP with new features and publish the new versions monthly within this post. The MP Authoring tutorial will be a series of different posts explaining the elements of the MP starting with this one.

MP Features (7.0.2.1)

Monitors / Alerts / Reports Unused Databases based on Index Usages (only for 2008 databases currently , next version)

Scripts in this MP support CookDown (only one script per SQL instance)
Uses existing SQL MP Profile (SQL Server Monitoring Account) so no extra security configuration required (if there’s already one)
Well documentation via <KnowledgeArticles> (just check the Product Knowledge of the Rule / Monitor / Reports etc. Everything needed is there).

Noise is pre-filtered (only necessary objects are enabled)

Ready to Run Reports included (objects are pre-filtered)

Support Statement

There is no support for this MP, test and use under own risk
I only will test this mp against Operations Manager 2012 test environment therefore the schema is 2.0, for 2007 users I reccomend to move to 2012

Important Notes

The “Database Usage” Monitor is based on Index usages (kept on masterdb of every instance) and these counts are reset every time the SQL service starts. So this monitor will resetting to green on every server/service restart but if the database is unused again for the same days (default 7) the state will go back to RED. This is not a big deal and does not break figuring out Unused Databases but only worth to notice to inform about the behavior.

Requirements

System Center Monitoring Pack for SQL Server; http://www.microsoft.com/en-us/download/details.aspx?id=10631 has to be installed and configured (please check SQL MP guide)
Currently Workflows are targeted to only SQL Server 2008 Databases.

How to Use the MP

Before importing the MP Ensure that System Center Monitoring Pack for SQL server is installed and configured
Please create a new Management Pack for Overrides and save the Overrides in this New Overrides Management Pack
Import the MP using any method desired
Change the thresholds / Alerting / Intervals as required after checking “Workflows in This MP” table
Please check Product Knowledges, the behavior of the workflows/reports are explained in detail.

Workflows in This MP

Following is the table for the Workflows included in this MP and their defaults

Workflow Type	Name	Data Source	Threshold	Intervals	Enabled By Default	Alerting	Knowledge	Comment
Monitor	Database Usage	Script Based	7 Days	14400	Yes	Yes	Yes	Enabled for Non-System Databases Group(for details please check knowledge of the monitor). Health is reset if SQL service is restarted.
Rule	Collection Rule For Database Usage	Script Based	N/A	14400	No	No	Yes	Enable if a graph is really required (for details please check knowledge of the rule)

Undiscovering / Stop Monitoring SQL 2008 Express Editions

2013-01-21T14:24:00Z

In this post I will try to cover an alternate method of undiscovering SQL Express editions other than using the ExcludeList Property where I have a decent reason to do so. The MP explained in detail is also attached to the post so that you can test an deploy.

Even though SQL MP has ExcludeList override property for “Discover SQL Server 2008 Database Engines (Windows Server)” this property requires manually specifying Instance Names in format of (SQLExpress;MICROSOFT##SSEE). Therefore if a 3rd party application using instance name other than the default ones SCOM Admin has to know the name and exclude it manually for the discovery. This method is very well documented in Kevins Blog.

My method is a bit more complex but more dynamic so that SCOM Admin has almost nothing else to do. For the ones who are not interested how this method works ,they can use the MP attached in this blog of course after testing and reading How to Use The MP section.

The High Level Logic;

Create a custom seed class whose base class is Microsoft.SQLServer.2008.Seed
Discover this custom class and add property of Edition using a wmi query through Microsoft.Windows.Discovery.WMISinglePropertyProvider2
Create a custom Group of Computers which contains the Custom Seed Class
Discover the Members of the Group
Disable the DB Engine Discovery in SQL MP through an Override for our Custom Group
Remove disabled instances

With the above logic you will be able stop discovering SQL DB Engines on the Computers where SQL Express editions is installed.

I am sure you are wondering what will happen to the group (in step 3 above) once the instances are removed (step 6). This a question I got from my friends that “wont the group be emptied once the instances are deleted??” The answer is a big “NO”. That’s why I created a custom seed class taking the SQL seed class as the base class which is upper than the DB engines (DB engines will be removed not the seed) in the hierarchy. I also added the edition property to my seed class (please check step 2 details below in MP Explained) so that I can group filtering the seeds of express editions.

Important notes

if a computer has SQL express edition plus the Standard or Enterprise edition all of them will be undiscovered (but I guess this is a very minor case but just wanted to point out if you have such configurations).
the discovery for the seed class has an interval of one hour (it will be ok in terms of performance since this is a basic wmi discovery). If SQL DB Engine discovery runs before our seed class discovcery than the sql express edition will be discovered. This is not going to happen frequently but once a month running Remove-SCOMDisabledClassInstance would do the trick

Recommended Reading

Extending Windows Computer Class using VSAE; I wont be starting from scratch to mp Authoring but this post will do fine for the ones who want to adapt to Visual Studio Authoring.

MP Explained

1) Create a custom seed class; First we need to declare our seed class which is based on the seed class in SQL MP.

2) Discover this custom class; The edition string (PropertyStrValue) exists in WMI of the SQL Server computer under Root\Microsoft\SQLServer\ComputerManagement10 Namespace on SqlServiceAdvancedProperty. to discover this property I used Microsoft.Windows.Discovery.WMISinglePropertyProvider2 Data Source (documented here)

<Discovery ID="SQLExpress.RemoveMP.SQLSeed.Class.Discovery" Comment="Discovers SQL Edition Seeds" Target="MS2D!Microsoft.SQLServer.2008.Seed" Enabled="true" ConfirmDelivery="false" Remotable="true" Priority="Normal">
<Category>Discovery</Category>
< DiscoveryTypes>
    < DiscoveryClass TypeID="SQLExpress.RemoveMP.SQLSeed.Class">
      <Property TypeID="SQLExpress.RemoveMP.SQLSeed.Class" PropertyID="SQLEdition" />
    </DiscoveryClass>
</DiscoveryTypes>
< DataSource ID="DiscoveryDS" TypeID="Windows!Microsoft.Windows.Discovery.WMISinglePropertyProvider2">
    < NameSpace>Root\Microsoft\SQLServer\ComputerManagement10</NameSpace>
    <Query>SELECT * from SqlServiceAdvancedProperty WHERE propertyname='SKUNAME' and PropertyStrValue like '%Express%'</Query>
    <Frequency>3600</Frequency>
    < ClassID>$MPElement[Name="SQLExpress.RemoveMP.SQLSeed.Class"]$</ClassID>
    < PropertyName>PropertyStrValue</PropertyName>
    < InstanceSettings>
      <Settings>
        <Setting>
          <Name>$MPElement[Name="SQLExpress.RemoveMP.SQLSeed.Class"]/SQLEdition$</Name>
          <Value>$Data/Property[@Name="PropertyStrValue"]$</Value>
        </Setting>
        <Setting>
          <Name>$MPElement[Name="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Name>
          <Value>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Value>
        </Setting>
      </Settings>
    </InstanceSettings>
</DataSource>
< /Discovery>

Once the above discovery works you will notice this custom seed class instances in Discovered Inventory.

3. Create a custom Group; Creating a custom group is easy we just first need to declare the class like any other classes but use the Microsoft.SystemCenter.InstanceGroup class as the base class and chance the class type to Singleton.

4. Discover the Members; We will use GroupPopulator like in all other group membership discoveries but will utilize the <Contains> expression documented here and sample mps here. This discovery will populate the Microsoft.Windows.Computer instances which contains our custom seed class (step 1 - SQLExpress.RemoveMP.SQLSeed.Class)

<Discovery ID="SQLExpress.RemoveMP.SQL.Express.Group.Discovery" Enabled="true" Target="SQLExpress.RemoveMP.SQL.Express.Group" ConfirmDelivery="true" Remotable="true" Priority="Normal">
<Category>Discovery</Category>
< DiscoveryTypes>
    < DiscoveryRelationship TypeID="SCIGL!Microsoft.SystemCenter.InstanceGroupContainsEntities" />
</DiscoveryTypes>
< DataSource ID="DiscoveryDS" TypeID="SC!Microsoft.SystemCenter.GroupPopulator">
    < RuleId>$MPElement$</RuleId>
    < GroupInstanceId>$MPElement[Name="SQLExpress.RemoveMP.SQL.Express.Group"]$</GroupInstanceId>
    < MembershipRules>
      < MembershipRule>
        < MonitoringClass>$MPElement[Name="Windows!Microsoft.Windows.Computer"]$</MonitoringClass>
        < RelationshipClass>$MPElement[Name="SCIGL!Microsoft.SystemCenter.InstanceGroupContainsEntities"]$</RelationshipClass>
        <Expression>
          <Contains>
            < MonitoringClass>$MPElement[Name="SQLExpress.RemoveMP.SQLSeed.Class"]$</MonitoringClass>
          </Contains>
        </Expression>
      </MembershipRule>
    </MembershipRules>
</DataSource>
< /Discovery>

5. Disable the DB Engine Discovery; This is just an override to DB Engine Discovery for our class created in step 3 (SQLExpress.RemoveMP.SQL.Express.Group).

<Overrides>
< DiscoveryPropertyOverride ID="SQLExpress.RemoveMP.DisableDBEDiscovery.Override" Context="SQLExpress.RemoveMP.SQL.Express.Group" Enforced="false" Discovery="MS2D!Microsoft.SQLServer.2008.DBEngineDiscoveryRule.Server" Property="Enabled">
< Value>false</Value>
< /DiscoveryPropertyOverride>
< /Overrides>

The whole MP is attached to this post so that you can also see the references, displaystrings how the mp comes together.

6. Remove disabled instances; just run the Remove-SCOMDisabledClassInstance command on OpsMgr Shell

How to use the MP

Import the mp (like in all other mps)
Wait till you get the mp active in your sql agents and you notice the seed class is populated (check the screenshot in step 2 of MP explained section above) and also you will see the members populated for the group in powershell. Once you verify you can proceed with the next step.

Open SCOM Powershell console and run the following command.

Remove-SCOMDisabledClassInstance

REFERENCES;

1) Stop monitoring SQL Express and Windows Internal Database
http://blogs.technet.com/b/kevinholman/archive/2010/02/13/stop-monitoring-sql-express-and-windows-internal-database.aspx

2) Microsoft.Windows.Discovery.WMISinglePropertyProvider2
http://msdn.microsoft.com/en-us/library/ee692988.aspx

3) ExpressionType (GroupPopulationSchema)
http://msdn.microsoft.com/en-us/library/ff472337.aspx

4) Creating a Group of Windows Computer and Health Service Watcher Objects
http://blogs.technet.com/b/jimmyharper/archive/2012/03/20/3487667.aspx

Extending Windows Computer Class using Visual Studio Authoring Extensions (VSAE)

2012-11-06T22:38:00Z

We can utilize Operations Manager Console to create attributes to existing classes (if you havent met Kevin’s great blog with a real life scenario please do so). Using SCOM console is great but when you need to add only ONE Property.

Once you need a second or third property, advanced authoring MP is required. The real life case might be sth like;

“We want to create Views, set Overrides , set Notifications for specific to the Departmant that owns the Server and/or for the test environments. ”

I wanted to take this scenario as an opportunity to use VSAE for authoring.

We will achieve this via deploying a registry key (during server provisioning, via a startup script – whatever you prefer) and then extending Windows computer class based on this registry key.

Once we have the registry entries deployed time to author the MP but before start authoring followings are required

Visual Studio 2010 Professional
Vsual Studio Authoring Extensions (http://www.microsoft.com/en-us/download/details.aspx?id=30169)

Basic things to know for a kick start VSAE Authoring

Management Pack Versions; There are two options while creating a Project.

Opearations Manager 2012 Add-On Management Pack; This is for only OM12 (Schema is 2.0). Use this Project if you are going to utilise the new Schema (widgets etc) in your MP and you wont be needing this MP to be imported to 2007 management group.
Operations Manager Core Monitoring Management Pack; This is valid for both versions (Schema is 1.0) 2007 and 2012. Use this Project if you wont need the widgets etc in your mp. (In our scenario in this post we will use the this one)

MP Fragments; These are part of the MP and are XML containining the class/discovery (for our case we will only need a class and a discovery) definitions. These fragments (we may call them small part of MPs) are merged into the resulting MP XML (or even the .mp file – VSAE is capable of providing the seal management pack) that can be found in the bin\debug folder of the Project). MP Fragements are .mpx files those are added to the solution and contains the actual mp xml parts within the <ManagementPackFragment></ManagmentPackFragment>.

Management Pack Templates; There are two types and we will be using the simple one.

Simple Templates; These are basic xml skeletons (skelaton ready mp fragments) and you need to edit the xml to complete the element. We will be using Class Simple Template to create our extended Windows.Computer class in “Computer Class.Mpx”
Advanced Templates; We wont be using these ones in this sample but just for simple explanation, these are half ui – half xml templates. Used for Workflows especially. Personally I prefer editing the xml myself so my discovery will be pure XML Editing.

Folders; To be more organised you can use folders to keep our fragments (.mpx files).

With the above kickstart my Project will look sth like this;

Highlevel Steps

Design Your MP (decide your class type, what datasources you will use etc)
Create VSAE Project (we will be using the Core One – Schema 1.0)
Add Folders as necessary (my preference here is to add folder containing class declarations and a folder for discoveries)
Add MP Fragments using the Templates (one for class one for discovery) and Edit the XML
Check for errors , correct them.
Generate the MP
import into Management Group

Design Your MP

From the simplest to the most complex mps the author needs the design. In our case we will need a custom class based on the Windows.Computer Class and the datasource module we will use is Microsoft.Windows.RegistryDiscoveryProvider (http://msdn.microsoft.com/en-us/library/ff400189.aspx) because this provider lets multiple keys discovered at the same time for the class. The Provider that Operations Manager Console uses the Microsoft.Windows.RegistryDiscoverySingleProvider http://msdn.microsoft.com/en-us/library/ff400197.aspx which is limited by one key and is the reason for this post : ) )

Create VSAE Project

Easiest step. Just create an MP Project choosing the core on.

Add Folders

Create two folders name what ever you like, mine were “Class” and “Discovery” (see Folders section above).

Add MP Fragments and Edit the XML

First we need our class to be declared and defined to do so add a “new item” under the “Class” folder and select the basic xml template for “Class”

And then fill the XML skelaton as follows (added comments that might help in xml). Save close and rename this fragment as “computer class.mpx”

        <DisplayString ElementID="CONTOSO.CustomComputerDiscovery.Computer">
          <Name>CONTOSO Computer</Name>
          <Description>Custom Computer Class for ING</Description>
        </DisplayString>
        <DisplayString ElementID="CONTOSO.CustomComputerDiscovery.Computer" SubElementID="Owner">
          <Name>CONTOSO Computer Owner</Name>
          <Description>TH Owner</Description>
        </DisplayString>
        <DisplayString ElementID="CONTOSO.CustomComputerDiscovery.Computer" SubElementID="Environment">
          <Name>CONTOSO Computer Environment</Name>
          <Description>Test, Prod or Development</Description>
        </DisplayString>
        <DisplayString ElementID="CONTOSO.CustomComputerDiscovery.Computer" SubElementID="ServiceName">
          <Name>CONTOSO Computer Service</Name>
          <Description>Service that owns CONTOSO Computer</Description>
        </DisplayString>
      </DisplayStrings>
    </LanguagePack>
</LanguagePacks>
</ManagementPackFragment>

Now we need the Discovery to be created , I wont be using the Advanced template as mentioned earlier since this is a very basic discovery I will edit the XML from scratch refering the providers MSDN Sample. To do so please add a “new item” under the “Discovery” folder naming “Computer Discovery.Mpx” and by selecting the “Empty Management Pack Fragment Template”.

Paste the below fragment (I added comments that might help)

<Category>Discovery</Category>
    <DiscoveryTypes>
    <DiscoveryClass TypeID="CONTOSO.CustomComputerDiscovery.Computer">
        <Property TypeID="CONTOSO.CustomComputerDiscovery.Computer" PropertyID="Owner" />
        <Property TypeID="CONTOSO.CustomComputerDiscovery.Computer" PropertyID="Environment" />
        <Property TypeID="CONTOSO.CustomComputerDiscovery.Computer" PropertyID="ServiceName" />
     </DiscoveryClass>
    </DiscoveryTypes>

   <DataSource ID="ComputerDiscoveryDS" TypeID="Windows!Microsoft.Windows.RegistryDiscoveryProvider">
      <ComputerName>$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
        <RegistryAttributeDefinitions>
        <RegistryAttributeDefinition>
          <AttributeName>Owner</AttributeName>
          <Path>SOFTWARE\CONTOSO\ComputerProperties\Owner</Path>
          <PathType>1</PathType>
          <AttributeType>1</AttributeType>
        </RegistryAttributeDefinition>
        <RegistryAttributeDefinition>
            <AttributeName>Environment</AttributeName>
            <Path>SOFTWARE\CONTOSO\ComputerProperties\Environment</Path>
            <PathType>1</PathType>
            <AttributeType>1</AttributeType>
        </RegistryAttributeDefinition>
        <RegistryAttributeDefinition>
        <AttributeName>ServiceName</AttributeName>
        <Path>SOFTWARE\CONTOSO\ComputerProperties\ServiceName</Path>
        <PathType>1</PathType>
        <AttributeType>1</AttributeType>
      </RegistryAttributeDefinition>
      </RegistryAttributeDefinitions>
      <Frequency>86400</Frequency>
      <ClassId>$MPElement[Name="CONTOSO.CustomComputerDiscovery.Computer"]$</ClassId>
      <InstanceSettings>
        <Settings>
          <Setting>
            <Name>$MPElement[Name="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Name>
            <Value>$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Value>
          </Setting>
          <Setting>
            <Name>$MPElement[Name="CONTOSO.CustomComputerDiscovery.Computer"]/Owner$</Name>
            <Value>$Data/Values/Owner$</Value>
          </Setting>
          <Setting>
            <Name>$MPElement[Name="CONTOSO.CustomComputerDiscovery.Computer"]/Environment$</Name>
            <Value>$Data/Values/Environment$</Value>
          </Setting>
          <Setting>
            <Name>$MPElement[Name="CONTOSO.CustomComputerDiscovery.Computer"]/ServiceName$</Name>
            <Value>$Data/Values/ServiceName$</Value>
          </Setting>
        </Settings>
      </InstanceSettings>
    </DataSource>
</Discovery>
</Discoveries>
</Monitoring>

        <DisplayString ElementID="CONTOSO.CustomComputerDiscovery.ComputerDiscovery" SubElementID="ComputerDiscoveryDS">
          <Name>CONTOSO Computer Discovery DataSource</Name>
          <Description>Registry Discovery Data Source based on Microsoft.Windows.RegistryDiscoveryProvider</Description>
        </DisplayString>
        <DisplayString ElementID="CONTOSO.CustomComputerDiscovery.ComputerDiscovery">
          <Name>CONTOSO Computer Discovery</Name>
          <Description>Registry Discovery based on Microsoft.Windows.RegistryDiscoveryProvider</Description>
        </DisplayString>
      </DisplayStrings>
    </LanguagePack>
</LanguagePacks>
</ManagementPackFragment>

Check for Errros and Correct Them

While building the solution VSAE will let you know if there are any issues like syntax errors, logical issues etc.

Generate the MP

Press F6 or click Build\Build Solution in the menu bar. You will notice the MP XML output in the Bin\Debug in your Project Folder.

Import the MP

VSAE has the capability of importing to the MG during build but I do this old school way by importing through Operations Manager console.

Once the Discovery Runs for the comptuers those have the registry keys deployed you will see the properties populated. I wont be going through create a dynamic group in this post but just to refer again you can check with the Kevins post refered and use the groups in creating views, setting overrides or notifications.

I hope you enjoyed this post and helps as a kick start to VSA MP Authoring.

REFERENCES;

Kevins “Creating Custom Dynamic Computer Group” Post - http://blogs.technet.com/b/kevinholman/archive/2009/06/10/creating-custom-dynamic-computer-groups-based-on-registry-keys-on-agents.aspx
Microsoft.Windows.RegistryDiscoverySingleProvider - http://msdn.microsoft.com/en-us/library/ff400197.aspx (the provider scom console uses to create attributes)
Microsoft.Windows.RegistryDiscoveryProvider - http://msdn.microsoft.com/en-us/library/ff400189.aspx (the provider I used to be able to use multiple keys as properties)
Technet Wiki for using VSAE - http://social.technet.microsoft.com/wiki/contents/articles/5236.visual-studio-authoring-extensions-for-system-center-2012-operations-manager-en-us.aspx

Activating Expired OM 2012 to RTM

2012-10-12T13:23:00Z

As we all know, Operations Manager 2012 requires activation through entering the well-known powershell command “set-scomlicense”. This is documented in http://support.microsoft.com/kb/2699998

Even though the license isn’t entered, OM12 will let you work 180 days and at the end SDK service will not authorize the connections from ANY console including powershell – hey wait! how am I supposed to run the powershell command then??? – wait till the end of the post

Before diving into the details please check your license status using “Get-SCOMManagementGroup” and if your MG is not expired please set the license ASAP not to cause an operations outage.

The exception details can be found in Operations Manager Event Log , Event id 26319 (a generic authorization event);

Exception message: You have exceeded the evaluation period of this product. Please upgrade to the retail version to continue using the product. For information on purchasing the retail version of this product, go to http://go.microsoft.com/fwlink/?LinkID=74446.
Full Exception: System.UnauthorizedAccessException: You have exceeded the evaluation period of this product. Please upgrade to the retail version to continue using the product. For information on purchasing the retail version of this product, go to http://go.microsoft.com/fwlink/?LinkID=74446

With Operations Manager 2012 Update Rollup 2 license can be entered after the period expired but if only you can connect to SDK right?? (Actually No)

If you Install UR2 and try to open SCOM PowerShell console (Operations Manager Shell) you will receive the same exception because the console will try to connect to SDK service. However the Set-SCOMLicense cmdlet does not require SDK connection and does its job on the Management Server side with UR2.

The easier solution (Solution 1) is based on bypassing the SDKconnection requirement by just loading the OM module but not connecting to SDK, the second option (Solution 2) is a Disaster Recovery of an MS (actually all MSs)

Solution 1;

The high-level steps of Activating the expired OM12;

Update to UR2 For details on UR2 please check Kevin’s Blog http://blogs.technet.com/b/kevinholman/archive/2012/07/31/opsmgr-2012-update-rollup-2-ships-and-my-experience-installing-it.aspx
On the updated MS open a Windows PowerShell Console (with run as Admin of course)
Load OperationsManager Module

import-module OperationsManager

set the license without connecting to SDK

Set-SCOMLicense -ProductId "yourlicensekey“

Restart SDK Service
Please wait a couple minutes if you get a timeout, it might take sometime for SDK to initialize

Solution 2;

In case the above steps don’t work (I hope it won’t be required) the second option is to run a disaster recovery for the management servers for details http://technet.microsoft.com/en-us/library/hh531578.aspx.

The high-level steps of MS Recovery are as follows – This has to be applied on all Management Servers;

Uninstall the MS role
Reinstall the MS using the “to recover a Management Server” section in above technet article.
Reenter all RunAs Accounts (all of them) using the steps “to Reconfigure the RunAs Accounts” and “To Remove SQL server Authentication” sections of the same article
Once you can connect to SDK run Set-SCOMLicense

I hope this explains and helps.

References;

Disaster Recovery in System Center 2012 - Operations Manager - http://technet.microsoft.com/en-us/library/hh531578.aspx
Kevin’s UR2 Experience - http://blogs.technet.com/b/kevinholman/archive/2012/07/31/opsmgr-2012-update-rollup-2-ships-and-my-experience-installing-it.aspx
How to add a product key to an eval version of System Center 2012 Operations Manager - http://support.microsoft.com/kb/2699998 (please note this valid before the expiration)

Adding Class Filter to Object Picker For Custom SCOM Reports

2011-10-18T11:45:00Z

We all know selecting the correct object type in the reports had been an issue since RTM till R2. With SCOM R2 Object Picker Control has a new feature that allows you to filter report author to put a filter so that the Report Operator does not need figure out which one is right.

New Management Packs usually utilize this feature and the reports are pre-filtered for us. In this post we will be adding this functionality to our custom reports those are Linked and as the sample report we will be using the one in my previous post here.

Once the following steps were applied our report will have the functionality of out of box filtering.

High Level Steps

Find out the class system name to filter
Download the RPDL file from Report Manager
Edit the RPDL (maybe a rename to xml)
Upload the RPDL to Report Manager

Find out the class name to filter

Our report is based on the “Computer Not Reachable” monitor. Monitor Target is the class default name which is “Health Service Watcher”.

But in the RPDL we are going to edit we will be using the ManagedEntityTypeSystemName. The following query will help finding out this (highlighted)

The we are going to use is the Microsoft.SystemCenter.HealthServiceWatcher.

Download the RPDL

To Download the RPDL browse to the folder and download

Edit The RPDL

The RPDLs are in XML format you can use any xml editor of your choice. I have cut the section in the xml which is our concern in this case

is the Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.MonitoringObjectXmlPicker control which is our object picker.

By adding the property nodes circled with filtering the highlighted class (gathered in the first step)

Once our properties were edited the RPDL don’t forget to save it before upload :).

Upload the RPDL

Upload is not worth to mention other then;

keep the RPDL name save with the RDL name
Don’t forget to Overwrite the existing one

References:

How to Create a Linked Availability Report - http://technet.microsoft.com/en-us/library/ff833010.aspx

Using Linked Reports in SCOM to Create Custom Availability Reports for Specified Monitors

2011-10-13T11:14:00Z

As a SCOM engineer we face a common scenario when the Generic Report Library Availability Reports returns more than we expected. This is because the Generic Report returns the result for System.Health.AvailabilityState including the states that we don’t want to include in our reports.

For example; Lets think of the scenario we are just interested in The Computer Not Reachable Monitor (the one that does a wmi ping when heartbeats are missed) and would like to provide just the Availability of this monitor. If we used the generic availability it will be including the action account misconfigurations, heartbeats etc.

This blog post tries to address this kind of customization using linked reports and at the end you will be able to create a report like this (note the highlighted monitor name).

This solution can be very useful in if you are interested in the duration of the peak of a performance counter like total CPU utilization peak durations by just changing the MonitorName parameter explained in this post.

There’s a great blog on details of the linked reports (please check references) of Eugene covering the xml internals of the linked reports with almost the same example scenario.

High Level Steps

Create A linked Report to existing Availability Report in Generic Report Library
Customize the linked Report to filter the monitor (change the MonitorName parameter) that we require in our scenario
Copy the Report Definition File of the Original Availability report to use the controls in scom reporting.
Run the report.

Creating the Linked Report

The report and the we are going to link is in the The report in this example we are going to link is in DataWarehouse library. We will be using report manager for this (default: http://servername/reports).

The report we are going to link (the Availability) report and the RPDL to be copied are both highlighted.

In the properties of the Availability Report we use the Create Linked Report button/link (this is the same for 2005 reporting services and 2008) and then specify the name and target location of where our new linked report will be stored. Note that the default is the same location of the existing report so it would be a good practice to create in a custom folder.

Customize the linked Report To filter for the desired monitor

Now we have our linked report (Computer Not Reachable Availability) in Emres Custom Reports Folder waiting to be customized. First we browse to the properties of the report.

Under Parameters we will be changing the MonitorName to from System.Health.Availability to HealthService.ComputerDown

We need to use the Monitor System Name to pass to MonitorName Parameter. Using a query similar to the following one will help in figuring out the System Name for the monitor. In

Copy the RPDL to be able to use the controls

We now have our linked report customized for the desired monitor (Computer Not Reachable) and to be able to use the SCOM Reporting controls like datetime picker, objectpicker etc. we need to provide the report definition file. (RPDL is highlighted in the “creating linked report” section).

From the Datawarehouse Report Library Folder copy the Microsoft.SystemCenter.Datawarehouse.Report.Availability.rpdl to the folder where the linked report exists with the same name of the report (highlighted)

Run The Report

Running the report is just the same experience with the existing availability reports, we just need to be careful in adding objects that are of the target type of the monitor we filtered.

Things not to forget

Be careful in adding the objects of the target class of the monitor to your report
Use the Monitor System Name in the linked report not the display name.

Rerefences:

How to Add linked reports to an MP - http://blogs.msdn.com/b/eugenebykov/archive/2007/05/21/linked-reports.aspx
Great detail on the XMLs for the SCOM linked Reports - http://blogs.msdn.com/b/eugenebykov/archive/2007/09/14/linked-availability-reports.aspx

Empty Exchange 2010 Server Statistics/Mailflow Reports if default log directory has changed

2011-09-26T11:51:00Z

Exchange 2010 mp 14.02.0071.0

You might be experiencing empty report issues for the following reports if the default log paths for exchange message tracking are changed.

Daily Mailflow Statistics
Hourly Mailflow Statistics
Distribution Group Usage

This post is covering the solution and the way that Exchange 2010 MP collecting the data for these Reports.

Exchange 2010 MP utilizes System.ApplicationLog.GenericCSVLogReader module type as the datasource to collect data from message tracking logs for server statistics. The overridable parameters for this data source (therefore the rules using this datasource) are LogFileDirectory and LogFilePattern.

There are 6 rules using this datasource module with write actions collecting the data to DW in Microsoft.Exchange.2010.Reports.mp.

These rules collect the data which can be viewed through the vServerStatisticsRawV14 view in DW. Then the data is aggregated to hourly and daily views using custom aggregations (lest pause our story here and leave these custom aggregations not to distract from the root issue) so that the reports (mentioned in the beginning) query.

The default LogFileDirectory for all these roles (edge and hub transport roles) includes the default exchange installation path are as follows.

For ServerStats:

For ActiveUserStats:

If the Exchange Admin Decides to change this path to a custom one then the rule in OpsMgr wont be aware of the change unless the OpsMgr Admin overrides and therefore wont be collected to DW.

Solution

The following Exchange shell command could be used to determine which servers are not on defaults.

for determining the ActiveUserStatistics “get-transportServer | Format-Table Name,ActiveUserStatisticsLogPath” can be used.

Once the servers and the custom LogFileDirectories are Identified the following rules needs to be overrided for the new ones.

Below is a simple table with what to override and how.

Target	Rule Name	Override	Example
Edge Transport	Microsoft.Exchange.2010.Reports.Transport.Edge.ActiveUserMailflowStatistics.DataCollection	LogFileDirectory	F:\Exchange Logs\ActiveUsersStats
Edge Transport	Microsoft.Exchange.2010.Reports.Transport.Edge.ServerMailflowStatistics.DataCollection	LogFileDirectory	F:\Exchange Logs\ServerStats
Edge Transport	Microsoft.Exchange.2010.Reports.Transport.Edge.ServerMailflowStatisticsNew.DataCollection	LogFileDirectory	F:\Exchange Logs\ServerStats
Hub Transport	Microsoft.Exchange.2010.Reports.Transport.Hub.ActiveUserMailflowStatistics.DataCollection	LogFileDirectory	F:\Exchange Logs\ActiveUsersStats
Hub Transport	Microsoft.Exchange.2010.Reports.Transport.Hub.ServerMailflowStatistics.DataCollection	LogFileDirectory	F:\Exchange Logs\ServerStats
Hub Transport	Microsoft.Exchange.2010.Reports.Transport.Hub.ServerMailflowStatisticsNew.DataCollection	LogFileDirectory	F:\Exchange Logs\ServerStats

References

System.ApplicationLog.GenericCSVLogReader - http://msdn.microsoft.com/en-us/library/ff427477.aspx

Exchange 2010 MP Guide - http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=692