<#
.SYNOPSIS
Retrieves SQL Server error log entries
.DESCRIPTION
Pulls error log details from SQL Server and outputs them to the client
.PARAMETER <paramName>
-SQLInstance (required) The SQL Instance to pull the logs from
-MaxDate (required) The most recent log record to return
-MinDate (required) the oldest log record to return
.EXAMPLE
./Get-SQLErrorLogs -SQLInstance MyInstance -MinDate 2012-05-10 -MaxDate "2012-05-15 09:00"
#>
param
(
[string]$SQLInstance = $(Read-Host -prompt "SQL Instance [e.g. MyClusterInstance]"),
[string] $MinDate = $(Read-Host -prompt "Lowest log date entry (yyyy-mm-dd hh:mm)"),
[string] $MaxDate = $(Read-Host -prompt "Highest log date entry (yyyy-mm-dd hh:mm"),
[int] $LogNumber
)
cls
$CheckDateMin = [datetime]::Parse($MinDate)
$CheckDateMax = [datetime]::Parse($MaxDate)
Function Main {
#Load the SQL snapins
Load-SQLSnapins
#Create a datatable to hold the data
$Results = New-Object system.Data.DataTable "Results"
#Setup columns
$LogDate = New-Object system.Data.DataColumn LogDate,([DateTime])
$ProcessInfo = New-Object system.Data.DataColumn ProcessInfo,([string])
$Text = New-Object system.Data.DataColumn Text,([string])
#Add columns to datatable
$Results.columns.add($LogDate)
$Results.columns.add($ProcessInfo)
$Results.columns.add($Text)
#Another to hold logs read
$ReadLogs = New-Object system.Data.DataTable "ReadLogs"
$ReadLogNum = New-Object system.Data.DataColumn LogNumber,([int])
$ReadLogs.columns.add($ReadLogNum)
#Shred the SQL Instance to get the right data
$SQLName = Shred-SQLName $SQLInstance
$InstanceName = Shred-SQLInstance $SQLInstance
#Grabs SQL Logs by Date
Get-SQLLogByDate $SQLName $InstanceName $SQLInstance $CheckDateMin $CheckDateMax
$results | Sort-Object LogDate | ogv
}
Function Shred-SQLInstance ($SQLInstance) {
$SQLInstance = $SQLInstance.ToUpper()
if ($SQLInstance -ilike "**")
{
$string = $SQLInstance.Split("")
$Instance = $string[1]
Return $Instance
}
else
{
$Instance = "DEFAULT"
Return $Instance
}
}
Function Shred-SQLName ($SQLInstance) {
$SQLInstance = $SQLInstance.ToUpper()
if ($SQLInstance -ilike "**")
{
$string = $SQLInstance.Split("")
$Name = $string[0]
Return $Name
}
else
{
$Name = $SQLInstance
Return $Name
}
}
Function Get-SQLLogByDate ($SQLName, $InstanceName, $SQLInstance,$CheckDateMin ,$CheckDateMax) {
IF ($InstanceName -eq "DEFAULT") {$Logs = DIR SQLSERVER:SQL$SQLName | Where-Object {$_.Name -eq $SQLInstance}; }
ELSE {$Logs = DIR SQLSERVER:SQL$SQLName | Where-Object {$_.Name -eq $SQLInstance}; }
$LogCount = ($Logs.EnumErrorLogs() | Measure-Object).Count
Write-Output "--------------------------------------------------------------------------------------------------------------"
Write-Output "Checking error logs on server $SQLInstance for logs created between $CheckDateMin & $CheckDateMax"
Write-Output "--------------------------------------------------------------------------------------------------------------"
Write-Output "Checking through $LogCount logs, please wait."
Write-Output "-----------------------------------------------"
$LogList = $Logs.EnumErrorLogs()
#we'll need to do something hokey here. SQL error log creation is not when the log was created, rather when it was closed
#not intended circumstances. This means we'll have to get the min log and then get the one before that
ForEach ($ErrorLog in $LogList) {
IF ($ErrorLog.CreateDate -ge $CheckDateMin -and $ErrorLog.CreateDate -le $CheckDateMax)
{
$CurrentLog = $ErrorLog.CreateDate
$CurrentLogName = $ErrorLog.Name
Write-Output "Currently reading error log $CurrentLogName dated $CurrentLog..."
#write the log number to the $ReadLogs datatable
$NewLogRow = $ReadLogs.NewRow(); $NewLogRow.LogNumber = $CurrentLogName; $ReadLogs.Rows.Add($NewLogRow);
$Logs.ReadErrorLog(($ErrorLog.Name)) | Where-Object {$_.LogDate -ge $CheckDateMin -AND $_.LogDate -le $CheckDateMax} |
% {$NewRow = $Results.NewRow();
$NewRow.LogDate = $_.LogDate;
$NewRow.ProcessInfo = $_.ProcessInfo;
$NewRow.Text = $_.Text;
$Results.Rows.Add($NewRow);
}
}
}
#Now we've read all the logs read the one before the newest so we get that extra data
#unless the most current log (0) is included in the results already
#Write-Output $ReadLogs | ft -AutoSize
$LowestLog = ($ReadLogs | Measure-Object LogNumber -Minimum).Minimum
IF ($LowestLog -gt 0)
{
$LogToRead = $LowestLog - 1
Write-Output "Currently reading error log $LogToRead..."
#write the log number to the $ReadLogs datatable
$NewLogRow = $ReadLogs.NewRow(); $NewLogRow.LogNumber = $CurrentLogName; $ReadLogs.Rows.Add($NewLogRow);
$Logs.ReadErrorLog($LogToRead) | Where-Object {$_.LogDate -ge $CheckDateMin -AND $_.LogDate -le $CheckDateMax} |
% {$NewRow = $Results.NewRow();
$NewRow.LogDate = $_.LogDate;
$NewRow.ProcessInfo = $_.ProcessInfo;
$NewRow.Text = $_.Text;
$Results.Rows.Add($NewRow);
}
}
#on the off chance that the dates reside in the current log only pull that one in
#but only if no other logs have been loaded (edge case)
IF (!$LowestLog)
{
Write-Output "Currently reading error log 0..."
$Logs.ReadErrorLog(0) | Where-Object {$_.LogDate -ge $CheckDateMin -AND $_.LogDate -le $CheckDateMax} |
% {$NewRow = $Results.NewRow();
$NewRow.LogDate = $_.LogDate;
$NewRow.ProcessInfo = $_.ProcessInfo;
$NewRow.Text = $_.Text;
$Results.Rows.Add($NewRow);
}
}
}
Function Load-SQLSnapins
{ $CheckSQLSnapins = get-pssnapin | where {$_.Name -match "SqlServerCmdletSnapin100"}
if (!$CheckSQLSnapins)
{
Add-PSSnapin SqlServerCmdletSnapin100
Write-Host "SqlServerCmdletSnapin100 Loaded"
}
else { Write-Host "SqlServerCmdletSnapin100 Already Loaded" }
$CheckSQLSnapins = get-pssnapin | where {$_.Name -match "SqlServerProviderSnapin100"}
if (!$CheckSQLSnapins)
{
Add-PSSnapin SqlServerProviderSnapin100
Write-Host "SqlServerProviderSnapin100 Loaded"
}
else { Write-Host "SqlServerProviderSnapin100 Already Loaded" }
}
main
SirSQL.net 