Collect CloudWatch metrics for a PostgreSQL RDS instance in AWS using PowerShell

CollectAWSRDSPostgreSQLCloudWatchData.ps1

# Author : Jayan Kandathil (Adobe Managed Services)
# Date : July 17, 2018
# https://docs.aws.amazon.com/AmazonCloudWatch/latest/APIReference/API_GetMetricStatistics.html
# https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/rds-metricscollected.html
# https://aws.amazon.com/blogs/developer/writing-and-archiving-custom-metrics-using-amazon-cloudwatch-and-aws-tools-for-powershell/

# Get authentication credentials
Set-AWSCredential -AccessKey YOURAWSACCESSKEY -SecretKey yOUR354aws39875358sECRET93453-96kEY -SessionToken yOURawssESSIONtOKENEaDKGld+XcXoR/yOUtHINKtHISiSreAL2XD1k9tgQ/TKIjhXXTXxM

# Set Test Start Time and Test End Time (remember to adjust for your time zone - CloudWatch data is in UTC)
$start = "2018-07-16T12:30:00Z"
$end = "2018-07-17T12:30:00Z"

$region = "us-east-1"
$rdsinstanceid = "gameofthrones-hbo-prod1"
$logfilepath = "C:\TEMP\"
$logfile = "RDS-Instance_Metadata.txt"

Set-DefaultAWSRegion -Region $region
$rdsinstance = Get-RDSDBInstance -DBInstanceIdentifier $rdsinstanceid

$dbparametergroups = $rdsinstance.DBParameterGroups
foreach ($dbparametergroup in $dbparametergroups)
{
	$dbparametergroup = $dbparametergroup.DBParameterGroupName
}

$dimension = New-Object Amazon.CloudWatch.Model.Dimension
$dimension.set_Name("DBInstanceIdentifier")
$dimension.set_Value($rdsinstanceid)

Add-Content -Path $logfilepath$logfile -Value $rdsinstance.DBInstanceIdentifier
Add-Content -Path $logfilepath$logfile -Value $rdsinstance.DBInstanceClass
Add-Content -Path $logfilepath$logfile -Value $rdsinstance.DBInstanceStatus
Add-Content -Path $logfilepath$logfile -Value $rdsinstance.DBName
Add-Content -Path $logfilepath$logfile -Value $rdsinstance.Engine
Add-Content -Path $logfilepath$logfile -Value $rdsinstance.EngineVersion
Add-Content -Path $logfilepath$logfile -Value $rdsinstance.StorageType
Add-Content -Path $logfilepath$logfile -Value $rdsinstance.AllocatedStorage
Add-Content -Path $logfilepath$logfile -Value $dbparametergroup

# ---
# CPU
# ---

$logfile = "RDS-CPU_Utilization_Percent.csv"

# Get CPU utilization by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName CPUUtilization -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# --------------------
# Database Connections
# --------------------

$logfile = "RDS-DB_Connections.csv"

# Get max number of database connections by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName DatabaseConnections -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# ----------------
# Freeable Memory
# ----------------

$logfile = "RDS-Freeable_Memory_Bytes.csv"

# Get minimum freeable memory by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName FreeableMemory -StartTime $start -EndTime $end -Period 60 -Statistic Minimum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Minimum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Minimum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# ------------
# Free Storage
# ------------

$logfile = "RDS-Free_Storage_Bytes.csv"

# Get minimum available storage space by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName FreeStorageSpace -StartTime $start -EndTime $end -Period 60 -Statistic Minimum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Minimum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Minimum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# -----------------------
# Max Used Transaction ID
# -----------------------

$logfile = "RDS-Max_Used_Transaction_ID.csv"

# Get maximum used transaction ID by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName MaximumUsedTransactionIDs -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# -------------------------
# gp2 Burst Balance Credits
# -------------------------

$logfile = "RDS-gp2_Burst_Balance_Credits_Percent.csv"

