Chris, that was my reply in another thread. The ResourceIDs are allocated from the NextIDs table and a stored procedure is used to increment the value. So the max ResourceID is the most current record.
Thanks, Mark Sent from my iPhone > On Nov 20, 2013, at 3:01 PM, <[email protected]> wrote: > > Hi Mark, > > If the obsolete jobs aren’t purging your old records quick enough, here is a > query that can be tweaked to identify resource id’s to be deleted. > > It’s my personal belief that the most current resource ID (Max) created in > SCCM is typically the correct machine being used. (Just my opinion) > > drop table #Wimer > > SELECT Name0, MAX(itemkey) as MaxItemKey > into #Wimer > FROM System_DISC > where ItemKey is not null > group by Name0 > having COUNT(user_name0)>1 > > select * > from System_DISC > where > Name0 in (select Name0 from #Wimer) > and > Itemkey not in (select MaxItemKey from #Wimer) > > From: [email protected] [mailto:[email protected]] > On Behalf Of Mark Mears > Sent: Wednesday, November 20, 2013 10:45 AM > To: [email protected] > Subject: RE: [mssms] Duplicate computer names with different Resource ID - > SQL Query > > Agreed, but how would you define the collection such that the valid resources > are not deleted along with the dupes? > > Thanks, > > > Mark Mears > [email protected] > Phone: (757) 945-2651 > > <image001.png> > > <image002.jpg> Check out our System Center App Store: > www.cireson.com/app-store > > > > From: [email protected] [mailto:[email protected]] > On Behalf Of [email protected] > Sent: Wednesday, November 20, 2013 10:04 AM > To: [email protected] > Subject: RE: [mssms] Duplicate computer names with different Resource ID - > SQL Query > > 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] > Phone: (757) 945-2651 > > <image001.png> > > <image002.jpg> Check out our System Center App Store: > www.cireson.com/app-store > > > > From: [email protected] [mailto:[email protected]] > On Behalf Of Eswar Koneti > Sent: Tuesday, November 19, 2013 9:22 PM > To: [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 > ------------------------------------ > From: [email protected] > To: [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]] > 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 ? > > > > > > >
