SQL - How to remove duplicate entities from SEC

Question

Hi there, its summer time, so maintenance time. I'm trying to remove deprecated computers from the SEC SOPHOS521 database using an SQL query that can get rid of hundreds of duplicates.

Our naming scheme for computernames is the following:

RoomNumber-Servicetag/Serial-Make

If a computers is renamed (or reimaged or when it receives an OS upgrade) the computername sometimes changes even though the Servicetag/Serial part stays the same. Sophos doesn't always recognize that this is the same entity thus creating duplicates that never Update and show up as Unknown and what not. Console clutter that distracts from the real issues!

Example:

old name: 506-TKJFREP-D 
 new name: ROAM-TKJFREP-D

or

old name: ROAM-0445001-H 
 new name: GH-0445001-H

The only constant is the Servicetag which is exactly 7 character (either letter or numbers) and that there is a dash encapsulating it. And the last character of the computername is always only one (1) character.

Right now I have the ability to find duplicates with the exact same name using the following SQL query: 
 SELECT c.Name,
	c.Description, 
	c.DomainName, 
	c.OperatingSystem, 
	c.Managed, 
	c.Deleted, 
	c.Connected,
	c.SAVOnAccess,
	c.LastMessageTime,
	c.insertedat, 
	c.IdentityTag,
	c.IPAddress,	
	c.QuarantineCount,
	c.LastLoggedOnUser,
	c.MessageSystemAddress,
	cgm.GroupID
FROM [SOPHOS521].[dbo].[ComputersAndDeletedComputers] as c
 inner join [SOPHOS521].[dbo].[ComputerGroupMapping] as cgm on cgm.ID = c.id
WHERE c.Name in(
	SELECT c.Name
	FROM [SOPHOS521].[dbo].[ComputersAndDeletedComputers] as c
 WHERE Deleted = 0
	GROUP BY c.name
	HAVING ( COUNT(c.name) > 1 )
)
order by c.name 
 This is done regularly and spitting out about 20 duplicates each month which I then delete manually from SEC.

Now, how would I change this query to find and automatically delete the entities that have different computernames, but the same Servicetag/Serial based on last report date (c.LastMessageTime)? Both LastMessageTimes would need to be compared and the one that is more recent stays while the other, older one gets deleted.

Jak for the rescue. :50158

ruckus · Accepted Answer

Oh, and just to add it you select the inner part of the SQL you get to see the ranking column and the row with '1' should always be the current/active endpoint with any 2's, 3's, etc. being the duplicates.

Example:

:51452

QC · Answer

Hello RRR, the "full" statement (from this post ) SELECTs only ServiceTag and LastMessageTime for the rows WHERE Ranking<>1 . Thus there should be a few duplicates (if ruckus' screenshot shows "your data" at the latest in rows 64/65). You'll get the output shown in the screenshot (assuming you're using the Management Studio) by selecting the inner block exactly as shown and pressing F5 (or clicking Execute). The 1610 rows are the n-plicates, significantly less than the expected 1800+. The sophisticated statement should not select less data than the q&d one (at least I can't see a reason). Please check if the "inner" statement spits out the 3700+ rows, run also the q&d SELECT. Christian :51600

QC · Answer

Hello RRR, In one of my tests  .. I deleted _all_ entries Look on the bright side - at least you removed all the n-plicates :smileylol: You already know how to restore the database just in case (and you do stop the management service before deleting, don't you?). You'd have to map the results so far back to the entries in the database table, I'd suggest using the ID. There's likely a better way than just wrapping the query but this isn't a coding contest. DELETE FROM [SOPHOS521].[dbo].[ComputersAndDeletedComputers] 
 WHERE ID IN 
 ( 
SELECT ID FROM (
SELECT c.ID , 
ROW_NUMBER() over ( partition by SUBSTRING(c.name,LEN(c.name)-9,9) order by c.LastMessageTime DESC) as Ranking
FROM [SOPHOS521].[dbo].[ComputersAndDeletedComputers] as c
 inner join [SOPHOS521].[dbo].[ComputerGroupMapping] as cgm on cgm.ID = c.id
WHERE SUBSTRING(c.name,LEN(c.name)-9,9) in(
SELECT SUBSTRING(c.name,LEN(c.name)-9,9)
FROM [SOPHOS521].[dbo].[ComputersAndDeletedComputers] as c
GROUP BY SUBSTRING(c.name,LEN(c.name)-9,9)
HAVING (COUNT(SUBSTRING(c.name,LEN(c.name)-9,9))> 1
)
)
) aaa
WHERE Ranking<>1
 )  I have removed the unneeded columns from the second SELECT and the, now inapplicable, ORDER BY (the line with inner join could be removed as well, the joined data is not used). Use at your own risk, make note of the rows affected :smileyhappy: Christian :51654