Powershell script to report SQL Error Log

23 February,2017 by Jack Vamvas

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 = "jack@sqlserver-dba.com"
$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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Powershell script to report SQL Error Log


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer