Powershell script to report SQL Error Log

When I first started using Powershell scripting to monitor and report on SQL Server Error Logs - I used to use the script on Powershell_ErrorLogs.  Since then I've made some adjustments , adding email send functionality, sql query change,  and stylesheet functionality

set-location E:\health\SQL_Server


$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath


$serverpath=$basepath + "\config\instances_prod.txt"
$outputfile="\logs\sql_server_health_SQL_Server_Logs_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile



$emailFrom = "jack@sqlserver_dba.com"
$emailTo = "[email protected]"
$subject = "SQL Server Logs Critical in the last 24 hrs - Prod "
$body = "SQL Server Logs Critical in the last 24 hrs - Prod "
$smtpServer = "my.smtp.net"
$filePath = ""


#invoke stylesheet
. .\modules\stylesheet.ps1

#intro smtp function 
. .\modules\smtp.ps1

$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $serverpath)
{
    $instance
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "DECLARE @sqlStatement1 VARCHAR(200)
		SET @sqlStatement1 = 'master.dbo.xp_readerrorlog'
            BEGIN		
            CREATE TABLE #Errors_t2 (LogDate DATETIME,ProcessInfo NVARCHAR(50),vchMessage varchar(2000))
		INSERT #Errors_t2 EXEC @sqlStatement1
		SELECT @@servername as Server , LogDate, RTRIM(LTRIM(vchMessage)) as Message FROM #Errors_t2 WHERE 
		([vchMessage] like '%err%'
		or [vchMessage] like '%warn%'
		or [vchMessage] like '%kill%'
		or [vchMessage] like '%dead%'
		or [vchMessage] like '%cannot%'
		or [vchMessage] like '%could%'
		or [vchMessage] like '%fail%'
		or [vchMessage] like '% not %'
		or [vchMessage] like '%stop%'
		or [vchMessage] like '%terminate%'
		or [vchMessage] like '%bypass%'
		or [vchMessage] like '%roll%'
		or [vchMessage] like '%truncate%'
		or [vchMessage] like '%upgrade%'
		or [vchMessage] like '%victim%'
		or [vchMessage] like '%recover%'
		or [vchMessage] like '%I/O requests taking longer than%'
            or [vchMessage] like '%Timeout%' )
		AND [vchMessage] not like '%errorlog%'
		AND [vchMessage] not like '%dbcc%'
            AND DATEDIFF(hh, LogDate, GETDATE()) <= 24
	      DROP TABLE #Errors_t2
	       END"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()

    
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle -body "SQL Server Logs Critical in the last 24 hrs" | Set-Content $outputfilefull  


$filepath = $outputfilefull  
#Call smtp Function 
sendEmail $emailFrom $emailTo $subject $body $smtpServer $filePath

To reference the  "smtp.ps1"  - create a new powershell file and place the function found on : Send email from Powershell with attachment .   You don't need to keep the file in the modules folder , you can place the file in the same folder and reference. You are passing certain values into this function , including the reference to the attachment , which is added to the email

To reference the "stylesheet.ps1"    - create a new powershell file with that name , and place in code similar to:
$reportstyle="<!--
TABLE{border-width: 2px;border-style: solid}"
$style=$style + "TD{border-width: 2px;border-style: solid}"
$style=$style + "
-->"

Alternatively place the code directly in the script - such as on Powershell to HTML - SQL Server DBA.

Placing the code directly in the script , has the disadvantage of not being reusable , by other scripts

For an alternative method to read SQL Server Error Logs with powershell use : Read SQL Server Error Logs with Powershell and SMO