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.