AWS Aurora - reading Cloudwatch logs with Powershell for QUERYs

aws_aurora_read_cloudwatch_logs.ps1

import-module awspowershell

# removing variables. Note that this will wipe out default region, credentials, etc, if before this line.
Remove-Variable -Name * -ErrorAction SilentlyContinue

Set-DefaultAWSRegion -Region us-west-1

$now = get-date

# For conversion from Unix Epoch 
$origin = New-Object -Type DateTime -ArgumentList 1970, 1, 1, 0, 0, 0, 0

# here, we use implicit credentials, so there's no $credentials
set-awscredentials -accesskey youraccesskey -secretkey yoursecretkey 

$myemail = "dev@null.com"

# 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 = 72
$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 "*/myprefix-*"}

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.

#$CW_Results = 
DO {
#write-host "CWToken $CW_NextToken"

$CW_RDS += 
Get-CWLFilteredLogEvent `
-LogGroupName $Specific_CW_Log_Group.LogGroupName `
-StartTime $Past_Unix_Timestamp `
-EndTime $Current_Unix_Timestamp `
-FilterPattern "QUERY" `
-Limit 2000 `
-NextToken $CW_NextToken
#FilterPattern can't use commas. ",QUERY," should show all create/truncate/drop, but we use QUERY instead

#unlike the regular logs, this one returns a normal powershell dataset - no need to parse it out, just query events
# $CW_RDS.Events # moved to outside the loop.

$CW_NextToken = $CW_RDS.NextToken
}WHILE ($CW_NextToken -ne $null)

}

# Regex-based filtering
$Log_Filters = "temporary table","truncate "
#We use this line to take our list of log filters and generate a regex that is then compared against
# https://blogs.technet.microsoft.com/heyscriptingguy/2011/02/18/speed-up-array-comparisons-in-powershell-with-a-runtime-regex/
# the replace is needed because I want an exact match of those commas, and the escape($_) does weird stuff to it
# 2018/05/09 removed [regex] at beginning as per https://stackoverflow.com/questions/50261383/powershell-3-regex-why-is-case-insensitive-not-working#50261506
$Log_Filter_regex = (($Log_Filters|foreach {[regex]::escape($_) -replace ",","\,"}) –join "|") 

# Applying the regex filter
$final_log = $CW_RDS.Events| where {$_.Message -inotmatch $Log_Filter_regex} `
| select @{Label='TimeStampHR';Expression={$origin.AddSeconds($_.TimeStamp/1000).ToLocalTime()}}, LogStreamName, message


# Making an HTML table 
# https://jamesdatatechq.wordpress.com/2014/12/23/how-to-create-an-html-table-from-powershell/
$Style = "
<style>
    TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
    TH{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
    TD{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
</style>
"
$error_table = $final_log| ConvertTo-HTML -AS Table -Fragment -PreContent "<h2>Cloudwatch Alerts as of $now</h2>"|Out-String 

#Save the HTML Web Page as a string so we can email it
[string]$email_table = (ConvertTo-HTML -head $Style -PostContent $error_table)

# Now send the mail
if ($final_log){
#Send email to $myemail. SMTP is a CNAME to the corporate SMTP server we use.
    Send-MailMessage -smtpserver smtp.mycompany.com `
    -To $myemail `
    -From "Do_Not_Reply_PoSH <dev@null.com>" `
    -Subject "[AWS] Cloudwatch errors in Prod as of $now" `
    -body $email_table -BodyAsHtml
}