Sample configure clone hook for SQL server

How the configure clone hook works

The Delphix Engine executes the Configure clone hook after the initial provisioning of a virtual database (VDB) and after each refresh of that VDB. However, during refresh operations, the Configure clone hook runs before the Post-refresh hook.
Because it executes after the Delphix Engine brings fresh data from the dSource into the VDB, the Configure Clone hook is an ideal place for data masking and other obfuscation and reconfiguration operations to occur.
The Windows PowerShell script (shown below) is intended to demonstrate how to call the HTTP interface of the Delphix DmSuite v4.7.3 from a Configure Clone hook.
Disclaimer
This sample code is offered for illustration purposes only, and does not carry any warranty or guarantee. Employing copies of this code, in whole or in part, indicates acceptance of all risks.
CODE
#================================================================================
# File:        mask.ps1
# Type:        powershell script
# Author:      Delphix Professional Services
# Date:        02-Nov 2015
#
# Copyright and license:
#
#       Licensed under the Apache License, Version 2.0 (the "License"); you may
#       not use this file except in compliance with the License.
#
#       You may obtain a copy of the License at
#     
#               http://www.apache.org/licenses/LICENSE-2.0
#
#       Unless required by applicable law or agreed to in writing, software
#       distributed under the License is distributed on an "AS IS" basis,
#       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#
#       See the License for the specific language governing permissions and
#       limitations under the License.
#     
#       Copyright (c) 2015 by Delphix. All rights reserved.
#
# Description:
#
#    Powershell script to automate the call to Delphix/AXIS data-masking when
#    the appropriate parameters are passed.
#
#    Otherwise, this script just returns without doing anything.
#
# Command-line parameters:
#
#    $dmSuiteJobId        Delphix/AXIS DmSuite Job ID value
#    $dmSuiteAppName        Delphix/AXIS DmSuite Application Name
#    $dmSuiteEnvName        Delphix/AXIS DmSuite Target Environment Name
#    $dmSuiteConnName    Delphix/AXIS DmSuite Target Connector Name
#
# Environment inputs expected:
#
#    VDB_DATABASE_NAME    SQL Server database name for the VDB
#    VDB_INSTANCE_NAME    SQL Server instance name for the VDB
#    VDB_INSTANCE_PORT    SQL Server instance port number for the VDB
#    VDB_INSTANCE_HOST    SQL Server instance hostname for the VDB
#
# Note:
#
# Modifications:
#================================================================================
param([string]$dmSuiteJobId = "~~~"
    ,[string]$dmSuiteAppName = "~~~"
    ,[string]$dmSuiteEnvName = "~~~"
    ,[string]$dmSuiteConnName = "~~~"
)
#
#--------------------------------------------------------------------------------
# Set fixed variables for later use when submitting the Delphix/AXIS DmSuite job...
#--------------------------------------------------------------------------------
$dirPath = [Environment]::GetFolderPath("Desktop")
$dmSuiteServer = "XXXXXXXXX"
$dmSuitePort = "8282"
$dmSuiteUser = "XXXXXXXX"
$dmSuitePwd = "XXXXXXXXX"
$dmSuiteBaseURL = "http://" + $dmSuiteServer + ":" + $dmSuitePort + "/dmsuite/apiV4"
$dmSuiteWaitSecs = 5
#
#--------------------------------------------------------------------------------
# If no Delphix/AXIS DmSuite Job ID is provided, then this database is not to be
# masked, and so then don't do anything...
#--------------------------------------------------------------------------------
if ( $dmSuiteJobId -eq "~~~" -or $dmSuiteJobId -eq "0" ) {
    exit 0
}
#
#--------------------------------------------------------------------------------
# Otherwise, if we're going to mask this database, then first let's create a log
# file to capture output from this script and also create an XML file to store
# responses from the Delphix/AXIS DmSuite engine...
#--------------------------------------------------------------------------------
$timeStamp = Get-Date -UFormat "%Y%m%d_%H%M%S"
$logFile = $dirPath + "\" + $env:VDB_DATABASE_NAME + "_" + $timeStamp + "_MASK.LOG"
"logFile is " + $logFile
$xmlFile = $dirPath + "\" + $env:VDB_DATABASE_NAME + "_" + $timeStamp + "_MASK.XML"
"INFO: xmlFile = '" + $xmlFile + "'" | Out-File $logFile
$errFile = $dirPath + "\" + $env:VDB_DATABASE_NAME + "_" + $timeStamp + "_MASK.ERR"
"INFO: errFile = '" + $errFile + "'" | Out-File $logFile -Append
#
#--------------------------------------------------------------------------------
# Housekeeping: remove any existing log files older than 15 days...
#--------------------------------------------------------------------------------
"INFO: removing log files older than 15 days..." | Out-File $logFile -Append
$ageLimit = (Get-Date).AddDays(-15)
$logFilePattern = $env:VDB_DATABASE_NAME + "_*_MASK.LOG"
"INFO: logFilePattern = '" + $logFilePattern + "'" | Out-File $logFile -Append
Get-ChildItem -Path $dirPath -recurse -include $logFilePattern |
    Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $ageLimit } |
    Remove-Item
