Remapping Variables in MDT Database Queries
There are occasions where the variables I needed to use to query or retrieve data from the database were not the ones that match the field names in the database. Luckily, the MDT Gather process supports variable remapping in the database sections of CustomSetting.ini to do just that.
I demonstrated one type of variable remapping in my post on Model Aliases. I wanted to query Model settings, apps, etc. using the ModelAlias instead of the Model. Since Model is the field in the database that the queries use to find the records, we have to tell Gather that is should use the value found in the ModelAlias variable to query the Model field in the database instead.
The standard Make/Model Settings database section looks like the one below. The table (view) in the database is MakeModelSettings and the fields used to find the records are Make and Model.
[MMSettings]
SQLServer=%MDTSQLServer%
Database=%MDTDatabase%
Netlib=%MDTNetlib%
SQLShare=%MDTSQLShare%
Table=MakeModelSettings
Parameters=Make, Model
For my ModelAlias Settings sections I only was interested in finding records with a matching Model field, but the value I wanted to match was actually stored in the ModelAlias variable. The changes shown in red tell Gather to do exactly that:
[MASettings]
SQLServer=%MDTSQLServer%
Database=%MDTDatabase%
Netlib=%MDTNetlib%
SQLShare=%MDTSQLShare%
Table=MakeModelSettings
Parameters=ModelAlias
ModelAlias=Model
This tells Gather to use ModelAlias for the value of the input parameter and that this parameter is to match the Model field.
I recently needed to do the second type of variable remapping, placing the results from a field in a returned records into a variable that is different from the name of the field. In my scenario, I needed to place the results of the different application queries into different list variables. The default database sections for getting applications (CApps, LApps, MMApps, RApps) will fill the results into the Applications list variable, which matches the name of the field in the returned results. I needed each of the queries to return the results into different variables so that I could later append them back together in a certain order with results from some other steps in the task sequence in the mix. For example, say I wanted the store the results of MMApps into variable called ModelApps instead of applications.
The standard MMApps sections looks like this:
[MMApps]
SQLServer=%MDTSQLServer%
Database=%MDTDatabase%
Netlib=%MDTNetlib%
SQLShare=%MDTSQLShare%
Table=MakeModelApplications
Parameters=Make, Model
Order=Sequence
To return the results into ModelApps, you first need to define the custom variable in the Properties line and then add the remapping line to the MMApps section (shown in red). (In case you haven’t see this, adding (*) at the end of a variable name in the Properties line tells Gather that the custom variable is a list variable.)
[Settings]
Priority=MMApps, Default
Properties=ModelApps(*)[MMApps]
SQLServer=%MDTSQLServer%
Database=%MDTDatabase%
Netlib=%MDTNetlib%
SQLShare=%MDTSQLShare%
Table=MakeModelApplications
Parameters=Make, Model
Order=Sequence
ModelApps=Applications
One unfortunate quirk of this type of remapping is that the original variable (Applications) will also be filled with the results. So you have to be aware of this if you had hoped that Applications would remain empty so it could be used else ware, as I had . I was going to use Applications as the final appended list. I had to use another custom list variable instead.
If necessary, you can also use both types of remapping in database sections at the same time.
Update 2013-08-20: I forgot to mention that this same remapping of input parameters and output variables can also be done in CustomSetting.ini Web Service query sections.
Update 2013-09-10: I recently discovered that when remapping input parameters, if the parameter is a list item then the remapped value will be added to the original list item. For example, recently I wanted to query a list of applications from a specific role name and map it to a specific output list item like this:
[Settings]
Priority=Common, RAppsCore
Properties=RoleCore, CoreApps(*)[Common]
RoleCore=Core Applications
MDTSQLServer=SQLServer001
MDTDatabase=MDT
MDTNetlib=DBNMPNTW
MDTSQLShare=SQLShare$[RAppsCore]
SQLServer=%MDTSQLServer%
Database=%MDTDatabase%
Netlib=%MDTNetlib%
SQLShare=%MDTSQLShare%
Table=RoleApplications
Parameters=RoleCore
Order=Sequence
RoleCore=Role
CoreApps=Applications
In this example, RoleCore is being input for the parameter Role. Since Role is a list item, the value stored in RoleCore, “Core Applications” in this example, will be added as Role001. This is another thing to keep in mind when remapping variables.
I’ll describe ways to clear out the list items that are getting filled unintentionally by remapping in another post.
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.