Examples of PowerShell scripts that make DPA API calls

The following examples show PowerShell scripts that call the DPA API to retrieve information and perform DPA management functions. The first examples are snippets that demonstrate each API call individually. The last example is a full script that shows how to put the snippets together into a working script.

You can call the DPA API with any programming language that can send HTTP requests. See this topic for Python script examples.

See the following sections:

Prerequisite

Before you can use scripts to make API calls, you must create a refresh token.

If your DPA server does not use HTTPS or your certificates are self-signed

The examples all use HTTPS, which can cause problems if your DPA server is not configured to use HTTPS or if your certificates are self signed. If this is the case, you can do either of the following:

  • Run the examples using HTTP.
  • Add the following code below the configuration section.
#----------------------------------------------------------
# Adding certificate exception to prevent API errors
#----------------------------------------------------------
add-type @"
  using System.Net;
  using System.Security.Cryptography.X509Certificates;
  public class TrustAllCertsPolicy : ICertificatePolicy {
    public bool CheckValidationResult(
      ServicePoint srvPoint, X509Certificate certificate,
      WebRequest request, int certificateProblem) {
      return true;
    }
  }
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

Get an access token

The first step in using the API is to get an access token. An access token is required to make any API calls. This call POSTs the refresh token to DPA, which returns an access token to be used by all other API calls.

  • If the call is successful, it prints out the data that was returned from DPA, including the access_token, and then goes on to create HTTP Headers that will contain the access token and other information to be used on subsequent calls.
  • If the call is not successful it prints out the error message.

You must set the $baseURL and the $refreshToken variables to match your environment.

#----------------------------------------------------------
# Configure the variables below for the DPA Host
#----------------------------------------------------------
$baseURL = "https://localhost:8124/iwc/api/"
$refreshToken = "eyJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJNeVRva2VuIiwiaXN..."

#----------------------------------------------------------
# Get an access token
#----------------------------------------------------------
$authTokenURL = $baseURL + "security/oauth/token"
$body = @{"grant_type" = "refresh_token"
          "refresh_token" = "$refreshToken"}
Try {
  Write-Host "Getting Access Token..."
  $dpaAuthResponse = Invoke-RestMethod -Uri $authTokenURL -Method POST -Body $body
  $dpaAuthResponse | Format-List
}
Catch {
  $_.Exception.ToString()
  return
}

# If successful we will create our headers to be used for all API calls
$tokenType = $DpaAuthResponse.token_type
$accessToken = $DpaAuthResponse.access_token
$dpaHeader = @{}
$dpaHeader.Add("Accept", "application/json")
$dpaHeader.Add("Content-Type", "application/json;charset=UTF-8")
$dpaHeader.Add("Authorization", "$tokenType $accessToken")

# This will print out data like this:

Getting Access Token...

access_token : eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VyX25hbWUiOiJpZ25pdGUiLCJzY29wZSI6W10sImlkIjotMSwidXNlcl...
token_type   : bearer
expires_in   : 365
id           : -1
userType     : repo
jti          : e0d51295-2010-4ed4-b5ea-982a4e6ae1c5

Database Monitor examples

The following examples show how to use all Database Monitor calls.

Get information about one monitored database instance

# Get Monitor Information for a single database
$databaseId = 1
$monitorURL = $baseURL + "databases/$databaseId/monitor-information"
Try {
  Write-Host "Get Monitor Information for database with id of $databaseId..."
  $monitorJSON = Invoke-RestMethod -Method Get -Uri $monitorURL -Headers $dpaHeader -TimeoutSec 60
  $monitor = $monitorJSON.data
  $monitor | Format-List
}
Catch {
  $_.Exception.ToString()
}

# This will print out data like this:
Get Monitor Information for database with id of 1...
dbId                      : 1
name                      : DEV-DPA\SQLEXPRESS
ip                        : 127.0.0.1
port                      : 1433
jdbcUrlProperties         : applicationIntent=readOnly
connectionProperties      :
databaseType              : SQL Server
databaseVersion           : 12.0.6205.1
databaseEdition           : Enterprise Edition: Core-based Licensing (64-bit)
monitoringUser            : ignite_next
defaultDbLicenseCategory  : DPACAT2
assignedDbLicenseCategory : DPACAT2
assignedVmLicenseCategory :
monitorState              : Monitor Running
oldestMonitoringDate      : 2018-12-04T00:00:00.000-07:00
latestMonitoringDate      : 2019-01-02T00:00:00.000-07:00
agListenerName            :
agClusterName             :
agName                    :
racInfo                   :
rac                       : False
linkedToVirtualMachine    : False
rds                       : False
pdb                       : False
ebusiness                 : False

Start and stop monitoring a database instance given its database ID

$databaseId = 1
$monitorURL = $baseURL + "databases/$databaseId/monitor-status"

# Start monitoring a database instance given its database ID.
Try {
  Write-Host "Start Monitor for database $databaseId..."
  $command = @{"command" = "START"} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
  $result = $monitorJSON.data
  Write-Host "Result: $result"
  Write-Host "Waiting 15 seconds...`r`n"
  Start-Sleep -s 15
}
Catch {
  $_.Exception.ToString()
}

# Stop monitoring a database instance given its database ID.
Try {
  Write-Host "Stop Monitor for database $databaseId..."
  $command = @{"command" = "STOP"} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
  $result = $monitorJSON.data
  Write-Host "Result: $result"
  Write-Host "Waiting 15 seconds...`r`n"
  Start-Sleep -s 15
}
Catch {
  $_.Exception.ToString()
}
# This will print out data like this:
Start Monitor for database 1...
Result: SUCCESS
Waiting 15 seconds...

