Q212691: Delete old computers that haven't been reporting inventories from EDS Databases

Some computers had RayManageSoft client installed and reported inventories at some stage. Then these computers are removed from network or put into storage, and they haven't been reporting inventories for a while. However, the records for these computers may still be stored in RMS database and they may still be counted towards RMS license counts.

 

The following SQL query will delete the computer records that haven't been reporting inventories for 90 days from RMS database. You can change the number "90" to any number of days that suits your needs.

 

--------------

DECLARE csr CURSOR LOCAL FOR

SELECT c.ComputerID

FROM Computer c

INNER JOIN InventoryReport ir ON ir.ComputerID = c.ComputerID

WHERE ir.UserID = 1

AND DATEDIFF(day, ir.HWDate, GetDate()) > 90

 

 

DECLARE @id INT

OPEN csr

FETCH NEXT FROM csr INTO @id

 

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC DeleteComputer @id

FETCH NEXT FROM csr INTO @id

END

 

CLOSE csr

DEALLOCATE csr

--------------