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:

  1. Connect-MDTDatabase.  Establish a connection to an MDT database.
  2. New-MDTComputer.  Create a new computer entry.
  3. Get-MDTComputer.  Get an existing computer entry, or a list of all computer entries.
  4. Set-MDTComputer.  Modify the settings of an existing computer entry.
  5. Remove-MDTComputer.  Remove an existing computer entry.
  6. Get-MDTComputerApplication.  Get the applications for an existing computer entry.
  7. Clear-MDTComputerApplication.  Remove all applications from an existing computer entry.
  8. Set-MDTComputerApplication.  Modify the list of applications for an existing computer entry.
  9. Get-MDTComputerPackage.  Get the ConfigMgr packages for an existing computer entry.
  10. Clear-MDTComputerPackage.  Remove all packages from an existing computer entry.
  11. Set-MDTComputerPackage.  Modify the list of packages for an existing computer entry.
  12. Get-MDTComputerRole.  Get the list of roles for an existing computer entry.
  13. Clear-MDTComputerRole.  Remove all roles from an existing computer entry.
  14. Set-MDTComputerRole.  Modify the list of roles for an existing computer entry.
  15. Get-MDTComputerAdministrator.  Get the list of administrators for an existing computer entry.
  16. Clear-MDTComputerAdministrator.  Remove all administrators from an existing computer entry.
  17. Set-MDTComputerAdministrator.  Modify the list of administrators for an existing computer entry.
  18. New-MDTRole.  Create a new role.
  19. Get-MDTRole.  Get an existing role, or a list of all roles.
  20. Set-MDTRole.  Modify the settings of an existing role.
  21. Remove-MDTRole.  Remove an existing role.
  22. Get-MDTRoleApplication.  Get the applications for an existing role.
  23. Clear-MDTRoleApplication.  Remove all applications from an existing role.
  24. Set-MDTRoleApplication.  Modify the list of application for an existing role.
  25. Get-MDTRolePackage.  Get the ConfigMgr packages for an existing role.
  26. Clear-MDTRolePackage.  Remove all packages from an existing role.
  27. Set-MDTRolePackage.  Modify the list of packages for an existing role.
  28. Get-MDTRoleRole.  Get the list of roles for an existing role.
  29. Clear-MDTRoleRole.  Remove all roles from an existing role.
  30. Set-MDTRoleRole.  Modify the list of roles for an existing role.
  31. Get-MDTRoleAdministrator.  Get the list of administrators for an existing role.
  32. Clear-MDTRoleAdministrator.  Remove all administrators from an existing role.
  33. Set-MDTRoleAdministrator.  Modify the list of administrators for an existing role.
  34. New-MDTLocation.  Create a new location.
  35. Get-MDTLocation.  Get an existing location, or a list of locations.
  36. Set-MDTLocation.  Modify the settings of an existing location.
  37. Remove-MDTLocation.  Remove an existing location.
  38. Get-MDTLocationApplication.  Get the applications for an existing location.
  39. Clear-MDTLocationApplication.  Remove all applications from an existing location.
  40. Set-MDTLocationApplication.  Modify the list of applications for an existing location.
  41. Get-MDTLocationPackage.  Get the ConfigMgr packages for an existing location.
  42. Clear-MDTLocationPackage.  Remove all packages from an existing location.
  43. Set-MDTLocationPackage.  Modify the list of packages for an existing location.
  44. Get-MDTLocationRole.  Get the roles for an existing location.
  45. Clear-MDTLocationRole.  Remove all roles from an existing location.
  46. Set-MDTLocationRole.  Modify the list of roles for an existing location.
  47. Get-MDTLocationAdministrator.  Get the administrators for an existing location.
  48. Clear-MDTLocationAdministrator.  Remove all administrators from an existing location.
  49. Set-MDTLocationAdministrator. 
  50. New-MDTMakeModel
  51. Get-MDTMakeModel
  52. Set-MDTMakeModel
  53. Remove-MDTMakeModel
  54. Get-MDTMakeModelApplication
  55. Clear-MDTMakeModelApplication
  56. Set-MDTMakeModelApplication
  57. Get-MDTMakeModelPackage
  58. Clear-MDTMakeModelPackage
  59. Set-MDTMakeModelPackage
  60. Get-MDTMakeModelRole
  61. Clear-MDTMakeModelRole
  62. Set-MDTMakeModelRole
  63. Get-MDTMakeModelAdministrator
  64. Clear-MDTMakeModelAdministrator
  65. Set-MDTMakeModelAdministrator
  66. New-MDTPackageMapping
  67. Get-MDTPackageMapping
  68. Set-MDTPackageMapping
  69. 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’

