I recently had to add a few custom properties to the computer settings in the MDT database and discovered the custom properties weren’t being read using PowerShell or during a deployment simulation. A quick look into the MDT database on the SQL server revealed this was due to the database Views not being updated with the new settings, despite running the recommended stored procedures using sp_refreshview.
In this post we’ll take a look at how to extend the database with your custom properties, the issue I experienced and the resolution.
A Quick “How To” on Adding Your Own Custom Properties
Launch SQL Server Management Studio and connect to your SQL server, expand the MDT database > Tables > File Tables > dbo.Settings > right-click on Columns and select New Column. Add your properties in the Column field like so:
Once you’ve added your custom properties, it is recommended to run the following queries against the MDT database:
EXECUTE sp_refreshview ‘[dbo].[ComputerSettings]’
EXECUTE sp_refreshview ‘[dbo].[LocationSettings]’
EXECUTE sp_refreshview ‘[dbo].[MakeModelSettings]’
EXECUTE sp_refreshview ‘[dbo].[RoleSettings]’
If you then launch the Deployment Workbench you will find the new properties in the Details tab for any given Computer or Role.
The Problem
For testing purposes I configured a couple of the new properties as shown below:
However, when I tried to retrieve the properties using PowerShell the properties were missing:
Digging into the database on the SQL server revealed the “ComputerSettings” and “RoleSettings” Views haven’t been updated despite running the sp_refreshview stored procedures. The properties were missing from the Views entirely as shown below:
The Resolution
A “View” is simply a collection of properties/columns from multiple tables in a database. The cmdlets in the MDTDB PowerShell module such as Get-MDTComputer and also the Gather step in MDT runs a query against these Views to retrieve computer properties. An incomplete View = broken OSD :).
Fortunately fixing the offending Views are quite simple.
In the SQL Server Management Studio and expand the MDT database > Views > right-click on “dbo.ComputerSettings” and choose “Script VIEW as” > Alter To > New Query Editor Window.
In the resulting SQL query simply add your custom properties at the end of the SELECT statement as shown below:
Once this is done, you can repeat this process for the views “RoleSettings”, “LocationSettings” and “MakeModelSettings”. In my case only the views “ComputerSettings” and “RoleSettings” needed updating.
Once complete right-click on your MDT database and click Refresh.
Now when I run Get-MDTComputer the properties are being returned just fine:
