Wednesday, January 9, 2013

Manipulating the Microsoft Deployment Toolkit (MDT) database using PowerShell

I am helping at the school of our children to run the Windows server and workstations. For this and also in my lab I setup MDT to deploy new physical or virtual machines.
At school we buy used/re-furbished desktop or laptop computers a lot and all of them come with a unique Windows license. And we just cannot afford to buy a additional Enterprise license for each.
So MDT helps us also to keep track of all Windows product keys and we put them one time in a spreadsheet (we get some help from students here). So end of 2012 we got 35 new machines and I thought it would be nice to import the spreadsheet into the MDT database and assign the role to install Windows 7 32bit to them as well.

So I found this post http://blogs.technet.com/b/mniehaus/archive/2009/05/15/manipulating-the-microsoft-deployment-toolkit-database-using-powershell.aspx from Michael Niehaus and I downloaded the PowerShell command lets he provided as well.

Our spreadsheet has the format:
- 1. line includes the data headers: computername, Windows product code, machine's serial number
- the other lines have the data
The spreadsheet is delimited by tabs.

And her is the PowerShell I created:

##########


# Import the CSV file into a PowerShell array
$collection = @(Import-CSV .\HPlabcomputers20121231.csv -Delimiter "`t")

# Connect to the SQL database
connect-mdtdatabase -sqlserver . -database MDTDB

# Create for each computer an entry in the MDT database
foreach($computer in $collection)
{
$PCNAME=$computer.ComputerName
$SerialNum=$computer.SerialNum
$ProdKey=$computer.WinProductKey
write-host Importing $PCNAME
new-mdtcomputer -assetTag $computer.ComputerName -serialNumber $SerialNum -settings @{OSInstall='YES'; OSDComputerName=$PCNAME; ProductKey=$ProdKey}
}

# Assign for each computer the role to install Win7 Pro 32 bit
foreach($computer in $collection)
{
$PCNAME=$computer.ComputerName
write-host Updating $PCNAME
# Search for the computername and set the computer role
get-mdtcomputer -assetTag $PCNAME | Set-MDTComputerRole -roles @('Win7_lab_computers')
}


##########

So obviously the user account you use must have SQL permissions on the MDT database to update the database.
Also note that the MDT MMC (aka Deployment Workbench) does not show the new computers if you just click refresh on the Computers view under MDT Deployment Share/Advanced Configuration/Database. Click instead on Database and hit refresh, and go back to the Computers view.