My Experience Manipulating MDT Database Using SMA, SCORCH and SharePoint

Background

At work, there is an implementation team who’s responsible for building Windows 8 tablets in a centralised location (we call it integration centre) then ship these tablets to remote locations around the country. We use SCCM 2012 R2 and MDT 2013 to build these devices using a MDT enabled task sequence in SCCM. The task sequence use MDT locations to apply site specific settings (I’m not a OSD expert, I’m not even going to try to explain exactly what these locations entries do in the task sequence).

In order to build these tablets for any remote sites, before kicking off the OSD build, the integration centre’s default gateway IP address must be added to the location entry for this specific site, and removed from any other locations.

Because our SCCM people didn’t want to give the implementation team access to MDT Deployment Workbench, my team has been manually updating the MDT locations whenever the implementation team wants to build tablets.

I wasn’t aware of this arrangement until someone in my team went on leave and asked me to take care of this when he’s not around. Soon I got really annoyed because I had to do this few times a day! Therefore I decided to automate this process using SMA, SCORCH and SharePoint so they can update the location themselves without giving them access to MDT.

The high level workflow is shown in the diagram below:

Design

01. SharePoint List

Firstly, I created a list on one of our SharePoint sites, and this list only contains one item:

02. Orchestrator Runbook

I firstly deployed the SharePoint integration pack to the Orchestrator management servers and all the runbook servers. Then I setup a connection to the SharePoint site using a service account

The runbook only has 2 activities:

Monitor List Items:

Link:

The link filters the list ID. ID must equal to 1 (first item in the list). This is to prevent users adding additional item to the list. They must always edit the first (and only) item on the list.

Start SMA Runbook called “Update-MDTLocation”:

This activity runs a simple PowerShell script to start the SMA runbook. The SMA connection details (user name, password, SMA web service server and web service endpoint) are all saved in Orchestrator as variables.

03. SMA Runbook

Firstly, I created few variables, credentials and connections to be used in the runbook:

Connections:

• SMTP connection from the SendEmail module I posted earlier.
• Email recipient connection from SendPushNotification module I posted earlier.

Credential:

• Windows Credential that has access to the MDT database (we have MDT DB located on the SCCM SQL server, so it only accepts Windows authentication). I named the credential “ProdMDTDB”

Variables:

• MDT Database SQL Server address. I named it “CM12SQLServer”
• Gateway IP address. I named it “GatewayIP”

Here’s the code for the SMA runbook:

Workflow Update-MDTLocation
{
  PARAM (
    [Parameter(Mandatory=$true,HelpMessage='Please enter the new location')][Alias('l')][String]$Location
  )
  $SQLServer = Get-AutomationVariable -Name 'CM12SQLServer'
  $SQLInstance = 'MSSQLSERVER'
  $DBName = 'MDT'
  $CredName = 'ProdMDTDB'
  Write-Verbose "SQL Server: $SQLServer"
  Write-Verbose "SQL Instalce: $SQLInstance"
  Write-Verbose "Database: $DBName"
  Write-Verbose "Retrieving saved SMA credential $CredName"
  $SQLCred = Get-AutomationPSCredential -Name $CredName
  $SQLUserName = $SQLCred.UserName
  Write-Verbose "Connecting to $SQLServer using account $SQLUserName"
  $ConnString = "Server=$SQLServer\$SQLInstannce;Database=$DBName;Integrated Security=SSPI"
  $GatewayIP = Get-AutomationVariable -Name 'GatewayIP'
  $strQuery = @"
  USE $DBName
  Declare @Gateway Varchar(max)
  Declare @NewLocation Varchar(max)
  Declare @NewLocationID int
  Set @Gateway = `'$GatewayIP`'
  Set @NewLocation = `'$Location`'
  Set @NewLocationID = (Select L.ID From LocationIdentity L Where L.Location = @NewLocation)
  Update LocationIdentity_DefaultGateway Set ID = @NewLocationID Where DefaultGateway = @Gateway
  "@
  Write-Verbose "Executing SQL query: $strQuery"
  $Result = InlineScript
  {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $USING:ConnString
    $connection.Open()
    $command = $connection.CreateCommand()
    $command.CommandText = $USING:strQuery
    $result = $command.ExecuteNonQuery()
    $result
  } -PSComputerName $SQLServer -PSCredential $SQLCred
  Write-Output "$result row(s) updated."
  if ($result -gt 0)
  {
    $EmailMessage = "MDT location updated for $result site(s). New Location specified: $Location."
  } else {
    $EmailMessage = "MDT location did not get updated. Please contact STS to investigate further."
  }

  #Email result
  $SMTPSettings = Get-AutomationConnection -Name '[SMTP connection name]'
  $Recipient = Get-AutomationConnection -Name '[email recipient’s connection name]'
  Write-Verbose "Emailing result to $Recipient.Email"
  Send-Email -SMTPSettings $SMTPSettings -To $Recipient.Email -Subject 'MDT Location Gateway address update result' -Body $EmailMessage -HTMLBody $False
}

Putting Everything Together

As demonstrated in the diagram in the beginning of this post, here’s how the whole workflow works:

1. User login to the SharePoint site and update the only item in the list. He / She enters  the new location in the “New Gateway IP Location” field.
2. The Orchestrator runbook checks updated items in this SharePoint list every 15 seconds.
3. if the Orchestrator runbook detects the first (and only) item has been updated, it takes the new location value, start the SMA runbook and pass the new value to the SMA runbook.
4. SMA runbook runs a PowerShell script to update the gateway location directly from the MDT database.
5. SMA runbook sends email to a nominated email address when the MDT database is updated.

The email looks like this:

The Orchestrator runbook and the SMA runbook execution history can also be viewed in Orchestrator and WAP admin portal:

Room for Improvement

I created this automation process in a quick and easy way to get them off my back. I know in this process, there are a lot of areas can be improved. i.e.

• Using a SMA runbook to monitor SharePoint list direct so Orchestrator is no longer required (i.e. using the script from this article. – Credit to Christian Booth and Ryan Andorfer).
• User input validation
• Look up AD to retrieve user’s email address instead of hardcoding it in a variable.

Maybe in the future when I have spare time, I’ll go back and make it better , but for now, the implementers are happy, my team mates are happier because it is one less thing off our plate .

Conclusion

I hope you find my experience in this piece of work useful. I am still very new in SMA (and I know nothing about MDT). So, if you have any suggestions or critics, please feel free to drop me an email.