Stop Monitor for database 1...
Result: SUCCESS
Waiting 15 seconds...

Get information about all monitored database instances

# Get Monitor Information for all databases
$monitorURL = $baseURL + "databases/monitor-information"
Try {
  Write-Host "Get Monitor Information for all databases..."
  $monitorListJSON = Invoke-RestMethod -Method Get -Uri $monitorURL -Headers $dpaHeader -TimeoutSec 60
  $monitorList = $monitorListJSON.data
  $monitorList | Format-Table -AutoSize
}
Catch {
  $_.Exception.ToString()
}

# This will print out data like this:
Get Monitor Information for all databases...

dbId name                ip          port  jdbcUrlProperties            connectionProperties  databaseType  databaseVersion  ...
---- ----                --          ----  -----------------            --------------------  ------------  ---------------  ---
   1 DEV-DPA\SQLEXPRESS  10.10.10.1  1433  applicationIntent=readOnly                         SQL Server    12.0.6205.1      ...
   3 DEVORA11_DEVORA11   10.10.10.2  1521                                                     Oracle        11.2.0.1.0       ...
  10 DEV-MYSQL:3306      10.10.10.3  3306  dumpQueriesOnException=true                        MySQL         5.7.19           ...
etc.

Stop and start monitoring for all database instances

$monitorURL = $baseURL + "databases/monitor-status"

# Start monitoring all database instances.
Try {
  Write-Host "Starting all Monitors..."
  $command = @{"command" = "START"} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
  $result = $monitorJSON.data
  Write-Host "Result: $result"
  Write-Host "Waiting 30 seconds...`r`n"
  Start-Sleep -s 30
}
Catch {
  $_.Exception.ToString()
}

# Stop monitoring all database instances.
Try {
  Write-Host "Stopping all Monitors..."
  $command = @{"command" = "STOP"} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
  $result = $monitorJSON.data
  Write-Host "Result: $result"
  Write-Host "Waiting 30 seconds...`r`n"
  Start-Sleep -s 30
}
Catch {
  $_.Exception.ToString()
}

# This will print out data like this:
Starting all Monitors...
Result: SUCCESS
Waiting 30 seconds...

Stopping all Monitors...
Result: SUCCESS
Waiting 30 seconds...

Update the user password for a monitored database instance

$databaseId = 1
$monitorURL = $baseURL + "databases/$databaseId/update-password"
Try {
  Write-Host "Update the Monitor password for database $databaseId..."
  $command = @{"password" = "NewPassword!"} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
  $result = $monitorJSON.data
  Write-Host "Result: $result`r`n"
}
Catch {
  $_.Exception.ToString()
}

# This will print out data like this:
Update the Monitor password for database 1...
Result: SUCCESS

License Allocation examples

The examples below show how to use all License Allocation calls.

Get information about currently installed licenses

$licenseURL = $baseURL + "databases/licenses/installed"

Try {
  Write-Host "Getting Installed license information with total amounts available for use and total amounts used..."
  $licenseListJSON = Invoke-RestMethod -Method Get -Uri $licenseURL -Headers $dpaHeader -TimeoutSec 60
  $licenseList = $licenseListJSON.data
  $licenseList | Format-Table -AutoSize
}
Catch {
  $_.Exception.ToString()
}

# This will print out data like this:
Getting Installed license information with total amounts available for use and total amounts used...

licenseProduct licenseCategory licensesAvailable licensesConsumed
-------------- --------------- ----------------- ----------------
DPACAT1        DPA_DB                        100               22
DPACAT2        DPA_DB                        100               16
DPAAzureSQL    DPA_DB                          0                0
DPAVM          DPA_VM                        100               12

Get license information for a single database instance

$databaseId = 1
$licenseURL = $baseURL + "databases/$databaseId/licenses"
Try {
  Write-Host "Getting current license information for the database instance with database ID of $databaseId."
  $licenseListJSON = Invoke-RestMethod -Method Get -Uri $licenseURL -Headers $dpaHeader -TimeoutSec 60
  $licenseInfo = $licenseListJSON.data
  $licenseInfo | Format-Table -AutoSize
}
Catch {
  $_.Exception.ToString()
}

# This will print out data like this:
Getting current license information for the database instance with database ID of 1.

serverName overLicensed vmLicenseProduct performanceLicenseProduct
---------- ------------ ---------------- -------------------------
DEV-DPA           False DPAVM            DPACAT2

Update license information for a database instance

$databaseId = 1
$licenseURL = $baseURL + "databases/$databaseId/licenses"

# Add a DPACAT2 and a DPAVM license
$licenseAllocation = @{"performanceLicenseProduct" = "DPACAT2";
                       "vmLicenseProduct" = "DPAVM"} | ConvertTo-Json
Try {
  Write-Host "Updating license for database id $databaseId..."
  $licenseResultJSON = Invoke-RestMethod -Method Put -Uri $licenseURL -Body $licenseAllocation -Headers $dpaHeader -TimeoutSec 60
  $licenseResult = $licenseResultJSON.data
  Write-Host "New License Allocation result for the database instance with database ID of $databaseId."
  $licenseResult | Format-Table -AutoSize
}
Catch {
  $_.Exception.ToString()
}

