Manipulating the Microsoft Deployment Toolkit database using PowerShell

Article
05/14/2009

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 https://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 https://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 = 'https://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'

MDTDB.zip

Comments

Anonymous
January 01, 2003
You mention: "in MDT 2010 Beta 2 or later, where you can just point to the deployment share that contains all the needed details." Would that be: Connect-MDTDatabase -drivepath \mdtservermdtpath ?
Anonymous
January 01, 2003
Set-MDTComputerRole expects to be passed the whole list of roles to be assigned to a computer. So if you wanted to add one, you would need to use "Get-MDTComputerRole" to get the current list as an array, add a new item to that array, and then use Set-MDTComputerRole to set the list to the newly-extended version.
Anonymous
January 01, 2003
Great blog entry Mike! Thanks for this useful contribution.
Anonymous
January 01, 2003
PingBack from http://asp-net-hosting.simplynetdev.com/scripts-powershell-pour-gerer-la-base-de-donnees-mdt-2010/
Anonymous
January 01, 2003

Set-MDTComputerRole doesn't seem to do exactly what it claims to, it deletes all existing roles and adds the selected one instead of adding it to the list of roles. Bug?

Anonymous
January 01, 2003
Wow this is going to save a lot of time for setting up redundant systems. Thanks Mike!
Anonymous
January 01, 2003
http://blogs.technet.com/mniehaus/archive/2009/05/15/manipulating-the-microsoft-deployment-toolkit-database
Anonymous
January 01, 2003
Is there any way to delete a record in MDT Database from WinPE during Pre install ?
Anonymous
January 01, 2003
On the Connect-MDTDatabase comment, you can do this by specifying the logical drive path. For example: Connect-MDTDatabase -drivepath DS001:
Anonymous
November 04, 2010
So, there's probably a better place to post this but. So far i LOVE this, thank you so much. but, I'm failing and coming up with a way to mass update machines in my database with the set-MDTComputer string. I failed to add a couple of settings when i mass imported my machines and am lacking in my powershell skills to be able to com up with the correct syntax to do such a thing. I'd also like to apply the same type of deal with set-MDTComputerApplication.
Anonymous
November 01, 2012
This looks very advanced to me. I need simpler steps to install apps based on make and model of the computers. is there any other way that is simpler compare to this? thanks
Anonymous
March 19, 2013
Is there any way to add Description to the database? It's more friendly then MAC address.
Anonymous
April 11, 2013
Hello, and many many thanks. Any idea why Get-MDTComputer -description MYPCNAME always throws an error? Walking through the psm file, it should work, but the error is that ComputerSettings has no such column. In fact, in the output of Get-MDTComputer, no computer has the description listed. Is that intentional? Any help would be greatly appreciated. Thanks! --Matt
Anonymous
April 11, 2013
Sorry, I forgot to mention that for thousands of PCs, we use Description=OSDComputerName just so that I could support querying by name...
Anonymous
April 12, 2013
The comment has been removed
Anonymous
September 10, 2013
Does this work with MDT 2012 Update 1? If it does not is there a more up to date version of these scripts?
Anonymous
October 01, 2013
While working with these powershell commands last week, a co-worker discovered that the ComputerSettings view did not have the Description from the ComputerIdentity Table. Once you add it in, the description then works.
Anonymous
December 29, 2013
Pingback from OS Deployment – OOB install of Windows Server 2012 R2 Using MDT,WDS and PowerShell « The Deployment Bunny
Anonymous
December 29, 2013
Pingback from OS Deployment – OOB install of Windows Server 2012 R2 Using MDT,WDS and PowerShell « The Deployment Bunny
Anonymous
July 01, 2015
MDT allow you to query the MDT Database during a Gather step using database sections in CustomSetting
Anonymous
July 12, 2015
Is there any way to use newcomputer with OSDComputername through .csv file? want to create new Computer in bulk thorugh .csv file in MDT DB with - settings OSDComputerName
Anonymous
January 08, 2016
How I completely automated with WMI and AD using a powershell profile that Imports AD module and MDT Database module.

Connect-MDTDatabase -sqlServer SQLServer -instance INSTANCE -database DATABASE

Get-ADComputer -Filter {OperatingSystem -Notlike "Server"} | sort Name | select -

ExpandProperty Name | out-file "C:ScriptOutputsMDTImportCOMPS.txt"

$Computers = get-Content -Path "C:ScriptOutputsMDTImportCOMPS.txt"

foreach ($computer in $Computers) {
write-host `n
if (!(Get-MDTComputer -assettag $Computer)){
if (Test-Connection -ComputerName $computer -count 4 -delay 2 -Quiet)
{
write-host Processing Computer $computer -ForegroundColor Magenta
$ErrorActionPreference = 'SilentlyContinue'
########Computer############

$SerialNumber = Get-WmiObject -Class Win32_BIOS –Computername $computer | Select-Object -ExpandProperty serialnumber
$CS = Get-WmiObject -Class Win32_ComputerSystem –Computername $computer
$Model = $CS.Model
$Manufacturer = $CS.Manufacturer

$MACAdress = Get-WmiObject -ClassName win32_networkadapterconfiguration -ComputerName $computer | where {$.ipenabled -eq "true" -and $.IPAddress -ne "0.0.0.0"}
$MAC = $MACAdress.MACAddress

$UUID = Get-WmiObject -Class Win32_ComputerSystemProduct –Computername $Computer | select -ExpandProperty UUID

New-MDTComputer -assetTag $computer -macAddress $MAC -SerialNumber $SerialNumber -uuid $UUID -description "$Manufacturer $Model" -settings @{
OSInstall='YES';
OSDComputerName=$computer;
ComputerName=$Computer;
}

} ###End Get-MDTComputer###
} ###End test-connection###
} ###End ForEach Computer###

Read-Host "Press Enter to exit"

Share via

Manipulating the Microsoft Deployment Toolkit database using PowerShell

Comments

Additional resources