Blog - Insert Computers Automatically into the MDT Database

Location: Blogs Johan Arwidmark
Posted by: johan	9/17/2010
MDT 2010 Lite Touch by default doesn't automatically add the information you specify in the deployment wizard, here is a stored procedure and rules file that will do just that... This sample will inject the computername you specify in the deployment wizard to the OSDComputerName and Description field in the database, but it can easily be extended to add more information. Step-by-step instructions Download the sample files and copy InsertDB.ini to your Deployment Share \ Control folder. Using SQL Management Studio, modify the first line in InsertComputerName.sql script to match your database name, and then execute the InsertComputerName.sql script to create the InsertComputerName stored procedure .Assign execute permissions on the InsertComputerName stored procedure to the user you use to run the Lite Touch installation. The user also needs read/write permissions to the database. Setting the permissions on the InsertComputerName stored procedure Modify your rules to ask for the computername (SkipComputerName=NO) In the Task Sequencer, add an extra Gather action, name it Insert Computer Name, and configure it for full processing with the InsertDB.ini rules file Configuring the Task Sequence Note: To see the newly added computer entry after the Insert Computer Name actions has executed, you need to close and open the deployment workbench. The Deployment Workbench is not very good understanding that you added info to it outside the UI :) / Johan

Permalink \| Trackback

Comments (16)

Re: Insert Computers Automatically into the MDT Database	By MrDeployment on 9/27/2010
I have been trying to do something like this using vbscript. The only problem I have is that after creating the record, the linked View ComputerSettings isn't updated. Is there a way to refresh the view using VB? The MDTWebfrontend somehow updates the view once you select the computer properties, I'm just not sure how it does it. Any ideas?

Re: Insert Computers Automatically into the MDT Database	By johan on 9/27/2010
You need to edit the view, and add the Description field. running the sp_refreshview is not enough to add the description field. / Johan

Re: Insert Computers Automatically into the MDT Database	By MrDeployment on 9/28/2010
Johan, which table should I be adding the new record to?

Re: Insert Computers Automatically into the MDT Database	By johan on 9/28/2010
No table, the computersettings view. And why do you create a vbscript for functions that MDT has native to it? / Johan

Re: Insert Computers Automatically into the MDT Database	By MrDeployment on 9/29/2010
The intention is to run the script on existing Windows XP machines to automatically populate the MDT database. It will also continue to run to maintain current information on existing Windows 7 machines. The MDT databse is being used for asset tracking as well as deplyment settings. Do you think there is a better way to achieve this?

Re: Insert Computers Automatically into the MDT Database	By johan on 9/29/2010
Well, MDT knows quite much about the machine (check the ZTIGather.log) and that information can easily be imported into the database. That being said, a vbscript which gather additional information is just fine as well. / Johan

Re: Insert Computers Automatically into the MDT Database	By TWillson on 10/20/2010
Thanks Johan, this is an excellent addition. I'm not very proficient with SQL at all, so how would I add the Serial Number as a listed item?

Re: Insert Computers Automatically into the MDT Database	By johan on 10/20/2010
You need to modify the InsertComputerName.sql script to take one more parameter, and also update the rules to send the SerialNumber parameter. / Johan

Re: Insert Computers Automatically into the MDT Database

By Jeroendv on 8/10/2011

Hi,

I was trying to add functionality to the Stored Procedure that I could store and retreive productkeys. I work for a small company that uses OEM keys and it would be very nice to store the installation keys in the MDT database. After a lot of puzzling I've succeded and devided it in two parts.

The first part adds a computer account if it doesn't exist in the MDT DB.

I've added the table MachineNameSequence with the Prefix and Sequence columns. I've added an entry with Prefix PC and number 75 (We are currently at number 76).
Then I Added the stored procedure and changed it (thanks also to Rune Bakken and Ulrik Skadhauge Jensen). I'll get back on this a little later.
I added the following in the CustomSettings.ini:

[Settings]
Priority=CreateComputer

[CreateComputer]
SQLServer=MDT01
Instance=SQLEXPRESS
Database=MDT
Netlib=DBNMPNTW
SQLShare=MDTLogs
StoredProcedure=CreateComputer
Parameters=MacAddress, SerialNumber, IsLaptop, IsDesktop

Now back to the Stored Procedure.

I've adjusted the stored Procedure into this:

USE [MDT]
GO
/****** Object: StoredProcedure [dbo].[CreateComputer] Script Date: 08/10/2011 16:01:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
– Description: Create New Computer Account during deployment
– ======================
*/

