Thomas Ellermanns view on System Center

Data Warehouse problems after upgrading to SP1

2013-05-13T11:59:00Z

Recently I discovered that an otherwise working SCSM installation had begun to fail the MP synchronization after upgrading to SP1.

The following error was logged in the event log:

Event ID: 33333

Description:

Data Access Layer rejected retry on SqlError:
Request: Ral_ExecuteSql -- (statement=exec('IF OBJECT_ID(''[dbo].[WorkItemAffectedUserFact_2013_Jan]'') IS NULL
BEGIN
CREATE TABLE [dbo].[WorkItemAffectedUserFact_2...), (RETURN_VALUE=0)
Class: 16
Number: 1779
Message: Table 'WorkItemAffectedUserFact_2013_Jan' already has a primary key defined on it.

This is caused by a problem with the management packs trying to sync to the Data Warehouse.

What's happening is that some of the data warehouse management packs are trying to redeploy after the upgrade to SP1. Several other management packs under the Data Warehouse wunderbar are in a "waiting" state, and the "System Center Data Warehouse Base Library" MP is in a "failed" state. Looking at a DB trace, you can see that when the MP tries to sync, the T-SQL is querying to see if a "base" PK exists, finds that it does not, and tries to create it

To recover from this:

1. Create a backup of your DWDataMart, DWStagingAndConfig, and DWRepository DBs.

2. For each primary key that shows up in your event logs with the message e.g. "Message: Table 'ConfigItemServicedByUserFact_2013_Jan' already has a primary key defined on it.", delete the primary key from the DWRepository DB. For me there were around 70 of them in total that were causing the "System Center Data Warehouse Base Library" MP to fail. This can be done with a T-SQL statement such as the following:

USE DWRepository ALTER TABLE
dbo.BillableTimeHasWorkingUserFact_2013_Jan dropconstraint
[PK_BillableTimeHasWorkingUserFact_2013_Jan]

Note that your fact table might be a different year and/or month than shown in the example above.

3. Go to the management packs under the Data Warehouse wunderbar and click on the "redeploy" task on the failed MP.

Once you delete the primary keys, the SQL call will be able to successfully create the new primary keys on the affected tables and the "System Center Data Warehouse Base Library" MP will now be imported, allowing the MPs in a "waiting" state to continue. You will have additional primary keys that you will need to do the same with as well, as the MPs that were waiting will try to create their own primary keys.

It is very important that you check your event logs for a listing to see which tables and primary keys are affected. If you no longer have these events, you can click on the "redeploy" task on the failed MP to get a new set of events written to the logs.

4. When cleaned up all Primary key errors and started the redelpoy, new errors migth occour and you then need to start with step 2 again, until all the MPs are synced with success

Thanks to Andrew Barton for creating the workaround.

Failed to Apply UR2 to SCSM 2012 SP1 - Performance Counters not found

2013-04-16T10:48:00Z

I've been updating a couple of SCSM2012 SP1 installations to UR2 and everytime time I run into the same problem - the installation fails.

The installations fails with: error occured while executing custom action: "_Installhealthserviceperfcountersforpatching"

Looking at the SCSM2012_CU_KB2802159_AMD64_7.5.2905.125.msp.0.log I can see the following errors:

InstallCounters: LoadPerfCounterTextStrings() failed . Error Code: 0x80070002. momv3 "D:\Microsoft System Center 2012\Service Manager\MOMConnectorCounters.ini"
InstallPerfCountersHelper: pcCounterInstaller->InstallCounters() for the default counters failed. Error Code: 0x80070002. MOMConnector
InstallPerfCountersLib: InstallHealthServicePerfCounters() failed . Error Code: 0x80070002.
InstallPerfCountersLib: Retry Count : .
InstallHSPerfCounters: Failed to install agent perf counters. Error Code: 0x80070002.
The arguments are: -2147024894, The system cannot find the file specified.,

It turns out that the environments were missing a registry entry for the MOMConnector Performance counters and this caused the error. The key missing is: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MOMConnector

I created the reg entry (found on a SCSM DW MS) and reran the installation of UR2 with success. The reg-file to created the entry is added to this post. Before importing the Reg-File, make sure the path of where SCSM is installed is correct, if not change this linie in the reg-file: ""Library"="C:\\Program Files\\Microsoft System Center 2012\\Service Manager\\MOMConnectorPerformance.dll" and then do the import.

Note: After the failed installation of the update, all the workflows stopped running and as soon as the updated completed successfully, they started to be processed again.

SCSM Active Directory Connector optimization

2013-04-08T11:26:00Z

Often customers setup Active Directory connectors to import all objects from the root of the Domain. This means that all users (enabled and disabled), groups and computer objects are imported.

First thing to consider about the AD Connector is to determine whether we need all objects in the CMDB.

Lets start with the computers: If you're also using the Configuration Manager connector, there really isn't a need for the AD connector to import all computers, as it only means SCSM needs to import, merge and maintain two sources. All relevant information about the computers are delivered by the SCCM connector. I therefore often recommend my customers not to import computers via the AD Connector. There could of course be examples where the AD connector needs to import computers from AD.

Next up are the AD Groups: Groups are often not used in SCSM as they don't contain membership and often WIs are assigned to support groups and then to individual user accounts. What I recommend my customer is not to import all AD groups and only import the relevant ones, if any.

Last and final are the User accounts: The AD connector imports all users in a domain, regardless enabled or disabled. Also if the customer has contacts in AD that are created as Domain users, these are imported as well. If is therefore very important to consider which OUs to import and also whether or not to import both Enabled and Disabled users.

What I typically do, depending on the customers OU structure, is create separate AD connectors to avoid populating the CMDB with unneeded data.

Note: Create a different run as account for each connector. This will create a separate Monitoringhost.exe process on the workflow management server for each connector when it runs. This makes it easier to see which connector is currently running and how much memory/CPU it is consuming. It also makes it easier to isolate that one process from other workflows/connectors so that it can be terminated without affecting other workflows/connectors running.

When configuring a connector to only import user accounts that are enabled, you need to use LDAP filters that were introduced in SCSM2012.

Create the Connector and at the Select Objects option, select 'Users or User Groups' and insert the following LDAP query:

(&(ObjectCategory=User)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))

Note:Unless needed for a purpose, always select the option ‘Do not write null values for properties not set in Active Directory’. Using this setting ensures the connectors do not update the same attributes, despite being null.

This will only import the User accounts that are Enabled.

When looking in the CMDB with SQL Management studio, you can identify which user is imported with what AD Connector.

Select * from LFXSTG.AD_User Where

Username = '<Username>'

This will return the source of the specific user, in this example 3 different connectors - (before the cleanup of duplicate connectors)

If you then run the SQL Query:

Select * from LFX.Datasource

You will get a list of connectors where the Datasource ID equals the LFX_SourceID from the previus query.

Setting up the connectors to only import relevant data, helps you keep an optimized and relevant CMDB.

Remember that Service Manager is not a laundrymat, dirty data doesn't get cleaned when imported :-)

Orchestrator Runbook to reassign SCSM Incidents

2013-03-12T13:06:00Z

I was working at a customer that was doing a domain migration. Service Manager was installed in the new domain, but up til now the analysts and users have used their old accounts. The task was to create a runbook, that reassinged all Incidents from the old user account to the new account.

It was a fairly new installation and there was less than 5.000 incidents to go through. So I decided to use Orhcestrator to be able to run the job, whenever new users were domain migrated.

I created the following runbook:

First part in the runbook was a Read line activity, to parse a file that contained the newest batch of migrated users. The file had a format of Username,Old_Domain.

Second part in the runbook found the relevant user in the CMDB, based on the input from the Read Line activity. In order to read the username and domain, I used the following filters:

[Field('{Line text from "Read Line"}',',',1)]

[Field('{Line text from "Read Line"}',',',2)]

Third part in the runbook was a powershell script, this runs the SMLets locally on the Orchestrator Server and and returns a list of incidents, where the user is the Affected User.
Note: To get SMLets to run remotely I normally follow these guidelines: http://datacenterautomation.at/?p=233

Import-module smlets

$computername = "SCSM Management Server"

$incident = Get-SCSMIncident -ComputerName $computername | Where-Object {$_.AffectedUser -like '{Display Name from "Get Old User details"}*'}

if ($incident -ne $Null)

{

$Incidentlist = @()

foreach ($ir in $Incident)

{

$IncidentList += $ir.ID.value.tostring()

}

The powershell script returned a variable named 'IncidentList' with the related incidents.

It's not best practise using the Where-Object function, but my limited powershell skills on how to do a filter instead, forced me to use the where-object. Luckily the environment didn't contain that many objects and I belive, it didn't affect performance in general.

Fourth part in the runbook, used in output from the powershell script to load all the incidents

Fifth part in the runbook, loads the newly migrated user account. I knew the username was the same as the source account and also the name of the new domain. So I read the username from the input file and hardcoded the domain name.

Last and final part of the runbook change the Assigned to user with the newly migrated User object for all the Incidents that was previuosly returned.

I also created a similar runbook that changed the Affected User, which only requires a few changes in the powershell script and the last activity.

Get-SCSMIncident -ComputerName $SCSMServer | Where-Object {$_.AffectedUser -like '{Display Name from "Get Old User details"}*'}

SCSM Portal error, due to Web Content Server being out of sync

2013-01-13T17:43:00Z

Recently one of my customers had issues with their SCSM Portal where new categories didn't appear and several of the Request offerings failed.

Looking in the SMPortalTrace.log it stated that a Management pack was missing from system:

Ressourcestrings for troubleshooting is not available. Keys and arguments often contains enough information to start the troubleshooting a problem. See http://go.microsoft.com/fwlink/?linkid=106663&Version=5.0.10411.00&File=mscorlib.dll&Key=Arg_TargetInvocationException ---> Microsoft.EnterpriseManagement.Presentation.DataAccess.DataAccessConverterException: Microsoft.EnterpriseManagement.Presentation.DataAccess.DataAccessConverterException: An error occurred during a results conversion using type: Microsoft.EnterpriseManagement.ServiceManager.Portal.DataProviders.RequestOfferingOutputConverter ---> Microsoft.EnterpriseManagement.Common.InconsistentDataException: The requested objects had an unknown management pack. ---> Microsoft.EnterpriseManagement.Common.InconsistentDataException: The call produced inconsistent results. The data on the client does not match the data returned from the server. The most likely cause is that client-side cache is out of sync with server data. ---> Microsoft.EnterpriseManagement.Common.ObjectNotFoundException: An object of class ManagementPack with ID 40b87bdd-608a-8858-9ada-e6de1ee8ed9f was not found.

See this blog post on where to the find the SMPortalTrace.log

Looking in the SCSM database the management pack missing is actually the MP containing the offering and is therefore not missing. Do this to check if the MP is in the database: SELECT * FROM ManagementPack WHERE ManagementPackID = ‘40b87bdd-608a-8858-9ada-e6de1ee8ed9f’

But looking at the line just above, there is a message about client-side cache being out of sync:

Microsoft.EnterpriseManagement.Common.InconsistentDataException: The call produced inconsistent results. The data on the client does not match the data returned from the server. The most likely cause is that client-side cache is out of sync with server data.

Since my customer is using a corporate SharePoint farm, the Web Content Server (WCS) is on a dedicated server. I therefore uninstalled the WCS and installed it again. After that the portal worked..

If you have this problem, maybe a simple restart of the application pools is enough, otherwise reinstall is the way to go.

Thanks to Ruth Lilly from Microsoft Support for helping with the troubleshooting.

SCSM - The item cannot be updated.....aka. Click Apply and die :-)

2012-11-15T12:53:00Z

I guess you all have seen the error message below:

The problem all relates to internal workflows, that kicks in as soon as you hit Apply.

Here's a short example:

1. The incident is created and you click Apply. This will add the following entry to the history log:

2. Internal workflow engine starts and will within a minute or two, add the newly created IR to queues that are used SLO measurement.

3. Next the workflow applies the SLO targets, based on queue membership

If you click apply and start working in the IR, you can end up getting the 'The item cannot be updated.....' because the workflows have updated the IR and you don't have these updates in the current form. If you have clicked apply, make sure to refresh before you start making any changes to the IR.

If you disable SLO and queues, you will not have these problems. So when you plan for introducing SLM to your organization, make sure to inform the SCSM console users about the 'Refresh' button.

Another new challenge is introduced with SCSM 2012, the new attribute "First Assigned Date' is introduced for SLA measurement. So when a user is added to the 'Assigned To', the workflow after a minute or 2, updates the First Assigned date:

If you don't plan on using the First Assigned date for any reporting purposes, you can disable this workflow, by importing the MP that Anders Asp has created. You can read more here: http://www.scsm.se/?p=853

For more information, see here: http://blogs.technet.com/b/servicemanager/archive/2010/09/24/faq-what-happens-when-two-people-edit-the-same-object-at-the-same-time.aspx

Update: There now is a official KB on the issue: http://support.microsoft.com/kb/2830814

Service Manager 2012 services won't start after applying KB2677070

2012-10-01T07:54:00Z

After applying the recently released security update KB2677070 the System Center Data Access Service and the System Center Management Configuration Service wont start. Both services will log a "Error 1053: The service did not respond to the start or control request in a timely fashion"

The following error is shown:

When applying KB2677070, the server is not required to be restarted and therefore the error will not show until the next planned reboot.

The problem is fixed by following KB2730040

SCSM 2012 Data Warehouse jobs keep failing

2012-08-27T00:11:00Z

I often run into the same problem with the SCSM 2012 Data Warehouse, that the cubes jobs all fail. When resuming one of the jobs the following errors appear in the log:

Message : An Exception was encountered while trying during cube processing. Message= Processing warning encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: 1092550657, Description: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'ConfigItemDim', Column: 'ConfigItemDimKey', Value: '13760'. The attribute is 'ConfigItemDimKey'..

And / Or

Message : An Exception was encountered while trying during cube processing. Message= Processing warning encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: 1092550657, Description: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'ProcessorDim', Column: 'ProcessorDimKey', Value: '156'. The attribute is 'ProcessorDimKey'..

And / Or

Message : An Exception was encountered while trying during cube processing. Message= Processing error encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: -1055129595, Description: Server: The operation has been cancelled due to memory pressure.. Processing error encountered - Location: , Source: Microsoft SQL Server 2008 R2 Analysis Services Code: -1055129598, Description: Server: The operation has been cancelled..

When the cube processing is started, the Analysis service slowly consumes all available memory and does not stop until it cancels the process due to memory pressure.

Connecting to the server hosting the analysis service and looking at the properties of the cubes, shows that the cubes haven’t been processed and looking in SCSM all the jobs are set to failed:

Looking at the errors that are logged, they often relate to errors in the dimensions and their attributes. Reading the SCSM 2012 Administration guide, there is a script that processes all the dimensions in one step.

The script is not entirely correct, so I've changed it to:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$Server = New-Object Microsoft.AnalysisServices.Server
$Server.Connect("serverdw.blog.com")
$Databases = $Server.Databases
$DWASDB = $Databases["DWASDataBase"]
$Dimensions = New-Object Microsoft.AnalysisServices.Dimension
$Dimensions = $DWASDB.Dimensions

foreach ($Dimension in $Dimensions){$Dimension.Process("ProcessFull")}

Login to the server hosting the Analysis service and run the powershell script, just remember to change the $Server.Connect and the $DWASDB with the correct server (being the name of the SQL Server) and database name. It usually runs for 5-15 minutes. When complete, you can verify by looking at the properties at one of the dimensions:

After all the dimensions have been processed, you can start the cube processing from the SCSM console and they should all complete with success. Note that the jobs will change from Failed to Running to Not Started.

Instead look at the Cubes tab in the Data Warehouse wunderbar to see the status

But remember, this will just get the cubes processed once which is because it gets your cubes up to the latest set of data, but if the cube processing jobs starts failing again right away, it could because it is doing a full
processing job instead of a less resource demanding job like incremental.

The only way to fix this is to:

1) Have a look at the HW and see if you could relocate DB-files, log files, temp etc. on more spindles / more VHD-files, add more processors or even RAM to help the SSAS with completing.

