How to filter line + following line using Get-SqlErrorLog? Something like Select-String -Context 1

From the PowerShell help:

The Context parameter captures the specified number of lines before and after the line with the match. This allows you to view the match in context.

If you enter one number as the value of this parameter, that number determines the number of lines captured before and after the match. If you enter two numbers as the value, the first number determines the number of lines before the match and the second number determines the number of lines after the match.

I was not sure if Get-SqlErrorLog supported a parameter like Context. So, I decided to check if we can do something like this, without too much code.

Add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.ErrorLogPath

Output is something like so, on my machine:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

From here it is pretty straight forward:

Get-ChildItem -Path $server.ErrorLogPath -Filter ERRORLOG* |
ForEach-Object -Process { Select-String -Path $_ -Pattern "Starting up database 'sqlchow'" -Context 1}

The output is something like:

  C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG:54:2013-08-17 08:33:58.26 spid52
Using 'dbghelp.dll' version '4.0.5'
> C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG:55:2013-08-17 12:55:59.64 spid53
Starting up database 'sqlchow'.
  C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG:56:2013-08-17 12:56:01.78 spid53
Setting database option COMPATIBILITY_LEVEL to 100 for database sqlchow.

Note: If you want to search only in the first error-log file then, just remove change the code to:

Get-ChildItem -Path $server.ErrorLogPath -Filter ERRORLOG |
ForEach-Object -Process { Select-String -Path $_ -Pattern "Starting up database 'sqlchow'" -Context 1}

Interesting stuff eh!

As a DBA, I get to use PowerShell rarely because most of the automation in our shop has been done in Perl. We are slowly moving towards PowerShell. One thing, I keep doing is fiddling with Perl scripts and to see if PowerShell can do it better.

Today I am going to write about a few things:

1. Getting a list of installed instances on a particular box.
2. Reading the SQL*Server error logs using SMO.
3. Querying the Windows event logs for errors.

Getting a list of installed instances on a box

So, what the below function does is, it looks in the ‘HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\’ registry path to see which SQL*Server instances are installed and for each installed instance returns the Instance name, Instance Id and path to sqlservr.exe.

Function Get-SQLInstanceList
{

    try
    {
		$msSqlKey = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\'
        $instNameKey = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\'
        $sqlInstLst = @()

        $instNames = (Get-Item -Path $instNameKey).GetValueNames()
        foreach($inst in $instNames)
        {
            $instIDKey = $instNameKey
            $instID = (Get-ItemProperty -Path $instIDKey -Name $inst).$inst
            $sqlBin = (Get-ItemProperty -Path ($msSqlKey + $instID + '\Setup') -Name SQLBinRoot).SQLBinRoot

            #HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.D11\Setup
            $objInstDtls = New-Object System.Object|
                            Add-Member NoteProperty InstName $inst -PassThru|
                            Add-Member NoteProperty InstID $instID -PassThru |
                            Add-Member NoteProperty SQLBin $sqlBin -PassThru

            $sqlInstLst += $objInstDtls
        }
        return $sqlInstLst
    }
    catch
    {
        $(throw "Unable to get SQL Server instances on: $($env:COMPUTERNAME)")
    }
}

Reading the SQL*Server error logs using SMO

If you have seen the SMO documentation; you will see that the ReadErrorLog() and ReadErrorLog(Int32) methods return a DataTable and I like that quite a bit because you can filter out a lot of noise.  By the way, for the overload acceptable values are only 0 through 9

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$SqlServerInstName"
$sqlServer.ReadErrorLog() | ? { $_.Text -match 'failed' -AND $_.ProcessInfo -eq 'backup'}

Querying the Windows event logs for Errors

I like ‘wmic’ a lot; one of my favourite commands is:
‘wmic service get name, caption, state, status | findstr /I /C:”sql”‘

So, when I got to know about the Get-WmiObject; I immediately feel in love. The below function will query the windows event logs and return the highest occurring error events along with their counts. In my personal experience I have found that using Win32_NTLogEvent with a filter is much faster returning results than Get-WinEvent and Get-EventLog; when running against remote computers.

$logsToCheck = @('Application', 'Setup' , 'System')
try
{
	$yesterday = (Get-WmiObject -Query "SELECT LocalDateTime FROM Win32_OperatingSystem").ConvertFromDateTime((Get-Date).AddDays(-1))
	foreach($evtLog in $logsToCheck)
	{
		Write-Host "Currently processing event log: $evtLog"

		$errCount = 0
		$filter = "LogFile='$evtLog' AND TimeGenerated >= '$yesterday' AND EventType=1"
		$evtWMIParams = @{
					'Class' 				= 'Win32_NTLogEvent';
					'NameSpace'				= 'ROOT\cimv2';
					'ComputerName'			= $env:COMPUTERNAME;
					'Filter'				= $filter;
					'Authentication'		= 'PacketPrivacy';
					'Impersonation'			= 'Impersonate';
					'EnableAllPrivileges'	= 1
				}

		$filteredEvnts = Get-WmiObject @evtWMIParams | Group-Object EventCode
		if($filteredEvnts){
			$errCount = ($filteredEvnts | Measure-Object -Property Count -Sum).Sum
			Write-Host "Number of errors in '$evtLog' log is: $errCount"
			$filteredEvnts | Select-Object -ExpandProperty Group | Select-Object @{n="Message";e={$_.Message}} |
			Group-Object Message | Sort-Object -Property Count -Descending | Select-Object Count, Name | Format-Table -AutoSize -Wrap
		}

	}
}
catch
{
	$(throw "Error getting data from event log")
}

Any comments or suggestions are welcome! Thank you, for your time.