# Remove the DPAVM license
$licenseAllocation = @{"performanceLicenseProduct" = "DPACAT2";
                       "vmLicenseProduct" = "REMOVE"} | ConvertTo-Json
Try {
  Write-Host "Updating license for database id $databaseId..."
  $licenseResultJSON = Invoke-RestMethod -Method Put -Uri $licenseURL -Body $licenseAllocation -Headers $dpaHeader -TimeoutSec 60
  $licenseResult = $licenseResultJSON.data
  Write-Host "New License Allocation result for the database instance with database ID of $databaseId."
  $licenseResult | Format-Table -AutoSize
}
Catch {
  $_.Exception.ToString()
}

# This will print out data like this:
Updating license for database id 1...
New License Allocation result for the database instance with database ID of 1.

serverName overLicensed vmLicenseProduct performanceLicenseProduct
---------- ------------ ---------------- -------------------------
DEV-DPA           False DPAVM            DPACAT2

Updating license for database id 1...
New License Allocation result for the database instance with database ID of 1.

serverName overLicensed vmLicenseProduct performanceLicenseProduct
---------- ------------ ---------------- -------------------------
DEV-DPA           False                  DPACAT2

Annotation examples

The examples below show how to use all Annotation calls.

Get a list of annotations for the last 30 days

$databaseId = 1
$annotationURL = $baseURL + "databases/$databaseId/annotations"

# Dates are in ISO 8601 format ( 2018-12-31T12:00:00.000-07:00 )
$endTime = Get-Date
$startTime = $endTime.AddDays(-30)
$startTime = [System.Web.HttpUtility]::UrlEncode($startTime.ToString("yyyy-MM-ddTHH\:mm\:ss.fffzzz"))
$endTime = [System.Web.HttpUtility]::UrlEncode($endTime.ToString("yyyy-MM-ddTHH\:mm\:ss.fffzzz"))

$request = [System.UriBuilder]$annotationURL
$request.Query = "startTime=$startTime&endTime=$endTime"
$annotationURL = $request.Uri

Try {
  Write-Host "Getting Annotations for the last 30 days..."
  $annotationListJSON = Invoke-RestMethod -Method Get -Uri $annotationURL -Headers $dpaHeader -TimeoutSec 60
  $annotationList = $annotationListJSON.data
  $annotationList | Format-Table -AutoSize
}
Catch {
  handleError $Error[0]
}

# This will print out data like this:
Getting Annotations for the last 30 days...

 id title          description                   createdBy time                      type
 -- -----          -----------                   --------- ----                      ----
 98 Test Title API Test Event created by DPA API DPA API   2018-12-04T18:13:04-07:00 Custom
 99 Test Title API Test Event created by DPA API DPA API   2018-12-04T18:14:27-07:00 Custom
100 Test Title API Test Event created by DPA API DPA API   2018-12-04T18:16:46-07:00 Custom
etc.

Create a new annotation

$databaseId = 1
$annotationURL = $baseURL + "databases/$databaseId/annotations"
$createTime = Get-Date
# Dates are in ISO 8601 format, no millis ( 2018-12-31T12:00:00-07:00 )
$createTime = $createTime.ToString("yyyy-MM-ddTHH\:mm\:sszzz")
$body = @{"title" = "API Test Title";
                    "description" = "API Test Description";
                    "createdBy" = "Test API User";
                    "time" = "$createTime"} | ConvertTo-Json
Try {
  Write-Host "Creating Annotation..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $annotationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $annotationId = $dpaResponse.id
}
Catch {
  $_.Exception.ToString()
}

# This will print out data like this:
Creating Annotation...

 id title          description          createdBy     time                      type
 -- -----          -----------          ---------     ----                      ----
148 API Test Title API Test Description Test API User 2019-01-03T15:20:36-07:00 API

Delete an annotation

$databaseId = 1
$annotationId = 148
$annotationURL = $baseURL + "databases/$databaseId/annotations/$annotationId"
Try {
  Write-Host "Deleting Annotation with id of $annotationID..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $annotationURL  -Method DELETE -Headers $dpaHeader -TimeoutSec 60
  Write-Host "Annotation with id of $annotationID deleted`r`n"
}
Catch {
  $_.Exception.ToString()
}

# This will print out data like this:
Deleting Annotation with id of 148...
Annotation with id of 148 deleted

Database Registration examples

The examples below show how to use all Database Registration calls.

Register and unregister a SQL Server database instance for monitoring

This example registers a new SQL Server database instance, waits 60 seconds, and then unregisters the database instance.

#----------------------------------------------------------
# Register a SQL Server database instance for monitoring.
#----------------------------------------------------------
$registrationURL = $baseURL + "databases/register-monitor"
$body = @{"databaseType" = "SQLSERVER";
          "serverName" = "127.0.0.1";
          "port" = "1433";
          "sysAdminUser" = "sa";
          "sysAdminPassword" = "Password";
          "monitoringUser" = "dpa_test_m";
          "monitoringUserPassword" = "Password";
          "monitoringUserIsNew" = $true;
          "displayName" = "DPA_SQL2K12"} | ConvertTo-Json
Try {
  Write-Host "Registering Database..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $newDbId = $dpaResponse.databaseId
}
Catch {
  $_.Exception.ToString()
}

Write-Host "Waiting 60 seconds...`r`n"
Start-Sleep -s 60

