ok i cant find the article link.. i save my notes in one note and usually i let it keep the source page so i can reference it back.
I attached the report in XML but here is the SQL code...
Report
declare @names int
set @names = '%names%'
declare @days int
set @days = '%days%'
select distinct a.ResourceGuid _ItemGuid, rk.KeyValue, left(dt1.Names,LEN(dt1.Names)-1) [Names AKA]
from ResourceKey rk
join ResourceKeyChanged a
on rk.ResourceGuid = a.ResourceGuid
and rk.KeyName = a.KeyName
join ResourceKeyChanged b
on a.ResourceGuid=b.ResourceGuid
and a.KeyName=b.KeyName
and left(a.KeyValue,charindex('.',a.KeyValue+'.')-1)<>left(b.KeyValue,charindex('.',b.KeyValue+'.')-1)
cross apply
(
select distinct top(@names) KeyValue + ', ' as [text()]
from ResourceKeyChanged c
where a.ResourceGuid = c.ResourceGuid
and a.KeyName = c.KeyName
and rk.KeyValue <> c.KeyValue
for xml path ('')
) dt1(Names)
where a.KeyName = 'name.domain'
and a.ChangeType = 'D'
and b.ChangeType = 'I'
and a.ChangeTime >= DATEADD(DAY, -@days, GETDATE())
and b.ChangeTime >= DATEADD(DAY, -@days, GETDATE())
This part is the sql code to get rid of the shared guid. it black lists both guids.. and then each come in and get a new guid...
-- 1. Identify shared Guids in last 'N' days and insert them into the AgentBlacklist table.
declare @days int
set @days = -3;
insert into AgentBlacklist (Guid, BlacklistDate) select distinct a.ResourceGuid, GETDATE() from ResourceKeyChanged a join ResourceKeyChanged b on a.ResourceGuid=b.ResourceGuid and a.KeyName=b.KeyName where a.KeyName = 'name.domain'
and a.ChangeType = 'D'
and b.ChangeType = 'I'
and a.ChangeTime >= DATEADD(DAY, @days, GETDATE()) and b.ChangeTime >= DATEADD(DAY, @days, GETDATE()) and left(a.KeyValue,charindex('.',a.KeyValue+'.')-1)<>left(b.KeyValue,charindex('.',b.KeyValue+'.')-1)
and a.ResourceGuid not in (select Guid from AgentBlacklist)
-- 2. Delete Guids shared in last 'N' days from the ResourceKey table
delete from ResourceKey where ResourceGuid in (select distinct a.ResourceGuid from ResourceKeyChanged a join ResourceKeyChanged b on a.ResourceGuid=b.ResourceGuid and a.KeyName=b.KeyName where a.KeyName = 'name.domain'
and a.ChangeType = 'D'
and b.ChangeType = 'I'
and a.ChangeTime >= DATEADD(DAY, @days, GETDATE()) and b.ChangeTime >= DATEADD(DAY, @days, GETDATE()) and left(a.KeyValue,charindex('.',a.KeyValue+'.')-1)<>left(b.KeyValue,charindex('.',b.KeyValue+'.')-1)
)
-- 3. Insert the blacklisted Guid to be deleted from the system to eliminate duplicate names
insert into ItemToDelete (Guid, DeleteDate) select c.Guid, GETDATE() from vComputer c join AgentBlacklist b on b.Guid = c.Guid left join ItemToDelete d on d.Guid = b.Guid where d.Guid is null