Querying the MDT database using a custom model
This seems to be a frequent question that comes up:
“I am working with PCs from <vendor> that have model strings that frequently change, although the first part is always consistent. How can I use these models with the MDT database without creating a new entry for each unique string?”
This seems to come up most often with computers from Lenovo, where the first four characters indicate the model and the last three indicate a specific configuration of that model. It’s also seen with various HP computers, although their pattern tends to be a little more difficult.
The “gather” process that MDT uses doesn’t provide a way to do wildcard or “like” queries, but it does provide extensibility to let you define your own property to use instead of “Model” when querying the database. Let me give a “real world” example for the Lenovo case. I can use a CustomSettings.ini like this:
This defines a new property called “CustomModel”. It includes a rule that has a very simple manipulation: it sets the value to the first four characters of the existing “Model” value, which in the case of my T61p laptop results in a value of “6458”.
I then modified the database query to tell it to use “CustomModel” as a parameter instead of “Model”. If that’s all I did, the query would fail because it would create a SQL statement that specified “WHERE CustomModel = ‘6458’” but that’s not valid since there isn’t a CustomModel column in the database. That’s where the next line comes in:
This says that the property “CustomModel” as we know it locally is called “Model” in the database. As a result, the correct query is generated:
About to issue SQL statement: SELECT * FROM MakeModelSettings WHERE MAKE = 'LENOVO' AND Model = '6458'
Successfully queried the database.
That’s all it takes. Now, there would typically be more than just the [MMSettings] section that needs the “CustomModel” updates – you would also want to change [MMPackages], [MMApps], [MMAdmins], and [MMRoles] the same way.
If you need to do a calculation that is more complex than the simple substring that I implemented above, you may need to use a user exit to do the calculation. The end of the exit just needs to set the same “CustomModel” property. The rest of the logic would be the same. So you could use something like this for the exit:
Function UserExit(sType, sWhen, sDetail, bSkip)
If sType = "SECTION" and sWhen = "BEFORE" then
oLogging.CreateEntry "Calculating custom model string.", LogTypeInfo
If UCase(oEnvironment.Item("Make")) = "LENOVO" then
oEnvironment.Item("CustomModel") = Left(oEnvironment.Item("Model"), 4)
ElseIf Instr(oEnvironment.Item("Model"), "(") > 2 then
oEnvironment.Item("CustomModel") = Trim(Left(oEnvironment.Item("Model"), Instr(oEnvironment.Item("Model"), "(") - 2))
oEnvironment.Item("CustomModel") = oEnvironment.Item("Model")
UserExit = Success
Save that as “CustomModelExit.vbs” in the same “Scripts” directory with ZTIGather.wsf, then edit the CustomSettings.ini to specify to run it:
The only change from the previous CustomSettings.ini sample is the [CalculateCustom] section. Now it specifies the run the user exit script. So what exactly does this script do? Well, if it’s a Lenovo machine, it takes the first four characters. If the models string contains a starting parenthesis, “(“, it will chop everything from that point off of the model (e.g. “My Model (Test)” will become “My Model”). In any other case, the script will assign the current model value to the CustomModel property. (That simplifies things somewhat.)
You might need to tweak the script some based on your specific requirements, but the basic setup should work for whatever manipulation you would like to do.