And/Or

2) Upgrade to SQL Enterprise (Enterprise has the feature that allows for incremental processing instead of a full)

SCOM 2007 R2 Universal Connector, Interop Provider certificate expired

2012-06-27T09:34:00Z

I recently ran into the problem that a normal working Universal connector stopped working and generated three different entries in the Application event log and alerts in the OpsMgr console:

EventID 1500:
2012-06-26 15:21:21.835 [Microsoft.SystemCenter.Interop.Framework.Modules.WSManModule] ERROR No
providers could be contacted. Could not get update events. Could not connect to any providers to retrieve update events

EventID 1501:
2012-06-26 15:20:53.944
[Microsoft.SystemCenter.Interop.Framework.Modules.WSManModule] ERROR Could not
deliver some events. No providers were available. Could not forward any provider

EventID 1516:
2012-06-26 15:20:53.944 [Microsoft.SystemCenter.Interop.Framework.Modules.WSManWrapper]
ERROR COMException An internal error occurred.
occurred while sending event <p:CreateEvt_INPUT
xmlns:p="http://schemas.microsoft.com/wbem/wsman/1/wmi/root/scx/SCX_Connector"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

......

On the Management server where the Universal connector is installed, I opened a command prompt and browsed to c:\Program Files\System Center Operations Manager 2007 R2 Connectors\OpsMgr Universal Connector and ran the Interop Certificate tool to verify the connection