# Get minimum gp2 burst balance credits by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName BurstBalance -StartTime $start -EndTime $end -Period 60 -Statistic Minimum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Minimum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Minimum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# --------------------------
# Network Receive Throughput
# --------------------------

$logfile = "RDS-Network_Receive_Throughput_BytesPerSec.csv"

# Get maximum network receive throughput by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName NetworkReceiveThroughput -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# --------------------------
# Network Transmit Throughput
# --------------------------

$logfile = "RDS-Network_Transmit_Throughput_BytesPerSec.csv"

# Get maximum network transmit throughput by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName NetworkTransmitThroughput -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# --------------------
# Storage READ Latency
# --------------------

$logfile = "RDS-Storage_READ_Latency_Seconds.csv"

# Get maximum READ latency by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName ReadLatency -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# --------------------
# Storage WRITE Latency
# --------------------

$logfile = "RDS-Storage_WRITE_Latency_Seconds.csv"

# Get maximum WRITE latency by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName WriteLatency -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# -----------------------
# Storage READ Throughput
# -----------------------

$logfile = "RDS-Storage_READ_Throughput_BytesPerSec.csv"

# Get maximum READ throughput by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName ReadThroughput -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# -----------------------
# Storage WRITE Throughput
# -----------------------

$logfile = "RDS-Storage_WRITE_Throughput_BytesPerSec.csv"

# Get maximum WRITE throughput by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName WriteThroughput -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# ------------------
# Storage READ IOPS
# ------------------

$logfile = "RDS-Storage_READ_IOPS.csv"

# Get maximum READ IOPS by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName ReadIOPS -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# ------------------
# Storage WRITE IOPS
# ------------------

$logfile = "RDS-Storage_WRITE_IOPS.csv"

# Get maximum WRITE IOPS by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName WriteIOPS -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# -----------------
# Disk Queue Depth
# -----------------

$logfile = "RDS-Disk_Queue_Depth.csv"

# Get maximum WRITE IOPS by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName DiskQueueDepth -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# ---------------------------
# Oldest Replication Slot Lag
# ---------------------------

$logfile = "RDS-Oldest_Replication_Slot_Lag_MB.csv"

# Get minimum available storage space by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName OldestReplicationSlotLag -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# ---------------------------------------------------
# Replica Lag (applies only to READ Replica instances)
# ---------------------------------------------------

$logfile = "RDS-Replica_Lag_Seconds.csv"

# Get minimum available storage space by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName ReplicaLag -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# ---------------------------
# Replication Slot Disk Usage
# ---------------------------

$logfile = "RDS-Replication_Slot_Disk_Usage_MB.csv"

# Get minimum available storage space by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName ReplicationSlotDiskUsage -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# -----------
# Swap Usage
# -----------

$logfile = "RDS-Swap_Usage_Bytes.csv"

# Get minimum available storage space by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName SwapUsage -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# ---------------------------
# Transaction Logs Disk Usage
# ---------------------------

$logfile = "RDS-Transaction_Logs_Disk_Usage_MB.csv"

# Get minimum available storage space by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName TransactionLogsDiskUsage -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}

# ---------------------------
# Transaction Logs Generation
# ---------------------------

$logfile = "RDS-Transaction_Logs_Generated_MBPerSec.csv"

# Get minimum available storage space by the minute, for the duration of the test
$data = Get-CWMetricStatistic -Dimension $dimension -Namespace AWS/RDS -MetricName TransactionLogsGeneration -StartTime $start -EndTime $end -Period 60 -Statistic Maximum
Write-Host -foregroundcolor Yellow "Number of datapoints is " $data.Datapoints.Count

# Sort by timestamp
$datapoints = $data.Datapoints | Sort-Object -Property Timestamp

foreach ($datapoint in $datapoints)
{
	# Print the observations to console	
	[single]$datapoint.Maximum

	$logentry = [string]$datapoint.Timestamp + "," + [single]$datapoint.Maximum

	# Log the observations to file
	Add-Content -Path $logfilepath$logfile -Value $logentry
}