#----------------------------------------------------------
# Un-register the SQL Server database instance.
#----------------------------------------------------------
if ($newDbId) {
  $registrationURL = $baseURL + "databases/unregister-monitor"
  $body = @{"databaseId" = $newDbId;
            "removeMonitoringUser" = $true;
            "removeDatabaseObjects" = $true;
            "sysAdminUser" = "sa";
            "sysAdminPassword" = "Password"} | ConvertTo-Json
  Try {
    Write-Host "Registering Database..."
    $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
    $dpaResponse = $dpaResponseJSON.data
    $dpaResponse | Format-Table -AutoSize
  }
  Catch {
    $_.Exception.ToString()
  }
}

# This will print out data like this:
Registering Database...

databaseId result
---------- ------
        70 SUCCESS

Waiting 60 seconds...

Unregistering Database...

databaseId result
---------- ------
        70 SUCCESS

Register and unregister an Oracle database instance for monitoring

This example registers a new Oracle database instance, waits 60 seconds, and then unregisters the database instance.

#----------------------------------------------------------
# Register an Oracle database instance for monitoring.
#----------------------------------------------------------
$registrationURL = $baseURL + "databases/register-monitor"
$body = @{"databaseType" = "ORACLE";
          "serverName" = "127.0.0.1";
          "serviceNameOrSID" = "DPA_ORA11R1";
          "port" = "1521";
          "sysAdminUser" = "system";
          "sysAdminPassword" = "Password";
          "sysPassword" = "Password";
          "monitoringUser" = "dpa_test_m";
          "monitoringUserPassword" = "Password";
          "monitoringUserIsNew" = $true;
          "monitoringUserTableSpace" = "USERS";
          "monitoringUserTempTableSpace" = "TEMP";
          "oracleEBusinessEnabled" = $false;
          "displayName" = "DPA_ORA11R1"} | ConvertTo-Json
Try {
  Write-Host "Registering Database..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $newDbId = $dpaResponse.databaseId
}
Catch {
  $_.Exception.ToString()
}

Write-Host "Waiting 60 seconds...`r`n"
Start-Sleep -s 60

#----------------------------------------------------------
# Un-register the Oracle database instance.
#----------------------------------------------------------
if ($newDbId) {
  $registrationURL = $baseURL + "databases/unregister-monitor"
  $body = @{"databaseId" = $newDbId;
            "removeMonitoringUser" = $true;
            "removeDatabaseObjects" = $true;
            "sysAdminUser" = "system";
            "sysAdminPassword" = "Password"} | ConvertTo-Json
  Try {
    Write-Host "Unregistering Database..."
    $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
    $dpaResponse = $dpaResponseJSON.data
    $dpaResponse | Format-Table -AutoSize
  }
  Catch {
    $_.Exception.ToString()
  }
}

# This will print out data like this:
Registering Database...

databaseId result
---------- ------
        71 SUCCESS

Waiting 60 seconds...

Unregistering Database...

databaseId result
---------- ------
        71 SUCCESS

Register and unregister a MySQL database instance for monitoring

This example registers a new MySQL database instance, waits 60 seconds, and then unregisters the database instance.

#----------------------------------------------------------
# Register a MySQL database instance for monitoring.
#----------------------------------------------------------
$registrationURL = $baseURL + "databases/register-monitor"
$body = @{"databaseType" = "MYSQL";
          "serverName" = "127.0.0.1";
          "port" = "3306";
          "sysAdminUser" = "root";
          "sysAdminPassword" = "Password";
          "monitoringUser" = "dpa_test_m";
          "monitoringUserPassword" = "Password";
          "monitoringUserIsNew" = $true;
          "displayName" = "DPA_MYSQL56"} | ConvertTo-Json
Try {
  Write-Host "Registering Database..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $newDbId = $dpaResponse.databaseId
}
Catch {
  $_.Exception.ToString()
}

Write-Host "Waiting 60 seconds...`r`n"
Start-Sleep -s 60

#----------------------------------------------------------
# Un-register the MySQL database instance.
#----------------------------------------------------------
if ($newDbId) {
	$registrationURL = $baseURL + "databases/unregister-monitor"
	$body = @{"databaseId" = $newDbId;
              "removeMonitoringUser" = $true;
              "removeDatabaseObjects" = $true;
              "sysAdminUser" = "root";
              "sysAdminPassword" = "Password"} | ConvertTo-Json
  Try {
    Write-Host "Registering Database..."
    $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
    $dpaResponse = $dpaResponseJSON.data
    $dpaResponse | Format-Table -AutoSize
  }
  Catch {
    $_.Exception.ToString()
  }
}

# This will print out data like this:
Registering Database...

databaseId result
---------- ------
        72 SUCCESS

Waiting 60 seconds...

Unregistering Database...

databaseId result
---------- ------
        72 SUCCESS

Database Custom Properties examples

The examples below show how to use Database Custom Properties calls. Custom property values can be included in custom email templates for alert notifications.

Create a custom property

This script creates a custom property and defines its name and description.

$propertyName = "Location"
$propertyDescription = "Location of the database server"
$createPropertyURL = $baseURL + "databases/properties"
$body = @{"name" = $propertyName; "description" = $propertyDescription;} | ConvertTo-Json

Try {
  Write-Host "Creating custom property ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $createPropertyURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $propertyId = $dpaResponse.id
} Catch {
    $_.Exception.ToString()
}

#This will print out data like this:
id    name     description
--    ----     -----------
10434 Location Location of the database server

Create a custom property value

This script creates a value for the custom property created by the previous script.

property_id = 1
property_value = "New York"
$createValueURL = $baseURL + "databases/properties/" + $propertyId + "/values"
$body = $propertyValue | ConvertTo-Json

