SQL Server 2008 Improvements Practicals – PowerShell Log Checks

I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 8:00am - "Good Morning Sunshine":

With the new PowerShell provider, I have access to lots of things in SQL Server natively. But even non-SQL-natively, I often need to check things on my system. So I created this simple PowerShell script that scrubs the Event Logs for Errors - not just SQL Server errors, but all of them. It's driven by a simple text file that lists the server names, called c:\temp\servers.txt:

# Buck Woody
# Last changed: 07/16/2007
# Requires an text file called "servers.txt"
# in the c:\temp\ directory

# Pretty Pretty HTML
$formatHTML = "<style>"
$formatHTML = $formatHTML + "BODY{background-color:black;color:white}"
$formatHTML = $formatHTML + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$formatHTML = $formatHTML + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:white}"
$formatHTML = $formatHTML + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:blue}"
$formatHTML = $formatHTML + "</style>"
ConvertTo-Html -head $formatHTML –title "SQL Error Logs" | Out-File c:\temp\SQLErrors.htm
ConvertTo-Html -head $formatHTML –title "Windows Event Logs" | Out-File c:\temp\WindowsErrors.htm

# Create a document object, read in the file
Write-Host "Read File"
$doc = Get-Content c:\temp\servers.txt

# Loop for servers
foreach ($server in $doc)
   $serverName = "\\"+$server+"\"
   $titleName = "<H2>" + $serverName + "</H2>"

# Read the Windows Event Logs, search for errors
 $WindowsLog = get-eventlog -logname application
 $WindowsLog | select-string -inputobject {$_.eventid, $_.source, $_.message} -pattern "failed" | ConvertTo-Html Line -body $titleName -title "Windows Event Logs" | Out-File c:\temp\WindowsErrors.htm -Append

Skip to main content