Deploying Applications Based on Previous Application Versions

Article
12/19/2007

Often, when installing an operating system on an existing computer, it is desirable to install the same applications that were previously on the computer. This can be achieved using the Microsoft Deployment scripts (in particular ZTIGather.wsf) to query two separate sources of information:

The Systems Management Server 2003 or System Center Configuration Manager hardware inventory database. Contains one record for each application package, in this case Add or Remove Programs information, installed as of the last time Systems Management Server 2003 or System Center Configuration Manager inventoried the computer.
A mapping table. Describes which package and program need to be installed for each record (because the Add or Remove Programs records do not specify exactly which package installed the application, making it impossible to automatically select the package based on inventory alone).

To perform a dynamic computer-specific application installation

Use the table in the Deployment Workbench database to relate specific packages with applications listed in Add or Remove Programs.
Populate the table with data that associates the appropriate package with the application listed in Add or Remove Programs (as illustrated in Listing 8).

Listing 8. Structured Query Language (SQL) Query to Populate the Table
```
use [DWDB]
```

go INSERT INTO [PackageMapping] (ARPName, Packages) VALUES('Office8.0', 'XXX0000F-Install Office 2003 Professional') go

In Listing 8, the inserted row relates any computer that has the **Office8.0** entry in Add or Remove Programs with the Microsoft Office 2003 Professional package.

This means that Microsoft Office 2003 Professional will be installed on any computer currently running Microsoft Office 97 (Office 8.0). Similar entries can be added for any other packages; any Add or Remove Programs for which there is no entry are ignored. (No package will be installed.)

Create a stored procedure to simplify joining the information in the new table with the inventory data (as illustrated in Listing 9).

Listing 9. SQL Query to Create a Stored Procedure to Simplify Joining the Information with Inventory Data
```
use [DWDB]
```

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RetrievePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[RetrievePackages] go

CREATE PROCEDURE [dbo].[RetrievePackages] @MacAddress CHAR(17) AS

SET NOCOUNT ON

/* Select and return all the appropriate records based on current inventory */ SELECT * FROM PackageMapping WHERE ARPName IN ( SELECT ProdID0 FROM SMS_SMS.dbo.v_GS_ADD_REMOVE_PROGRAMS a, SMS_SMS.dbo.v_GS_NETWORK_ADAPTER n WHERE a.ResourceID = n.ResourceID AND MACAddress0 = @MacAddress ) go

The stored procedure in Listing 9 assumes that the Systems Management Server 2003 or System Center Configuration Manager central primary site database resides on the same computer running SQL Server as the Deployment Workbench database (DWDB). If the central primary site database resides on a different computer, the appropriate modifications need to be made to the stored procedure. In addition, the name of the database (SMS\_SMS in Listing 9) must be updated. Team members may also need to grant additional accounts read access to the v\_GS\_ADD\_REMOVE\_PROGRAMS view in the Systems Management Server 2003 database.

Configure the CustomSettings.ini file to query this database table by specifying the name of a section ([DynamicPackages] in the Priority list) that points to the database information (as illustrated in Listing 10).

Listing 10. Excerpt from Customsettings.ini That Illustrates DynamicPackages Section in the Priority List
```
[Settings]
```

... Priority=MacAddress, DefaultGateway, DynamicPackages, Default ...

Create a [DynamicPackages] section to specify the name of a database section (as illustrated in Listing 11).

Listing 11. Excerpt from Customsettings.ini That Illustrates DynamicPackages Section with the Name of a Database Section
```
[DynamicPackages]
```

SQLDefault= DB_DynamicPackages

Create a database section to specify the database information and query details (as illustrated in Listing 12).

Listing 12. Excerpt from CustomSettings.ini That Illustrates the Database Section
```
 [DB_DynamicPackages]
```

SQLServer=SERVER1 Database=DWDB StoredProcedure=RetrievePackages Parameters=MacAddress SQLShare=Logs Instance=SQLEnterprise2005 Port=1433 Netlib=DBNMPNTW

In the example in Listing 12 the DWDB on the computer running the SQL Server named *SERVER1* will be queried. The database contains a stored procedure named RetrievePackages (created in step 3).

When ZTIGather.wsf runs, a SQL SELECT statement is automatically generated, and the value of the MakeModelQuery custom key is passed as a parameter to the query (as shown in Listing 13).

Listing 13. Sample SQL Query Created Automatically Based on the Database Section

EXECUTE RetrievePackages ?

The actual value of the MACAddress custom key will be substituted for the corresponding ? in Listing 13. This query returns a record set with the rows entered in step 2.

Team members cannot pass a variable number of arguments to a stored procedure. As a result, when a computer has more than one MAC address, a team member cannot pass all MAC addresses to the stored procedure. As an alternative, replace the stored procedure with a view that allows the team member to query the view with a SELECT statement with an IN clause to pass all the MAC address values.

Based on the scenario presented here, if the current computer has the Office8.0 value inserted into the table (step 2), the one row is returned (XXX0000F-Install Office 2003 Profession). This indicates that package XXX0000F-Install Office 2003 Professional will be installed by the ZTI process during the State Restore Phase.

Deploying Applications Based on Previous Application Versions

Additional resources