Wednesday, February 8, 2012

SCOM Service Level Objectives created not updated/available

There maybe instances where new Service Level Tracking objectives (SLO) are created in SCOM and these were not found in the Service Level Dashboard neither are the available on the service availability reports.


While troubleshooting, I have found this error on my SCOM RMS and I believe these are related.



Failed to store data in the Data Warehouse. Exception 'SqlException': Sql execution failed. Error 2627, Level 14, State 1, Procedure ManagementPackInstall, Line 2855, Message: Violation of UNIQUE KEY constraint 'UN_ManagementGroupManagementPackVersion_ManagementGroupRowIdManagementPackVersionRowId'. Cannot insert duplicate key in object 'dbo.ManagementGroupManagementPackVersion'. One or more workflows were affected by this. Workflow name: Microsoft.SystemCenter.DataWarehouse.Synchronization.Configuration Instance name: RMSServer Instance ID: {AF86A1AC-F1F5-9BF7-1E89-F60F73982EB6} Management group: GroupName

It turns out that MPS were queued awaiting synchronization which resulted in teh changes/additions made to the MPs not being updated. 


The SQL query below will enable you to find the MPs that are pending.

SELECT

ManagementPackId, MPFriendlyName,MPName, mp.MPVersionDependentId, MPLastModified, MPKeyToken, ContentReadable

FROM ManagementPack mp

WHERE MPVersionDependentId



NOT IN

(SELECT mpv.ManagementPackVersionDependentGuid

FROM OperationsMAnagerDW.dbo.ManagementPackVersion mpv

JOIN OperationsMAnagerDW.dbo.ManagementGroupManagementPackVersion mgmpv

ON (mpv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)

WHERE (mgmpv.LatestVersionInd > 0))



AND NOT EXISTS

(SELECT * FROM ManagementPackReferences mpr

JOIN ManagementPack mpv

ON (mpr.ManagementPackIdSource = mpv.ManagementPackId)

WHERE (mpr.ManagementPackIdReffedBy = mp.ManagementPackId)

AND (mpv.MPVersionDependentId NOT IN

(SELECT mpv.ManagementPackVersionDependentGuid

FROM OperationsMAnagerDW.dbo.ManagementPackVersion mpv

JOIN OperationsMAnagerDW.dbo.ManagementGroupManagementPackVersion mgmpv

ON (mpv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)

WHERE (mgmpv.LatestVersionInd > 0))))


Once the problematic MPs have been found , follow the procedures below to resolve the issue

We need to trigger sync manually. 
Follow the steps to starting at the top of the list, export the MP, and update the version attribute and reimport.

This will force that MP to resynchronize. Once all the MPs that are blocking synchronization have been updated, then all the other ones will update automatically

 Export MPs

For the unsealed MPs, please right click the MP in the SCOM console then export them as xml files.

For  sealed MPs, you can use following link and command to export as xml files http://blogs.technet.com/b/jonathanalmquist/archive/2009/03/30/export-a-management-pack.aspx

 Open the XML, Update the version attribute then save the files:

 For example:

<Identity>

<ID>Mpname<ID>

<Version>1.0.0.3</Version>

</Identity>



We increase the version to 1.0.0.4.



  1. Right click “management pack”, reimport the XML files.
  2. After reimporting all MPs in the query list, check whether we can get the service level object from report.



Note: Most of the MPs listing in the qurey result are customized MPs and will not have any impact.  

          For these Microsoft sealed MPs, the solution will impact following aspects:

          a. the sealed MP will be changed to unsealed b. it cannot auto update if new version of  this MPs are released. 


Hope this helps whoever i facing the same issue as I did.

6 comments:

  1. Hi
    I tried this ,but I get an error that line 4. Invalid column name "mpversiondependantId'.
    How do I get around this.

    ReplyDelete
  2. Hi,

    Which database are you running this against?
    It should be run against the database, operationsmanager.

    ReplyDelete
  3. Hello, I tried running this Query against our SCOM 2012 OperationsManager database and it failed. Our OperationsManager database is on a separate/different server from the OperationsManagerDW database.
    The error I got was: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'OperationsManagerDW.dbo.ManagementPackVersion'

    By they way, this worked fine in our Test/PreProduction environment, but both the OpsMgr and DW databases are on the same server there.

    Any ideas?

    ReplyDelete
    Replies
    1. Hi,

      You will have to create a linked server from the SQL server which is hosting the OperationsManager DB to the Server that is hosting the OperationsManager DB.
      Next, change replace "OperationsMAnagerDW.dbo.ManagementPackVersion" in the query to ".OperationsMAnagerDW.dbo.ManagementPackVersion". Replace with the name of the linked server you created earlier.
      Let me know if it works for you

      Delete
  4. Hi
    I tried this query with OperationsManagerDW database,but I get an error that line 4. Invalid column name "mpversiondependantId'. How can I find bad MP? Should I change all unsealed MPs versions to resolve this error?

    ReplyDelete
    Replies
    1. Hi,

      You should run this against the OperationsManager Database.
      This query is done with the assumption the OperationsManager and OperationsManagerDW databse is on the same server.

      Delete