Freigeben über

Querying the MDT database using a custom model

  • Artikel

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.

Comments

  • Anonymous
    January 01, 2003
    I agree that web services are a great way to do more complex manipulations, running the code on the back end instead of on the client and then just returning a set of variable values for the client to use. You just need to be a developer (or a developer at heart) to create and implement these. -Michael

  • Anonymous
    January 01, 2003
    It is also very useful to do this same type of lookup using webservices. This way you can pass the model name (such as HP's with the random digits at the end) to the webservice, and then using .NET code parse the model name before returning a result back to your build. This gives you the added benefit of it being central also - if all your build servers hit this webservice, you won't need to update customsettings on each one of your Deployment Pointss every time you add a new model, simply make sure your webservice returns the proper value, and you're all set.

  • Anonymous
    January 01, 2003
    @blynch6038 you can download http://mdtwizardeditor.codeplex.com/ which michael wrote, for just this purpose :) -Dustin

  • Anonymous
    January 01, 2003
    Because we're using a SQL query from the client side, we can't do wildcard matching without reading all of the rows and comparing them.  (It's backwards from the normal SQL "LIKE" processing.) That's doable, but potentially not very efficient - it would be better done on the server side through a stored procedure. It's something that was considered for MDT 2010, but we didn't have time to get it done. -Michael

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    Is it possible during the inital config screen to add a window that will have drop down boxes to select item's that will be written as variables for the deployment?   Basically what I am looking to do is to use the wizardeditor to add a new pane that will have a series of drop down boxes to select things like Grade and Floor that the pc is on so that I can use that to determine what OU to put the client workstation in?   I would also like to make two drop down's for computer name so they can select room number and pc location and those two varibles will be merged to equal the computer name.

  • Anonymous
    January 01, 2003
    Michael, Is there any way to do something similar in MDT 2010. In your script you adjust %model% to the new determined %CustomModel% based on your script.  Now that we have folders in MDT 2010 What I would like to do is use the newly determined %model% in the TS for injecting the drivers from a specific driver folder in MDT.  Somehow use the injectdrivers task to use the %model% folder for injecting the drivers maybe its a bad idea ;-) Thanks Dave

  • Anonymous
    January 01, 2003
    Hi Michael, This works well for us with some HP models, but it'd be really good if we could just use the database and put in "HPDC7600%" as the model name and have the scripts accept the wildcard. I'm sure there's a very good reason why we can't... JQ

  • Anonymous
    February 20, 2015
    Hi Mike, I just wanted to say thank you for all the posts you have created on MDT. Over the years they've been extremely helpful to me.