Gernot asked a question.
As we migrate all our compuers to Intune, which requrie a reset of the device, we have a lot of duplicates in our SLM and Inventory Infrastructure. Is there an easy way (SQL Statement?) to get rid of the obsolt dublicats with same serial number? (we have arround 1600 duplicates now.. so it is to much to clean manually.
As with any supplied SQL on this community, proceed with caution, if you don't know what you are doing or are unsure about the results then I would engage with Snow directly to supply the relevant deletion SQL.
That said, the following is what I use to remove duplication...
/* --- SQL script Start--- */
USE [SnowLicenseManager]
DECLARE @ComputerID INT = 0
DECLARE @ClientID INT = 0
DECLARE @ClientXMLDelete NVARCHAR(100)
/* Query to identify what records to delete */
DECLARE curDataToDelete CURSOR FOR
SELECT c.ComputerID,
ISNULL(m.ClientID, 0) As ClientID,
'<item id="' + CAST(ISNULL(m.ClientID, 0) AS VARCHAR(10)) + '"/>' AS ClientXMLDelete
FROM SnowLicenseManager.dbo.tblComputer c INNER JOIN
(SELECT ComputerID, HostName, LastScanDate, BiosSerialNumber,
RANK() OVER(PARTITION BY BiosSerialNumber ORDER BY LastScanDate DESC) As rnk
FROM SnowLicenseManager.dbo.tblComputer) c2 ON c.ComputerID = c2.ComputerID
LEFT JOIN SnowLicenseManager.inv.tblComputerInvSlmMap m ON m.ComputerID = c.ComputerID
LEFT JOIN SnowInventory.inv.DataClientView2 d on m.ClientId = d.ClientId
WHERE c2.rnk > 1
AND c.BiosSerialNumber IS NOT NULL
AND c.BiosSerialNumber != ''
ORDER BY c.BiosSerialNumber ASC, c.LastScanDate DESC
/* Loop round results, deleting each on from SnowLicenseManager and SnowInventory */
OPEN curDataToDelete;
FETCH NEXT FROM curDataToDelete INTO @ComputerID, @ClientID, @ClientXMLDelete
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'Deleting ComputerID:' + CAST(@ComputerID AS VARCHAR(10)) + ' from SLM';
EXEC SnowLicenseManager.dbo.ComputerDelete 1, @ComputerID, 'Manual Deletion', 1
PRINT 'Deleting ClientID:' + CAST(@ClientID AS VARCHAR(10)) + ' from Inventory';
EXEC SnowInventory.inv.DeleteClients @ClientXMLDelete
FETCH NEXT FROM curDataToDelete INTO @ComputerID, @ClientID, @ClientXMLDelete
END
CLOSE curDataToDelete
DEALLOCATE curDataToDelete
/* --- SQL script end --- */
Now a couple of points to consider. The first section identifies what records to delete. A SQL RANK/PARTITION function is used, in our cause partitioning by BiosSerialNumber and ordering by LastScanDate. This will attribute each block of duplicate BiosSerialNumbers a 'rank' and we will then filter by that rank (c2.rnk>1) effectively throwing away the oldest records (as we ordered by LastScanDate DESC) and retaining the most recently scanned one. Also note we are excluding NULL or empty BiosSerialNumbers as there may be a number of these in the system that we don't want to remove. You can obviously tweak the SQL statement to ensure that you are only retrieving the relevant data to remove. You can run that SQL statement in isolation (excluding the DECLARE curDataToDelete CURSOR FOR line) and review the resultant dataset.
If you are happy with resultset then the script can be run as a whole. The 2nd section (the WHILE block) will loop round the cursor curDataToDelete and make calls to the built in stored procedures dbo.ComputerDelete (in SnowLicenseManager) and inv.DeleteClients (in SnowInventory).
Note the first parameter of dbo.ComputerDelete call is set to 1. This is the Customer ID (CID) of my estate, its the default if you only have 1 customer (e.g. on premise) but make sure you set it accordingly.
Hi @Colin Hardie​ just a quick question about this script:
What is the last argument in the EXEC SnowLicenseManager.dbo.ComputerDelete statement?
SSMS indicates it is called @DeleteDirect and you set it to 1.
Hi Samuel, I'm guessing the parameter is used to control whether the record is marked for deletion (and then actually deleted by the SLM maintenance service...like what would happen if you deleted from the SLM GUI) or whether it is just deleted directly.
I have only ever set it to 1, as per this KB
As with any supplied SQL on this community, proceed with caution, if you don't know what you are doing or are unsure about the results then I would engage with Snow directly to supply the relevant deletion SQL.
That said, the following is what I use to remove duplication...
/* --- SQL script Start--- */
USE [SnowLicenseManager]
DECLARE @ComputerID INT = 0
DECLARE @ClientID INT = 0
DECLARE @ClientXMLDelete NVARCHAR(100)
/* Query to identify what records to delete */
DECLARE curDataToDelete CURSOR FOR
SELECT c.ComputerID,
ISNULL(m.ClientID, 0) As ClientID,
'<item id="' + CAST(ISNULL(m.ClientID, 0) AS VARCHAR(10)) + '"/>' AS ClientXMLDelete
FROM SnowLicenseManager.dbo.tblComputer c INNER JOIN
(SELECT ComputerID, HostName, LastScanDate, BiosSerialNumber,
RANK() OVER(PARTITION BY BiosSerialNumber ORDER BY LastScanDate DESC) As rnk
FROM SnowLicenseManager.dbo.tblComputer) c2 ON c.ComputerID = c2.ComputerID
LEFT JOIN SnowLicenseManager.inv.tblComputerInvSlmMap m ON m.ComputerID = c.ComputerID
LEFT JOIN SnowInventory.inv.DataClientView2 d on m.ClientId = d.ClientId
WHERE c2.rnk > 1
AND c.BiosSerialNumber IS NOT NULL
AND c.BiosSerialNumber != ''
ORDER BY c.BiosSerialNumber ASC, c.LastScanDate DESC
/* Loop round results, deleting each on from SnowLicenseManager and SnowInventory */
OPEN curDataToDelete;
FETCH NEXT FROM curDataToDelete INTO @ComputerID, @ClientID, @ClientXMLDelete
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'Deleting ComputerID:' + CAST(@ComputerID AS VARCHAR(10)) + ' from SLM';
EXEC SnowLicenseManager.dbo.ComputerDelete 1, @ComputerID, 'Manual Deletion', 1
PRINT 'Deleting ClientID:' + CAST(@ClientID AS VARCHAR(10)) + ' from Inventory';
EXEC SnowInventory.inv.DeleteClients @ClientXMLDelete
FETCH NEXT FROM curDataToDelete INTO @ComputerID, @ClientID, @ClientXMLDelete
END
CLOSE curDataToDelete
DEALLOCATE curDataToDelete
/* --- SQL script end --- */
Now a couple of points to consider. The first section identifies what records to delete. A SQL RANK/PARTITION function is used, in our cause partitioning by BiosSerialNumber and ordering by LastScanDate. This will attribute each block of duplicate BiosSerialNumbers a 'rank' and we will then filter by that rank (c2.rnk>1) effectively throwing away the oldest records (as we ordered by LastScanDate DESC) and retaining the most recently scanned one. Also note we are excluding NULL or empty BiosSerialNumbers as there may be a number of these in the system that we don't want to remove. You can obviously tweak the SQL statement to ensure that you are only retrieving the relevant data to remove. You can run that SQL statement in isolation (excluding the DECLARE curDataToDelete CURSOR FOR line) and review the resultant dataset.
If you are happy with resultset then the script can be run as a whole. The 2nd section (the WHILE block) will loop round the cursor curDataToDelete and make calls to the built in stored procedures dbo.ComputerDelete (in SnowLicenseManager) and inv.DeleteClients (in SnowInventory).
Note the first parameter of dbo.ComputerDelete call is set to 1. This is the Customer ID (CID) of my estate, its the default if you only have 1 customer (e.g. on premise) but make sure you set it accordingly.