Month: January 2016

MDT Database – The PowerShell module fix

A long time ago Michael Niehaus wrote this brilliant module for PowerShell to manipulate the MDT database. Works great for bulk import of computers, creating assigning roles and so forth. You can read more and download the module from his blog here http://blogs.technet.com/b/mniehaus/archive/2009/05/15/manipulating-the-microsoft-deployment-toolkit-database-using-powershell.aspx

The reason behind this blogpost is that there is an issue with the module, or with a view in the database used by the module. This gives the effect that when searching for computers you cannot use the description field to find them.

So if we take a look at my database I have two entries both with a description.

Computers

But when I have imported the module and connected to the database and use Get-MDTComputer -Description “LT-PETER” I get an error.

Error

So me and Mikael Nyström (http:///www.deploymentbunny.com) did some digging and found that there is a mismatch between the query and the view being used.

The Fix

There are two ways of fixing this. You can either do it manually or use the script I have included here.

The manual way. Open up SQL Management studio and browse to your database. Open up the view called dbo.ComputerSettings. Choose design and check the box in ci marked Description. Save and you’re done.

Manual

The script way, download the script here and run it using powershell the only thing you need to enter are the name to your sqlserver including instance name and the name of your mdt database.

Script

The script can be run with parameters on one line or just run the script and it will ask for server and database name.

Done!

Now when you run the command it can find the computer!

fixed

Download Script

/Peter

MDT Database and custom values

I recently wrote a post on why I use the MDT database and how to set it up, the link to the post is here https://syscenramblings.wordpress.com/2015/12/23/mdt-database-the-why-and-the-how/. There will almost always be scenarios that are not covered by the built-in features of MDT/SCCM and for this the database is excellent. The fact is that is supported and encouraged to extend the MDT database to cover other scenarios as well.

So this post will cover extending the database and using those custom values in a sequence.

Part 1 – Extending the database

First up is extending the database to have placeholders for whatever value you need. So on your MDT database box launch SQL management studio and open up your database.

Expand the tables view and then the table called dbo.settings. You will then have a “folder” called Columns. If you expand that you can see all the current settings on each object in the database.

Columns

Now just right click columns and select New Column.

You will be presented with a nice list on the right side, note that the last box is empty. Type in the name of the variable you want to create, in this case I will call it WSUS. Next you need to specify the type of value stored in the variable. For simplicity create a nvarchar(50) for this. This will create a stringvalue with a maximum of 50 characters. Don’t forget to save the table.

Column

Well that’s all well and good but if you now look inside the MDT workbench you won’t be able to find the new value. This is because the rest of the databases needs to be refreshed. To do this we use a small SQL script. To run this create a new query and make sure it points to your MDT database.

Then run the following query:

EXECUTE sp_refreshview ‘[dbo].[ComputerSettings]’
EXECUTE sp_refreshview ‘[dbo].[LocationSettings]’
EXECUTE sp_refreshview ‘[dbo].[MakeModelSettings]’
EXECUTE sp_refreshview ‘[dbo].[RoleSettings]’

Note that there are single quote signs around the tables being refreshed. Make sure they are copied correctly from the website.

Run the query and then you can see the value inside the Workbench.

CustomValues

(Yes I have included other variables as well but for now we will focus on the WSUS variable)

Part 2 – Using the values

Now let’s put that variable to good use. The variable can be set either on the computer object or a role or any other way you want to.

Here I have created a role and named it DebugRole and then set the value WSUS to OFF. Next we need to make sure we can use the value so open up Customsettings.ini and have a look at the second row called Properties. If you already have custom properties(variables) its fine and you should already know how this work but if not here is how it works.

The row is a simple comma delimited row. So it will look something like this Properties=MyCustomProperty, WSUS

If you have more options its fine just add them before or after with a comma between each.

Ok when a computer that has the role is not image the value WSUS will be set to NO, well that’s just a value than that in itself will do nothing so now what?

In your customsettings.ini under the [Default] block add a line saying WSUS=ON. Open up your task sequence and scroll down to the steps for Windows update (pre and post application) and go to the Options tab on each of them. First make sure the are enabled by deselecting the default option to “disable this step” then click the add button and add a Task Sequence variable. Set the name to WSUS and the option to Equals ON.

TS

Now it’s done! Your sequence will use a custom database value during deployment.

So how will this work then. Well a computer that has been assigned the role of DebugRole will have the WSUS variable set to OFF and as such it will not run the steps for Windows update.

This will decrease the time it takes to image a computer, making your testing faster. If you have other steps in your sequence, they can of course have the same type of condition on them and you can name the condition something like Debug instead and have a lot of steps that should run when debugging.

As a last note, the above have been done in MDT and the workbench but works the same way for SCCM with MDT integration. Haven’t integrated your SCCM with MDT? Do it now!

/Peter