Deploying Applications Based on Previous Application Versions

Article
03/05/2008

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 BDD 2007 scripts (in particular ZTIGather.wsf) to query two separate sources of information:

The SMS 2003 hardware inventory database. Contains one record for each application package, in this case Add/Remove Programs information, installed as of the last time SMS 2003 inventoried the computer.
A mapping table. Describes which SMS 2003 package and program should 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/Remove Programs.
Populate the table with data that associates the appropriate package with the application listed in Add/Remove Programs (as illustrated in Listing 14).

Listing 14. SQL Query to Populate the Table

Note Some parts of the following code snippet have been displayed in multiple lines only for better readability. These should be entered in a single line.
```
use [DWDB]
```

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

In Listing 14, the inserted row relates any computer that has the “Office8.0” entry in Add or Remove Programs with the SMS 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 will be ignored. (No package will be installed.)

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

Listing 15. SQL Query to Create a Stored Procedure to Simplify Joining the Information with SMS Inventory Data

Note Some parts of the following code snippet have been displayed in multiple lines only for better readability. These should be entered in a single line.
```
use [DWDB]
```

go 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 15 assumes that the SMS central primary site database resides on the same computer running SQL Server as the Deployment Workbench database (DWDB). If the SMS 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 SMS database (SMS\_SMS in Listing 15) must be updated. You may also need to grant additional accounts read access to the v\_GS\_ADD\_REMOVE\_PROGRAMS view in the SMS 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 16).

Listing 16. 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 17).

Listing 17. 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 18).

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

SQLServer=SERVER1 Database=DWDB StoredProcedure=RetrievePackages Parameters=MacAddress SQLShare=Logs

In the example in Listing 18, 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 19).

Listing 19. 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 19. This query returns a record set with the rows entered in step 2.

You cannot pass a variable number of arguments to a stored procedure. As a result, when a computer has more than one MAC address, you cannot pass all MAC addresses to the stored procedure. As an alternative, you could replace the stored procedure with a view that would allow you 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 will be returned (“XXX0000F-Install Office 2003 Profession”). This indicates that package “XXX0000F-Install Office 2003 Profession” will be installed by the ZTI process during the State Restore Phase.

Deploying Applications Based on Previous Application Versions

Additional resources