Lukasz Rutkowski on Operations Manager

KB2790831 - SCOM Hotfix for Windows Server 2012

2013-02-14T10:52:41Z

Just a short notice to all people who are using or monitoring Windows Server 2012 with System Center Operations Manager 2012 (both RTM and SP1), or managing those with SCVMM 2012 or SCDPM 2012. There is a very important hotfix which fixes a handle leak in WMI. Extract from KB:

This issue occurs because, when Performance Data Helper log files are opened, Pdh.dll creates a new thread by using the CreateThread() API to process the log files. The CreateThread() API then returns a handle to the newly created thread. The handle remains open after these log files are closed, causing a handle leak.

To download the hotfix, go to download location: http://support.microsoft.com/kb/2790831/en-us

Happy monitoring!

How to Reset the Administrators Group in Operations Manager 2012 – Working

2012-11-29T21:20:44Z

Scenario – imagine a quick hit on Delete button while standing on BUILTIN\Administrators entry in Operations Manager Administrators user role window. What happens few hours later? If this was a default setting and no-one was later added to OM Admins group – oh well – no-one is an administrator. That happens. There should be a fix.

NOTE – You can delete BUILTIN\Administrators only, if you added second entry as a member. You can’t be left without any group there (through console of course).

NOTE 2 – To this group you can add only domain groups, not local, so BUILTIN\Administrators are unable to be added.

Step 1 – Go to your favourite search engine and look for the topic above. You will be redirected to TechNet page - http://technet.microsoft.com/en-us/library/hh456441.aspx

Step 2 – You go step-by-step through the guide. You go to paragraph 2, point 2. You go into <drive letter>:\Program Files\System Center Operations Manager 2012\SDK Service State… wait a minute. There no such folder! Default path is <drive letter>:\Program Files\System Center 2012\Operations Manager\Server and even there there is no momauth.xml file. You hit search – it has to be somewhere – and you are punished by your naivety…

Just to be clear – as per Mihai Sarbulescu blog and post on missing Built-In User Roles, the MomAuth.xml is not there anymore.

NOTE: In OM 2007 the user roles and their permissions, roles, etc. were managed through Authorization Manager which is an OS component and the data was stored in an XML file called MomAuth.xml which could be found on the RMS. Now, in OM 2012, this has been changed and AzMan is no longer used - the user roles and security checks, permissionsm information, etc. about the OM Management Group is stored and checked directly in the OM operational database.

Now what? Now let’s go into SQL Server Management Studio and let’s play a bit. There are few new tables there which gives you an idea how the authorization works in OM12 and where to find specific data.

If you are familiar with AzMan and you created a totally custom user role, you now need to familiarize with those most important tables.

AzMan_AzOperation – list of all operations, which can be scoped (i.e. Agent__Deploy, Agent__Get, Agent__Set etc.)

AzMan_AzRoleAssignment – connects User Roles to AzMan profiles

AzMan_AzTask – list of User Roles with their descriptions

