The Multifunctioning DBA

Nov 11 2008   10:58PM GMT

MS SQL Server Monitoring Part 4

Colin Smith Colin Smith Profile: Colin Smith

It has been a while since my last post about MS SQL Server Monitoring using Powershell. This is because I found that I was not catching all the errors that I wanted. I was missing any jobs that were failing. SQL Server gives you the ability to output job failures to the Windows Event log but not into the SQL Server ERRORLOG file. So I decided that I wanted to monitor the Application Event logs on the servers as well. This was not as easy as I had hoped. First I have to get a connection into the event log file and then I can scan the file. I also only want to scan the event log from the last time that it was scanned until now. This took some creativity and some working with Data Data types. Anyway, that is enough about talking about the problems. Let’s look at some code.

##################################################

## Search Server Application Log for SQL Errors ##

##################################################

$elog = “Application”

if ($instance -eq “Null”)

{

    $instancename = “mssqlserver”

}

else

{

    $instancename = “MSSQL`$$instance”

}

echo “the Instance name is $instancename”

$time = Get-Content “h:\$folder\time.txt”

Echo “Looking for errors since $time`n”

$objlog = New-Object system.Diagnostics.EventLog($elog, $machine)

$logentries = $objlog.get_entries()

foreach ($logentry in $logentries)

{

    $eltime = $logentry.timegenerate | Get-Date -uformat “%m/%d/%Y %R”

    $Source = $logentry.source

    $message = $logentry.message

    if (($source -eq $instancename) -and ($eltime -ge $time))

{

        $ignoreit = 0

        foreach ($estring in$estrings)

{

            if ($message -like “*$estring*”)

{

                $ignoreit = 0

                foreach ($istring in $ignore)

{

                    if ($message -like “*$istring*”)

{

                        $ignoreit = 1

                    }

                }

                break

            }

            else

{

                $ignoreit = 1

            }

        }

        if ($ignoreit -eq 0)

{

            #Echo “Error Found”

            $mark = 1

            $descript = 1

            echo $folder >> “H:\Alert\Alert”

            if (!(test-path “H:\$folder\monitor.out”))

{

                New-Item -type File “H:\$folder\monitor.out”

            }

            Echo $message >> “H:\$folder\monitor.out”

            Echo “Error Found for $Source at $eltime”

        }

        else

{

            #Echo “No Error Found”

        }

    }

}

So I am looking at $elog and I have that set to Application. You can change this variable to look at any of the event logs that you would like. I also have to tell it what the last time was that I looked for an error. I do this by settting up a file in $folder that will keep track of the time of the last entry that I have looked at. I initially have this set to mm/dd/yyy hh:mm. I read this in and then I have to read in the time of the entries that I am reading. I convert that in order to do the compare of the last timestamp of the last entry. Once I do that I can also compare the source of the error to the instance name that I am searching for. If the entry meets the criteria then I can start looking for strings that are in my error_strings file and ignoring errors that have a string that is also in the ignore_strings file. After that it is pretty basic. If I find an error I put that in the monitor.out file and I put an entry in the alert file for this server. That will trigger the notifications until I or somebody responds to the error. Let me know if you have any questions or comments. Leave them here or stop by http://sysadminsmith.com and submit a question to me.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: