Now as we know how to create our own webservice and how to use it from a script, we can go ahead to the more useful topics. We actually want to extend the current MDT implementation.

So I would like to start with a function which was already quite useful on a couple of different implementations. We want to have an easy way to update the Computer information, mainly Serial Number, MAC Address and UUID. The benefit might not be obvious but to give you an example. We use this to prestage a computer to the MDT Database with a couple of additional information like specific SCCM Task Sequence if necessary or the old computer on replacement scenarios (more on this later).

However prestaging for SCCM means you need to have either the MAC Address or the UUID available. Most Computer vendors will not be able to supply you with this information in advance. If possible even before the computer arrives or at least on the shipped package itself. But most of them are able to supply something like serial number or service tag. So we can use the Service tag on a large amount of computers and have the information centrally available even on remote sites without local IT Support. As soon as the computer comes online it will publish the more important information like MAC to the database and will then continue with further processing as configured while we can make use of this values. As said, there will be a couple of other examples coming soon.

Enough talking, let's get to the implementation.

First we need to create a stored procedure which contains the logic to update the values. I'm a big fan of using views for getting information from a database and using stored procedures to put something into it. This way we have more control on any data manipulation. It also enables us to implement changes at a later time without the necessity to update all scripts and frontends flying around in the environment.

So just add this stored procedure to your MDT Database:

CREATE PROCEDURE [dbo].[UpdateComputerInfo]

@SerialNumber nvarchar(255),
@AssetTag nvarchar(255),
@MacAddress nvarchar(50),
@UUID as nvarchar(50),
@ComputerName nvarchar(255)

AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @NewID AS INT /* Store the MDTID if computer exists already */
   IF NOT EXISTS (SELECT 1 FROM dbo.ComputerIdentity
                  WHERE (SerialNumber = @SerialNumber AND @SerialNumber <> '') OR
                        (AssetTag = @AssetTag AND @AssetTag <> '') OR
                        (MacAddress = @MacAddress AND @MacAddress <> '') OR
                        (UUID = @UUID AND @UUID <> ''))
   BEGIN /* Computer does not exist in Database. Add Information */
      INSERT INTO dbo.ComputerIdentity
      (SerialNumber,
       AssetTag,
       MacAddress,
       UUID,
       [Description])
      VALUES
      (@SerialNumber,
       @AssetTag,
       @MacAddress,
       @UUID,
       @ComputerName)
     
      SET @NewID = SCOPE_IDENTITY()

      /* We also create an entry in Settings table to be used later */
      INSERT INTO dbo.Settings
      (ID,
       [Type],
       Computername)
      VALUES
      (@NewID,
       'C',
       @ComputerName)
   END

   ELSE
   BEGIN /* Computer is known, update information */
      SET @NewID = (SELECT TOP 1 ID FROM dbo.ComputerIdentity WHERE
                   (SerialNumber = @SerialNumber AND @SerialNumber <> '') OR
                   (AssetTag = @AssetTag AND @AssetTag <> '') OR
                   (MacAddress = @MacAddress AND @MacAddress <> '') OR
                   (UUID = @UUID AND @UUID <> ''))
     
      UPDATE dbo.ComputerIdentity
      SET SerialNumber = @SerialNumber,
          AssetTag = @AssetTag,
          MacAddress = @MacAddress,
          UUID = @UUID,
          [Description] = @ComputerName
      WHERE ID = @NewID
     
      UPDATE dbo.Settings
      SET ComputerName = @ComputerName
      WHERE ID = @NewID AND [Type] = 'C'

   END

SELECT @NewID AS MDTID

END

It will check if an entry for this computer already exists and update it with the supplied information, or create a new one with it if necessary. Now we can just call this stored procedure from any script to update the values within the MDT Database. The easiest way is probably directly from the customsettings.ini during the execution of any Task Sequence. Just add the following section to your customsettings.ini (adjust the values to your environment)

[UpdateComputerInfo]
SQLServer=YourMDTSQLServer
Database=YourMDTDatabase
StoredProcedure=UpdateComputerInfo
Parameters=SerialNumber,AssetTag,MacAddress,UUID,ComputerName
SQLShare=ShareOnYourSQLServer

If you now just add the UpdateComputerInfo Section to your Settings section

[Settings]
Priority=UpdateComputerInfo,...

all information will be updated during the gather step. For further information, a really good introduction into rule processing can be found on Ben Hunter: Understanding BDD Rule processing.

And finally we also would like to be able to call this via the webservice we have been talking about in the last two posts. So simply open your custom MDT Webservice and add the following function:

<WebMethod()> _
Public Function UpdateComputerInfo(ByVal SerialNumber As String, ByVal AssetTag As String, ByVal MacAddress As String, ByVal UUID As String, ByVal ComputerName As String) As Integer
    Dim Result As Integer = 0 'store Computer ID From MDT
   
    Dim connection As New SqlClient.SqlConnection(Me._ConnectionString)

    Try
        ' Create connection to MDT Database
        connection.Open()

        'Prepare command to execute Stored Procedure
        Dim command As New SqlClient.SqlCommand("UpdateComputerInfo", connection)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add("@SerialNumber", SqlDbType.NVarChar, 255).Value = SerialNumber
        command.Parameters.Add("@AssetTag", SqlDbType.NVarChar, 255).Value = AssetTag
        command.Parameters.Add("@MacAddress", SqlDbType.NVarChar, 50).Value = MacAddress
        command.Parameters.Add("@UUID", SqlDbType.NVarChar, 255).Value = UUID
        command.Parameters.Add("@ComputerName", SqlDbType.NVarChar, 255).Value = ComputerName

        'Execute Stored Procedure

    Catch ex As Exception
        Trace.WriteLine("UpdateComputerInfo: Error " & Err.Number & ": " & Err.Description)
        Trace.WriteLine("UpdateComputerInfo: Source: " & Err.Source)
    Finally
        connection.Close()
    End Try

    Return Result
End Function

As some explanation on this. We first define, that this new web methods will take 5 string values, the actual information which shall be inserted/updated, and returns an integer Value. This Value is the ID of the computer within the MDT Database. We will store this value in our scripts as it will ease further processing. Then we create the connection to the MDT Database, prepare the command to execute a stored procedure with the given information and finally execute the stored procedure.

After building and publishing we can use this new webservice method as described in Calling a webservice from a script using MDT 2008. We use this function as one of the first steps within a couple of Task Sequences including the capture Task sequence for old computers which will be replaced. This way we can easily assign old to new computers and have access to information of the old computer (e.g. old applications) as we set up the new computer. But be sure I will explain this in more detail in a later post.

For your convenience you can download the source files for this Post. It will contain the Sql script and the Service1.asmx.vb to drop it into the Project you could download in the last post about Creating your own MDT Webservice - Part 2.