SQL Server error logs offline parsing

get-errorlog-17053.ps1

# Operating system error %.*ls on file "%.*ls" during %ls.
cls;

$DirPattern = "D:\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG*";
$OutputDir = "D:\Temp";
$CSVDelimiter = ";";

$SearchLogFiles = [regex] "(?<Date>\d{4}-\d{2}-\d{2}) (?<Time>\d{2}:\d{2}:\d{2}\.\d{2}) (?<Source>.*?)\s{4}(?<FullFileName>.*?): Operating system error (?<OSErrorNumber>\d*) \((?<OSErrorText>.*?)\) encountered.";


dir $DirPattern | % {
	$log = [io.file]::ReadAllText($_);
	$match = $SearchLogFiles.Match($log);

	while ($match.Success) {
		$index = new-object System.Object;
		$index | add-member -membertype noteproperty -name Date -value $match.Groups['Date'].Value;
		$index | add-member -membertype noteproperty -name Time -value $match.Groups['Time'].Value;
		$index | add-member -membertype noteproperty -name Source -value $match.Groups['Source'].Value;
		$index | add-member -membertype noteproperty -name FullFileName -value $match.Groups['FullFileName'].Value;
		$index | add-member -membertype noteproperty -name OSErrorNumber -value $match.Groups['OSErrorNumber'].Value;
		$index | add-member -membertype noteproperty -name OSErrorText -value $match.Groups['OSErrorText'].Value;
		$index;
		$match = $match.NextMatch();
	};
} | Export-Csv (Join-Path $OutputDir "error-17053.csv") -Delimiter $CSVDelimiter -NoTypeInformation

get-errorlog-17883.ps1

# Process %ld:%ld:%ld (0x%lx) Worker 0x%p appears to be non-yielding on Scheduler %ld. Thread creation time: %I64d. Approx Thread CPU Used: kernel %I64d ms, user %I64d ms. Process Utilization %d%%. System Idle %d%%. Interval: %I64d ms.
cls;

$DirPattern = "D:\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG*";
$OutputDir = "D:\Temp";
$CSVDelimiter = ";";

$SearchLogFiles = [regex] "(?<Date>\d{4}-\d{2}-\d{2}) (?<Time>\d{2}:\d{2}:\d{2}\.\d{2}) (?<Source>.*?)\s*Process (?<ProcessID>\d*:\d*:\d*) \((?<ThreadID>.*?)\) Worker (?<WorkerAddress>.*?) appears to be non-yielding on Scheduler (?<SchedulerID>\d*)\. Thread creation time: (?<CreationTime>\d*)\. Approx Thread CPU Used: kernel (?<CPUKernelUsedMs>\d*) ms, user (?<CPUUserUsedMs>\d*) ms\. Process Utilization (?<ProcessUtilization>.*)\. System Idle (?<SystemIdle>.*)\. Interval: (?<IntervalMs>\d*) ms\.";

dir $DirPattern | % {
	$log = [io.file]::ReadAllText($_);
	$match = $SearchLogFiles.Match($log);

	while ($match.Success) {
		$index = new-object System.Object;
		$index | add-member -membertype noteproperty -name Date -value $match.Groups['Date'].Value;
		$index | add-member -membertype noteproperty -name Time -value $match.Groups['Time'].Value;
		$index | add-member -membertype noteproperty -name Source -value $match.Groups['Source'].Value;
		$index | add-member -membertype noteproperty -name ProcessID -value $match.Groups['ProcessID'].Value;
		$index | add-member -membertype noteproperty -name ThreadID -value $match.Groups['ThreadID'].Value;
		$index | add-member -membertype noteproperty -name WorkerAddress -value $match.Groups['WorkerAddress'].Value;
		$index | add-member -membertype noteproperty -name SchedulerID -value $match.Groups['SchedulerID'].Value;
		$index | add-member -membertype noteproperty -name CreationTime -value $match.Groups['CreationTime'].Value;
		$index | add-member -membertype noteproperty -name CPUKernelUsedMs -value $match.Groups['CPUKernelUsedMs'].Value;
		$index | add-member -membertype noteproperty -name CPUUserUsedMs -value $match.Groups['CPUUserUsedMs'].Value;
		$index | add-member -membertype noteproperty -name ProcessUtilization -value $match.Groups['ProcessUtilization'].Value;
		$index | add-member -membertype noteproperty -name SystemIdle -value $match.Groups['SystemIdle'].Value;
		$index | add-member -membertype noteproperty -name IntervalMs -value $match.Groups['IntervalMs'].Value;
		$index;
		$match = $match.NextMatch();
	};
} | Export-Csv (Join-Path $OutputDir "error-17883.csv") -Delimiter $CSVDelimiter -NoTypeInformation

get-errorlog-17884.ps1

# New queries assigned to process on Node %d have not been picked up by a worker thread in the last %d seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: %d%%. System Idle: %d%%.
cls;

$DirPattern = "D:\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG*";
$OutputDir = "D:\Temp";
$CSVDelimiter = ";";

