Ivanti Management Center – Delete Old Machines

Help others and share!

One of the jobs that I use frequently is to purge out old systems from the management center using SQL based on a date\time stamp. Below is a script that will do this and purge out devices older than 30 days.

SET NOCOUNT ON  
  
-- Set this to the number of days since the last poll.  
DECLARE @oldestDate DATETIME  
SET @oldestDate = DATEADD (dd, -30, GETDATE())  
  
  
DECLARE @msg NVARCHAR(256)  
SET @msg = 'Deleting machines older than ' + CONVERT(NVARCHAR(32), @oldestDate) PRINT @msg  
  
DECLARE @machineCount INT  
DECLARE @machineKey UNIQUEIDENTIFIER  
DECLARE @machineName NVARCHAR(256)  
DECLARE machineCursor CURSOR FAST_FORWARD FOR SELECT MachinePK, NetBiosName FROM Machines WHERE LastPollTime <= @oldestDate  
  
OPEN machineCursor  
  
SET @machineCount = 0  
FETCH NEXT FROM machineCursor INTO @machineKey, @machineName WHILE @@FETCH_STATUS =0 BEGIN  
   PRINT 'Deleting ' + @machineName + '...'  
   EXEC Machine_Delete @machineKey  
   SET @machineCount = @machineCount + 1  
   FETCH NEXT FROM machineCursor INTO @machineKey, @machineName END  
  
CLOSE machineCursor  
DEALLOCATE machineCursor  
  
SET @msg = CONVERT(NVARCHAR(32), @machineCount) + ' machines were found and deleted'  
PRINT @msg

Another use case I ran into, was when we had devices with no poll period. The above script does nothing with those. Since we disable LDAP device discovery, I wanted to purge out many of those systems that appeared in the console.

To do this I initially created a query to find out how many devices would appear on the list, and to determine if\how I should break up the list even more. Keep in mind that depending on how many records there are, when you go to delete, you can run into locks, so I find it best to purge in batches.

SELECT * FROM Machines WHERE LastPollTime IS NULL

Using the script I posted above, I modify it slightly. Also note that I have added a comment above the EXEC deletion command. If you want to give it a dry run, just comment out the EXEC line.

SET NOCOUNT ON  
  
DECLARE @msg NVARCHAR(256)  
SET @msg = 'Deleting machines that have no poll data.' PRINT @msg  
  
DECLARE @machineCount INT  
DECLARE @machineKey UNIQUEIDENTIFIER  
DECLARE @machineName NVARCHAR(256)  
DECLARE machineCursor CURSOR FAST_FORWARD FOR SELECT MachinePK, NetBiosName FROM Machines WHERE LastPollTime IS NULL 
  
OPEN machineCursor  
  
SET @machineCount = 0  
FETCH NEXT FROM machineCursor INTO @machineKey, @machineName WHILE @@FETCH_STATUS =0 BEGIN  
   PRINT 'Deleting ' + @machineName + '...'  
   -- Comment out the line below if you wanted to give it a dry run.
   EXEC Machine_Delete @machineKey  
   SET @machineCount = @machineCount + 1  
   FETCH NEXT FROM machineCursor INTO @machineKey, @machineName END  
  
CLOSE machineCursor  
DEALLOCATE machineCursor  
  
SET @msg = CONVERT(NVARCHAR(32), @machineCount) + ' machines were found and deleted'  
PRINT @msg

As always, ensure you have a good backup of any existing data before making changes to the system.

Hope this helps!

Help others and share!