How to scan SQL Server error log (and ignore some errors) with PowerShell?

Question

I need a PowerShell script (2.0 compatible) to scan SQL Server 2008 R2 and later error logs. I need to have a list of phrases to search for, and a list of phrases to exclude.

param ([String]$instanceName=$(throw "Instance name was not supplied"))

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')|Out-Null;
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instanceName;
$r = $sqlServer.ReadErrorLog();

$find = "Error:","Failed";
$exclude = "Error: 0x2098";

# need to do something with $r here and involve $find and $exclude

So, for example I want to find all lines in the ERRORLOG that contain Error: and Failed, but exclude the ones in the $exclude array. Any ideas?

Answer 1

You can find a lot of info on this here: Use PowerShell to Parse SQL Server 2012 Error Logs

But one way to do what you are asking is by filtering the results when setting $r

$r = $sqlServer.ReadErrorLog()  | ? { $_.Text -match 'error' -OR $_.text -match 'Failed' -and $_text -notmatch "Error: 0x2098"}

You can iterate through each list and update $r accordingly, something like below should get you started.

Foreach($ExcludeText in $exclude){
    $r = $r | ? {$_.text -notmatch $ExcludeText}
}

Foreach($IncludeText in $find){
   $r = $r | ?{$_.text -match $IncludeText}
}