AWS Aurora - reading errorlog files with Powershell

aws_aurora_read_errorlogs.ps1

###################################################
## Reading error logs for Aurora with Powershell ##
###################################################
# 1.01 - MDB 2018/05/30 Better trapping of time; there's a lot of rows that have no datetime that I want. Also added HTML email.
# 1.02 - mdb 2018/05/30 also dealing better with the regex filter. Recommend you convert it to a NOTMATCH; too many different error types
clear 

Remove-Variable -Name * -ErrorAction SilentlyContinue

$myemail="dev@null.com"

# Note that this one does NOT read the audit log, which means we can filter the log based on a normal timestamp.
# But it does filter messages added after the time given.

import-module awspowershell

$credentials = get-awscredentials -ProfileName mysavedprofilename
Set-DefaultAWSRegion -Region us-west-1

$now = (get-date).ToString('yyyy/MM/dd HH:mm:ss')
$final_log = @() #setting the array to prevent type errors when adding to the array
# Figure out what files we need to look at, since they have a "last written to" field. Despite the FLOOR, the Past_Unix_Timestamp is X hours ago, down to the second, from when it is run.
$hours_to_look_back = 1.25
$Past_Unix_Timestamp = [Math]::Floor([decimal](Get-Date((Get-Date).AddHours(-1*$hours_to_look_back)).ToUniversalTime()-uformat "%s")) * 1000
$Past_Timestamp  = (Get-Date((Get-Date).AddHours(-1*$hours_to_look_back)).ToUniversalTime())
#See far below for more on filters; be careful on what you filter. I'd probably use a NotMatch to ignore certain things
$Log_Filters = "[ERROR]","failed","Note","[Warning]"
#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/
# [regex] $a_regex = ‘(?i)^(‘ + (($a |foreach {[regex]::escape($_)}) –join “|”) + ‘)$’
# 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 "|") 


#removed this from the WHERE line below, though useful depending on what you're looking for: -and ($_.LogFileName -notlike '*audit*')
get-rdsdbinstance -Credential $credentials | `
where {$_.DBClusterIdentifier -like '*'}|% {
$instancename = $_.DBInstanceIdentifier
$instancename
get-rdsdblogfile -dbinstanceidentifier $instancename -Credential $credentials | `
where {($_.LastWritten -gt $Past_Unix_Timestamp) -and ($_.LogFileName -notlike '*slow*') `
-and ($_.LogFileName -notlike 'audit/*')
} | 
% {
$_.LogFileName
$LogFileName = $_.LogFileName
$Marker = 0

DO 
{
$AWSLogFile = Get-RDSDBLogFilePortion -DBInstanceIdentifier $instancename -LogFileName $_.LogFileName `
 -Credential $credentials -Marker $Marker -NumberOfLines 2000
#$AWSLogFile
if ($AWSLogFile.LogFileData)
{
$parsed_log = $AWSLogFile.LogFileData.split("`n`r")  #splitting it to multiple lines using the CRLF in the file

#Here, I try and parse the first 19 characters, which in the normal log, should be a timedatestamp.
# If it's either parseable and recent, or unparseable, I then filter and toss into a master array of data.
#https://social.technet.microsoft.com/Forums/ie/en-US/0a7cbba0-87fb-450d-b8e8-29aed0947fd7/cant-get-datetimetryparseexact-to-work-using-powershell?forum=winserverpowershell 
#https://stackoverflow.com/questions/2988880/extricate-a-substring-using-powershell
#https://blogs.technet.microsoft.com/heyscriptingguy/2014/12/19/powertip-convert-string-into-datetime-object/
#parsedDat is what we get back from TryParseExact - either a valid time or a big nothing.
$parsedDat=0 
$dateformat = 'yyyy-MM-dd HH:mm:ss'
$final_log += foreach($line in $parsed_log){
    #reset this each time so we don't get bad data
    $attemptedtime = $null
    #The actual substring, but unlike substring it won't throw an error if the line is empty or too short.
    [string]$attemptedtime = $line[0..18] -join ''
    #Trying to convert it to an actual date.
    $linetime = if([DateTime]::TryParseExact($attemptedtime, $dateformat, $null,'None',[ref]$parsedDat)){
        $parseddat
    }else{
         get-date #this way, anything that can't be parsed will be within our window
    }
    #Now we get recent lines, run it through the REGEX filter, and save it.  Use {1 -eq 1} to return all. 
    # There are a LOT of messages that don't have the right tags, so you might leave it off.
    if ($linetime -gt $Past_Timestamp) 
        { 
        $line | where {$_ -imatch $Log_Filter_regex}| `
        select @{LABEL="Instance"; EXPRESSION = {$instancename}}, `
                    @{LABEL="LogFileName"; EXPRESSION = {$LogFileName}}, `
                    @{LABEL="Message"; EXPRESSION = {$_}}
        }
    }
}
$Marker = $AWSLogFile.Marker
}WHILE ($AWSLogFile.AdditionalDataPending -eq $true)
 }
}


$final_log #you still need to get it into a format you can send; I'm using an HTML table
#echoing it out here so that the SQLAgent log has a record of what happened, if everything worked.

# 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>ErrorLog messages as of $now, starting at $Past_TimeStamp </h2>"|Out-String 
#Save the HTML Web Page
[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 <do_not_reply@dev.null>" `
    -Subject "[AWS] RDSDB Errors in Prod as of $now" `
    -body $string_log
"mail sent"
}