MDTDB.zip


Comments (24)
  1. Anonymous says:

    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

    ?

  2. Anonymous says:

    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.

  3. Anonymous says:

    Great blog entry Mike!

    Thanks for this useful contribution.

  4. Anonymous says:

    14. 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?

  5. Anonymous says:

    Wow this is going to save a lot of time for setting up redundant systems. Thanks Mike!

  6. Karthik Ramamoorthy says:

    Is there any way to delete a record in MDT Database from WinPE during Pre install ?

  7. Anonymous says:

    On the Connect-MDTDatabase comment, you can do this by specifying the logical drive path.  For example:

    Connect-MDTDatabase -drivepath DS001:

  8. kev.schu says:

    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.

  9. Siegbert says:

    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

  10. jem says:

    Is there any way to add Description to the database? It's more friendly then MAC address.

  11. MTM says:

    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

  12. MTM says:

    Sorry, I forgot to mention that for thousands of PCs, we use Description=OSDComputerName just so that I could support querying by name…

  13. LuCas says:

    Hello, and thanks for this module

    I just want ton know if it is possible to get just the ID of a location whith known name

    something like

    $ID = Get-MDTLocation.ID -name xxxx

  14. Steve Hoos says:

    Does this work with MDT 2012 Update 1?  If it does not is there a more up to date version of these scripts?

  15. Jonathan Engstrom says:

    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.

  16. Anonymous says:

    Pingback from OS Deployment – OOB install of Windows Server 2012 R2 Using MDT,WDS and PowerShell « The Deployment Bunny

  17. Anonymous says:

    Pingback from OS Deployment – OOB install of Windows Server 2012 R2 Using MDT,WDS and PowerShell « The Deployment Bunny

  18. Anonymous says:

    MDT allow you to query the MDT Database during a Gather step using database sections in CustomSetting

  19. MSExpert says:

    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

  20. showbox says:

    Thanks for the great info. I really loved this. I would like to apprentice at the same time as you amend your web site, how could i subscribe for a blog site?
    For more info on showbox please refer below sites:
    http://showboxandroids.com/showbox-apk/
    http://showboxappandroid.com/
    Latest version of Showbox App download for all android smart phones and tablets.
    http://movieboxappdownloads.com/ – It’s just 2 MB file you can easily get it on your android device without much trouble. Showbox app was well designed application for android to watch movies and TV shows, Cartoons and many more such things on your smartphone.
    For showbox on iOS (iPhone/iPad), please read below articles:
    http://showboxappk.com/showbox-for-ipad-download/
    http://showboxappk.com/showbox-for-iphone/
    Showbox for PC articles:
    http://showboxandroids.com/showbox-for-pc/
    http://showboxappandroid.com/showbox-for-pc-download/
    http://showboxforpcs.com/
    There are countless for PC clients as it is essentially easy to understand, simple to introduce, gives continuous administration, effectively reasonable. it is accessible at completely free of expense i.e., there will be no establishment charges and after establishment
    it doesn’t charge cash for watching films and recordings. Not simply watching, it likewise offers alternative to download recordings and motion pictures. The accompanying are the strides that are to be taken after to introduce Showbox application on Android.
    The above all else thing to be done is, go to the Security Settings on your Android telephone, Scroll down and tap on ‘Obscure sources’.

  21. Brad S Palmer says:

    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"

Comments are closed.