#
#--------------------------------------------------------------------------------
# Record variables and parameters to the log file...
#--------------------------------------------------------------------------------
"INFO: dirPath = '" + $dirPath + "'" | Out-File $logFile -Append
"INFO: dmSuiteJobId = '" + $dmSuiteJobId + "'" | Out-File $logFile -Append
"INFO: dmSuiteAppName = '" + $dmSuiteAppName + "'" | Out-File $logFile -Append
"INFO: dmSuiteEnvName = '" + $dmSuiteEnvName + "'" | Out-File $logFile -Append
"INFO: dmSuiteConnName = '" + $dmSuiteConnName + "'" | Out-File $logFile -Append
"INFO: env:VDB_INSTANCE_HOST = '" + $env:VDB_INSTANCE_HOST + "'" | Out-File $logFile -Append
"INFO: env:VDB_INSTANCE_NAME = '" + $env:VDB_INSTANCE_NAME + "'" | Out-File $logFile -Append
"INFO: env:VDB_INSTANCE_PORT = '" + $env:VDB_INSTANCE_PORT + "'" | Out-File $logFile -Append
"INFO: env:VDB_DATABASE_NAME = '" + $env:VDB_DATABASE_NAME + "'" | Out-File $logFile -Append
"INFO: dmSuiteServer = '" + $dmSuiteServer + "'" | Out-File $logFile -Append
"INFO: dmSuitePort = '" + $dmSuitePort + "'" | Out-File $logFile -Append
"INFO: dmSuiteUser = '" + $dmSuiteUser + "'" | Out-File $logFile -Append
"INFO: dmSuiteBaseURL = '" + $dmSuiteBaseURL + "'" | Out-File $logFile -Append
"INFO: xmlFile = '" + $xmlFile + "'" | Out-File $logFile -Append
"INFO: errFile = '" + $errFile + "'" | Out-File $logFile -Append
#
#--------------------------------------------------------------------------------
# Verify that the CURL executable is available...
#--------------------------------------------------------------------------------
$curlExe = $dirPath.ToUpper() + "\CURL.EXE"
if (-Not (test-path $curlExe )) {
    "ERROR: Executable '" + $curlExe + "' not found; aborting..." | Out-File $logFile -Append
    Throw "ERROR: Executable '" + $curlExe + "' not found; aborting..."
}
"INFO: curlExe = '" + $curlExe + "'" | Out-File $logFile -Append
#
#--------------------------------------------------------------------------------
# Obtain the CURL authentication token for the username and encrypted password...
#--------------------------------------------------------------------------------
"INFO: obtaining auth_token for username/encyrpted-password..." | Out-File $logFile -Append
$cmdLine = $curlExe + " -sIX GET `"" + $dmSuiteBaseURL + "/login?user=" + $dmSuiteUser + "&password=" + $dmSuitePwd + "`" 2>&1"
"INFO: powershell command-line is '" + $cmdLine + "'" | Out-File $logFile -Append
$output = invoke-expression $cmdLine
if ( $LastExitCode -ne 0) {
    "ERROR: GET auth_token failed; aborting..." | Out-File $logFile -Append
    $output | Out-File $logFile -Append
    Throw $output
}
if ($output | where {$_.ToUpper() -match "ERROR" -or $_.ToUpper() -match "FAILURE"}) {
    "ERROR: GET auth_token returned an error; aborting..." | Out-File $logFile -Append
    $output | Out-File $logFile -Append
    Throw $output
}
$curlAuthTokenLine = $output | Select-String -pattern auth_token
$curlAuthToken = $curlAuthTokenLine -split '\s+' | Select-Object -Last 1
if ([string]::IsNullOrEmpty($curlAuthToken)) {
    "ERROR: NULL authentication token; aborting..." | Out-File $logFile -Append
    $output | Out-File $logFile -Append
    Throw $output
}
"INFO: auth_token '" + $curlAuthToken + "' obtained" | Out-File $logFile -Append
#
#--------------------------------------------------------------------------------
# Retrieve the Delphix/AXIS DmSuite environment ID...
#--------------------------------------------------------------------------------
"INFO: retrieving DmSuite environment ID for dmSuiteAppName '" + $dmSuiteAppName +
    "..." | Out-File $logFile -Append
$cmdLine = $curlExe + " -H `"auth_token:" + $curlAuthToken + "`" `"" +
    $dmSuiteBaseURL + "/environments`" > " + $xmlFile + " 2> " + $errFile
