Writing Data to the MDT Database During Gather
MDT allow you to query the MDT Database during a Gather step using database sections in CustomSetting.ini. This is great for retrieving settings to drive the deployment. However, there are occasions where you may want to write information back to the database. MDT provides no built-in way to do this. Michael Niehaus provided a PowerShell module to manipulate the database on his blog (get it here). However, this is really more for provisioning entries into the database before the deployment. It could be used during the task sequence but is much less convenient than something like a database section in CustomSettings.ini. You could create stored procedures in SQL to do this as well but this also isn’t very convenient and creates a dependency have someone who can write and maintain T-SQL code.
I wanted something that would work like a database section in CustomSettings.ini but allow modifications of the database. So I took the code in ZTIDataAccess.vbs (the MDT script that actually does the work of the database queries for ZTIGather.wsf) and created a new script, MDTDataModify.vbs, that allows you to add sections to CustomSettings.ini that are very similar to the database query section but do database modify operations instead.
To use this script, the account used to access the database (MDT user account or ConfigMgr Network Access Account) must have at least db_datareader and db_datawriter on the MDT database.
Since this script contains a VBScript Class it must be loaded during in CustomSettings.ini using MDTExitInclude.vbs from this previous post.
[Settings]
Priority=IncludeExitScripts, Default
Properties=ExitScripts[IncludeExitScripts]
UserExit=MDTExitInclude.vbs
ExitScripts=#IncludeFiles("MDTDataModify.vbs")#
This script allow 3 different types of table operations: Update, Insert, and Delete. Update allows you to modify an existing record. Insert allows you to add a new record. Delete allows you to delete a record.
The CustomSettings.ini sections that are used with MDTDataModify.vbs are very similar to the standard database query sections supported by ZTIGather.wsf. The following properties are used:
Property |
Description |
|---|---|
SQLServer |
Specifies the name of the computer running SQL Server to use in the operation |
Instance |
Specifies the name of the SQL Server instance on the computer specified in the SQLServer property. If you are using:
|
Database |
Specifies the name of the MDT DB in the SQL Server instance specified in the Instance property. |
Netlib |
Specifies the network library to be used in connecting to the MDT DB specified in the Database property and can be either Named Pipes (DBNDPNTW) or TCP/IP Sockets (DBMSSOCN). |
SQLShare |
Specifies a network shared folder on the computer specified in the SQLServer property, which is used when performing user authentication using Windows Integrated Authentication with Named Pipes protocol. |
Table |
Specifies the table or view to be used in the operation. |
Statement |
Specifies the operation to be performed and can be Update, Insert, or Delete. |
Parameters |
Specifies the selection criteria for finding rows for the operation. Conceptually, this property is the WHERE clause in an SQL SELECT statement. |
ParameterCondition |
Specifies the Boolean operation to be performed when you specify more than one selection criterion in the Parameters property. You can select either Boolean AND (the default) or OR operations. If you want to use a Boolean OR operation, you must specify ParameterCondition=OR. |
WriteParameters |
Specifies properties to write back to the table or view during an Update or Insert operation. |
All the properties except the new Statement and WriteParameters properties work the same way as standard MDT database query section.
These sections also support variable remapping as a described in this post: https://blogs.technet.com/b/deploymentguys/archive/2013/08/12/remapping-variables-in-mdt-database-queries.aspx.
To have a database modify section run in CustomSetting.ini, you DO NOT add it to the Priority line directly. If you do ZTIGather.wsf would treat it as standard database query section. Instead, you need to use the ModifySQL function that is included in MDTDataModify.vbs to run the section. Here are a few examples.
Database Update
In this example, the computer name (OSDComputerName) is generated based on the string “ws” and the first 10 characters of the serial number. Since this name is generated during the deployment it would be nice to have this value written back to the computer record for reference later. The following sample CustomSettings.ini will update an existing computer record:
[Settings]
Priority=Common, IncludeExitScripts, RunDBChanges
Properties=ExitScripts(*), Type, ID, ComputerID, DBChanges(*), MDTSQLServer, MDTDatabase, MDTNetlib, MDTSQLShare[Common]
MDTSQLServer=SQLServer1
MDTDatabase=MDT
MDTNetlib=DBNMPNTW
MDTSQLShare=SQLShare$
OSDComputerName=ws#Right("%SerialNumber%", 10)#[IncludeExitScripts]
; Load all user exit scripts
UserExit=MDTExitInclude.vbs
ExitScripts001=#Include("MDTDataModify.vbs")#[RunDBChanges]
DBChanges001=#ModifySQL("UpdateCSettings")#[UpdateCSettings]
SQLServer=%MDTSQLServer%
Database=%MDTDatabase%
Netlib=%MDTNetlib%
SQLShare=%MDTSQLShare%
Table=ComputerSettings
Parameters=UUID, AssetTag, SerialNumber, MacAddress
ParameterCondition=OR
Statement=Update
WriteParameters=OSDComputerName
Database Insert
In this example I show how you can create the record for a computer by simply running a Gather step will the following sample CustomSetting.ini. This is useful if you are unboxing a new computer and don’t want to depend on manually entering a record in the database by hand (which can be error prone). For example, you could create MDT or ConfigMgr task sequence media with a two step task sequence (Use Toolkit Package and Gather steps only) and boot the new machine with this media. The following sample CustomSettings.ini will insert a new computer record by first creating the ComputerIdentity table entry with the hardware variable values and then creating the associated Settings table entry (Type=C and ID that matches the ID of the ComputerIdentity table entry). The ModifySQL function with return an InsertID value if it creates a record with an identity value. I remap this to ComputerID to use it as an input to the second Insert section, remapping it to the ID field in the table.
In this sample I add the record with OSInstall set to NO so that a deployment cannot happen until the rest of the values needed for the record are populated.
[Settings]
Priority=Common, IncludeExitScripts, RunDBChanges
Properties=ExitScripts(*), Type, ID, ComputerID, DBChanges(*), MDTSQLServer, MDTDatabase, MDTNetlib, MDTSQLShare[Common]
MDTSQLServer=SQLServer1
MDTDatabase=MDT
MDTNetlib=DBNMPNTW
MDTSQLShare=SQLShare$
OSInstall=NO
Type=C[IncludeExitScripts]
; Load all user exit scripts
UserExit=MDTExitInclude.vbs
ExitScripts001=#Include("MDTDataModify.vbs")#[RunDBChanges]
DBChanges001=#ModifySQL("InsertCIdentity")#
DBChanges002=#ModifySQL("InsertCSettings")#[InsertCIdentity]
SQLServer=%MDTSQLServer%
Database=%MDTDatabase%
Netlib=%MDTNetlib%
SQLShare=%MDTSQLShare%
Table=ComputerIdentity
Statement=Insert
WriteParameters=AssetTag, SerialNumber, MacAddress, UUID, Description
InsertID=ComputerID[InsertCSettings]
SQLServer=%MDTSQLServer%
Database=%MDTDatabase%
Netlib=%MDTNetlib%
SQLShare=%MDTSQLShare%
Table=Settings
Statement=Insert
WriteParameters=Type, ComputerID, OSInstall
ComputerID=ID
Database Delete
In the final example I demonstrate how you can delete the computer record. If you want to delete the record at the end of a successful deployment (groom records that are no longer needed) you can add a Gather step at the end of the task sequence that uses this sample CustomSettings.ini:
[Settings]
Priority=Common, IncludeExitScripts, RunDBChanges
Properties=ExitScripts(*), Type, ID, ComputerID, DBChanges(*), MDTSQLServer, MDTDatabase, MDTNetlib, MDTSQLShare[Common]
MDTSQLServer=SQLServer1
MDTDatabase=MDT
MDTNetlib=DBNMPNTW
MDTSQLShare=SQLShare$[IncludeExitScripts]
; Load all user exit scripts
UserExit=MDTExitInclude.vbs
ExitScripts001=#Include("MDTDataModify.vbs")#[RunDBChanges]
DBChanges001=#ModifySQL("DeleteCSettings")#[DeleteCSettings]
SQLServer=%MDTSQLServer%
Database=%MDTDatabase%
Netlib=%MDTNetlib%
SQLShare=%MDTSQLShare%
Table=ComputerIdentity
Parameters=UUID, AssetTag, SerialNumber, MacAddress
ParameterCondition=OR
Statement=Delete
MDTDataModify.vbs is provided below.
Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of included script samples are subject to the terms specified in the Terms of Use .
This post was contributed by Michael Murgolo, a Senior Consultant with Microsoft Services - U.S. East Region.