none
MDT 2013 Automatically naming computers from MDT DB Location Details variables and sequence numbers. RRS feed

  • Question

  • Hi All, 

    I am having some serious issues with trying to get the auto naming working. I am trying to pre-populate the computer name during a task sequence however the computer name will be built utilising variables added to the mdt database.


    Naming Convention

    The current naming convention for client devices is WXXYYYZZZZ as the client is in multiple countries and offices.  This is as below

    W = Hardware type - S = Server , V = VM , L = Laptop , D= Desktop etc

    XX = Country code - AU = Australia , US = United states etc

    YYY = Site Office - Syd = Sydney , AUC = Auckland etc

    ZZZZ  = numbering of device - 0001, 0020 etc

    So a fully fomulated name will be something like LAUSYD009


    MDT Database modifications

    I have extended the MDT DB schema to include custom variables that can be entered in the Details tab. These variables are as below

    LocationVarName - Updated in the locations part of the db and populated with the county code variables explained above in the naming convention

    MakeAndModelVarName - Updated in the make and models part of the db and populated with the different hardware types expected

    This was discovered and tweaked using the following link.

    http://blogs.technet.com/b/deploymentguys/archive/2011/08/05/dynamic-computer-naming-in-zti-deployments-using-mdt-and-configmgr.aspx

    By tweaking things in the above link i can effectively get the ZTIGather.log to create the OSD computername correctly according to its Make and model and Location Variables , i.e. LAUSYD  or DNZAUC. I can also run a build of a machine and can create unique computer names by also adding the  the Serialnumber variable. Personally that is the way id prefer to name the new devices  however the business is adamant that it wants to stick with the sequence numbering it is currently using (3k computers already out there in the environment with that naming convention). So based on above i believe i have the prefix part of the computer name down packed.


    The second part is the sequencing of the devices. I had a look at another link as below which advises how to auto name a computer with a defined prefix and a sequence number. This link will check the macaddress of the computer and if the name matches the mac address it will re-issue the same name. If there is no link to the mac address it will generate a new name with a incremented sequence number.

    http://www.deploymentresearch.com/Research/tabid/62/EntryId/103/Generate-computer-names-in-MDT-2012-2013-based-on-prefix-and-a-sequence-number.aspx

    I have tweaked the above to use the serialnumber instead of the mac address as this is imported into AD currently. Again i have got this working and can effectively name comptuers PC-0001 etc and match against the MDT database for known computers. As Prepwork i have run a couple powershell scripts and audited the AD and imported all known computers and associated serial numbers into the MDT DB already. So my MDT DB knows all devices has currently a range of 0001 to 0159 populated under the computer names tab in the DB.


    What i am trying to do is get these two solutions working in conjunction.  I am trying to get the initial part of the name (LAUSYD) created from the first step and then assign a sequence number to it and check the mdt db for a connection to serial number.  This is where i am stuck. Can anyone advise how i can combine these two solutions or if there is a better way to resolve this dilemma i am currently facing.

    Thanks in advance for any advice






    Tuesday, July 15, 2014 5:53 AM

Answers

  • To have the stored procedure take additional parameters you add them like this:

    ALTER PROCEDURE [dbo].[InsertComputerNamev1]
    @SerialNumber CHAR(17),
    @InitialBuildPrefix CHAR(17)

    You also need to modify the cs.ini so send the parameter...

    [InsertComputerName]
    SQLServer=CM01
    Database=MDT
    Netlib=DBNMPNTW
    SQLShare=Logs$
    StoredProcedure=InsertComputerNamev1
    Parameters=SerialNumber, InitialBuildPrefix

    / Johan


    Regards / Johan Arwidmark Twitter: @jarwidmark Blog: http://www.deploymentresearch.com FB: www.facebook.com/deploymentresearch


    Friday, July 25, 2014 8:33 AM
    Moderator

