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!

 

Powershell Image Conversion script – Part 2: Logging functionality

Standard

One of the important parts of the script is the logging functionality. Because we’re dealing with large amounts of files that are converted it’s good to know what has been done and may have gone wrong.

The following code will write the logfile:

# Function Write-Logfile
# This function writes, in case of an error, a rule to the logfile
# The logfile will be created with the following format: yyyymmdd
#
# Parameters:
# [string] $message: Message that need to be written
Function Write-Logfile([string]$message)
{
# set up date
$date = Get-Date -format "yyyyMMdd"
# get time for the logging
$logtime = Get-Date -Format "[HH:mm:ss]"
#set up the path to logfile
$logfile = $logdir + "\" + "logfile-" + $date + ".log"
# add the contect to the file
Add-Content $logfile "$logtime $message"
}

The function will write a line in a logfile. If no logfile exists it will create a logfile with the following name: logfile-yyyymmdd.log. An example of the name could be: logfile-20100922.log
If the file already exists it will not be created, instead it will write to the file. The log in the file would look similar to this:

09:22 Image 87623e8668762 converted

The function uses the local variable $logfile that has been added as a parameter during execution.
To add the log functionality to the “Convert-Image” do the following:

After the code:

# Create a new imageobject
$oldImage = new-object System.Drawing.Bitmap $sourceImage
[/cc]

add the following code:
[cc lang="powershell"]
# If there is an error write to logfile
If($Error.Count -ne 0)
{
Write-Logfile($Error)
$Error.Clear()
}
[/cc]

After the code:
[cc lang="powershell"]
# Save the new image
$newImage.Save($newFile, ([system.drawing.imaging.imageformat]::$imageFormat))

add the following code:

# Write the results to a logfile
Write-Logfile "Image $newFile converted"