Try {
  Write-Host "Creating custom property value ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $createValueURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
} Catch {
  $_.Exception.ToString()
}

#This will print out data like this:
id value
-- -----
1  "New York"

Assign a property value to a monitored database instance

This script assigns a property value to a monitored database instance.

property_id = 1
property_value_id = 1
$assignPropertyValueURL = $baseURL + "databases/" + $databaseId + "/properties/" + $propertyId + "/values/" + $propertyValueId;

Try {
  Write-Host "Assigning custom property value ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $assignPropertyValueURL -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  Write-Host "Custom property value assigned to the DB with ID: $databaseId`r`n"
} Catch {
  $_.Exception.ToString()
}

#This will print out data like this:
Custom property value assigned to the DB with ID: 1

Get all information about properties

This script returns information about all custom properties and their values.

$getPropertiesURL = $baseURL + "databases/properties?require=assignment"

Try {
  Write-Host "Getting custom property information ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $getPropertiesURL -Method GET -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
} Catch {
  $_.Exception.ToString()
}

#This will print out data like this:
Getting custom property information ...
id name     description                     values                                      unassigned
-- ----     -----------                     ------                                      ----------
1  Location Location of the database server {@{id=1; value="New York"; assignment=[1]}} {4, 8} 

Delete a custom property

This script deletes a custom property.

property_id = 1
$deletePropertyURL = $baseURL + "databases/properties/" + $propertyId

Try {
  Write-Host "Deleting custom property ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $deletePropertyURL -Method DELETE -Headers $dpaHeader -TimeoutSec 60
  Write-Host "Custom property with id of $propertyID deleted`r`n"
} Catch {
  $_.Exception.ToString()
}

#This will print out data like this:
Custom property with id of 1 deleted

Full working script

The following script combines all of the examples shown above into a script that can be run.

#----------------------------------------------------------
# Examples:
# - Get an access token
# - Database Monitor Examples
#   - Get Monitor Information for a single database
#   - Start or Stop monitoring a database instance given its database ID
#   - Get Monitor Information for all databases
#   - Start monitoring for all database instances
#   - Stop monitoring for all database instances
#   - ERROR: Get Monitor Information for a database that doesn't exist
#   - ERROR: Start a database that doesn't exist
# - Licensing Examples
#   - Get the currently installed license information
#   - Get License Information for a single database
#   - Update License Information for a single database
# - Annotation Examples
#   - Gets a List of annotations for the last 30 days
#   - Create a new annotation
#   - Delete an annotation
# - Registration Examples
#   - Register a MySQL database instance for monitoring
#   - Un-register the MySQL database instance
#----------------------------------------------------------

#----------------------------------------------------------
# Configure the variables below for the DPA Host
#----------------------------------------------------------
$baseURL = "https://localhost:8124/iwc/api/"
$refreshToken = "eyJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJNeVRva2VuIiwiaXN..."
$databaseId = 1
#----------------------------------------------------------
# Nothing to configure below this line
#----------------------------------------------------------

#----------------------------------------------------------
# Function to parse the Response Data from DPA and print
# out the error information
#----------------------------------------------------------
Function handleError ($thisError) {
  Write-Host "--------------------------------------------------------------------" -ForegroundColor Red
  Write-Host "Caught Exception at line:" $_.InvocationInfo.ScriptLineNumber -ForegroundColor Red
  if ($_.Exception.Response) {
    $streamReader = [System.IO.StreamReader]::new($_.Exception.Response.GetResponseStream())
    $errResp = $streamReader.ReadToEnd()
    $streamReader.Close()
  }
  if ($errResp) {
    # This will format the JSON
    $errResp = $errResp | ConvertFrom-Json | ConvertTo-Json -Depth 100
    Write-Host $thisError.Exception.Message -ForegroundColor Red
    Write-Host "Response:`r`n$errResp" -ForegroundColor Red
  }
  else {
    Write-Host $_.Exception.ToString() -ForegroundColor Red
  }
  Write-Host "--------------------------------------------------------------------" -ForegroundColor Red
}

#----------------------------------------------------------
# Adding certificate exception to prevent API errors
# Uncomment this if you are getting trust errors and would
# like to run with self-signed certificates.
#----------------------------------------------------------
# add-type @"
#   using System.Net;
#   using System.Security.Cryptography.X509Certificates;
#   public class TrustAllCertsPolicy : ICertificatePolicy {
#     public bool CheckValidationResult(
#       ServicePoint srvPoint, X509Certificate certificate,
#       WebRequest request, int certificateProblem) {
#       return true;
#     }
#   }
# "@
# [System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

#----------------------------------------------------------
# Get an access token
#----------------------------------------------------------
$authTokenURL = $baseURL + "security/oauth/token"
$body = @{"grant_type" = "refresh_token"
          "refresh_token" = "$refreshToken"}
Try {
  Write-Host "Getting Access Token..."
  $dpaAuthResponse = Invoke-RestMethod -Uri $authTokenURL -Method POST -Body $body
  $dpaAuthResponse | Format-List
}
Catch {
  handleError $Error[0]
  Write-Host 'Error getting authentication token, cannot continue' -ForegroundColor Red
  return
}

# If successful we will create our headers to be used for all API calls
$tokenType = $dpaAuthResponse.token_type
$accessToken = $dpaAuthResponse.access_token
$dpaHeader = @{}
$dpaHeader.Add("Accept", "application/json")
$dpaHeader.Add("Content-Type", "application/json;charset=UTF-8")
$dpaHeader.Add("Authorization", "$tokenType $accessToken")

