# encoding: utf-8
# api: powershell
# title: Search SQL Error Log
# description: #############################################################################################
# version: 0.1
# type: function
# author: Rob Sewell
# license: CC0
# function: Search-SQLErrorLog
# x-poshcode-id: 4456
# x-archived: 2014-08-18T21:33:37
# x-published: 2014-09-11T11:47:00
#
# #
# NAME: Search-SQLErrorLog.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:22/07/2013
# #
# COMMENTS: Load function for Searching SQL Error Log
# # #
#
#############################################################################################
#
# NAME: Search-SQLErrorLog.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:22/07/2013
#
# COMMENTS: Load function for Searching SQL Error Log
#
<# Usage: Search-SQLErrorLog backup fade2black
Output to file
Search-SQLErrorLog backup fade2black|Out-File c:\temp\error.txt
Email
$ErrorLog = Search-SQLErrorLog FADE2BLACK
Send-MailMessage -SmtpServer Mail.Server -to DBA@MyWork.com -Subject SQL Error Log Search for $Search -Body $ErrorLog
Email as attachment
$ErrorLog = Search-SQLErrorLog FADE2BLACK|Out-File c:\temp\log.txt
$Body = @"
Dear DBA,
Please find attached the Error Log for $Search.
You're Welcome.
The Beard
"@
Send-MailMessage -SmtpServer Mail.Server -to DBA@MyWork.com -Subject "SQL Error Log" `
-Body $Body -Attachments $ErrorLog
#>
# 覧覧覧覧覧覧覧覧覧覧覧覧
Function Search-SQLErrorLog ([string] $SearchTerm , [string] $SQLServer)
{
$Search = '*' + $SearchTerm + '*'
$server = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer
$server.ReadErrorLog(6)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text| Format-Table -Wrap -AutoSize
$server.ReadErrorLog(5)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text| Format-Table -Wrap -AutoSize
$server.ReadErrorLog(4)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text| Format-Table -Wrap -AutoSize
$server.ReadErrorLog(3)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text| Format-Table -Wrap -AutoSize
$server.ReadErrorLog(2)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text| Format-Table -Wrap -AutoSize
$server.ReadErrorLog(1)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text| Format-Table -Wrap -AutoSize
$server.ReadErrorLog(0)| Where-Object {$_.Text -like $Search} | Select LogDate, ProcessInfo, Text| Format-Table -Wrap -AutoSize
}