Mar 032014

It turns out that in System Center Operations Manager 2012 R2 (SCOM 2012 R2), there is a pretty nasty, but well documented bug that causes corruption and the following error:

SCOM Specified cast is not valid

After clicking OK a few times, you can attempt to view the Distribution tab, but will find it is now empty:

SCOM Empty Distribution Tab

The root cause of this is likely due to the uninstall of a previously managed agent. If you are in a large shop with multiple admins, which agent was it that is currently causing the problem? Since I’m a SQL guy, I took to Transact-SQL to find the answer. Log into your OperationsManager database instance, and run the following SQL to find the bad agent:

SELECT BME.DisplayName,
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
  JOIN [OperationsManager].[dbo].[CredentialHealthService] AS CHS
    ON CMSS.SecureStorageElementId = CHS.SecureStorageElementId
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service

Objects with the IsDeleted column set to 1 are likely your problem agents.

So, how to clean this up? Well, there are a few options. One, if you are lucky you can re-approve the agent and after which you should be back to normal. Two, you can delete the offending row from the dbo.CredentialHealthService table. Lastly, and the option I’ll use is to call the same procedure that the SCOM Console uses in the GUI. To do that, run the following SQL:

DECLARE @SecureStorageElementId NVARCHAR(36);

SELECT @SecureStorageElementId = CMSS.SecureStorageElementId
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service
   /* or use CMSS.DisplayName, which is what the SCOM console shows */;

SELECT @HealthServiceIdList = COALESCE(@HealthServiceIdList + N',', N'') + CAST(CHS.HealthServiceId AS NVARCHAR(MAX))
  FROM [OperationsManager].[dbo].[CredentialHealthService] AS CHS
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
   AND BME.IsDeleted = 0
 WHERE CHS.SecureStorageElementId = @SecureStorageElementId;

PRINT N'exec dbo.p_SetApprovedHealthServicesForDistribution 
            @SecureStorageElementId=''' + @SecureStorageElementId + N''', 
            @HealthServiceIdList=N''' + @HealthServiceIdList + N''', 

The output of which, you are free to inspect – no changes are made – until you are comfortable with running it. When you are, simply copy and paste the output and execute against your OperationsManager database. The output is exactly what the SCOM console would do if you were making changes to the distribution list. Running the output of the above script is done at your own risk. Changing data in the SCOM OperationsManager database is not officially supported. Contact Microsoft support if you are in an emergency. Feel free to mention the above solution to your support engineer, however.

 Leave a Reply



You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>