#----------------------------------------------------------
# Database Monitor Examples
#----------------------------------------------------------

# Get Monitor Information for a single database
$monitorURL = $baseURL + "databases/$databaseId/monitor-information"
Try {
  Write-Host "Get Monitor Information for database with id of $databaseId..."
  $monitorJSON = Invoke-RestMethod -Method Get -Uri $monitorURL -Headers $dpaHeader -TimeoutSec 60
  $monitor = $monitorJSON.data
  $monitor | Format-Table -AutoSize
}
Catch {
  handleError $Error[0]
}

# Start or Stop monitoring a database instance given its database ID.
# If it is already running stop it and then restart it
# If it is not running start it and then stop it
$monitorURL = $baseURL + "databases/$databaseId/monitor-status"
if ($monitor.monitorState -eq "Monitor Running") {
  $changeCommand = "STOP"
  $revertCommand = "START"
}
elseif ($monitor.monitorState -eq "Monitor Stopped") {
  $changeCommand = "START"
  $revertCommand = "STOP"
}
Try {
  Write-Host "$changeCommand Monitor for database $databaseId..."
  $command = @{"command" = $changeCommand} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
  $result = $monitorJSON.data
  Write-Host "Result: $result"
  Write-Host "Waiting 15 seconds...`r`n"
  Start-Sleep -s 15

  Write-Host "$revertCommand Monitor for database $databaseId..."
  $command = @{"command" = $revertCommand} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
  $result = $monitorJSON.data
  Write-Host "Result: $result"
  Write-Host "Waiting 15 seconds...`r`n"
  Start-Sleep -s 15
}
Catch {
  handleError $Error[0]
}

# Get Monitor Information for all databases
$monitorURL = $baseURL + "databases/monitor-information"
Try {
  Write-Host "Get Monitor Information for all databases..."
  $monitorListJSON = Invoke-RestMethod -Method Get -Uri $monitorURL -Headers $dpaHeader -TimeoutSec 60
  $monitorList = $monitorListJSON.data
  $monitorList | Format-Table -AutoSize

  # Keep a list of running or started monitors to be used later
  $runningIds = @()
  foreach ($monitor in $monitorList) {
    if ($monitor.monitorState -eq "Monitor Running" -or
      $monitor.monitorState -eq "Monitor Start No License" -or
      $monitor.monitorState -like '*Start*')
    {
      $runningIds += $monitor.dbId
    }
  }
  Write-Host "Running Monitors: $runningIds`r`n"
}
Catch {
  handleError $Error[0]
}

# Start monitoring all database instances.
$monitorURL = $baseURL + "databases/monitor-status"
Try {
  Write-Host "Starting all Monitors..."
  $command = @{"command" = "START"} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
  $result = $monitorJSON.data
  Write-Host "Result: $result"
  Write-Host "Waiting 30 seconds...`r`n"
  Start-Sleep -s 30
}
Catch {
  handleError $Error[0]
}

# Stop monitoring all database instances.
Try {
  Write-Host "Stopping all Monitors..."
  $command = @{"command" = "STOP"} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
  $result = $monitorJSON.data
  Write-Host "Result: $result"
  Write-Host "Waiting 30 seconds...`r`n"
  Start-Sleep -s 30
}
Catch {
  handleError $Error[0]
}

# Try to put it back the way we found it by restarting the ones that were running
$command = @{"command" = "START"} | ConvertTo-Json
foreach ($dbId in $runningIds) {
  Try {
    $monitorURL = $baseURL + "databases/$dbId/monitor-status"
    Write-Host "Starting Monitor for database $dbId..."
    $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
    $result = $monitorJSON.data
    Write-Host "Result: $result`r`n"
  }
  Catch {
    handleError $Error[0]
  }
}

# Update the monitor database user password (Un-comment to use)
# $monitorURL = $baseURL + "databases/$databaseId/update-password"
# Try {
#   Write-Host "Update the Monitor password for database $databaseId..."
#   $command = @{"password" = "NewPassword!"} | ConvertTo-Json
#   $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
#   $result = $monitorJSON.data
#   Write-Host "Result: $result`r`n"
# }
# Catch {
#   handleError $Error[0]
# }

# Try to cause some errors...

# Get Monitor Information for a database that doesn't exist
$monitorURL = $baseURL + "databases/-1/monitor-information"
Try {
  Write-Host "Get Monitor Information for invalid database..."
  $monitorJSON = Invoke-RestMethod -Method Get -Uri $monitorURL -Headers $dpaHeader -TimeoutSec 60
}
Catch {
  handleError $Error[0]
}

# Start a database that doesn't exist
$monitorURL = $baseURL + "databases/-1/monitor-status"
Try {
  Write-Host "START Monitor for invalid database..."
  $command = @{"command" = "START"} | ConvertTo-Json
  $monitorJSON = Invoke-RestMethod -Method Put -Uri $monitorURL -Body $command -Headers $dpaHeader -TimeoutSec 60
}
Catch {
  handleError $Error[0]
}

#----------------------------------------------------------
# Licensing Examples
#----------------------------------------------------------

# Get the currently installed license information
$licenseURL = $baseURL + "databases/licenses/installed"

