MSSQL Server Error Logs

MSSQL Server error logs exist in the same folder that contains the installed server bits.  They’re text files.  Easy-as-pie to read, right?

No.

MSSQL is often installed on a dedicated drive.  You can’t assume it will be in C:\Program Files\.  Aaaaand, a give computer may host multiple instances of MSSQL server running simultaneously.

I hate hitting the registry because that’s a private interface, but that’s the only way I could find to determine the installed folder for each instance.

 ####################
function Get-MsSqlLogPath
####################
{
    <#
    .synopsis
    Return log paths for all SQL server instances for specified computer(s)

    .description
    An MSSQL server will have a different log folder for each instance of MSSQL running on it.  This will return all log folder paths local to the machine itself that match the specified DatabaseName parameter value.

    .parameter ComputerName
    Name(s) of computers to query via WinRM.  Default is local computer.

    .parameter DatabaseName
    Pattern(s) of regular expressions matching specific database names, such as Perf, E2E, etc.  Default is '.*' (all logs)

    .outputs
    PSObject with two properties

    - ComputerName

    - MsSqlLogPath

    #>

    param (
        [parameter(ValueFromPipeline=$true)][string[]]$ComputerName = $env:computername,
        [string[]]$DatabaseName = '.*'
    );

    begin
    {
        $scriptBlock = {

            if ($args.Count -ge 1)
            {
                $DatabaseName = $args[0];

            } # if ($args.Count -ge 1)
            else
            {
                $DatabaseName = '.*';

            } # if ($args.Count -ge 1) .. else

            $computername = "$env:ComputerName.$((Get-WmiObject Win32_ComputerSystem).Domain)".ToLower();

            if (Test-Path -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server*')
            {

                Get-ChildItem -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server*' -Recurse -ErrorAction SilentlyContinue |
                ? { $_.Property -match 'ErrorLogFile' } |
                % { Split-Path -Parent -Path $_.GetValue('ErrorLogFile') } |
                Select-String -Pattern $DatabaseName |
                Select-Object -Property @{
                    n = 'ComputerName';
                    e = { $computerName; }

                }, @{
                    n = 'MsSqlLogPath';
                    e = { $_; }

                } # GetChildItem | ... | Select-Object -Property @{

            } # if (Test-Path -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server*')

        }; # $scriptBlock =

    } #begin

    process
    {
        (& { # needed for 'foreach (...) { ... }' to be able to be piped into a Select-Object
        
            foreach ($_computerName in $ComputerName)
            {
                if (
                    ($_computerName -match "^$env:ComputerName\.") -or
                    ($_computerName -match "^$env:ComputerName$")
                )
                {
                    Invoke-Command -ScriptBlock $scriptBlock -ArgumentList $DatabaseName;

                } # if )($_computerName -match "^$env:ComputerName\.") ...
                else
                {
                    Invoke-Command -ScriptBlock $scriptBlock -ArgumentList $DatabaseName -ComputerName $_computerName;

                } # if (($_computerName -match "^$env:ComputerName\.") ... else

            } # foreach ($_computerName in $ComputerName)

        }) | Select-Object -Property ComputerName, MsSqlLogPath;

    } # process

} # function Get-MsSqlLogPath

####################
function Get-MsSqlDbErrorLogEvent
####################
{
    <#
    .synopsis
    Return SQL error log data for specified computer(s), logfile path, database name pattern, and timespan

    .description
    Calls Get-MsSqlLogPath for specified computer(s) and database name pattern(s), then returns error log data.

    .parameter ComputerName
    Name(s) of computers to query via WinRM

    .parameter DatabaseName
    Pattern(s) of regular expressions matching specific database names, such as Perf, E2E, etc.  Default is '.*' (all logs)

    .parameter Hours
    Number of hours of SQL logs to scan, starting to at current time.  Defaults to 1 hours if Hours, Before, and After are not specified.
    
    .parameter Before
    Return entries before the specified time.  Defaults to current time if Hours, Before, and After are not specified.
    
    .parameter After
    Return entries after the specified time.  Defaults to an hour before current time if Hours, Before, and After are not specified.

    .outputs
    PSObject with properties.  The values correspond to the data colums in the Log Viewer under SQL Server Management Studio with the following differences:

    - Date: Date/timestamp of entry.

    - Source: From log.

    - Message: From log.  Content is delimited by `r for newlines

    - LogType: Hardcoded of 'SQL Server'

    - LogSource: \\uncpath\to\error\file


    .notes
    This is a very slow function due to PSRemoting.
    
    #>

    param (
        [string]$ComputerName = $env:computername,
        [int]$Hours = 1,
        [string[]]$DatabaseName = '.*',
        [object]$Before = $null,
        [object]$After = $null
    );

    begin
    {

        #region validate and populate parameters

        $now = Get-Date;

        if ($Before)
        {
            if ($Before -as [datetime])
            {
                if ($Before -gt $now) { $Before = $now; }

                if ($After)
                {
                    if ($After -as [datetime])
                    {
                        $After = $After -as [datetime];

                    }
                    else
                    {
                        Write-Error "$($MyInvocation.MyCommand.Name) -After'$After' cannot be converted to [DateTime]." -ErrorAction Stop;

                    }

                }
                else
                {
                    $After = $Before - (New-TimeSpan -Hours $Hours);

                }

            }
            else
            {
                Write-Error "$($MyInvocation.MyCommand.Name) -Before '$Before' cannot be converted to [DateTime]." -ErrorAction Stop;

            }

        } # if ($Before)
        else
        {
            $Before = $now;

            if ($After)
            {
                if ($After -as [datetime])
                {
                        $After = $After -as [datetime];

                }
                else
                {
                    Write-Error "$($MyInvocation.MyCommand.Name) -After'$After' cannot be converted to [DateTime]." -ErrorAction Stop;

                }

            }
            else
            {
                $After = $Before - (New-TimeSpan -Hours $Hours);

            }

        } # if (!$Before)

        if ($Before -lt $After)
        {
            Write-Warning "$($MyInvocation.MyCommand.Name) -Before $Before is earlier -After $After.  Swapping. ";
            ($Before, $After) = ($After, $Before);

        } # if ($Before -gt $After)

        #endregion

        $scriptblock = {

            #region validate parameters

            if ($args.count -ge 3)
            {
                [string]$MsSqlLogPath = $args[0];
                [datetime]$Before     = $args[1];
                [datetime]$After      = $args[2];

            } # if ($args.count -ge 3)
            else
            {
                throw 'insufficient arguments provided.';

            } # if ($args.count -ge 3) ... else

            if (!(Test-Path $MsSqlLogPath))
            {
                Write-Error -Message "Path '$MsSqlLogPath' not found." -ErrorAction Stop;

            } # if (!(Test-Path $MsSqlLogPath))

            if ($Before -lt $After) { ($Before, $After) = ($After, $Before); }

            #endregion

            # set up regular expressions for
            # removing all but the timestamp from a line
            [regex]$logLineToTimeRegeX = '[^\d\:\s-].*';

            # tokenizing a line into the three relevant fields
            [regex]$logLineSplitRegex = '^(?<Date>[\d\:\.\s-]+) (?<user>.{12})(?<message>.*)';

            # get full path/names of all SQL errorlog files in this folder
            $files = Get-ChildItem "$msSqlLogPath\ErrorLog*" |
                Sort-Object -Property $fullName |
                Select-Object -ExpandProperty FullName;

            # process each file
            foreach ($file in $files)
            {
                #region verify first (earliest) line is not before our endtime

                $firstLine = Get-Content -Path $file |
                    Select-Object -First 1;

                $firstLineTime = $logLineToTimeRegex.Replace($firstLine, '') -as [datetime];

                if ($firstLineTime)
                {
                    if ($firstLineTime -gt $before) { continue; }

                } # if ($firstLineTime)
                else
                {
                    continue;

                } # if ($firstLineTime)

                #endregion
                #region verify last (latest) line is not after our starttime

                $lastLine = Get-Content -Path $file |
                    Select-Object -Last 20 |
                    ? { $_ -match '^\d{4}-' } |
                    Select-Object -Last 1

                $lastLineTime = $logLineToTimeRegex.Replace($lastLine, '') -as [datetime];

                if ($lastLineTime)
                {
                    if ($lastLineTime -lt $After) { continue; }

                } # if ($lastLineTime)
                else
                {
                    continue;

                } # if ($lastLineTime)

                #endregion

                $object = $null;

                $logSource = "\\$($env:ComputerName.ToLower())\$file" -replace ':', '$';


                foreach ($line in (Get-Content -Path $file))
                {
                    if (
                        $loglineSplitRegex.Match($line) |
                        Tee-Object -Variable myMatch |
                        Select-Object -ExpandProperty Success
                    )
                    {

                        $date = $myMatch.Groups[1].Value -as [datetime];

                        if ($date -gt  $after)
                        {
                            if ($date -lt $before)
                            {
                                # output the previous buffer if it exists
                                if ($object) { $object; }

                                # initialize a new buffer
                                $object = New-Object -TypeName PsObject |
                                    Select-Object -Property Date, Source, Message, LogType, LogSource;

                                $object.Date      = $date;
                                $object.Source    = $myMatch.Groups[2].Value -replace '\s+$';
                                $object.Message   = $myMatch.Groups[3].Value.ToString();
                                $object.LogType   = 'SQL Server';
                                $object.LogSource = $logSource;

                            } # if ($date -lt $before)
                            else
                            {
                                # if we past the endtime, bail out of file
                                break;

                            } # if ($date -lt $before) ... else

                        } # if ($date -gt  $after)

                    } # if ($loglineSplitRegex.Match($line) ...
                    elseif ($object)
                    {
                        $object.Message += "`r$line"

                    } # if ($loglineSplitRegex.Match($line) ... else

                } # foreach ($line in (Get-Content -Path $file))

                # after each file, output the previous buffer if it exists
                if ($object) { $object; }

            } # foreach ($file in $files)

        } # $scriptblock =

    } # begin

    process
    {
        foreach ($_computerName in $ComputerName)
        {
            $MsSqlLogPath = Get-MsSqlLogPath -ComputerName $_computerName -DatabaseName $DatabaseName |
                Select-Object -ExpandProperty MsSqlLogPath;

            if (!$MsSqlLogPath)
            {
                $msg = "$($MyInvocation.MyCommand.Name) -ComputerName $_computerName did not return SQL log paths."
                Write-Warning -Message $msg;
                Write-Error -Message $msg -ErrorAction silentlyContinue;
                continue;

            } # if (!$MsSqlLogPath)

            foreach ($_msSqlLogPath in $MsSqlLogPath)
            {
                Write-Progress -Activity $_computerName -Status $_msSqlLogPath

                if (
                    ($_computerName -match "^$env:ComputerName\.") -or
                    ($_computerName -match "^$env:ComputerName$")
                )
                {
                    Invoke-Command -ScriptBlock $scriptBlock -ArgumentList $_msSqlLogPath, $Before, $After;

                } # if ($_computerName -match "^$env:ComputerName\.")
                else
                {
                    Invoke-Command -ScriptBlock $scriptBlock -ArgumentList $+msSqlLogPath, $Before, $After -ComputerName $_computerName;

                } # if (($_computerName -match "^$env:ComputerName\.")

            } # foreach ($_msSqlLogPath in ($MsSqlLogPath ...

        } # foreach ($_computerName in $ComputerName)

    } # process

} # function Get-MsSqlDbErrorLogEvent

Get-MsSqlErrorData.psm1