Thanks Guy's, Let me run this query in test environment , My blockers are like if we do delete special, It will remove both machine's from SCCM, after refresh the heartbeat it will come's in All systems which is active record, its fine.. But we have already created the collection's & adverts using static (not WQL query)..So we assuming that it will go out from the collection,again we need to re-add the machine's to collection's , for N number machine's in N number of collections...
On Wed, Nov 20, 2013 at 7:51 AM, Eswar Koneti <eswarkon...@outlook.com>wrote: > 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 > *------------------------------------* > ------------------------------ > From: mmur...@csuchico.edu > To: mssms@lists.myitforum.com > 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:* listsad...@lists.myitforum.com [mailto: > listsad...@lists.myitforum.com] *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 ? > > > > >
