[AWS] Aurora - reading the errorlog files with powershell

Been working on monitoring.  For some reason, when you tell Aurora to send errorlogs to Cloudwatch, all it sends are the Audit Logs, which will tell you that code had changed, etc, but doesn't (!?!?!??!!!) actually put your logs in Cloudwatch.  I don't understand it, so I built this process to look through logs and return the data.  The next step would be to format it and either upload to Cloudwatch manually, or log it, or send email.  Whatever works for you.  You be you.  :D

	###################################################
	## 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"
	}

view raw aws_aurora_read_errorlogs.ps1 hosted with ❤ by GitHub