sccm Configmgr 2012 Delete duplicate Obsolete records etc using collections

Few days back,I was working on Client health remediation issues.for this activity,I created some cool SSRS reports based on client health statistics,you can find them on the below link:

With above report,found that ,Count of missing clients very few hundred which is 2% from total --not bad  but still need to install the client on missing computers using different client installation methods like client push,logon script etc . Since i have few hundred computers,i would prefer to go with client push installation using clientpushgenerator tool.

while troubleshooting the client installation ,I found many clients appearing twice --duplicate records,obsolete ,One computer having multiple resource IDs.More you can refer the link

All these duplicate ,obsolete issues are addressed in multiple blogs---- how to create collection to find duplicate records and delete them from database ,So  I thought of presenting all in one blog including new issue—One computer with different Resource IDs.

How did i trace this out ?

I ran below SQL query on SSMS (management Studio) --the below query basically tells ,get computer names with count more than 2 .I Found many entries .I pick one computer and ran select * from v_r_system where name0=’computer name’ This query gave me  2 entries for one computer name,both the entries show as active,No Obsolete,Client is 1 .I really stumbled on up by seeing this results as all the entries are same .what else I can check to differentiate between these entries ?

select sys.name0

from v_r_system sys
group by  sys.name0
having count(sys.name0) >=2

I gave up finding a way to cleanup only the duplicate entries and decided to cleanup both the entries so clients which are online will send  DDR  back to Site,which will get update in Database as simple as that.

Below are collections what you need to have in place to cleanup the duplicate/obsolete /computers not contacted domain( use atribute LastLogontimestamp) during X days which means they are no more on the network and no hardware inventory for 30 days.

Duplicate Computer Names:

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client from SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Name = s2.Name and s1.ResourceId != s2.ResourceId and R.Client = null

Duplicate Resource ID with same Computer Name(based on Active Status):

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Active is null  and SMS_R_System.Name in (select Name  from  SMS_R_System where SMS_R_System.Active = "1")

Obsolete Computers:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = "1" and SMS_R_System.Name not like "Unknown"

 Computers not contacted Domain during 90 days and Hardware Inventory is more than 30 days old:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) > 30 and DATEDIFF(dd,SMS_R_System.LastLogonTimestamp,GetDate()) > 90) and SMS_R_System.LastLogonTimestamp is not null  and SMS_G_System_OPERATING_SYSTEM.Caption not like "%server%"

You can either manually delete the entries from collection or create schedule job which performs deletion of resources at regular intervals .

16 Responses to "sccm Configmgr 2012 Delete duplicate Obsolete records etc using collections"

  1. Hello Eswar ,

    Thanks for information shared, i have one question .Regarding collection "Duplicate Resource ID with same Computer Name(based on Active Status)" i can see lot of systems in collection but none of them has same resourceid or name .
    Can you please assist on this .Thanks in adv.


    1. Lot of system in collection without same host name ? if not same host name, why does it bother you ? if you have same host name for some reason and other fields like active ,obsolete etc are same for these host names ,you may have to go with their last contact date to server name to identify which is not contacted recently for deletion.


  2. Hi Eswar,

    We are going to deploy new image through SCCM2012 R2 to all the workstations

    Once image is installed, It will rename the PC as per the new standards.

    How to query the old names and the respective new name of the PCs.

    Also how to delete only Old PCs..

    Please help me.

    Thanks in advance.

    1. I would suggest to add a registry key in the task sequence to add the Image installation date so you can query this reg key using SCCM to confirm which is old image and which is newly imaged .If you have followed the naming convention for your old image installed PC's ,you can try creating collection with that.


      1. HOW does one check for syntax errors? Cut and pasting queries from website ALWAYS fails and the SCCM wizard just complains that the query is wrong instead of telling WHAT exactly is wrong

        1. when you copy the code from internet, quotes replace with fancy quotes ,so you will have to replace the quotes manually before using it.


  3. Not sure whether this is the correct forum to discuss this topic or not but I have deployed Microsoft Office Update Patches and on 4 computers it says Non-compliant. Could you please clarify whether it means that the MS update is not required to be installed on the computers therefore it says non compliant or there is something else which should be checked and resolved.

  4. Awesome stuff
    but please tell me in your last sentance you state
    "You can either manually delete the entries from collection or create schedule job which performs deletion of resources at regular intervals "

    how will i go about automating the removing of machines in those collections.

  5. Hi,

    I have a SCCM 2012 as hyper-v virtual machine in windows server 2008 r2 to deploy and capture a Windows 8.

    When starting the client, From PXE it downloads Boot.wim and it starts loading the files, says it is initializing the hardware (it detects the network card), then initializing the network connections. and then, the computer reboots!

    Please help



Leave a Reply