$SearchLogFiles = [regex] "(?<Date>\d{4}-\d{2}-\d{2}) (?<Time>\d{2}:\d{2}:\d{2}\.\d{2}) (?<Source>.*?)\s*New queries assigned to process on Node (?<Node>\d*) have not been picked up by a worker thread in the last (?<Seconds>\d*) seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the `"max worker threads`" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: (?<CPUBusyPercent>\d*)%. System Idle: (?<CPUIdlePercent>\d*)%.";

dir $DirPattern | % {
	$log = [io.file]::ReadAllText($_);
	$match = $SearchLogFiles.Match($log);

	while ($match.Success) {
		$index = new-object System.Object;
		$index | add-member -membertype noteproperty -name Date -value $match.Groups['Date'].Value;
		$index | add-member -membertype noteproperty -name Time -value $match.Groups['Time'].Value;
		$index | add-member -membertype noteproperty -name Source -value $match.Groups['Source'].Value;
		$index | add-member -membertype noteproperty -name Node -value $match.Groups['Node'].Value;
		$index | add-member -membertype noteproperty -name Seconds -value $match.Groups['Seconds'].Value;
		$index | add-member -membertype noteproperty -name CPUBusyPercent -value $match.Groups['CPUBusyPercent'].Value;
		$index | add-member -membertype noteproperty -name CPUIdlePercent -value $match.Groups['CPUIdlePercent'].Value;
		$index;
		$match = $match.NextMatch();
	};
} | Export-Csv (Join-Path $OutputDir "error-17884.csv") -Delimiter $CSVDelimiter -NoTypeInformation

get-errorlog-18056.ps1

# The client was unable to reuse a session with SPID %d, which had been reset for connection pooling. The failure ID is %d. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
cls;

$DirPattern = "D:\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG*";
$OutputDir = "D:\Temp";
$CSVDelimiter = ";";

$SearchLogFiles = [regex] "(?<Date>\d{4}-\d{2}-\d{2}) (?<Time>\d{2}:\d{2}:\d{2}\.\d{2}) (?<Source>.*?)\s*Error: 18056, Severity: 20, State: 29.";


dir $DirPattern | % {
	$log = [io.file]::ReadAllText($_);
	$match = $SearchLogFiles.Match($log);

	while ($match.Success) {
		$index = new-object System.Object;
		$index | add-member -membertype noteproperty -name Date -value $match.Groups['Date'].Value;
		$index | add-member -membertype noteproperty -name Time -value $match.Groups['Time'].Value;
		$index | add-member -membertype noteproperty -name Source -value $match.Groups['Source'].Value;
		$index;
		$match = $match.NextMatch();
	};
} | Export-Csv (Join-Path $OutputDir "error-18056.csv") -Delimiter $CSVDelimiter -NoTypeInformation

get-errorlog-833.ps1

# SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d).  The OS file handle is 0x%p.  The offset of the latest long I/O is: %#016I64x
cls;

$DirPattern = "D:\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG*";
$OutputDir = "D:\Temp";
$CSVDelimiter = ";";

$SearchLogFiles = [regex] "(?<Date>\d{4}-\d{2}-\d{2}) (?<Time>\d{2}:\d{2}:\d{2}\.\d{2}) (?<Source>.*?)\s*SQL Server has encountered (?<NumberIOs>\d*) occurrence\(s\) of I/O requests taking longer than (?<Seconds>\d*) seconds to complete on file \[(?<FullFileName>.*)\] in database \[(?<DatabaseName>.*)\]";

dir $DirPattern | % {
	$log = [io.file]::ReadAllText($_);
	$match = $SearchLogFiles.Match($log);

	while ($match.Success) {
		$index = new-object System.Object;
		$index | add-member -membertype noteproperty -name Date -value $match.Groups['Date'].Value;
		$index | add-member -membertype noteproperty -name Time -value $match.Groups['Time'].Value;
		$index | add-member -membertype noteproperty -name Source -value $match.Groups['Source'].Value;
		$index | add-member -membertype noteproperty -name NumberIOs -value $match.Groups['NumberIOs'].Value;
		$index | add-member -membertype noteproperty -name Seconds -value $match.Groups['Seconds'].Value;
		$index | add-member -membertype noteproperty -name FullFileName -value $match.Groups['FullFileName'].Value;
		$index | add-member -membertype noteproperty -name DatabaseName -value $match.Groups['DatabaseName'].Value;
		$index;
		$match = $match.NextMatch();
	};
} | Export-Csv (Join-Path $OutputDir "error-833.csv") -Delimiter $CSVDelimiter -NoTypeInformation

get-errorlog-errors.ps1

# Ideas from
# https://www.simple-talk.com/sql/database-administration/the-posh-dba---reading-and-filtering-errors
# http://stackoverflow.com/a/4192419

cls;

$DirPattern = "D:\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG*";

$MatchingText = '(Error|Fail|IO requests taking longer|is full)';
$NotMatchingText = '(without errors|found 0 errors)';


dir $DirPattern | % {
		Write-Output "Processing log $($_.FullName)";
		$FileReader = [System.IO.File]::OpenText($_.FullName);
		try {
		    for(;;) {
		        $ReadLine = $FileReader.ReadLine()
		        if ($ReadLine -eq $null) { break }
				if ($ReadLine -match $MatchingText -and $ReadLine -notmatch $NotMatchingText) { $ReadLine }
			}
		}
		finally {
			[Void]$FileReader.Close;
		}
	}