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:

[Settings]
Priority=CalculateCustom, MMSettings
Properties=CustomModel

[CalculateCustom]
CustomModel=#Left("%Model%", 4)#

[MMSettings]
SQLServer=MNiehaus-T61p-7
Instance=SQLExpress
Database=MDTDatabase
Netlib=DBNMPNTW
SQLShare=DeploymentShare$
Table=MakeModelSettings
Parameters=Make, CustomModel
CustomModel=Model

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:

CustomModel=Model

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))
        Else
            oEnvironment.Item("CustomModel") = oEnvironment.Item("Model")
        End if

    End if

    UserExit = Success

End Function

Save that as “CustomModelExit.vbs” in the same “Scripts” directory with ZTIGather.wsf, then edit the CustomSettings.ini to specify to run it:

[Settings]
Priority=CalculateCustom, MMSettings
Properties=CustomModel

[CalculateCustom]
UserExit=CustomModelExit.vbs

[MMSettings]
SQLServer=MNiehaus-T61p-7
Instance=SQLExpress
Database=MDTDatabase
Netlib=DBNMPNTW
SQLShare=DeploymentShare$
Table=MakeModelSettings
Parameters=Make, CustomModel
CustomModel=Model

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.