Try {
  Write-Host "Getting Installed license information with total amounts available for use and total amounts used..."
  $licenseListJSON = Invoke-RestMethod -Method Get -Uri $licenseURL -Headers $dpaHeader -TimeoutSec 60
  $licenseList = $licenseListJSON.data
  $licenseList | Format-Table -AutoSize
}
Catch {
  handleError $Error[0]
}

# Get License Information for a single database
$licenseURL = $baseURL + "databases/$databaseId/licenses"
Try {
  Write-Host "Getting current license information for the database instance with database ID of $databaseId."
  $licenseListJSON = Invoke-RestMethod -Method Get -Uri $licenseURL -Headers $dpaHeader -TimeoutSec 60
  $licenseInfo = $licenseListJSON.data
  $licenseInfo | Format-Table -AutoSize
}
Catch {
  handleError $Error[0]
}

# This will Update License Information for a single database setting the
# Performance License and the VM License to what it currently is.
# It should succeed but it should make no changes.
$dbProduct = $licenseInfo.performanceLicenseProduct
$vmProduct = $licenseInfo.vmLicenseProduct
$licenseAllocation = @{"performanceLicenseProduct" = $dbProduct;
                       "vmLicenseProduct" = $vmProduct} | ConvertTo-Json
Try {
  Write-Host "Updating license for database id $databaseId..."
  $licenseResultJSON = Invoke-RestMethod -Method Put -Uri $licenseURL -Body $licenseAllocation -Headers $dpaHeader -TimeoutSec 60
  $licenseResult = $licenseResultJSON.data
  Write-Host "New License Allocation result for the database instance with database ID of $databaseId."
  $licenseResult | Format-Table -AutoSize
}
Catch {
  handleError $Error[0]
}

#----------------------------------------------------------
# Annotation Examples
#----------------------------------------------------------

# Gets a List of annotations for the last 30 days
$annotationURL = $baseURL + "databases/$databaseId/annotations"

# Dates are in ISO 8601 format ( 2018-12-31T12:00:00-07:00 )
$endTime = Get-Date
$startTime = $endTime.AddDays(-30)
$startTime = [System.Web.HttpUtility]::UrlEncode($startTime.ToString("yyyy-MM-ddTHH\:mm\:ss.fffzzz"))
$endTime = [System.Web.HttpUtility]::UrlEncode($endTime.ToString("yyyy-MM-ddTHH\:mm\:ss.fffzzz"))

$request = [System.UriBuilder]$annotationURL
$request.Query = "startTime=$startTime&endTime=$endTime"
$annotationURL = $request.Uri

Try {
  Write-Host "Getting Annotations for the last 30 days..."
  $annotationListJSON = Invoke-RestMethod -Method Get -Uri $annotationURL -Headers $dpaHeader -TimeoutSec 60
  $annotationList = $annotationListJSON.data
  $annotationList | Format-Table -AutoSize
}
Catch {
  handleError $Error[0]
}

# Create a new annotation
# Dates are in ISO 8601 format, no millis ( 2018-12-31T12:00:00-07:00 )
$annotationURL = $baseURL + "databases/$databaseId/annotations"
$createTime = Get-Date
$createTime = $createTime.ToString("yyyy-MM-ddTHH\:mm\:sszzz")
$body = @{"title" = "API Test Title";
          "description" = "API Test Description";
          "createdBy" = "Test API User";
          "time" = "$createTime"} | ConvertTo-Json
Try {
  Write-Host "Creating Annotation..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $annotationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $annotationId = $dpaResponse.id
}
Catch {
  handleError $Error[0]
}

# Delete an annotation
if ($annotationId) {
  $annotationURL = $baseURL + "databases/$databaseId/annotations/$annotationId"
  Try {
    Write-Host "Deleting Annotation with id of $annotationID..."
    $dpaResponseJSON = Invoke-RestMethod -Uri $annotationURL  -Method DELETE -Headers $dpaHeader -TimeoutSec 60
    Write-Host "Annotation with id of $annotationID deleted`r`n"
  }
  Catch {
    handleError $Error[0]
    }
  }

#----------------------------------------------------------
# Registration Examples
#----------------------------------------------------------

#----------------------------------------------------------
# Register a SQL Server database instance for monitoring.
#----------------------------------------------------------
$registrationURL = $baseURL + "databases/register-monitor"
$body = @{"databaseType" = "SQLSERVER";
          "serverName" = "127.0.0.1";
          "port" = "1433";
          "sysAdminUser" = "sa";
          "sysAdminPassword" = "Password";
          "monitoringUser" = "dpa_test_m";
          "monitoringUserPassword" = "Password";
          "monitoringUserIsNew" = $true;
          "displayName" = "DPA_SQL2K12"} | ConvertTo-Json
Try {
  Write-Host "Registering Database..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $newDbId = $dpaResponse.databaseId
}
Catch {
  handleError $Error[0]
}

Write-Host "Waiting 60 seconds...`r`n"
Start-Sleep -s 60

#----------------------------------------------------------
# Un-register the SQL Server database instance.
#----------------------------------------------------------
if ($newDbId) {
  $registrationURL = $baseURL + "databases/unregister-monitor"
  $body = @{"databaseId" = $newDbId;
            "removeMonitoringUser" = $true;
            "removeDatabaseObjects" = $true;
            "sysAdminUser" = "sa";
            "sysAdminPassword" = "Password"} | ConvertTo-Json
  Try {
    Write-Host "Unregistering Database..."
    $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
    $dpaResponse = $dpaResponseJSON.data
    $dpaResponse | Format-Table -AutoSize
  }
  Catch {
    handleError $Error[0]
  }
}