AzMan_Role_SIDMember – list of SIDs (stored in DB as varbinary(85) connected to Roles

And the last one is what we want. When you have a deleted an account from OM Admins you will have a table like this (fresh install, in your environment there can be lots of them)

Well, there is something here, so why I don’t see it in console? Because the first one is a default entry for LocalSystem, which is always an OM Admin member, but hidden from list. So how can we add a member here? Well, that’s a bit nasty, but just creating a simple INSERT. But what to insert? A varbinary value for BUILTIN\Administrators is:

0x01020000000000052000000020020000

So just copy the SQL query from below to your Query Window:

INSERT INTO [OperationsManager].[dbo].[AzMan_Role_SIDMember]

([RoleID]

,[MemberSID])

VALUES

(1, 0x01020000000000052000000020020000)

Now re-run the SELECT query.

And let’s check the console too.

OH NO! Aaaah, wait, let’s use /clearcache

Hope this will help anyone when falling into trouble of need to recreate Operations Manager Administration User Role.

DISCLAIMER: THIS SOLUTION IS PROVIDED "AS IS". YOU ARE RUNNING IN ON YOUR OWN RISK AS THIS IS NOT MICROSOFT’S SUPPORTED SOLUTION AT THE DATE OF POSTING.

Where is my SCOM installed?

2012-11-15T07:22:06Z

This will be a short tip on how to get something really useful, especially if you have many OM environment to work with regardless its version. Recently there was a question internally how to run a script or a command and pass SCOM installation directory within XML tag. This was normal in OM07, but install directory has changed in OM12 from

%ProgramFiles%\System Center Operations Manager 2007

%ProgramFiles%\System Center 2012\Operations Manager\Server

If you migrated to OM12, you have a mixture of OM07 and OM12, you don’t want to change your management packs

Method #1:

You can create a junction to this new folder on every OM12 server:

mklink /J “C:\Program Files\System Center Operations Manager 2007” “C:\Program Files\System Center 2012\Operations Manager\Server”

So now everytime something points to first folder it will be really pointing to new one, but will never know about it.

Method #2:

If you can’t make any modifications to folder structure or it is easier to create a global solution, you can populate environmental variable with installation directory from registry, but this will enforce you to change your MPs to reflect your new environmental variable too. If you are writing a new MP – this is probably better solution.

$scomdir = (New-Object –ComObject Wscript.Shell).RegRead("HKLM\SOFTWARE\Microsoft Operations Manager\3.0\Setup\InstallDirectory")

[Environment]::SetEnvironmentVariable("SCOMDir", $scomdir, "Machine")

And then you can use it as:

<OpsMgrInstallPath>"%SCOMDir%"</OpsMgrInstallPath>

Discovery never stops… but tried everything!

2012-10-24T20:05:00Z

So have you ever been in situation where everything you did just didn’t help and every blog post, every forum entry and every link in search engine provided an information which you tried three times or more? Such things happen and then you have to fight on your own (or with little help from Support Escalation Engineers).

Whenever you see a blue bar just going from left to right for ages on discovery wizard, your mind starts to refresh every blog post you’ve seen and remind yourself of three things you have to check before going wild. First thing is a Microsoft SQL Server Service Broker (which was discussed thousand Times). In Operations Manager 2012 SQL Server Broker is enabled on OperationsManager database from default, which eases your pain (http://support.microsoft.com/kb/941409).

Second thing will be great, old Health Service State folder, which can be stale after many operations and this will prevent showing results in discovery pane. Third option – you didn’t specify SPNs for SDK Service correctly. Good blog post and nice bunch of links can be found in Marnix Wolf’s post from two years ago (http://thoughtsonopsmgr.blogspot.gr/2010/08/discovery-wizard-is-running-for-ever.html). What if there’s no luck? What if all of those options fail?

The Mystery

If everything works fine, but one thing doesn’t and you don’t know why – it has to be a permission, as old legends say. But what, why, when? Let’s see exactly how the discovery proces works (from http://blogs.technet.com/b/momteam/archive/2007/12/10/how-does-computer-discovery-work-in-opsmgr-2007.aspx)

Since I’ve been discovering servers only, the option to check if they are accessible is enabled by default, in Alert View I saw multiple entries on no connection to remote host. That means that steps 8 to 10 went through, but returning results didn’t finish. Since step 11 didn’t show proper results and there’s no connection with permissions and returning results from discovery engine, something had to happen before step 8.

One of the steps which are not documented in this list is when SQL Server checks if SDK Account is in the proper group to use OperationsManager’s SQL Server Broker. This is done somewhere between steps 5 and 7.

The Solution

What was the turning point? It was the Application log in SQL Machine which showed such entry:

Log Name:      Application
Source:        MSSQLSERVER
Date:          10/24/2012 4:55:48 PM
Event ID:      28005
Task Category: Server
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      omdb.om.domain
Description:
An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'domain\OmSDK', error code 0x5.

So it turns out that SQL Server service account should be able to check attributes of OMSDK service account. Normally you should see permissions like this:

The real life shows something different. Authenticated Users on the new OU for SCOM accounts didn’t have this entry. In fact it was totally missing. Didn’t want to break anything, maybe it was on purpose, maybe not. Adding Read right to SQL Server account resolved the hanging discovery process.

Hope you can add it to the list of possible solutions of hanging discovery problem.

A nice report on Distributed Application component state

2012-10-09T04:04:00Z

When you need a report and you suddenly see that it’s impossible (or possible, but there are easier ways to accomplish this) via standard reports in Operations Manager, you first starting to fight with a tool that comes in handy on ACS – Report Builder. Well, maybe that’s just me, but playing with RB on standard reporting OM playground is like trying to fish a shark with a jelly bait.

When I was young and full of grace… I tried not to anything which had a bit of Visual part in its name. For me Visual Studio = programming and boring typing of code blocks. That may be true for 90% of work, but fortunately writing (that’s a tough word, composing is more accurate) reports in Visual Studio is a really piece of cake. That’s what I found out lately when pushed to the wall was given a task – report which shows live state of objects which are bits of custom Distributed Application.

Before I go into this task step-by-step, I will confess myself. I tried to do that by copying Kevin Holman’s custom stuff, some example reports, I’ve read few posts on this – nothing lit a candle in my dark space of brain. I’m a type of person who loves two things – getting it done by myself and… wizards. All types of wizards are having +1 for my happiness.

Before you start… make sure you follow few steps, which are needed to be done on Business Intelligence 2008 and 2008 R2 Studio from SQL Server installation (yes, you need to install that to start work – just launch setup and from Shared Features choose that BI one). All necessary steps are available on Oleg Kapustin’s blog.

Let’s start… and to do that we need to prepare a plan. This plan would contain vision, materials and data to work with. Let’s begin with vision.

Vision: A tabular report on main DA part and Level 1 containers which we create in Distributed Application designer. Level 2 report can also be available as a detailed version of our report (real world scenario: business wants less data, administrators – more of it). The report should show graphical indicator of healthy, warning or critical state, not only numeric or word entries. This would be fun!

Materials: This should be prepared basing of our vision. So graphical indicators… will be icons from OM or any other icons you can find for ok, warning or critical values. Best ones would be max 20x20 px. Also we will make use of SQL Server Management Studio to connect to the database, so you can start opening this one right now, as we will need…

Data to work with: I’ve had a discussion whether to work on OperationsManagerDW or OperationsManager database for this report. It’s easier to find all the data in OM live database but there’s one point you should be aware of. Live data is live data – it changes a lot. When you create a small report from one table and this is a state change for example which doesn’t flicker a lot, it’s rather safe. Be sure to use WITH (NOLOCK) clause on SQL queries as this won’t lock tables for query and will allow other queries to run against it without waiting for your process to finish.

How to query an OperationsManager database for health state of components of distributed application? Good question. You can do it in two ways – like a robot or like a brain. Robot method would be to query BaseManagedEntity table for known part names of application. This however is causing a need to rewrite dataset if your DA will change in future. So second method is much nicer. We can use in-built procedure to query for all predecessors of any object. In our case those would be kids of our DA.

------

select s.HealthState, b.DisplayName, UPPER(SUBSTRING(b.FullName, CHARINDEX(':', b.FullName)+1, 50)) as CompService

from dbo.fn_ContainedBaseEntitiesFromList('84BB11C7-29D1-DD9C-C228-3FEC82533499', 2) a

left join dbo.BaseManagedEntity b on

a.BaseManagedEntityId = b.BaseManagedEntityId

inner join dbo.State s on

a.BaseManagedEntityId = s.BaseManagedEntityId

where Depth <= 2 and s.MonitorId = 'E3AB86A1-34FA-35B5-B864-DA4DB993C0F5'

order by b.DisplayName, CompService asc

-------

Let’s explain a bit. dbo.fn_ContainedBaseEntitiesFromList is an in-built procedure which retrieves contained items from specific object (represented as a GUID here) and we can but how many levels down it should return. Funny thing is that every number below 1 returns all contained objects, so when I put two, it gave up to level 6. That is why I restricted values only to level 2 (for admin report) and we can do it also only for level 1. When you want to return only level 1 items (LOB App –> Databases, LOB App –> Web Servers, LOB App –> Services), you don’t need that CompService column as this part trims down those long values which are seen as path in return from query (it cuts it just to have computer FQDN name of instance). So for Level 1 items it would be like this:

------

select s.HealthState, b.DisplayName

from dbo.fn_ContainedBaseEntitiesFromList('84BB11C7-29D1-DD9C-C228-3FEC82533499', 1) a

left join dbo.BaseManagedEntity b on

a.BaseManagedEntityId = b.BaseManagedEntityId

inner join dbo.State s on

a.BaseManagedEntityId = s.BaseManagedEntityId

where Depth = 1 and s.MonitorId = 'E3AB86A1-34FA-35B5-B864-DA4DB993C0F5'

order by b.DisplayName asc

-------

Order by clause is just for fun and to be sure everything is properly arranged. '84BB11C7-29D1-DD9C-C228-3FEC82533499' GUID is my DA main application (like LOB App) and 'E3AB86A1-34FA-35B5-B864-DA4DB993C0F5' GUID is a monitor ID for Entity (so top one). That way I represented in numeric value (s.HealthState) a state of all main parts of DA. The first GUID you should search in BaseManagedEntiity table by your app name.

Having those queries we can then do our first report. Let’s start then. Open Business Intelligence (properly configured).

The first screen allow you to choose from variety of Visual Studio templates. Choose Report Server Project.

In new project window on the right side you will see small box called Solution Explorer. From there right click Reports and choose Add New Report.

My beloved wizard opens :)

On the next screen we will create a data source to our main OperationsManager database (not Warehouse!). In the Select Data Source window click New Data Source and Edit. This will pop-up Properties window which you can intuitively fill in.

You can Test Connection is everything is ok. After accept you can mark this data source as a shared one for future references which is a good idea.

On the next screen paste your query from Management Studio. For our demonstration I will use level 1 query.

Next select Tabular report type.

On Design Table do not drag any fields, just hit Next.

Then choose your layout and complete the wizard. You should see something similar to mine.

Now for some tweaking. As you see my report name will only be about Level 1 items. Let’s hide the Health State column first and then add second one as just State for future images. Click the Health State column and then right click it and choose Column Visibility.

Change the value to Hide.

Now add new column. Again right click Health State column and choose Add New Column to the left. Click a header, then double click and enter State name.

Now for the best part. We need to put images somehow here and change them basing on values of HealthState field (1 is healthy, 2 is warning, 3 is critical, 0 will be uninitialized, so if you want to show this one too, prepare 4th icon for this one).

On the left side click Images and from right click menu choose Add Image.

After you finish you should see your items.

Now drag one of the images from this list into empty field below State. Right click this field then and open Properties.

You can see that this field will use ok image every time. We want to change that. Click fx button. In function field paste this formula:

=Switch(Fields!HealthState.Value="1","ok",Fields!HealthState.Value="2","warn",Fields!HealthState.Value="3","error")

This tells the field that basing on HealthState value it needs to get proper image from those three we imported. As I said you can make another switch option to put Uninitialized value there. When you accept and close all windows you should see an image icon in the field.

When you click Preview above the design pane, you should see also proper report.

Now from the File menu just save this specific report and later import it to you SQL Reporting Server reports website (http://yourssrs/reports and choose Import File to a folder you like).

Happy Reporting! :)