All replies

  • Skip using the prefix field in the datanase, and send the first part of the name as a parameter to the stored procedure, and have it add it before the sequence number, as a prefix. That way the complete name is also injected into the database, as well as being set on the machine.

    / Johan


    Regards / Johan Arwidmark Twitter: @jarwidmark Blog: http://www.deploymentresearch.com FB: www.facebook.com/deploymentresearch

    Thursday, July 17, 2014 2:01 AM
    Moderator
  • Hi Johan,

    I have modified the first script to create the prefix as required and it gets stored in the customsettings.ini as the initialbuildprefix variable.

    [BuildComputerName]
    UserExit=MachineNameExit.vbs

    InitialBuildPrefix=#BuildComputerName()#
    OSDComputername=namefromstoredprocedure


    There is 2 things i am unsure about.


    1) How to code the stored procedure to use the variable from the initial vb script instead of the prefix stored in a DB.  I am not really sure


    2) The second thing i am not sure about is this. From what i can understand the stored procedure checks the serial number of the machine being deployed to see if it already exists in the DB and then goes to create the name.  Once it has created a name with the prefix and the sequence number, does it check if that new name exists in the DB?

    I see this as another hurdle i have to overcome. Is there any way to put some smarts in this to ensure that after it has checked the serialnumber to confirm the serial number doesn't exist in the db , to then check when it creates a new name using the prefix and sequence number that that new name also doesn't exist (And if it does increment the sequence number and check again until it finds one that doesnt exist)

    I have tried to allow for this in the below modified stored procedure however i am not sure if my coding is correct.  Can someone please review the below stored procedure and let me know if it will do what i am hoping or how to modify the syntax.  SQL queries are not one of my strong points.

    USE [MDT]
    GO
    /****** Object:  StoredProcedure [dbo].[InsertComputerNamev1]    Script Date: 18/07/2014 1:28:59 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[InsertComputerNamev1]
    @SerialNumber CHAR(17)
    
    AS
    
    DECLARE @Cnt INT,
            @Cnt1 INT,              'not sure if i need to declare this for the looping or if i can use the @Cnt'
            @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 ComputerIdentity
    WHERE SerialNumber = @SerialNumber
    
    /* No record?  Add one.  */
    
    IF @Cnt = 0
    BEGIN
    
        /* Create a new machine name */
    
    	BEGIN TRAN
    
            SET @Prefix = #BuildComputerName()#  'to be captured from previous vbscript to create prefix not sure on the syntax for this'
            SET @Sequence = 0001				 'instead of using the sequence table i want to always start on 0001'
    	SET @NewName = @Prefix + Right('0000'+LTrim(Str(@Sequence)),4) 'Will always set the first computername to 0001'
    	
     
     	/* Loop until it finds a available name */
    
    	SELECT @Cnt1=COUNT(*) FROM ComputerIdentity   'Is this correct  by using @Cnt1 or should it still be @cnt'
    	WHERE AssetTag = @NewName		 'Trying to match the newly created name above with the Asset information in the MDT DB'
    		If @Cnt1 = 1				 'If names match it will increment the sequence number and recreate the @newname'
    		BEGIN
    			SET @Sequence = @Sequence + 1
    			SET @NewName = @Prefix + Right('0000'+LTrim(Str(@Sequence)),4)
    		END
    
    
        /* Insert the new record */
    
    	INSERT INTO ComputerIdentity (MacAddress, Description) 
    	VALUES (@Serialnumber, 'New York Site - ' + @NewName)
    	INSERT INTO Settings (Type, ID, OSDComputerName, OSInstall, SkipComputerName) 
    	VALUES ('C',@@IDENTITY, @NewName, 'Y', 'YES')
    
        COMMIT TRAN
    
    END
    
    /*  Return the record as the result set */
    
    SELECT * FROM ComputerIdentity
    WHERE SerialNumber = @SerialNumber


    Friday, July 18, 2014 4:10 AM
  • To have the stored procedure take additional parameters you add them like this:

    ALTER PROCEDURE [dbo].[InsertComputerNamev1]
    @SerialNumber CHAR(17),
    @InitialBuildPrefix CHAR(17)

    You also need to modify the cs.ini so send the parameter...

    [InsertComputerName]
    SQLServer=CM01
    Database=MDT
    Netlib=DBNMPNTW
    SQLShare=Logs$
    StoredProcedure=InsertComputerNamev1
    Parameters=SerialNumber, InitialBuildPrefix

    / Johan


    Regards / Johan Arwidmark Twitter: @jarwidmark Blog: http://www.deploymentresearch.com FB: www.facebook.com/deploymentresearch


    Friday, July 25, 2014 8:33 AM
    Moderator
  • Thanks for the heads up.  i will give that a try and see how i go.

    Will keep you posted.

    Wednesday, July 30, 2014 12:19 AM
  • Hi Johan,


    Thanks for your advise to date, i think i have got the stored procedure sorted however this has led to another issue.




    USE [MDT] GO /****** Object: StoredProcedure [dbo].[InsertComputerNamev1] Script Date: 1/08/2014 9:33:30 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[InsertComputerNamev1] @SerialNumber CHAR(255), @InitialBuildPrefix CHAR(255), @MacAddress CHAR(50), @UUID CHAR(50) 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 ComputerIdentity WHERE SerialNumber = @SerialNumber /* No record? Add one. */ IF @Cnt = 0 BEGIN /* Create a new machine name */ BEGIN TRAN SET @Prefix = RTrim(LTrim(@InitialBuildPrefix)) /*to be captured from previous vbscript to create prefix not sure on the syntax for this*/ SET @Sequence = 0001 /*instead of using the sequence table i want to always start on 0001 as the initial sequence everytime*/ SET @NewName =@Prefix +Right('0000'+LTrim(Str(@Sequence)),4) Print @NewName /* Loop until it finds a available name */ WHILE(SELECT COUNT(*) FROM ComputerIdentity WHERE AssetTag = @NewName)=1 BEGIN SET @Sequence = @Sequence + 1 SET @NewName = @Prefix + Right('0000'+LTrim(Str(@Sequence)),4) END /* Insert the new record */ --INSERT INTO ComputerIdentity ( Description,AssetTag, UUID, SerialNumber, MacAddress) --VALUES ( @NewName,@NewName,@UUID,@Serialnumber,@MacAddress) --INSERT INTO Settings (Type, ID, OSDComputerName, OSInstall, SkipComputerName) --VALUES ('C',@@IDENTITY, @NewName, 'Y', 'NO') -- COMMIT TRAN END /* Return the record as the result set */ SELECT * FROM ComputerIdentity WHERE SerialNumber = @SerialNumber

    [BuildComputerName] UserExit=MachineNameExit.vbs InitialBuildPrefix=#BuildComputerName()# [InsertComputerName] SQLServer=Servername Instance=MDT2013 Database=MDT Netlib=DBNMPNTW SQLShare=DeploymentShare$ StoredProcedure=InsertComputerNamev1

    Parameters=SerialNumber, InitialBuildPrefix ,MacAddress, UUID


    I debug in the SQL Management console by running

    Exec dbo.InsertComputerNamev1 'testserial', 'testprefix','testmac','testuuid'

    If the serial number doesn't exist it will take the prefix add a sequence number and then check computeridentity to see if the name exists.  It will continue incrementing the sequence number until it finds one available and will then set it to that. If the serialnumber exists it will re-use that name.


    Deployments are failing to get the correct name.  I added powershell and dism to my winpe so i could do some testing.  I setup the a test deployment share and ran the ZTIGather in debug mode to see the errors. 

    This is what i am seeing.

    I can see that the MachineNameExit.VBS is correctly creating the prefix name, i can also see that the correct details are being passed through to the stored procedure in terms of the

    Update progress [ 87 ] : Processing rule: BUILDCOMPUTERNAME
    ------ Processing the [BUILDCOMPUTERNAME] section ------
    USEREXIT:MachineNameExit.vbs started: SECTION BEFORE BUILDCOMPUTERNAME
    User exit "\\Servername\DeploymentShare$\simulation\MachineNameExit.vbs" called successfully, skip = False.
    USEREXIT:MachineNameExit.vbs started: SECTION AFTER BUILDCOMPUTERNAME
    User exit "\\Servername\DeploymentShare$\simulation\MachineNameExit.vbs" called successfully, skip = False.
    Update progress [ 88 ] : Processing rule: INSERTCOMPUTERNAME
    ------ Processing the [INSERTCOMPUTERNAME] section ------
    Determining the INI file to use.
    Using DEFAULT VALUE: Ini file = \\Servername\DeploymentShare$\simulation\CustomSettings.ini
    Finished determining the INI file to use.
    Using specified INI file = \\Servername\DeploymentShare$\simulation\CustomSettings.ini
    CHECKING the [INSERTCOMPUTERNAME] section
    Using from [INSERTCOMPUTERNAME]: SQLServer = Servername
    Using from [INSERTCOMPUTERNAME]: Instance = MDT2013
    Port key not defined in the section [INSERTCOMPUTERNAME]
    Using from [INSERTCOMPUTERNAME]: Database = MDT
    Using from [INSERTCOMPUTERNAME]: Netlib = DBNMPNTW
    Table key not defined in the section [INSERTCOMPUTERNAME]
    Using from [INSERTCOMPUTERNAME]: StoredProcedure = InsertComputerNamev1
    DBID key not defined in the section [INSERTCOMPUTERNAME]
    DBPwd key not defined in the section [INSERTCOMPUTERNAME]
    Using from [INSERTCOMPUTERNAME]: SQLShare = DeploymentShare$
    ParameterCondition key not defined in the section [INSERTCOMPUTERNAME]
    Default ParameterCondition 'AND' will be used for building queries with multiple parameters.
    Validating connection to \\Servername\DeploymentShare$
    Mapping server share: \\Servername\DeploymentShare$
    Already connected to server Servername as that is where this script is running from.
    OPENING TRUSTED SQL CONNECTION to server Servername.
    Connecting to SQL Server using connect string: Provider=SQLOLEDB;OLE DB Services=0;Data Source=Servername\MDT2013;Initial Catalog=MDT;Network Library=DBNMPNTW;Integrated Security=SSPI
    Successfully opened connection to database.
    Only the first MACADDRESS value will be used in the stored procedure call.
    Only the first MACADDRESS value will be used in the stored procedure call.
    About to issue SQL statement: EXECUTE InsertComputerNamev1 'VMware-56 4d f8 42 f0 20 80 dc-c8 3d 2f ea e9 49 b4 52', 'VAUSYD', '00:50:56:91:21:2B', '42F84D56-20F0-DC80-C83D-2FEAE949B452', '00:50:56:91:21:2B'
    ERROR - Opening Record Set (Error Number = -2147217887) (Error Description: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.).
      ADO error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. (Error #-2147217887; Source: Microsoft OLE DB Provider for SQL Server; SQL State: ; NativeError: 0)
    Unable to execute database query.
    Event 41039 sent: Unable to execute database query.

    I saw a post from yourself about 2012 and winpe 4.0 where certain mui files were not around.  I am running MDT2013 on SQL 2012 SP1. 

    http://www.deploymentresearch.com/Research/tabid/62/EntryId/62/Cannot-call-SQL-Stored-Procedures-from-MDT-2012-generated-WinPE-4-0-boot-image.aspx

    I checked and attempted your recommendations from that article as per below

    However it has not helped

    I ensured that the account used in the bootstrap.ini and customsettings.ini for the deployment share had the initial execute rights to the stored procedure and the account is currently a SA on the DB instance and dbo on the DB itself to eliminate permission errors.

    Any  ideas on what i am missing




    Friday, August 1, 2014 12:15 AM
  • Ok after alot of stressing and scratching my head i finally have got this working.  Johan just wanted to give you a little (or massivvveeeee) Thanks for pointing me in the right direction.

    The above sql errors were not related to the winpe but the action sql query. After i tested the stored procedure initially and found it working i didnt want to fill up the mdt db with junk while i did further testing.  So i commented out a few lines that i thought would just stop it from adding details.  What i also accidently did was commented out the COMMIT TRANS Section as well.  What i found out was this was actually causing a null entry errors as well as a transaction mismatch error.  Once u removed the -- lines the stored procedure now works as i like.  I can boot a machine up from wds it will point to the mdt deployment share and run the customsettings.ini file and generate the name exactly as i want.

        /* Insert the new record */
    
    	--INSERT INTO ComputerIdentity ( Description,AssetTag, UUID, SerialNumber, MacAddress) 
    	--VALUES ( @NewName,@NewName,@UUID,@Serialnumber,@MacAddress)
    	--INSERT INTO Settings (Type, ID, OSDComputerName, OSInstall, SkipComputerName) 
    	--VALUES ('C',@@IDENTITY, @NewName, 'Y', 'NO')
    
     --   COMMIT TRAN

    Once again thanks for all your help and assistance.  You nudged me just enough in the right direction to figure this out.

    Sunday, August 3, 2014 1:59 AM
  • OK a bit more testing and i have found a weird problem that has truely got me stumped. I am testing this by using multiple vm machines to replicate new serial numbers and mac addresses etc.

    The following machines exist in the mdt db already

    VAUSYD001

    VAUSYD002

    I create a new VM and pxe boot straight into mdt.  I can see the bootstrap.ini and the customsetting.ini running and then it shows me the screen for TS selection.  I specify the TS and it then goes to the computer name section where it shows the computer name as MININT-SDFERR. So it is not picking up the new name just generated by the stored procedure. Leaving the new machine at this stage and not touching it i check the mdt db and can see a new entry from the stored procedure that just ran for this new computer. It has added a new computer name to the db with all the correct details however does not seem to be passing this straight back to the computer being deployed.  If i cancel the deployment and reboot the 2nd time it gets to that stage it will have the correct computer name , i.e. vausyd003 and not the original MININT-SDFERR.

    So to summaries the issue i now have 

    NEW MACHINE THAT DOES NOT EXIST IN THE MDT DB

    1) Machine boots into winpe first time and generates a new name from the machinenameexit.vbs and stored procedure. The new name along with serial number, mac address and UUID populates the MDT DB

    2) After the ZTI Gather and customsettings.ini section is run and a task sequence is selected it still uses the generic name MININT-%%%%%%%

    3) Cancelling the initial deployment and reboot the machine. 2nd time it loads into winpe seems to run correctly as the name now exists in the DB and once a task sequence is selected computername shows VAUSYD#### name.

    A MACHINE ACCOUNT THAT ALREADY EXISTS IN MDT DB

    1) Machine boots into winpe first time and runs the stored procedure. Finds that the name exists in the database and sends that name.

    2) After the ZTIGather and customsettings section is run and a task sequence is selected it shows the correct name from the MDTDB i.e. VAUSYD0003

    Here is my stored procedure.  Any idea on what i am missing

    USE [MDT]
    GO
    /****** Object:  StoredProcedure [dbo].[InsertComputerNamev1]    Script Date: 3/08/2014 3:04:58 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    ALTER PROCEDURE [dbo].[InsertComputerNamev1]
    @SerialNumber NVARCHAR(255),
    @InitialBuildPrefix NVARCHAR(255),
    @MacAddress NVARCHAR(255),
    @UUID NVARCHAR(255)
    
    AS
    
    DECLARE @Cnt INT,
            @Prefix VARCHAR(255),
            @Sequence INT,
            @NewName VARCHAR(255)
    
    SET NOCOUNT ON
    /* See if there is an existing record for this machine */
    
    SELECT @Cnt=COUNT(*) FROM ComputerIdentity
    WHERE SerialNumber = @SerialNumber
    
    /* No record?  Add one.  */
    
    IF @Cnt = 0
    BEGIN
    
        /* Create a new machine name */
    
    	BEGIN TRAN
    
            SET @Prefix = RTrim(LTrim(@InitialBuildPrefix))		/*to be captured from previous vbscript to create prefix not sure on the syntax for this*/
    		SET @Sequence = 0001								 /*instead of using the sequence table i want to always start on 0001 as the initial sequence everytime*/
    		SET @NewName  =@Prefix +Right('0000'+LTrim(Str(@Sequence)),4)
    		Print @NewName
     
     	/* Loop until it finds a available name */
    
    	WHILE(SELECT COUNT(*) FROM ComputerIdentity WHERE AssetTag = @NewName)=1  
    	BEGIN
    		SET @Sequence = @Sequence + 1
    		SET @NewName = @Prefix + Right('0000'+LTrim(Str(@Sequence)),4)
    	END
    
    
        /* Insert the new record */
    	INSERT INTO ComputerIdentity ( Description,AssetTag, UUID, SerialNumber, MacAddress) 
    	VALUES ( @NewName,@NewName,@UUID,@Serialnumber,@MacAddress)
    	INSERT INTO Settings (Type, ID, OSDComputerName, OSInstall, SkipComputerName) 
    	VALUES ('C',@@IDENTITY, @NewName, 'Y', 'NO')
    
        COMMIT TRAN
    
    
    END
    
    /*  Return the record as the result set */
    
    SELECT * FROM ComputerIdentity
    WHERE SerialNumber = @SerialNumber
    
    GO


    Sunday, August 3, 2014 6:43 AM
  • Ignore that last comment.  I found the issue after some further checking and testing. I stumbled upon your updated stored procedure for mdt 2012/2013 and noticed that you had the csettings after the insertcomputername part in the customsettings.ini file. 

    I made the same change in my customsettings.ini file and now it generates and applies the new computer name in the first step without having to reboot the build process.

    I have been staring at this screen for to long my square eyes must of missed that part.

    Sunday, August 3, 2014 7:43 AM
  • Hi Johan, 

    I have created this and its working fine form a long time, But now we are in a situation. Many of the devices has been End of life and now there are new devices has take place for that one. Now in database thousand of entries are stale entries in MDT  store procedure. 

    Now if we remove these entries, can it possible to modify in store procedure that sequencing is always start form beginning and use the free number instead from the last number. 

    Monday, May 27, 2019 10:21 AM