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: http://eskonr.com/2014/05/sccm-configmgr-2012-ssrs-dashboard-client-health-summary-report/
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 http://social.technet.microsoft.com/Forums/systemcenter/en-US/8f3fd7cd-0e3d-4429-bcde-02b2ec77324d/report-showing-servers-showing-two-os?forum=configmgrgeneral#ac775d37-141a-404c-a345-7d882b1bbe17
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 ?
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")
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 .