Manipulating the Microsoft Deployment Toolkit database using PowerShell

A couple of weeks ago at the Microsoft Management Summit conference in Las Vegas, I demonstrated some PowerShell scripts for doing a variety of things. I had promised to post those to my blog so that you could use them as well. This is the first of those scripts, designed to help maintain the contents of the MDT database. I have done some additional work on this one since I demonstrated it, and it’s now ready for your enjoyment.

This PowerShell script leverages the PowerShell 2.0 “advanced function” capabilities to write PowerShell cmdlets using PowerShell scripts – no compiled code is required. That does mean that you must be running PowerShell 2.0 CTP3 or later (e.g. the version of PowerShell included in Windows 7). There is no dependency on a particular version of MDT, so you can use this with MDT 2008, MDT 2010, or even BDD 2007 if you so choose.

The script files themselves are rather bare – lots of scripts, very few comments, and no documentation at all beyond what is in this blog post. There just aren’t enough hours in the day to do those types of things. It’s bad enough that the PowerShell module script has already grown to over 1,500 lines.

Here’s the quick list of Cmdlets included in the MDTDB.psm1 script and a very brief description of each:

Connect-MDTDatabase. Establish a connection to an MDT database.
New-MDTComputer. Create a new computer entry.
Get-MDTComputer. Get an existing computer entry, or a list of all computer entries.
Set-MDTComputer. Modify the settings of an existing computer entry.
Remove-MDTComputer. Remove an existing computer entry.
Get-MDTComputerApplication. Get the applications for an existing computer entry.
Clear-MDTComputerApplication. Remove all applications from an existing computer entry.
Set-MDTComputerApplication. Modify the list of applications for an existing computer entry.
Get-MDTComputerPackage. Get the ConfigMgr packages for an existing computer entry.
Clear-MDTComputerPackage. Remove all packages from an existing computer entry.
Set-MDTComputerPackage. Modify the list of packages for an existing computer entry.
Get-MDTComputerRole. Get the list of roles for an existing computer entry.
Clear-MDTComputerRole. Remove all roles from an existing computer entry.
Set-MDTComputerRole. Modify the list of roles for an existing computer entry.
Get-MDTComputerAdministrator. Get the list of administrators for an existing computer entry.
Clear-MDTComputerAdministrator. Remove all administrators from an existing computer entry.
Set-MDTComputerAdministrator. Modify the list of administrators for an existing computer entry.
New-MDTRole. Create a new role.
Get-MDTRole. Get an existing role, or a list of all roles.
Set-MDTRole. Modify the settings of an existing role.
Remove-MDTRole. Remove an existing role.
Get-MDTRoleApplication. Get the applications for an existing role.
Clear-MDTRoleApplication. Remove all applications from an existing role.
Set-MDTRoleApplication. Modify the list of application for an existing role.
Get-MDTRolePackage. Get the ConfigMgr packages for an existing role.
Clear-MDTRolePackage. Remove all packages from an existing role.
Set-MDTRolePackage. Modify the list of packages for an existing role.
Get-MDTRoleRole. Get the list of roles for an existing role.
Clear-MDTRoleRole. Remove all roles from an existing role.
Set-MDTRoleRole. Modify the list of roles for an existing role.
Get-MDTRoleAdministrator. Get the list of administrators for an existing role.
Clear-MDTRoleAdministrator. Remove all administrators from an existing role.
Set-MDTRoleAdministrator. Modify the list of administrators for an existing role.
New-MDTLocation. Create a new location.
Get-MDTLocation. Get an existing location, or a list of locations.
Set-MDTLocation. Modify the settings of an existing location.
Remove-MDTLocation. Remove an existing location.
Get-MDTLocationApplication. Get the applications for an existing location.
Clear-MDTLocationApplication. Remove all applications from an existing location.
Set-MDTLocationApplication. Modify the list of applications for an existing location.
Get-MDTLocationPackage. Get the ConfigMgr packages for an existing location.
Clear-MDTLocationPackage. Remove all packages from an existing location.
Set-MDTLocationPackage. Modify the list of packages for an existing location.
Get-MDTLocationRole. Get the roles for an existing location.
Clear-MDTLocationRole. Remove all roles from an existing location.
Set-MDTLocationRole. Modify the list of roles for an existing location.
Get-MDTLocationAdministrator. Get the administrators for an existing location.
Clear-MDTLocationAdministrator. Remove all administrators from an existing location.
Set-MDTLocationAdministrator.
New-MDTMakeModel
Get-MDTMakeModel
Set-MDTMakeModel
Remove-MDTMakeModel
Get-MDTMakeModelApplication
Clear-MDTMakeModelApplication
Set-MDTMakeModelApplication
Get-MDTMakeModelPackage
Clear-MDTMakeModelPackage
Set-MDTMakeModelPackage
Get-MDTMakeModelRole
Clear-MDTMakeModelRole
Set-MDTMakeModelRole
Get-MDTMakeModelAdministrator
Clear-MDTMakeModelAdministrator
Set-MDTMakeModelAdministrator
New-MDTPackageMapping
Get-MDTPackageMapping
Set-MDTPackageMapping
Remove-MDTPackageMapping

