Troubleshooting Invalid Objects in Collection Evaluator, Computer Association

[SRed]

Hi guys,

 

At two of my clients, I've recently noticed my primary sites going red due to an SCCM event 620. This event correlates to a SQL Message 208 or 547, the text of which is

 

"

Microsoft SQL Server reported SQL message 547, severity 16: [23000][547][Microsoft][ODBC SQL Server Driver]

The DELETE statement conflicted with the REFERENCE constraint "StateMigrationAssociation_MachineIdGroupXRef_FK". The conflict occurred in database "SCCM_US1", table "dbo.StateMigrationAssociation

"

 

The other error is :

 

"

Microsoft SQL Server reported SQL message 208, severity 16: [42S02][208][Microsoft][ODBC SQL Server Driver][sql Server] Invalid object name '_RES_COLL_CCP00A37'

"

 

Both of these refer me to my SQL or SMS documentation to troubleshoot, but I've found nothing really on the web that helps me with this. I'm thinking I'll need to remove these bad objects from within SQL using Management Studio (My ID has full rights to the database, so this can be done if need be) But where do I even start to trouble shoot these issues?

 

For the error related to computer associations, I checked that out in the SCCM console and noticed some old associations which never even completed the capture, or the restore completed months ago. I deleted those but I'm still not sure if I deleted the right one.

 

Any guidance would be appreciated!

 

 

[SRed]

Actually, I've figured out how to delete bad computer associations and fix invalid certificates.

 

Open management Studio

Browse down to the table you need to change. Choose Open Table

Get the GUID from the relevant event in the SCCM console

Create a select Statement to test what the query will affect

Modify above command to make a delete command

DELETE FROM ClientKeyData WHERE SMSID = 'GUID:1C53E10F-8BED-44AB-8671-B4A749156F4A'

Now, can someone tell me where I would find collections in SQL? I've got two that appear to be bad and I'd like to remove them from the database.

[SRed]

I also fixed my other issue! In case anyone else is plagued by those annoying errors in ConfigMan, here is how to fix them.

 

Find the error (in this case SMS_Coll_Evaluator)

Look for the time stamp and the relevant bad collection name

Check the colleval.log file on the primary. Look for the time indicated, then search for the offending collection name(CCP00A37, in this example)

Notice that the step before the highlighted step is refreshing results for collection CCP00ADD. If we look at the SQL Statement below it, we will see a direct rule stating If "the OS is Win NT 6.1 and the item is not found in these two collections " then include in this collection. So we can assume that the issue is that someone made a query based collection in for CCP00ADD, and then deleted one of the collections it referenced.

This organization uses folders to organize their collections, so I can't just search for this in the console. Instead, a quick look at the 'All Collections' SCCM Report will show us which collection is correlated to this ID.

if your organization has very difficult to traverse collection folders, I highly recommend this Report (Display all collections along with collection path http://social.technet.microsoft.com/Forums/systemcenter/en-US/d5afe0dc-562e-441c-a856-eb4f2cb97de2/how-to-find-sccm-collection-location)

Alright, the collection we need to check is Microsoft Updates - Workstations - Production Release - win7 SP1.

Finding the offending collection, lets check out its Membership Rules

We see here the direct mention in this query of the currently nonexistent collection. We want to remove the final 'AND' statement, in my case. Its always smart to test a query using the Queries node in SCCM before saving a change, of course!

[GarthMJ]

Editing the CM07/CM12 database directly is COMPLETELY unsupported and can have other effects such as data/resource might not be properly clean out of the db. You should never do this unless have an open case with Microsoft support.