Delete special seems to be more resource intensive depending on the number of PC's but it's typically cleaner than executing SQL queries directly (quick way I used to also leverage) so I suggest always using "Delete Special".
From: [email protected] [mailto:[email protected]] On Behalf Of Mark Mears Sent: Wednesday, November 20, 2013 9:46 AM To: [email protected] Subject: RE: [mssms] Duplicate computer names with different Resource ID - SQL Query Once a resource is deleted from the CM Database several cascading deletes occur which remove the data associated with that ResourceID, current data as well as historical. Thanks, ________________________________ Mark Mears [email protected]<mailto:[email protected]%0d> Phone: (757) 945-2651 [cid:[email protected]]<http://www.cireson.com/> [cid:[email protected]]<http://twitter.com/teamcireson> Check out our System Center App Store: www.cireson.com/app-store ________________________________ From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Eswar Koneti Sent: Tuesday, November 19, 2013 9:22 PM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] Duplicate computer names with different Resource ID - SQL Query I doubt if these SQL quiries cleanup all client data from the SQL tables completly or any part of it remains? ------------------------------------ Eswar Koneti Configmgr Consultant www.eskonr.com<http://www.eskonr.com> ------------------------------------ ________________________________ From: [email protected]<mailto:[email protected]> To: [email protected]<mailto:[email protected]> Date: Tue, 19 Nov 2013 13:41:25 -0800 Subject: RE: [mssms] Duplicate computer names with different Resource ID - SQL Query Note sure if this helps, but I run these scripts on my server every week to remove duplicates. Someone else on this list shared it, I don't remember who. First script shows all duplicates: SELECT DIS.* FROM System_DISC DIS Join ( Select Name0, resource_domain_or_workgr0 FROM System_DISC group by Name0, resource_domain_or_workgr0 having count(*) > 1) As DUP on DUP.Name0 = DIS.Name0 And DUP.resource_domain_or_workgr0 = DIS.resource_domain_or_workgr0 order by Netbios_Name0 Second script deletes the duplicates but keep the most recently discovered record: delete from system_disc where itemkey in (SELECT distinct DIS.itemkey FROM System_DISC DIS Join ( Select Name0, Creation_Date0 FROM System_DISC where Name0 IN ( Select Name0 FROM System_DISC group by Name0 having count(*) > 1)) As DUP on DUP.Name0 = DIS.Name0 where (DIS.Creation_Date0 < DUP.Creation_Date0 or DIS.Creation_Date0 is null)) From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Kevin Ray Sent: Tuesday, November 19, 2013 10:48 AM To: mssms Subject: [mssms] Duplicate computer names with different Resource ID - SQL Query Hi All I'm trying to create the SQL / WQL Query for duplication machines with different resource ID. 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 I got the collection query... But due to some constrains , we should not use the delete special.... So now I'm looking for SQL Query.. It should show up the duplicate machine's with Last Heart discovery date with resource id... Ultimately we need to remove the machine's withought using the delete special ..How we can do it... I'm thinking to check the Heartbeat of 2 duplicate machine's Which is the Old time stamp we need to remove it... If we get the duplicate machine name with Heartbeat Time stamp with resource ID.. We can remove it.. Any help, Any other suggestion to do this ?
<<inline: image001.png>>
<<inline: image002.jpg>>
