Examples of using PowerShell scripts to 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.

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 = $startTime.ToString("yyyy-MM-ddTHH\:mm\:ss.fffzzz")
$endTime = $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

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 = $startTime.ToString("yyyy-MM-ddTHH\:mm\:ss.fffzzz")
$endTime = $endTime.ToString("yyyy-MM-ddTHH\:mm\:ss.fffzzz")

$request = [System.UriBuilder]$annotationURL
$request.Query = "startTime=$startTimeZZZendTime=$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]
	}
}
 
# End of script