#----------------------------------------------------------
# Register an Oracle database instance for monitoring.
#----------------------------------------------------------
$registrationURL = $baseURL + "databases/register-monitor"
$body = @{"databaseType" = "ORACLE";
          "serverName" = "127.0.0.1";
          "serviceNameOrSID" = "DPA_ORA11R1";
          "port" = "1521";
          "sysAdminUser" = "system";
          "sysAdminPassword" = "Password";
          "sysPassword" = "Password";
          "monitoringUser" = "dpa_test_m";
          "monitoringUserPassword" = "Password";
          "monitoringUserIsNew" = $true;
          "monitoringUserTableSpace" = "USERS";
          "monitoringUserTempTableSpace" = "TEMP";
          "oracleEBusinessEnabled" = $false;
          "displayName" = "DPA_ORA11R1"} | ConvertTo-Json
Try {
  Write-Host "Registering Database..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $newDbId = $dpaResponse.databaseId
}
Catch {
  handleError $Error[0]
}

Write-Host "Waiting 60 seconds...`r`n"
Start-Sleep -s 60

#----------------------------------------------------------
# Un-register the Oracle database instance.
#----------------------------------------------------------
if ($newDbId) {
	$registrationURL = $baseURL + "databases/unregister-monitor"
	$body = @{"databaseId" = $newDbId;
              "removeMonitoringUser" = $true;
              "removeDatabaseObjects" = $true;
              "sysAdminUser" = "system";
              "sysAdminPassword" = "Password"} | ConvertTo-Json
  Try {
    Write-Host "Unregistering Database..."
    $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
    $dpaResponse = $dpaResponseJSON.data
    $dpaResponse | Format-Table -AutoSize
  }
  Catch {
    handleError $Error[0]
  }
}

#----------------------------------------------------------
# Register a MySQL database instance for monitoring.
#----------------------------------------------------------
$registrationURL = $baseURL + "databases/register-monitor"
$body = @{"databaseType" = "MYSQL";
          "serverName" = "127.0.0.1";
          "port" = "3306";
          "sysAdminUser" = "root";
          "sysAdminPassword" = "Password";
          "monitoringUser" = "dpa_test_m";
          "monitoringUserPassword" = "Password";
          "monitoringUserIsNew" = $true;
          "displayName" = "DPA_MYSQL56"} | ConvertTo-Json
Try {
  Write-Host "Registering Database..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $newDbId = $dpaResponse.databaseId
}
Catch {
  handleError $Error[0]
}

Write-Host "Waiting 60 seconds...`r`n"
Start-Sleep -s 60

#----------------------------------------------------------
# Un-register the MySQL database instance.
#----------------------------------------------------------
if ($newDbId) {
  $registrationURL = $baseURL + "databases/unregister-monitor"
  $body = @{"databaseId" = $newDbId;
            "removeMonitoringUser" = $true;
            "removeDatabaseObjects" = $true;
            "sysAdminUser" = "root";
            "sysAdminPassword" = "Password"} | ConvertTo-Json
  Try {
    Write-Host "Unregistering Database..."
    $dpaResponseJSON = Invoke-RestMethod -Uri $registrationURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
    $dpaResponse = $dpaResponseJSON.data
    $dpaResponse | Format-Table -AutoSize
  }
  Catch {
    handleError $Error[0]
  }
}

#----------------------------------------------------------
# Custom Property Examples
#----------------------------------------------------------
# Create custom property

$propertyName = "Location"
$propertyDescription = "Location of the database server"
$createPropertyURL = $baseURL + "databases/properties"
$body = @{"name" = $propertyName; "description" = $propertyDescription;} | ConvertTo-Json

Try {
  Write-Host "Creating custom property ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $createPropertyURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $propertyId = $dpaResponse.id
} 
Catch {
  handleError $Error[0]
}

#Create value of the custom property

$propertyValue = "New York"
$createValueURL = $baseURL + "databases/properties/" + $propertyId + "/values"
$body = $propertyValue | ConvertTo-Json

Try {
  Write-Host "Creating custom property value ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $createValueURL -Body $body -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  $propertyValueId = $dpaResponse.id
} 
Catch {
  handleError $Error[0]
}

#Assign property value to DB

$assignPropertyValueURL = $baseURL + "databases/" + $databaseId + "/properties/" + $propertyId + "/values/" + $propertyValueId;

Try {
  Write-Host "Assigning custom property value ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $assignPropertyValueURL -Method POST -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
  Write-Host "Custom property value assigned to the DB with ID: $databaseId`r`n"
} 
Catch {
  handleError $Error[0]
}

#Get all information about properties (including DB assignment)

$getPropertiesURL = $baseURL + "databases/properties?require=assignment"

Try {
  Write-Host "Getting custom property information ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $getPropertiesURL -Method GET -Headers $dpaHeader -TimeoutSec 60
  $dpaResponse = $dpaResponseJSON.data
  $dpaResponse | Format-Table -AutoSize
} 
Catch {
  handleError $Error[0]
}

#Delete custom property

$deletePropertyURL = $baseURL + "databases/properties/" + $propertyId

Try {
  Write-Host "Deleting custom property ..."
  $dpaResponseJSON = Invoke-RestMethod -Uri $deletePropertyURL -Method DELETE -Headers $dpaHeader -TimeoutSec 60
  Write-Host "Custom property with id of $propertyID deleted`r`n"
} 
Catch {
  handleError $Error[0]
}

# End of script