Yes, there really are 69 different cmdlets in the script. I could have tried generalizing some of these, but then you’d have to provide more parameters to each one, so this actually simplifies things a little. A few notes:

Connect-MDTDatabase can connect in two ways. One way works with MDT 2010 Beta 1 or earlier, where you have to specify the connection details (server, instance, database name). The other works with MDT 2010 Beta 2 or later, where you can just point to the deployment share that contains all the needed details.
Only the “Get” cmdlets are used to select items by some identifier (e.g. MAC address). The other commands use the ID. So the standard process will be to use “Get” with the other cmdlets in the same pipeline.
The settings are specified using a hash table. See http://www.microsoft.com/technet/scriptcenter/resources/pstips/sept07/pstip0914.mspx for details. An example for MDT might be @{SkipWizard=’YES’;DoCapture=’TRUE’}.
Lists are specified using an array. See http://blogs.msdn.com/powershell/archive/2007/01/23/array-literals-in-powershell.aspx for samples. An MDT-specific example might be @(‘10.1.1.1’,’10.1.2.1’, ‘10.1.3.1’).
You may not have any need to call the “Clear” cmdlets, but they are used behind the scenes by the “Set” cmdlets.
Get-MDTLocation behaves a little differently than the other “Get” cmdlets. This is because it would otherwise return one location item per default gateway, and that would mess up the other cmdlets. So by default, Get-MDTLocation will only return the location name and ID (single record). If you want to see the settings and list of gateways, add “-detail” to the PowerShell command.
In most cases, parameters can be retrieved from the pipeline. This lets you do some interesting batch processes.

So how do you use all of these? See the included “MDTDB_Test.ps1” script for some simple examples (used to test the logic). Here are a few more examples, just to give you some ideas.

Create a new computer and set its details

To add a new computer, just specify some identifying details (at least a MAC address, serial number, asset tag, or UUID, or any combination of those), along with whatever settings you want to configure. This example adds a machine, specifies its settings, and configures it with a list of applications:

import-module .\MDTDB.psm1
connect-mdtdatabase -sqlserver MNiehaus-T61P-7 -instance SQLEXPRESS -database MDTDatabase
new-mdtcomputer -macAddress '00:00:00:11:22:33' -settings @{OSInstall='YES'; OSDComputerName='MYPC'}
get-mdtcomputer -macaddress '00:00:00:11:22:33' | Set-MDTComputerApplication -applications @('{dc83b044-f018-40ec-828f-5e2b77058bb4}','{e7d31704-af9d-4105-ad4b-dbb95e23b692}')

You could actually shorten this too, since New-MDTComputer returns the new item which can be piped into Set-MDTComputerApplication:

import-module .\MDTDB.psm1
connect-mdtdatabase -sqlserver MNiehaus-T61P-7 -instance SQLEXPRESS -database MDTDatabase
new-mdtcomputer -macAddress '00:00:00:11:22:33' -settings @{OSInstall='YES'; OSDComputerName='MYPC'} | Set-MDTComputerApplication -applications @('{dc83b044-f018-40ec-828f-5e2b77058bb4}','{e7d31704-af9d-4105-ad4b-dbb95e23b692}')

Import a list of makes and models

Assume you have a “Models.csv” file that looks like this (maybe exported from ConfigMgr):

Make,Model
Dell,Latitude e6400
Lenovo,T61p
HP,dx5150
Toshiba,M400

With that, you can then issue a simple PowerShell command to quickly create multiple MDT database entries:

import-module .\MDTDB.psm1
connect-mdtdatabase -sqlserver MNiehaus-T61P-7 -instance SQLEXPRESS -database MDTDatabase
import-csv .\Models.csv | New-MDTMakeModel -settings @{OSInstall='YES'}

Create a new role and set its details

Creating a new role just requires specifying its name, along with any settings you want. In this case, I’ll also add a list of ConfigMgr packages that should be installed with this role:

import-module .\MDTDB.psm1
connect-mdtdatabase -sqlserver MNiehaus-T61P-7 -instance SQLEXPRESS -database MDTDatabase
New-MDTRole -name 'My Role' -settings @{Home_Page = 'http://www.live.com'} | Set-MDTRolePackage -packages @('XXX00001:Install','XXX00002:Install')

List all contents

The “get” cmdlets can be used to retrieve a list of existing items by not specifying any criteria:

import-module .\MDTDB.psm1
connect-mdtdatabase -sqlserver MNiehaus-T61P-7 -instance SQLEXPRESS -database MDTDatabase
Get-MDTComputer
Get-MDTLocation
Get-MDTMakeModel
Get-MDTRole

Note that you might want to specify -detail on the Get-MDTLocation cmdlet to get the details; this will cause it to return one row per gateway value.

Add a package mapping entry

MDT provides a sample table and stored procedure for reinstalling software on a computer based on ConfigMgr inventory. To support this, you need to populate the package mapping table with entries to indicate which package should be installed for machine with a particular Add/Remove Programs entry. Adding an entry can be done fairly easily:

import-module .\MDTDB.psm1
connect-mdtdatabase -sqlserver MNiehaus-T61P-7 -instance SQLEXPRESS -database MDTDatabase
New-MDTPackageMapping -ARPName 'Zune' -package 'XXX00003:Install'