scicert.exe <hostname name of interop provider> <Domain\Username of the user also used in the connector config> <password> "OpsMgr Universal Connector" Test

This command opens a connection to the Interop Connector and verifies it, including the certificate. The result of the test showed that the SSL certificate was expired.

The SSL Certificate is generated when installing the Interop Provider and is valid for 2 years.

Following the guide here: http://technet.microsoft.com/en-us/library/dd795252.aspx solved the problem and the steps are:

1. Uninstall the Interop Provider

2. Delete the certificate

3. Install the Interop Provider again

4, Verify the certificate is valid for the next two years

5. Run the scicert.exe command on the connector server to import the certificate

6. Restart the connector service and you're back on track.

Note: The guide states the certificates are valid for 3 years, which is not correct, it is only valid for 2.

Orchestrator Runbooks not appearing on the web console

2012-05-29T12:40:00Z

I was creating new runbooks in System Center Orchestrator 2012 and wanted to synchronize them to Service Manager 2012, but after running the connector the runbooks still didn't appear.

Browsing to the Orchestrator web console I could see the runbooks wasn't even available here.

Solution:

1. Open SQL Management studio and connect to the SQL Server and instance hosting the Orchestrator Database

2.Type in the following SQL Query:

TRUNCATE TABLE
[Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache

3. Refresh the Orchestrator web console and the runbooks are there.

4. Start the SCO -> SCSM connector and the runbooks will be available as well.

If the problem reoccurs, you can do the following to run a scheduled cleanup of the Authorization table:

1. Open SQL Management studio and connect to the SQL Server and instance hosting the Orchestrator Database

2.Type in the following SQL Query:

EXEC [Microsoft.SystemCenter.Orchestrator.Maintenance].EnqueueRecurrentTask ‘ClearAuthorizationCache’

This will establish a new SQL Service Broker conversation to flush the authorization cache every 10 minutes.