Get SQL Server Logs with Powershell

Standard

In my last posts I talked about retrieving the Event Logs of Windows with Powershell. I had to do the same thing for the SQL Server logs with the same output.

The script works with three parameters:

  1. ServerList
  2. Output
  3. Rows

1. ServerList

This a mandatory parameter and has to point to a comma-seperated file with the following format:

Server,Instance
Server01,SSI01
Server02,SSI02

2. Output

This is the output directory for the results. The parameter is not mandatory but it’s very usefull when you have many servers.

3. Rows

This is the amount of rows that you want to search through. Some SQL Server logs tend to get pretty big (over 100.000 rules a day with some of my servers). The parameter is not mandatory but will be set to 100 if no value is given.

The script uses a script that I posted earlier to ping the host. This script can be found here.

In case the output parameter is used, the script will create a CSV file with the following format:

Servername,[X,!,V],[Comment]

The servername will be server from the server list. The second part will indicate if an error, warning or no error has been presented. The last part will show why a certain warning occured.

The code:

# PowerShell script to return errors and events status of a host
#
# Author: Sander Stad
# Version: 1.1 Januari 2011 tested on Powershell v2.0
#
# Changes:
# Version 1.0:
# Initial version worked as it should
# Version 1.1:
# Implementation of parameters
# Implementation of constants
# Error check on parameters
# Partially loaded classes
#
# Usage:
# Get-SQLLog2.ps1 -ServerList 'C:\Temp\Servers.txt' -Output 'C:\temp'

Param(
[Parameter(position=1,Mandatory = $true )][string] $ServerList, # List of servers that needs to be checked. Has to a text file!
[Parameter(position=2,Mandatory = $false )][string] $Output, # Output directory for the Excell file. If no value is given an Excell sheet will open.
[Parameter(position=3,Mandatory = $false )][string] $Rows # Amount of rows to search through
)

Process
{
# Loading partial classes
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Import the Library with the functionality
. ./Function_Ping-Host.ps1

# Clear the screen
CLS

# Set global error check
# Value 1: High error program will stop
# Value 2: Warning program will continue
# Value 3: Information about execution
$ErrorStatus = 0;

#Set file activity
# Value 1: Data will be written to screen (Default value)
# Value 2: Data wil be written to given path
$FileAction = 0;

If($Output -eq '')
{
Write-Host "INFO: Path for output not given. Data will be written to screen." -ForegroundColor Red -BackgroundColor Green
$FileAction = 1
$ErrorStatus = 3
}
ElseIf(Test-Path $Output)
{
$Output = $Output + '\Report_DailyCheck_Log_' + (Get-Date).ToString("yyyyMMddHHmmss") + '.csv'
Write-Host "INFO: CSV file will be written to $Output." -ForegroundColor Red -BackgroundColor Green
$FileAction = 2
$ErrorStatus = 3
}
Else
{
Write-Host "INFO: Path for output is set. Data will be written to $Output." -ForegroundColor Red -BackgroundColor Green
$FileAction = 2
$ErrorStatus = 3
}
# Check all the parameters
If($ServerList -eq '')
{
Write-Host "ERROR: Server list not given" -ForegroundColor Red -BackgroundColor Yellow
$ErrorStatus = 1
}
ElseIf(!(Test-Path $ServerList))
{
Write-Host "ERROR: Path to serverlist doesn't exist" -ForegroundColor Red -BackgroundColor Yellow
$ErrorStatus = 1
}

$Servers = Import-Csv $ServerList
#$ExportFile = "C:\temp\sander\"

# Setup date
$fromDate = (Get-Date).Adddays(-1)

# Initialize rows
If($Rows -eq '')
{
$Rows = 2000
}

Foreach ($Server in $Servers)
{
# Setup the full path to the server: Servername + Domain
If($Server.Instance -ne '')
{
$instance = $Server.Server + '\' + $Server.Instance
}
Else
{
$instance = $Server.Server
}

#Ping the host
If(Ping-Host $Server.Server)
{
try{
#Display instance
$instance

#Connect to the instance
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$instance"

# Get the warnings and errors into variables
$Errors = $sqlServer.ReadErrorLog() | where {$_.Text -like "Error*"} | where {$_.LogDate -gt $fromDate} | select -Last $Rows
$Warnings = $sqlServer.ReadErrorLog() | where {$_.Text -like "Warning*"} | where {$_.LogDate -gt $fromDate} | select -Last $Rows

# Check of the data needs to be wriiten to screen of to file
If($FileAction -eq 1)
{
If($Errors.Count -ge 1)
{
Write-Host "$instance`tX"
}
Elseif($Warnings.Count -ge 1)
{
Write-Host "$instance`t!"
}
Else
{
Write-Host "$instance`tV"
}
}
ElseIf($FileAction -eq 2)
{
If($Errors.Count -ge 1)
{
Write-Output "$instance,X" | Out-File -Append -Encoding Unicode -FilePath $Output
}
Elseif($Warnings.Count -ge 1)
{
Write-Output "$instance,!" | Out-File -Append -Encoding Unicode -FilePath $Output
}
Else
{
Write-Output "$instance,V" | Out-File -Append -Encoding Unicode -FilePath $Output
}
}
}
Catch [InvalidOperationException]
{
Write-Host "$instance: Could not connect to host!" -ForegroundColor Red
If($FileAction -eq 1)
{
Write-Host "$instance`t!"
}
ElseIf($FileAction -eq 2)
{
Write-Output "$instance,!,Could not connect" | Out-File -Append -Encoding Unicode -FilePath $Output
}
}
Catch [IO.IOException]
{
Write-Host "$instance: Could not connect to host!" -ForegroundColor Red
If($FileAction -eq 1)
{
Write-Host "$instance`t!"
}
ElseIf($FileAction -eq 2)
{
Write-Output "$instance,!,Network path not found" | Out-File -Append -Encoding Unicode -FilePath $Output
}
}
}
Else
{
Write-Host "$instance: Host could not be pinged!"
If($FileAction -eq 1)
{
Write-Host "$instance`t!"
}
ElseIf($FileAction -eq 2)
{
Write-Output "$instance,!,Host could not be pinged!" | Out-File -Append -Encoding Unicode -FilePath $Output
}
}
}
}

Hope you enjoy the script!

 

5 thoughts on “Get SQL Server Logs with Powershell

  1. AKP

    Hi,
    Thanks for your great script. But when i tried to run it as
    .\Get_SQLLog2.ps1 -ServerList ‘C:\Temp\Servers.txt’ -Output ‘C:\temp’

    It is giving me following error message:

    INFO: CSV file will be written to C:\temp\Report_DailyCheck_Log_20110831150133.csv.
    Exception calling “Send” with “1” argument(s): “Value cannot be null.
    Parameter name: hostNameOrAddress”
    At F:\docs\PS Scripts\Function_Ping-Host.ps1:19 char:16
    + if ($ping.send <<<< ($H).status -eq "Success")
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    Any idea, much more appreciated.

  2. Sean

    I like this script very much but when I run it I get the error that server can not be pinged. I have my Servers.txt file and it contains one servername without semicolons around it. Not sure how to get it to work? If you can offer any help I would greatly appreciate it. With regards.

    • Sander Stad

      Can you send me your servers.txt file. Maybe there is something wrong in there.
      Send it to “sanderstad at gmail dot com”

  3. vinod

    Hi I would like to have the script with send mail feature of the ping servers and error log . Appreciate if i could get with that modification.

Leave a Reply

Your email address will not be published.