SCCM 2007: Status Filter Rules to Delete Duplicate IDs

With the recent issue we have regarding duplicate IDs, we decided to go with the deleting the objects "manually" because we can't change the interval the discovery happens or as I've suggested to use a unique naming convention which wasn't approved using serial instead of username.

To accomplish this task, it could have been done a few ways that I could think of but I decided to look into Status Filter Rules to do this in an automated fashion as opposed to using Task Scheduler which could be run every a few hours but may involve creating a network service account.

Create a script to look for the same named object but only select the ones with Obsolete0 as NULL or any column that has a NULL value.  I ran the script to retrieve duplicates a few times to validate then modified it to select only NULL.  If delete the "and Obsolete0 is NULL" it will retrieve both but for the deletion, we only want to delete the ones with NULL.   When done save the sql file to a location on the system.

Set NOCount ON
USE SMS_YOURSITECODE;
Select b.resourceID
from (SELECT Name0, COUNT(*) as rec_cnt
FROM dbo.v_R_System AS a
group by name0
having count(*)>1) A
join v_R_System AS b ON
b.name0 = a.name0 and Obsolete0 is NULL
SET NOCOUNT OFF

I created a batch file to use SQLCMD to run the sql file and export it to the text file that contains on the resource IDs.  I created another that would also export the Name and SMS Unique Identifier, the change date, and creation date so I can monitor what gets exported.  My delete script exports the items delete with the time so I can see when they are done.

Using the command lines as below will export with correct formatting w/o the white space and header information.

sqlcmd -m 1 -h -1 -W -S SERVERNAME -i c:\sql\nullid.sql > c:\dupid\dupids.txt


The batch file runs both queries and exports them to 2 separate text file.  One is the ResourceID only and the other has more info and appends to a file with the extra data.

Before creating the Status Filter Rule, you may want to review what message IDs you want to use a the trigger.  I'm looking at the SMS_AD_Group_Discovery_Agent message logs and decided to use 500 and 1105 because of the time gap.

Select the Component and Message ID to match and click the Action tab.

In the Actions tab, I have it run the batch file to export the duplicate IDs.  Then repeat it again with the other script that runs against the txt file to delete.

The upside of this method is that it doesn't require additional permission settings or using a service account to run it.