Deployment Solution

Do you have unnecessary machines that are not in use, but are still present in the MSDE database in your Deployment Server? Is there a way to clean them out?

This is a script that will allow the user to clean all old machines manually from the MSDE database. Go to Deployment Server 6.5 build > Tools > Options, then 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 computer
WHERE 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%).

Method 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 database
SET DAYS=30
SET SQLSERVER=localhost
REM 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=sa
SET 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

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)