CREATE PROCEDURE [dbo].[CreateComputer]
@MacAddress VARCHAR(50),
@SerialNumber as nvarchar(255),
@IsLaptop nvarchar(50),
@IsDesktop nvarchar(50)

AS

DECLARE @Cnt INT,
@baseOU VARCHAR(255),
@MachineType VARCHAR(50),
@MachineOU VARCHAR(255),
@Prefix VARCHAR(50),
@Sequence INT,
@NewName VARCHAR(50)

SET NOCOUNT ON

/* See if there is an existing record for this machine */

SELECT @Cnt=COUNT(*) FROM dbo.ComputerIdentity
WHERE MacAddress = @MacAddress

/* Check type of computer and determine OU */

SET @baseOU = 'OU=Workstations,OU=Computers,DC=Company,DC=lan'

IF @ISLaptop = 'True'
Begin
SET @MachineOU = 'OU=Laptops,'+ @baseOU
SET @MachineType = 'Laptop'
End

IF @ISDesktop = 'True'
Begin
SET @MachineOU = 'OU=Desktops,'+ @baseOU
SET @MachineType = 'Desktop'
End

/* No record? Add one. */

IF @Cnt = 0
BEGIN

/* Create a new machine name */

BEGIN TRAN

IF @MachineType = 'Laptop'

Begin
SELECT @Prefix=Prefix, @Sequence=Sequence FROM MachineNameSequence WHERE Prefix = 'LAP'
SET @Sequence = @Sequence + 1
UPDATE MachineNameSequence SET Sequence = @Sequence WHERE Prefix = 'LAP'
End

ELSE
Begin

SELECT @Prefix=Prefix, @Sequence=Sequence FROM MachineNameSequence WHERE Prefix = 'PC'
SET @Sequence = @Sequence + 1
UPDATE MachineNameSequence SET Sequence = @Sequence WHERE Prefix = 'PC'
End

SET @NewName = @Prefix + Right('0'+LTrim(Str(@Sequence)),3)

/* Insert the new record */

INSERT INTO ComputerIdentity (MacAddress, Description, SerialNumber)
VALUES (@MacAddress, @NewName, @SerialNumber)
INSERT INTO Settings (Type, ID, ComputerName, OSDComputerName, MachineObjectOU)
VALUES ('C',@@IDENTITY, @NewName, @NewName, @MachineOU)

COMMIT TRAN

END

/* Return the record as the result set */

SELECT dbo.ComputerIdentity.*, dbo.settings.*
FROM dbo.ComputerIdentity INNER JOIN
dbo.Settings ON dbo.ComputerIdentity.ID = dbo.Settings.ID
WHERE (dbo.ComputerIdentity.MacAddress = @MacAddress)

Then I created an ini file, UpdateDB.ini

[Settings]
Priority=UpdateComputerInformation

[UpdateComputerInformation]
SQLServer=MDT01
Instance=SQLEXPRESS
Database=MDT
Netlib=DBNMPNTW
SQLShare=MDTLogs
StoredProcedure=UpdateComputerInformation
Parameters=MacAddress, SerialNumber, Computername, OSDComputername, ProductKey, TaskSequenceID

Then another Stored Procedure:

USE [MDT]
GO
/****** Object: StoredProcedure [dbo].[UpdateComputerInformation] Script Date: 08/10/2011 16:37:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
– Description: Update Description, Serialnumber, ProductKey, ComputerName information in the MDT database during deployment
– ======================
*/

ALTER PROCEDURE [dbo].[UpdateComputerInformation]
@MacAddress nvarchar(50),
@SerialNumber nvarchar(255),
@ComputerName nvarchar(255),
@OSDComputerName nvarchar(255),
@ProductKey nvarchar(255),
@TaskSequenceID nvarchar(255)

AS

SET NOCOUNT ON;

DECLARE @COMPUTERID AS INT, /*— The ID of the computer within MDT */
@ExistingProductKey VARCHAR(50)

IF EXISTS (SELECT 1 FROM dbo.ComputerIdentity WHERE (SerialNumber = @SerialNumber AND @SerialNumber <> '') OR (MacAddress = @MacAddress AND @MacAddress <> ''))

BEGIN /*— The Computer already exists, lets update the information*/

SET @COMPUTERID = (SELECT ID From dbo.ComputerIdentity Where (SerialNumber = @SerialNumber AND @SerialNumber <> '') OR (MacAddress = @MacAddress AND @MacAddress <> ''))

/*–Update the information*/

UPDATE dbo.ComputerIdentity
SET SerialNumber = @SerialNumber,
MacAddress = @MacAddress,
Description = @ComputerName
WHERE ID = @COMPUTERID

UPDATE dbo.Settings
SET ComputerName = @ComputerName,
OSDComputerName = @OSDComputerName,
ProductKey = @ProductKey,
TaskSequenceID = @TaskSequenceID
WHERE Type = 'C' AND ID = @COMPUTERID

/* Update ProductKey only if different or entered key is NOT NULL */
SET @ExistingProductKey = (SELECT ProductKey FROM dbo.Settings WHERE ID = @COMPUTERID)

IF @ProductKey <> @ExistingProductKey AND @ProductKey IS NOT NULL
Begin
UPDATE Settings
SET ProductKey=@ProductKey WHERE Type= 'C' AND ID = @COMPUTERID
End
END

/* Return the record as the result set */

SELECT dbo.ComputerIdentity.*, dbo.settings.*
FROM dbo.ComputerIdentity INNER JOIN
dbo.Settings ON dbo.ComputerIdentity.ID = dbo.Settings.ID
WHERE (dbo.ComputerIdentity.MacAddress = @MacAddress)

Now, in the tasksequence I've created another Gather step under the Validate Processtep that updates the information entered in the wizard.

This allows you to change the PC name if you need and to update and gather information regarding the PC.

Hope this helps other people!

Regards,

Jeroen

Re: Insert Computers Automatically into the MDT Database	By johan on 8/12/2011
Hi Jeroen, Wow! and thanks for contributing... / Johan

Re: Insert Computers Automatically into the MDT Database

By kbenedict on 1/18/2012

I have a version of this that will automatically insert Make and Model into the database. However, now that I'm using SQL Server Express 2008 R2 (instead of non-R2), it does not work. I have everything set up correctly. Any thoughts? Here's the SQL procedure...

USE [MDT]
GO
/****** Object: StoredProcedure [dbo].[InsertMakeModel] Script Date: 01/18/2012 13:23:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertMakeModel]
@Make VARCHAR(255),
@Model VARCHAR(255)

AS

DECLARE @Cnt INT,
@Prefix VARCHAR(50),
@Sequence INT,
@NewName VARCHAR(50)

SET NOCOUNT ON

/* See if there is an existing record for this machine */

SELECT @Cnt=COUNT(*) FROM MakeModelIdentity
WHERE Make = @Make AND Model = @Model

/* No record? Add one. */

IF @Cnt = 0
BEGIN

/* Create a new machine name */

BEGIN TRAN

/* Insert the new record */

INSERT INTO MakeModelIdentity (Make, Model)
VALUES (@Make, @Model)

COMMIT TRAN

END

/* Return the record as the result set */

SELECT * FROM MakeModelIdentity
WHERE Make = @Make AND Model = @Model

Re: Insert Computers Automatically into the MDT Database	By kbenedict on 1/18/2012
Also, here's the content of the INI file I'm using in the TS. [Settings] Priority=InsertMakeModel [InsertMakeModel] SQLServer= Instance=SQLEXPRESS Database=MDT Netlib=DBNMPNTW SQLShare=Logs$ StoredProcedure=InsertMakeModel Parameters=Make, Model I have tried this with the firewall OFF, so I know it's not a firewall issue.

Re: Insert Computers Automatically into the MDT Database	By kbenedict on 1/18/2012
Not sure why it blanked out that SQLServer=. Our server name is right there.

Re: Insert Computers Automatically into the MDT Database	By johan on 1/21/2012
SQL Server should have the SQLEXPRESS server hostname, don't forget to start the SQL Browser services (since SQLEXPRESS is a non-default instance). You also need to open up 1434 UDP in the firewall for the SQL Browser service (not needed for a full SQL running default instance). / Johan

Re: Insert Computers Automatically into the MDT Database	By kbenedict on 1/23/2012
Yes, the SQLServer is populated with the server name. Not sure why it posted it as blank on this comment thread. Also, all the things you say were done. As I said, the firewall was even OFF during some of the tests.

Re: Insert Computers Automatically into the MDT Database	By johan on 1/23/2012
If you can read but not write it's most likely database permissions. Use the below technique to troubleshoot from a domin-joined machine, logged in as the account you normally drive your deployment with. Save time (and avoid pain) - Create a MDT test environment http://www.deploymentresearch.com/Blog/tabid/62/EntryId/22/Save-time-and-avoid-pain-Create-a-MDT-test-environment.aspx / Johan