Windows 7 / Getting Started

---

Filling the MDT Database Using PowerShell

After you configure the MDT database and define access to it, you can add computers to the database and define their settings. If you need to perform this task for hundreds of computers, you don't want to do it one by one. You can instead use PowerShell to perform a bulk import of computer objects in the MDT database. In this example, we are going to use a CSV file that contains information about the computers we want to import.

Setting Up the Module

To begin, you'll use the MDT PowerShell module, provided by Michael Niehaus described in his blog posting:

http://blogs.technet.com/b/mniehaus/archive/2009/05/15/manipulating-themicrosoft- deployment-toolkit-database-using-powershell.aspx

Also keep in mind that you need PowerShell 2.0, which is included in Windows Server 2008 R2 by default. Follow these steps:

1. Download the MDTDB.zip file from the blog posting and unblock the file after you downloaded it. You can do this by opening the properties of the MDTDB.zip file and clicking Unblock on the General tab.
2. Unzip the contents of MDTDB.zip to a new folder and explore that folder. The zip file contains the MDTDB.psm1 file, which is the PowerShell module, and the MDTDB_Test.ps1 script, which contains samples on how to use the module in your own script. Notice that you should make a reference in your script to the module using the import-module cmdlet in PowerShell.
3. Be sure you set the PowerShell Execution policy to Unrestricted.

Creating the CSV File

You will use Microsoft Excel to create an input CSV file, which you can use to import a batch of machines into the database.

1. In Excel, open a new workbook and create three columns named Name, MAC, and Roles, respectively.
2. Fill the rows with the information you have on each computer you want to import: its name, its MAC address, and which role it should belong to.
3. Export the spreadsheet to a CSV file and name it bulkimport.csv.

Setting the Correct Separator
Depending on your regional settings, sometimes when exporting an Excel sheet to a CSV file, you can end up having a semicolon (;) instead of a comma (,) as the separator. You can modify this by going to Control Panel → Region And Language, selecting the Formats tab, and clicking Additional Settings. Here you have the option to set the list separator value, which should be set to comma. If you don't use the comma as a separator, PowerShell will not understand the CSV when using the import-csv cmdlet

Using the CSV File with the PowerShell Module

You can use the following PowerShell script to import your CSV file into your MDT database. You should modify the Connect-MDTDatabase cmdlet line with your own SQL Server name and MDT database name. If you are using a named instance, you should also add the option -instance instance name after the provided SQL Server computer name. Make sure that the PowerShell script, the CSV file, and the MDTDB.psm1 file are in the same folder.

Import-Module -name .\MDTDB.psm1
Connect-MDTDatabase -sqlServer SRV-SQL01 -database MDT
$computers = Import-Csv .\bulkimport.csv
$computers
For ($x=1; $x -le $computers.count; $x++)
{
    New-MDTComputer -macAddress $computers[$x-1].mac -description
$computers[$x-1].description -settings @{
    OSInstall='YES';
    OSDComputerName=$computers[$x-1].name;
  }
  Get-MDTComputer -macAddress $computers[$x-1].mac ? Set-MDTComputerRole -roles
$computers[$x-1].roles
}

If you want to see the output of the scripts directly, run a refresh on your database in order for the new computers to be reflected.

This is just one example of what you can do with the PowerShell module provided by Michael Niehaus.

Extending the MDT Database with Custom Settings

In addition to working with the default tables and views in the database, you can extend the schema of the MDT database so that you can add custom values. In this scenario, you are going to add the name of an application server that differs for each location, so that you can automatically use this name when you configure the custom application during the installation of the workstation.

Before extending your MDT database, make sure that you have created a backup of the database in case something goes wrong. To create the new ApplicationServer variable, you must add a new column to the settings table called ApplicationServer, which will contain the name of the application server you are going to use in a specific location:

1. Open SQL Server Management Studio, and in the Object Explorer browse to the MDT database → Tables → dbo.settings → Columns.
2. Right-click Columns and select New Column.
3. Type the name of the new column you want to define (in this case, ApplicationServer), select nvarchar(50) as the data type, and make sure that Allow Nulls is selected.
4. Refresh all views that have a reference to the settings table by running the sp_refreshview stored procedure. You can do this by defining a new query in SQL Server Management Studio that contains the following lines:

   Execute sp_refreshview ComputerSettings
   Execute sp_refreshview LocationSettings
   Execute sp_refreshview MakeModelSettings
   Execute sp_refreshview RoleSettings
   

5. Execute this query against the MDT database by clicking "! Execute" on the main toolbar.
6. You should now have a new option available on the Details tab of your computer settings, in your location settings, in your make and model settings, and in your role settings.

Next you must provide the name of the application server in the location settings:

1. Open the location for which you want to set the ApplicationServer setting.
2. Select the Details tab and scroll all the way down to the ApplicationServer setting.
3. Modify its value with the name of the application server for that location.

Before you can use the new ApplicationServer value, you need to create a reference in your CustomSettings.ini file so that this value is queried:

1. Open the CustomSettings.ini file using a text editor and find the Settings section. Find the Properties setting and modify its value from MyCustomProperty to ApplicationServer, as follows:

   [Settings]
   Priority=CSettings, CPackages, CApps, CAdmins, CRoles, Locations, LSettings,
   LPackages, LApps, LAdmins, LRoles, MMSettings, MMPackages, MMApps, MMAdmins,
   MMRoles, RSettings, RPackages, RApps, RAdmins, Default
   Properties=ApplicationServer
   

2. You can now use the %ApplicationServer% variable in your MDT task sequence step to install the application, providing %ApplicationServer% as an argument for the installation procedure.