"INFO: powershell command-line is '" + $cmdLine + "'" | Out-File $logFile -Append
$output = invoke-expression $cmdLine
if ( $LastExitCode -ne 0) {
    "ERROR: retrieving DmSuite environment ID failed; aborting..." | Out-File $logFile -Append
    $output | Out-File $logFile -Append
    cat $xmlFile | Out-File $logFile -Append
    cat $errFile | Out-File $logFile -Append
    Remove-Item $xmlFile
    Remove-Item $errFile
    Throw $output
}
if ($output | where {$_.ToUpper() -match "ERROR" -or $_.ToUpper() -match "FAILURE"}) {
    "ERROR: retrieving DmSuite environment ID returned an error; aborting..." | Out-File $logFile -Append
    $output | Out-File $logFile -Append
    cat $xmlFile | Out-File $logFile -Append
    cat $errFile | Out-File $logFile -Append
    Remove-Item $xmlFile
    Remove-Item $errFile
    Throw $output
}
[xml]$xml = Get-Content $xmlFile
$environmentURL = ""
$xml.SelectNodes("//Environment") | % {
    if (($_.Name -eq $dmSuiteEnvName) -and ($_.Application -eq $dmSuiteAppName)) {
        $environmentURL = $_.Link.href
    }
}
if ([string]::IsNullOrEmpty($environmentURL)) {
    "ERROR: retrieving DmSuite environment ID returned an error; aborting..." | Out-File $logFile -Append
    cat $xmlFile | Out-File $logFile -Append
    cat $errFile | Out-File $logFile -Append
    Remove-Item $xmlFile
    Remove-Item $errFile
    Throw "ERROR: retrieving DmSuite environment ID returned an error; aborting..."
}
"INFO: environmentURL = '" + $environmentURL + "'" | Out-File $logFile -Append
Remove-item $xmlFile
Remove-Item $errFile
#
#--------------------------------------------------------------------------------
# Retrieve the Delphix/AXIS DmSuite connector ID...
#--------------------------------------------------------------------------------
"INFO: retrieving DmSuite connector ID for dmSuiteConnName '" + $dmSuiteConnName +
    "..." | Out-File $logFile -Append
$cmdLine = $curlExe + " -H `"auth_token:" + $curlAuthToken + "`" `"" +
    $dmSuiteBaseURL + "/" + $environmentURL + "/connectors`" > " + $xmlFile + " 2> " + $errFile
"INFO: powershell command-line is '" + $cmdLine + "'" | Out-File $logFile -Append
$output = invoke-expression $cmdLine
if ( $LastExitCode -ne 0) {
    "ERROR: retrieving DmSuite connector ID failed; aborting..." | Out-File $logFile -Append
    $output | Out-File $logFile -Append
    cat $xmlFile | Out-File $logFile -Append
    cat $errFile | Out-File $logFile -Append
    Remove-Item $xmlFile
    Remove-item $errFile
    Throw $output
}
if ($output | where {$_.ToUpper() -match "ERROR" -or $_.ToUpper() -match "FAILURE"}) {
    "ERROR: retrieving DmSuite connector ID returned an error; aborting..." | Out-File $logFile -Append
    $output | Out-File $logFile -Append
    cat $xmlFile | Out-File $logFile -Append
    cat $errFile | Out-File $logFile -Append
    Remove-Item $xmlFile
    Remove-item $errFile
    Throw $output
}
[xml]$xml = Get-Content $xmlFile
$connectorURL = ""
$xml.SelectNodes("//Connector") | % {
    if ( $_.Name -eq $dmSuiteConnName ) {
        $connectorURL = $_.Link.href
    }
}
if ([string]::IsNullOrEmpty($connectorURL)) {
    "ERROR: retrieving DmSuite connector ID returned an error; aborting..." | Out-File $logFile -Append
    cat $xmlFile | Out-File $logFile -Append
    cat $errFile | Out-File $logFile -Append
    Remove-Item $xmlFile
    Remove-item $errFile
    Throw "ERROR: retrieving DmSuite connector ID returned an error; aborting..."
}
"INFO: connectorURL = '" + $connectorURL + "'" | Out-File $logFile -Append
Remove-item $xmlFile
Remove-item $errFile
#
#--------------------------------------------------------------------------------
# Start the Delphix/AXIS DmSuitemasking job...
#--------------------------------------------------------------------------------
"INFO: starting DmSuite JobID " + $dmSuiteJobId + "..." | Out-File $logFile -Append
$cmdLine = $curlExe + " -X POST -H `"auth_token:" + $curlAuthToken +
    "`" -H `"Content-Type:application/xml`" -d `"<MaskingsRequest>" +
