[AWS] Querying CloudWatch logs using powershell to find DDL changes in your Aurora instance.

Did I get enough buzzwords in that post title? 

So, I've been working on an AWS Aurora MySQL project lately (yes, you need to qualify Aurora, because these days there's a Postgres variant).

One problem we had was getting info out of the logs.  Querying the logs is possible from within MySQL, but time consuming.  Ditto querying the logs that are being saved to disk.  We used the AWS directions (https://aws.amazon.com/blogs/database/monitoring-amazon-aurora-audit-events-with-amazon-cloudwatch/) to get our logs to dump into CloudWatch, which offers a Powershell cmdlet to filter them (although it limits to 1mb at a time, hence the DO WHILE with a low rowcount).  Unfortunately, I didn't find any actual working examples. 

So while this may not be the best, it does work. 

NOTE: results currently go to an out-gridview window.  Obviously, change that to what you need, or:
* remove the write-host
* remove the ogv line
* wrapper the entire DO WHILE into a variable then return that at the end. BE CAREFUL DOING THIS. I tried it with a decent-sized results set (30k) and got a weird error back:

Get-CWLFilteredLogEvent : Error unmarshalling response back from AWS. Request ID:

	import-module awspowershell
	#You should have already saved your credentials locally using:
	# set-awscredentials -accesskey myaccesskey -secretkey mysecretkey -StoreAs MyDeveloper
	$credential = get-awscredentials -ProfileName MyDeveloper
	# Get the current timestamp
	$Current_Unix_Timestamp = [Math]::Floor([decimal](Get-Date(Get-Date).ToUniversalTime()-uformat "%s")) * 1000
	# Get the timestamp to start at.
	$hours_to_look_back = 48
	$Past_Unix_Timestamp = [Math]::Floor([decimal](Get-Date((Get-Date).AddHours(-1*$hours_to_look_back)).ToUniversalTime()-uformat "%s")) * 1000
	# Get a list of all of our CloudWatch log groups
	$All_CW_RDS_Logs = get-CWLLogGroup -LogGroupNamePrefix "/aws/rds" | where {$_.LogGroupName -like "*/myproject*"}
	foreach ($Specific_CW_Log_Group in $All_CW_RDS_Logs) {
	write-host $Specific_CW_Log_Group.LogGroupName
	$CW_NextToken = $null # reset for each log group. Required for NextToken to work
	#Using $null for NextToken means we can use the same pattern as for regular logs
	#NOTE: this hangs if the FilterPattern is invalid. Which apparently includes commas, backslashes, etc.
	DO {
	$CW_RDS =
	Get-CWLFilteredLogEvent `
	-LogGroupName $Specific_CW_Log_Group.LogGroupName `
	-StartTime $Past_Unix_Timestamp `
	-EndTime $Current_Unix_Timestamp `
	-FilterPattern "QUERY" `
	-Limit 500 `
	-NextToken $CW_NextToken
	#FilterPattern can't use commas. ",QUERY," should show all create/truncate/drop, but we must use QUERY instead
	#unlike the regular logs, this one returns a normal powershell dataset - nothing left to do
	$CW_RDS.Events|ogv
	$CW_NextToken = $CW_RDS.NextToken
	}WHILE ($CW_NextToken -ne $null)
	}

view raw AWS_CW_RDS_Log_with_Filter.ps1 hosted with ❤ by GitHub