Insert Computers Automatically into the MDT Database

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?

Johan, which table should I be adding the new record to?

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?

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?

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

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

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.

Not sure why it blanked out that SQLServer=. Our server name is right there.

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.