###
### The two following lines are needed if the Delphix/AXIS DmSuite masking job is "multitenant"...
###
###    "<Links>" + "<Link rel=`"SourceConnector`" href=`"" + $connectorURL + "`"/>" + 
###    "<Link rel=`"TargetConnector`" href=`"" + $connectorURL + "`"/>" + "</Links>" +
    "</MaskingsRequest>`" `"" + $dmSuiteBaseURL + "/applications/" + $dmSuiteAppName +
    "/maskingjobs/" + $dmSuiteJobId + "/run`" > " + $xmlFile + " 2> " + $errFile
"INFO: powershell command-line is '" + $cmdLine + "'" | Out-File $logFile -Append
$output = invoke-expression $cmdLine
if ( $LastExitCode -ne 0) {
    "ERROR: DmSuite job start returned failure exit code; aborting..." | Out-File $logFile -Append
    $output | Out-File $logFile -Append
    cat $xmlFile | Out-File $logFile -Append
    cat $errFile | Out-File $logFile -Append
    Remove-Item $xmlFile
    Remove-Item $errFile
    Throw "ERROR: DmSuite job start returned failure exit code; aborting..."
}
[xml]$xml = Get-Content $xmlFile
if ($xml.MaskingsResponse.ResponseStatus.Status -ne "SUCCESS") {
    "ERROR: DmSuite job run failed; aborting..." | Out-File $logFile -Append
    $output | Out-File $logFile -Append
    cat $xmlFile | Out-File $logFile -Append
    cat $errFile | Out-File $logFile -Append
    Remove-Item $xmlFile
    Remove-item $errFile
    Throw "ERROR: DmSuite job start failed; aborting..."
}
Remove-Item $xmlFile
Remove-item $errFile
#
#--------------------------------------------------------------------------------
# Check status of the submitted Delphix/AXIS DmSuite masking job...
#--------------------------------------------------------------------------------
$dmSuiteStatus = "RUNNING"
while ( $dmSuiteStatus -match "RUNNING" ) {
    #
    Start-Sleep -s $dmSuiteWaitSecs
    $totalWaitSecs = $totalWaitSecs + $dmSuiteWaitSecs
    #
    "INFO: Checking DmSuite JobID " + $dmSuiteJobId + "status after " +
         $totalWaitSecs + " secs..." | Out-File $logFile -Append
    $cmdLine = $curlExe + " -H `"auth_token:" + $curlAuthToken +
        "`" -H `"Content-Type:application/xml`" `"" +
        $dmSuiteBaseURL + "/applications/" + $dmSuiteAppName +
        "/maskingjobs/" + $dmSuiteJobId + "/results`" > " +
        $xmlFile + " 2> " + $errFile
    "INFO: powershell command-line is '" + $cmdLine + "'" | Out-File $logFile -Append
    $output = invoke-expression $cmdLine
    if ( $LastExitCode -ne 0) {
        "ERROR: DmSuite job status returned failure exit status; aborting..." | Out-File $logFile -Append
        $output | Out-File $logFile -Append
        cat $xmlFile | Out-File $logFile -Append
        cat $errFile | Out-File $logFile -Append
        Remove-Item $xmlFile
        Remove-Item $errFile
        Throw "ERROR: DmSuite job status returned failure exit status; aborting..."
    }
    [xml]$xml = Get-Content $xmlFile
    if ($xml.MaskingsResponse.ResponseStatus.Status -ne "SUCCESS") {
        "ERROR: DmSuite job status failed; aborting..." | Out-File $logFile -Append
        $output | Out-File $logFile -Append
        cat $xmlFile | Out-File $logFile -Append
        cat $errFile | Out-File $logFile -Append
        Remove-Item $xmlFile
        Remove-item $errFile
        Throw "ERROR: DmSuite job status failed; aborting..."
    }
    $dmSuiteStatus = $xml.MaskingsResponse.Maskings.Masking.Status
    #
}
#
#--------------------------------------------------------------------------------
# Return final exit status of completed Delphix/AXIS DmSuite masking job...
#--------------------------------------------------------------------------------
if ($dmSuiteStatus -eq "SUCCESS") {
    #
    "INFO: DmSuite JobID '" + $dmSuiteJobId + "' SUCCESS after " +
        $totalWaitSecs + " secs..." | Out-File $logFile -Append
    Remove-Item $xmlFile
    Remove-item $errFile
    exit 0
    #
} else {
    #
    cat $xmlFile | Out-File $logFile -Append
    cat $errFile | Out-File $logFile -Append
    "ERROR: DmSuite JobID '" + $dmSuiteJobId + "' failed with '" + $dmSuiteStatus +
        " after " + $totalWaitSecs + " secs running..." | Out-File $logFile -Append
    Throw "ERROR: DmSuite JobID '" + $dmSuiteJobId + "' failed with '" +
        $dmSuiteStatus + " after " + $totalWaitSecs + " secs running..."
    #
}