Get-SqlErrorLog.ps1

<#
	.SYNOPSIS
		Get-SqlErrorLog is designed to quickly retrieve data from SQL Server error logs, negating the slowness of SSMS and awkwardness of manually crawling files.
	
	.DESCRIPTION
		Get-SqlErrorLog is designed to quickly retrieve data from SQL Server error logs, negating the slowness of SSMS and awkwardness of manually crawling files.
		
		It is designed to be quite light in it's process, and should be quick (loading SQLPLS being the exception.
		
		Output comes in the form of a sorted datatable, so it's ready for using anywhere else that you need it to.
	
	.PARAMETER SqlServer
		The SQL Server that you want to pull logs from, include the instance name if not a default instance:
		SERVER1
		SERVER1\INSTANCE2
	
	.PARAMETER StartDate
		The earliest date to search for.
	
	.PARAMETER EndDate
		The most recent date to search for
	
	.PARAMETER LogNumber
		Returns all rows for the given SQL Server Errorlog number - not yet implemented
	
	.PARAMETER TimeSpan
		Time in minutes to pull back log data for.
	
	.EXAMPLE
		Get-SqlErrorLog -SqlServer LOCALHOST
		Returns all logs for a given server
	
	.EXAMPLE
		Get-SqlErrorLog -SqlServer LOCALHOST -StartDate "2016-01-01"
		Returns all logs for a given server since 2016-01-01 (limited by the log retention on the SQL Server in question)
	
	.EXAMPLE
		Get-SqlErrorLog -SqlServer LOCALHOST -EndDate "2016-03-20"
		Returns all logs for a given server up to 2016-03-20
	
	.EXAMPLE
		Get-SqlErrorLog -SqlServer LOCALHOST -StartDate "2016-03-20 05:00" -EndDate "2016-03-21 17:00"
		Returns all log data for a given server between the two dates specified
	

	.EXAMPLE
		Get-SqlErrorLog -SqlServer LOCALHOST -TimeSpan 60
		Returns all log data for a given server from the last 60 minutes. 
		NOTE: The time is local to the client, so may not be correct for servers in different timezones.
	
	.NOTES
		
#>
function Get-SQLErrorLog
{
	[CmdletBinding(SupportsPaging = $true)]
	param
	(
		[Parameter(Mandatory = $true)]
		[string]
		$SqlServer,
		[datetime]
		$StartDate,
		[datetime]
		$EndDate,
		[int]
		$LogNumber,
		[bigint]
		$TimeSpan
	)
	
	#If null dates were passed in we can set start of time and end of time defaults
	IF ($StartDate -eq $null) { $StartDate = "1900-01-01" }
	IF ($EndDate -eq $null) { $EndDate = "9999-01-01" }
	
	IF ($TimeSpan -ne $null)
	{
		$StartDate = (Get-Date).AddMinutes(- $TimeSpan);
		$EndDate = Get-Date;
	}
	
	
	
	if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") -eq $null)
	{
		Write-Error "Unable to load the SMO assembly. Please validate that the SMO components are installed on this system.";
		break;
	}
	
	$smoConnection = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer
	$smoConnection.ConnectionContext.ConnectTimeout = 5;
	try
	{
		$smoConnection.ConnectionContext.Connect()
	}
	catch
	{
		Write-Error "Unable to connect to server: $SqlServer";
		break;
	}
	
	
	
	#region DataTables
	#Because we all cool and stuff we're going to return a datatable for the results, so let's create one with the right columns
	$ErrorLogData = New-Object System.Data.DataTable "ErrorLogData";
	$LogDate = New-Object System.Data.DataColumn LogDate, ([DateTime]);
	$Source = New-Object System.Data.DataColumn Source, ([string]);
	$Message = New-Object System.Data.DataColumn Message, ([string]);
	
	$ErrorLogData.Columns.Add($LogDate);
	$ErrorLogData.Columns.Add($Source);
	$ErrorLogData.Columns.Add($Message);
	
	#One more datatable to hold the logs that we've read
	$ReadLogs = New-Object System.Data.DataTable "ReadLogs";
	$ReadLogNumber = New-Object System.Data.DataColumn LogNumber, ([int]);
	$ReadLogs.Columns.Add($ReadLogNumber);
	#endregion
	
	
	#region Grab the logs
	
	
	#Grab a count of the number of error logs
	$ErrorLogCount = ($SMOConnection.EnumErrorLogs() | Measure-Object).Count;
	
	Write-Verbose "--------------------------------------------------------------------------------------------------------------"
	Write-Verbose "Checking error logs on server $SQLInstance for logs created between $StartDate & $EndDate"
	Write-Verbose "--------------------------------------------------------------------------------------------------------------"
	Write-Verbose "Checking through $ErrorLogCount logs, please wait."
	Write-Verbose "-----------------------------------------------"
	
	
	$ErrorLogList = $SMOConnection.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 $ErrorLogList)
	{
		
		IF ($ErrorLog.CreateDate -ge $StartDate -and $ErrorLog.CreateDate -le $EndDate)
		{
			
			$CurrentLog = $ErrorLog.CreateDate
			$CurrentLogName = $ErrorLog.Name
			Write-Verbose "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);
			$SMOConnection.ReadErrorLog(($ErrorLog.Name)) | Where-Object { $_.LogDate -ge $StartDate -AND $_.LogDate -le $EndDate } |
			% {
				$NewRow = $ErrorLogData.NewRow();
				$NewRow.LogDate = $_.LogDate;
				$NewRow.Source = $_.ProcessInfo;
				$NewRow.Message = $_.Text;
				$ErrorLogData.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-Verbose "Currently reading error log $LogToRead..."
		#write the log number to the $ReadLogs datatable
		$NewLogRow = $ReadLogs.NewRow(); $NewLogRow.LogNumber = $CurrentLogName; $ReadLogs.Rows.Add($NewLogRow);
		$SMOConnection.ReadErrorLog($LogToRead) | Where-Object { $_.LogDate -ge $StartDate -AND $_.LogDate -le $EndDate } |
		% {
			$NewRow = $ErrorLogData.NewRow();
			$NewRow.LogDate = $_.LogDate;
			$NewRow.Source = $_.ProcessInfo;
			$NewRow.Message = $_.Text;
			$ErrorLogData.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-Verbose "Currently reading error log 0..."
		$SMOConnection.ReadErrorLog(0) | Where-Object { $_.LogDate -ge $StartDate -AND $_.LogDate -le $EndDate } |
		% {
			$NewRow = $ErrorLogData.NewRow();
			$NewRow.LogDate = $_.LogDate;
			$NewRow.Source = $_.ProcessInfo;
			$NewRow.Message = $_.Text;
			$ErrorLogData.Rows.Add($NewRow);
		}
	}
	#>
	#endregion
	
	#Send back the datatable, already sorted, cos we're nice like that
	$ErrorLogData | Sort-Object LogDate
}