Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save jayankandathil/dacdc300545a1e87ec6d5242bd646a90 to your computer and use it in GitHub Desktop.
Save jayankandathil/dacdc300545a1e87ec6d5242bd646a90 to your computer and use it in GitHub Desktop.
Collect CloudWatch metrics for a PostgreSQL RDS instance in AWS using PowerShell
# 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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment