Tuesday, 25 March 2008

How to clean out old computers in Deployment Server database

Article ID: 24561
Here is a Script to delete all old computers manually. Within the Deployment Server 6.5 build > Tools > Options, check the box to clean out old computers by date range.There is a SQL script you can run in Query Analyzer that will clean out computers that have not reported inventory in 35 days. To use this script just open SQL Query Analyzer from the SQL Administrations tools. Select the eXpress Database then copy the following code into the Query Analyzer window and click run.
DELETE from computerWHERE datediff(dd,last_inventory,getdate())>35
Here is a sample Deployment Server job that can be scheduled in DS to run regularly (daily/monthly) that will automate this task. It is a server-side script and must be assigned to a client PC before it can run on the Deployment server. You can schedule it on any computer that will likely be on most of the time; using scheduling have it run once a month.Without modification it assumes SQL is on the same server as DS and that you are using SA with a blank password. It will delete any computer older than 30 days. It is self-explanatory how to change this behavior. Just look at the script (%DAYS%, %SQLSERVER%, %SQLUSER%, and %SQLPASS%).Steps to create this job:
Create a Run script event that runs in Windows. Here is the Script to use for this event:REM This will delete entries that are older than DAYS from the eXpress databaseSET DAYS=30SET SQLSERVER=localhostREM Use the following line for trusted authentication. Note that the script will run by default as LOCAL SYSTEM and may not have rights to the SQL server.REM osql -E -S %SQLSERVER% -d eXpress -Q "DELETE from computer WHERE datediff(dd,last_inventory,getdate())>%DAYS%" >> %WINDIR%\Temp\DS_Delete.log
REM Use the following lines for SQL authentication. You will need to supply an appropriate SQL username and password.SET SQLUSER=saSET SQLPASS=""osql -U %SQLUSER% -P %SQLPASS% -S %SQLSERVER% -d eXpress -Q "DELETE from computer WHERE datediff(dd,last_inventory,getdate())>%DAYS% " >> %WINDIR%\Temp\DS_Delete.log
NOTE: If Inventory is turned off then Last_Inventory will not be an effective or reliable way to target which computers need to be deleted. If Inventory collection is not enabled then the following query will be better to use in the previous scripts and or command lines:
DELETE FROM computer WHERE computer_id IN (select computer_id from sessions where datediff(dd, last_update, getdate()) > 35)

No comments: