How to purge old computers from the Deployment Solution

Products

• Deployment Solution

Issue/Introduction

 

Resolution

Question
How do I  purge old computers based on the last time they communicated with the Deployment Solution?

Answer
Important: Before running this script in production, test it on a backup of your production database in your lab.
 
When you run the script, it will prompt:

The script will purge machines without inventory updates within the last x days. Please input
the number of days to use.

After placing in a value for the number of days inventory should have uploaded, the script will delete all computers from the Deployment Server database that have not reported inventory within the number of days supplied. This is helpful because the duplicate records never get a newer inventory.

The only thing you would want to be careful about is not choosing a day limit too small causing valid computers to delete themselves because they simply have not connected in a couple of days.

Also note that in Deployment Solution 5.6 and below, this inventory was forced every day (if connected). Deployment Solution 6.0 has the option of turning inventory completely off. You will want to make sure that inventory is running at some interval and not set to 0. This setting is changed in the eXpress Configuration in Control panel.

Script:

on error resume next
set objWshNetwork = CreateObject("WScript.Network")
strNSMachineName = objWshNetwork.ComputerName
set objWshNetwork = nothing
strConnection = "DRIVER={SQL Server};SERVER=" & strNSMachineName & ";DATABASE=express" 
Set objConn = CreateObject("ADODB.Connection") 
objConn.Open strConnection 
if err.number <> 0 then  
wscript.echo "Error - doing SQL connection as the current logged on user."  
strDBUser = inputbox("The integrated SQL logon failed." & _
 vbCRLF & "Please enter a SQL user -" & vbCRLF & "(sa will always work if you have the password)" & vbCRLF & _
 "The password will be asked for in the next box.", "SQL logon", "Enter SQL user")  
strDBUserPw = inputbox("Please enter the SQL user password", "SQL password", "Enter SQL user password")  
Err.clear  
strConnection = strConnection & ";UID=" & strDBUser & ";PWD=" & strDBUserPw  
objConn.Open strConnection  
If err.number <> 0 then   
 wscript.echo "Error - doing SQL connection with new user and pw."   
 strDBMachine = inputbox("Please enter the SQL server machine name", "SQL machine name", "Enter SQL machine name")   
 strDBName = inputbox("Please also enter the NS database name", "NS database name", "Enter NS database name")   
Err.clear
strConnection = "DRIVER={SQL Server};SERVER=" & strDBMachine & ";DATABASE=" &strDBName & ";UID=" & strDBUser & ";PWD=" & strDBUserPw   
objConn.Open strConnection   
If err.number <> 0 then
 wscript.echo "Error - doing SQL connection with new machine name, db, user and pw."    
 Wscript.echo "The connection to SQL was not created." & vbCRLF & vbCRLF & _
 "The script will halt now and open explorer to the location of the NSCheck.txt file." & vbCRLF & vbCRLF & _
 "Please send the NSCheck.txt to Altiris Support if you can't resolve the connection error."    
 objWShell.Run ("cmd /c explorer ."),7,1    
 Wscript.quit
 Else
  wscript.echo"SQL connection with SQL user name, password, machine and db name."   
 End if
 else
  wscript.echo"SQL connection with SQL user name and password."
  End If
  Else
   wscript.echo"SQL connection as the current logged on user." 
  End If
objConn.CommandTimeout = 600
strDayCount = inputbox("The script will purge machines without" & vbCRLF & _
 "inventory updates within the last x days." &vbCRLF & "Please input the number of days to use", "INPUT") 
Set objFS = Wscript.CreateObject("Scripting.FileSystemObject")
strFileName = ".\DSDelete.txt"
Set objDSDelete = objFS.CreateTextFile (strFilename)
objDSDelete.writeline("This script was run on date: " & Now())
objDSDelete.writeline("This script was run on machine " & strNSMachineName)
objDSDelete.writeline("The number of days selected was: " & strDayCount)
objDSDelete.writeline("The data is next." & vbCRLF & vbCRLF)
set objRS = objConn.execute("select * from computer where datediff(dd,last_inventory,getdate())>" & strDayCount)
if err.number<>0 then
 objDSDelete.writeline("There was an error in the select.  Script exited")
 wscript.echo "There was an error.  Please contact support" 
 wscript.exit
end if 
intCounter=0
 while not objRS.EOF
  objDSDelete.writeline(objRS("computer_id") & vbTAB & vbTAB & objRS("name") & vbTAB & vbTAB & objRS("domain_name") & vbTAB & vbTAB & objRS("last_inventory"))
  objRS.movenext
  intCounter = intCounter + 1
 wend
 if err.number<>0 then 
  objDSDelete.writeline("There was an error writing to the file.  Script exited") 
  wscript.echo "There was an error.  Please contact support" 
  wscript.exit
 end if
 objDSDelete.writeline(vbCRLF & vbCRLF & "The number of machines identified for deletion is:  " & intCounter)
 strContinue = inputbox("This will delete " & intCounter & " machines." & vbCRLF & "The list is now in the same directory" _
 & vbCRLF & " as this script is running from" & vbCRLF & "with the name DSDelete.txt" & vbCRLF & _
 "Do you wish to continue and do the delete (y/n)?","Continue")
 if lcase(strContinue) = "y" then 
  objConn.execute("delete computer where datediff(dd,last_inventory,getdate())>" & strDayCount)
  if err.number<>0 then 
   objDSDelete.writeline("There was an error in the delete.  Script exited") 
   wscript.echo "There was an error.  Please contact support" 
   wscript.exit
   else
    objDSDelete.writeline("The machines WERE deleted successfully.")
   end if
   else 
    objDSDelete.writeline("The machines WERE NOT deleted because they were not selected to be deleted.")
   end if
objDSDelete.closeset
objDSDelete = nothing
Wscript.echo "Finished"