Testing Log Shipping with PowerShell and Pester

Standard

Thanks to my good friend Rob Sewell (b | t) I got into testing with PowerShell with Pester.
He showed me how to use the Pester module to test about anything you can test with PowerShell.

In my environment I have lots of databases being log shipped. To see the current status of the log shipping I either have to execute a procedure or execute a standard report from SSMS. And I know there are other solutions but in most cases it comes back to these two.

The procedure returns all the databases, the primary and the secondary, with the values for last backup time, copy time, restore time and the thresholds. This doesn’t show me the problems right away but it’s fast.
The report shows me in nice green and red colors which which is good and what’s not but I’d have to do it for every server manually. So it’s pretty but it’s slow.

Why isn’t there anything that has the best of both worlds where I have the speed of the query with the clear results of the report?

That’s where Pester comes in!

I can use Pester to test the values that come back from the procedure and do basically the same thing the report does.

The procedure returns all the databases, the primary and the secondary, with the values for last backup time, copy time, restore time and the thresholds.
There is also an overall value called “status” which shows a 0 when everything is fine and a 1 when something is wrong.

I wanted to keep the check as simple as possible. If everything is fine, I don’t need to go into much detail. If there is something wrong I want to know which part of the log shipping failed, like the backup, copy or restore.

The Overall Test

This test only looks at the the marker the log shipping sets to indicate if everything is OK or not.

.\LogShipping.Tests.ps1 -SqlServer sql1

Log Shipping Pester Overall

Ooh oh! Something failed. Good thing we have our test.

The Detailed Test

The detailed test goes into the detail of the last backup, last copy and last restore time. We want to know what went wrong and where.

In this case I only want to zoom in on the database with the failed test so I use the -Database parameter to give the name of the database.

.\LogShipping.Tests.ps1 -SqlServer sql1 -Database db1 -Detailed

Log Shipping Pester Detailed Single DB

Of course it’s possible to execute the detailed test for all the databases. Just remove the database parameter and the same tests are run for all the databases.

Log Shipping Pester Detailed All DB

The entire test took less than two seconds and made it possible for me to quickly see if everything is fine with the log shipping.

The code can be downloaded from github. I hope this helps you out and make your life a little easier as it did for me.

 

 

First version PSSQLLib released

Standard

libraryUPDATE:

The scripts for the PSSQLLib are now on GIT!

It’s been long in the making and I’ve finally made a Powershell module (PSSQLLib) which makes my life a little easier when it comes to getting information from my instances.

The module in question makes it possible to get information like the databases, databse users, the privileges of the logins and the database users, the configution settings and a lot more from any instance.

The module is just a first release and new features can certainly be added. If you’re missing a feature, leave a comment and I’ll get it in there.

For more information about the Powershell module can be found here.

Hope you enjoy it!

Tracking E-mail on Exchange With Powershell

Standard

I had a problem with the delivery of e-mails. The script presented below creates an Excel document with all the e-mails send from a specific e-mail address with the event.

The script takes four arguments:

  1. AddressList
  2. StartDate
  3. EndDate
  4. Output

AddressList

The address list is a mandatory parameter containing the full path to the text file contacining the e-mail adresses.

The file is formatted as follows:

Email
user01@domain1.com
user02@domain2.com

StartDate

The start date is the date from when the list is generated. The start date is not mandatory. If no parameter is given the standard start date is seven days from the day the script is executed.

EndDate

The end date is the date at which the list ends. The end date is not mandatory. If no parameter is given the standard end date is the day the script is executed.

Output

This parameter is full path to a directory where the files need to be saved. The parameter is not mandatory. If no paremeter is given the script will open a new Excel document for each e-mail address present in the source file.

The script outputs an Excel document containing e-mail information with the columns: Timestamp, Sender, Receiver, Event.

Usage
Without the start date and end date

Get-MessageTracking.ps1 -AddressList 'c:\addresses.txt' -Output 'c:\output'

With the start date and end date

Get-MessageTracking.ps1 -AddressList 'c:\addresses.txt' -StartDate '24/01/2011' -EndDate '31/01/2011' -Output 'c:\output'

The Code

# PowerShell script to get messagetracking of an e-mail address
#
# 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
# Implemented output to Excell file on disk
#
# Usage:
# .\Get-MessageTracking.ps1 -AddressList 'c:\addresses.txt' -Output 'c:\output'

Param(
[Parameter(position=1,Mandatory = $true )][string] $AddressList, # List of servers that needs to be checked. Has to a text file!
[Parameter(position=2,Mandatory = $false )][string] $StartDate, # Start date for the querie
[Parameter(position=2,Mandatory = $false )][string] $EndDate, # End date for the querie
[Parameter(position=2,Mandatory = $false )][string] $Output # Output directory for the Excell file. If no value is given an Excell sheet will open.

)

Process
{
# Set constant variables
Set-Variable -Name _DATESTART -Option Constant -Value (Get-Date).Adddays(-7)
Set-Variable -Name _DATEEND -Option Constant -Value (Get-Date)
Set-Variable -Name _WAITTIME -Option Constant -Value 2

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 Excel activity
# Value 1: Excell sheet will open on execute (Default value)
# Value 2: Excell sheet will be written to given path
$ExcelAction = 0;

If($Output -eq '')
{
Write-Host "INFO: Path for output not given. Excell sheet will be opened." -ForegroundColor Red -BackgroundColor Green
$ExcelAction = 1
$ErrorStatus = 3
}
ElseIf(Test-Path $Output)
{
Write-Host "INFO: Excell sheet will be opened written to $Output." -ForegroundColor Red -BackgroundColor Green
$ExcelAction = 2
$ErrorStatus = 3
}
Else
{
Write-Host "INFO: Path for output is set. Data will be written to $Output." -ForegroundColor Red -BackgroundColor Green
$ExcelAction = 2
$ErrorStatus = 3
}
# Check all the parameters
If($AddressList -eq '')
{
Write-Host "ERROR: Server list not given" -ForegroundColor Red -BackgroundColor Yellow
$ErrorStatus = 1
}
ElseIf(!(Test-Path $AddressList))
{
Write-Host "ERROR: Path to serverlist doesn't exist" -ForegroundColor Red -BackgroundColor Yellow
$ErrorStatus = 1
}
If($StartDate -eq '')
{
$dateStart = (Get-Date).Adddays(-7)
Write-Host "INFO: Start date is not given. Start date is set to: $dateStart." -ForegroundColor Red -BackgroundColor Green
$ExcelAction = 2
$ErrorStatus = 3

}
Else
{
$dateStart = $StartDate
}
If($EndDate -eq '')
{
$dateEnd = (Get-Date)
Write-Host "INFO: End date is not given. End date is set to: $dateEnd." -ForegroundColor Red -BackgroundColor Green
$ExcelAction = 2
$ErrorStatus = 3
}
Else
{
$dateEnd = $EndDate
}
# Check if any error occured in the parameters
If($ErrorStatus -eq 0)
{
Write-Host ""
Write-Host "No errors found in the parameters. Continuing....." -ForegroundColor Green
Start-Sleep -Seconds $_WAITTIME
}
ElseIf($ErrorStatus -eq 1)
{
Write-Host ""
Write-Host "Critical errors found. Exiting..." -ForegroundColor Red
Start-Sleep -Seconds $_WAITTIME
Exit
}
ElseIf($ErrorStatus -eq 2)
{
Write-Host ""
Write-Host "Warnings found in the parameters. Continuing....." -ForegroundColor Blue
Start-Sleep -Seconds $_WAITTIME
}
ElseIf($ErrorStatus -eq 3)
{
Write-Host ""
Write-Host "Informational messages found. Continuing....." -ForegroundColor Green
Start-Sleep -Seconds $_WAITTIME
}

#Get all the adresses
$addresses = Import-Csv $AddressList

#Read through the contents of the source file
Foreach($address In $addresses)
{

# Set the settings for the Excel sheet
If($ExcelAction -eq 1)
{
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
}
ElseIf($ExcelAction -eq 2)
{
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $False
#$Excel = $Excel.Workbooks.Open()
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

}

#Counter variable for rows
$intRow = 1

$Sheet.Cells.Item($intRow,1) = "TIMESTAMP"
$Sheet.Cells.Item($intRow,2) = "SENDER"
$Sheet.Cells.Item($intRow,3) = "RECIPIENTS"
$Sheet.Cells.Item($intRow,4) = "EVENT"

#Format the column headers
For ($col = 1; $col –le 4; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}

#Increase the row number
$intRow++

#Get the e-mail addres from the list
$emailAddress = $address.Email

#Get the results
$results = Get-TransportServer | Get-MessageTrackingLog -Sender $emailAddress -Start $_DATESTART -End $_DATEEND | Select-Object Timestamp, Sender, Recipients, EventId | sort Timestamp

Foreach($result In $results)
{
#Write-Host $result.Timestamp ',' $result.Sender ',' $result.Recipients ',' $result.EventId

# Insert the values
$Sheet.Cells.Item($intRow, 1) = $result.Timestamp
$Sheet.Cells.Item($intRow, 2) = $result.Sender
$Sheet.Cells.Item($intRow, 3) = $result.Recipients
$Sheet.Cells.Item($intRow, 4) = $result.EventId

# Increment the row number
$intRow ++
}

# Increment the row number
$intRow ++

# Transform the email address for a valid file name
$at = $emailAddress.IndexOf("@")
$filename = $emailAddress.ToLower().Replace(".", "_").Substring(0, $at)
$destination = $Output + "\$filename"

# Automatically size the field in the excel sheet
$Sheet.UsedRange.EntireColumn.AutoFit()

# Close the document if it's automatically written to disk
If($ExcelAction -eq 2)
{
$Excel.SaveAs($destination, 56)
$Excel.Close($False)
}
}
}

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!

 

Check SQL Server Jobs with Powershell

Standard

I like to automate as much as possible using scripts with SQL Server. So one of the things I want to know is if any job has failed in a list of servers. I’m a fan of Powershell because it’s so powerfull and is easy to develop.

This script uses Excel to write the status of the different jobs on a server.

The script can be run using two arguments:

  1. ServerList
  2. Output

ServerList

The server list parameter is a mandatory list that holds all the different instances that need to be checked. The file is a text file with on every new line a instance. Make sure the full instance is put in when a named instance is used like: SQL01\SQLEXPRESS or SQL01\name.

In case of a default instance only the name of the server needs to be put in.

Output

The ouput parameter is used when you don’t want to see the Excel sheets. Instead the scripts writes the file directly to the given folder.

Usage

The script can be used like this:

powershell CheckSQLJobs.ps1 -Serverlist 'c:\temp\servers.txt' -Output 'c:\temp\sqljobscheck'

The code can be seen below:

# PowerShell script to return all the database disk recovery points
# from the DPM database
#
# Author: Sander Stad
# Version: 1.1 November 2010 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
# 	Adjusted query to give percentage with precission of 2
# 	Adjusted query to order the datasource names from a-z
#	Implemented output to Excell file on disk
#	Color implemented for several values
#
# Format of ServerList file
#	Named instance: 	SERVERNAME\INSTANCENAME 
#	Default instance: 	SERVERNAME
#	Every server must be on a new row.

Param(
	[string] $ServerList,				# List of servers that needs to be checked. Has to a text file!
	[string] $Output					# Output directory for the Excell file. If no value is given an Excell sheet will open.
)

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

# Set constant variables
Set-Variable -Name _WAITTIME	-Option Constant -Value 2

# 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 Excel activity
# Value 1: Excell sheet will open on execute (Default value)
# Value 2: Excell sheet will be written to given path
$ExcelAction = 0;


If($Output -eq '')
{
	Write-Host "INFO: Path for output not given. Excell sheet will be opened." -ForegroundColor Red -BackgroundColor Green
	$ExcelAction = 1
	$ErrorStatus = 3
}
ElseIf(Test-Path $Output)
{
	$Output = $Output + '\SQLJobReport_' + (Get-Date).ToString("yyyyMMddHHmmss") + '.xls'
	Write-Host "INFO: Excell sheet will be opened written to $Output." -ForegroundColor Red -BackgroundColor Green
	$ExcelAction = 2
	$ErrorStatus = 3
}
Else
{
	Write-Host "INFO: Path for output is set. Data will be written to $Output." -ForegroundColor Red -BackgroundColor Green
	$ExcelAction = 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
}




# Check if any error occured in the parameters
If($ErrorStatus -eq 0)
{
	Write-Host ""
	Write-Host "No errors found in the parameters. Continuing....." -ForegroundColor Green
	Start-Sleep -Seconds $_WAITTIME
}
ElseIf($ErrorStatus -eq 1)
{
	Write-Host ""
	Write-Host "Critical errors found. Exiting..." -ForegroundColor Red 
	Start-Sleep -Seconds $_WAITTIME
	Exit
}
ElseIf($ErrorStatus -eq 2)
{
	Write-Host ""
	Write-Host "Warnings found in the parameters. Continuing....." -ForegroundColor Blue
	Start-Sleep -Seconds $_WAITTIME
}
ElseIf($ErrorStatus -eq 3)
{
	Write-Host ""
	Write-Host "Informational messages found. Continuing....." -ForegroundColor Green
	Start-Sleep -Seconds $_WAITTIME
}

# Set the settings for the Excel sheet
If($ExcelAction -eq 1)
{
	#Create a new Excel object using COM 
	$Excel = New-Object -ComObject Excel.Application
	$Excel.visible = $True
	$Excel = $Excel.Workbooks.Add()
	$Sheet = $Excel.Worksheets.Item(1)
}
ElseIf($ExcelAction -eq 2)
{
	#Create a new Excel object using COM 
	$Excel = New-Object -ComObject Excel.Application
	$Excel.visible = $False
	#$Excel = $Excel.Workbooks.Open()
	$Excel = $Excel.Workbooks.Add()
	$Sheet = $Excel.Worksheets.Item(1)
	
}

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
Foreach ($instance in Get-Content $ServerList)
{
	$instance
    #Create column headers
    $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME: " + $S
    #$Sheet.Cells.Item($intRow,2) = $instance
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,2).Font.Bold = $True

    $intRow++

    $Sheet.Cells.Item($intRow,1) = "JOB NAME"
    $Sheet.Cells.Item($intRow,2) = "LAST RUN OUTCOME"
    $Sheet.Cells.Item($intRow,3) = "LAST RUN DATE"

    #Format the column headers
    For ($col = 1; $col –le 3; $col++)
    {
        $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
        $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
        $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
    }

    $intRow++     

    # Create an SMO connection to the instance
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
	
	# Get the jobs from the instance
    $jobs = $srv.JobServer.Jobs

	# Loop through the jobs
	ForEach ($job in $jobs)  
	{ 
		# Formatting for the failed jobs 
		if ($job.LastRunOutcome -eq 0) { $fgColor = 3 } 
		else {$fgColor = 0 } 

		# Insert the values
		$Sheet.Cells.Item($intRow, 1) =  $job.Name 
		$Sheet.Cells.Item($intRow, 2) = $job.LastRunOutcome.ToString() 
		$Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor 
		$Sheet.Cells.Item($intRow, 3) =  $job.LastRunDate
		
		# Increment the row number
		$intRow ++ 	   
	} 
	
	# Increment the row number
   	$intRow ++ 

}

# Automatically size the field in the excel sheet
$Sheet.UsedRange.EntireColumn.AutoFit()

# Close the document if it's automatically written to disk
If($ExcelAction -eq 2)
{
	$Excel.SaveAs($Output, 56)
	$Excel.Close($False)
}

 

 

Powershell Ping GUI

Standard

When you have a lot of servers in your environment you may want to know if a server isn’t reachable anymore.

I created a little script that shows a Window with a start button. On the moment the start button is clicked it will start sending ping commands to the server/computers in the text file.

Printscreen GUI Ping

The file with the servers\computers is a textfile with a different server/computer on every line.

Powershell Image Conversion script – Part 4: Finalizing and check functionality

Standard

This the final part of the series. I’ll show the different functions that check several parameters so the script will run flawlessly.

The Init function

The name “Init” is a name I gave to the function becaue it’s the initial function that calls all the other functions.
The first part of the function look like this:

Function Init(){
$Error.Clear()

# If the createconfig parameters is set
If($createconfig -ne "")
{
Create-Config $createconfig
exit 0
}

First all the errors are cleared so no messages from former executions will be in the logfile.
Second the script checks if the script is executed to create a config. If the parameter is set the “Create-Config” function is called.

The next part of the script checks if the parameter “config” is set.

# Check for the "config" parameter. If so load the file
If($config -ne "")
{
# Does the file exist
If((Check-Config $config) -eq 0)
{
$xmlconfig = [xml]( Get-Content $config)
$source = $xmlconfig.Configuration.SourceImages

$sourceorg = @()
Foreach($s In $xmlconfig.Configuration.SourceReorganisation.Source)
{
$sourceorg = $sourceorg + $s
}

$destimage = $xmlconfig.Configuration.DestinationImage
$destthumb = $xmlconfig.Configuration.DestinationThumb

$destorg = @()
Foreach($d In $xmlconfig.Configuration.DestinationReorganisation.Destination)
{
$destorg = $destorg + $d
}

$archive = $xmlconfig.Configuration.ImageArchive
$sizeimage = $xmlconfig.Configuration.SizeImage
$sizethumb = $xmlconfig.Configuration.SizeThumb
$sourceext = $xmlconfig.Configuration.SourceExtension
$targetext = $xmlconfig.Configuration.TargetExtension
$logdir = $xmlconfig.Configuration.Logdirectory

$cleanfolder = @()
Foreach($c In $xmlconfig.Configuration.CleanFolder.Folder)
{
$cleanfolder = $cleanfolder + $c
}
}
Else
{
exit 0
}
}

It checks if the file exists. If it does it the file is loaded. If not the script is stopped. The config file is not mandatory for the script because it can work with only parameters.

The next part is to check all the values:

# Check values
If((Check-Logdir $logdir) -ne 0){exit 0}
If((Check-SourceExtension $sourceext) -ne 0){ exit 0 }
If((Check-TargetExtension $targetext) -ne 0){ exit 0 }
If((Check-Dimensions $sizeimage $sizethumb) -ne 0){ exit 0}
If((Check-Directories $source) -ne 0){ exit 0}
If((Check-Directories $destimage) -ne 0){ exit 0}
If((Check-Directories $destthumb) -ne 0){ exit 0}
If((Check-Directories $archive) -ne 0){ exit 0}
If((Check-Directories $archive) -ne 0){ exit 0}

Function Check-Logdir

The code check if the logdir is given or exists. It look like this:

# Function Check-Logdir
# This function checks the logdirectory
#
# Parameters:
# [string] $strLogdir: Directory variable
# Return:
# [int] 0: Succes
# [int] 1: Error
Function Check-Logdir([string]$strLogdir)
{
#If no log directory is given
If([string] $logdir -eq "")
{
$message = "No log directory is given"
Write-Host $message
Return 1
}
#If the log directory does not exist
If(!(Test-Path $strLogdir))
{
$message = "The log directory $strLogdir does not exist!"
Write-Logfile $message
Write-Host $message
Return 1
}

Return 0
}

Function Check-SourceExtension

The code check for a source extension parameter. If no parameter is given it uses the standard source extension.

# Function Check-SourceExtension
# This function checks the the given extension
#
# Parameters:
# [string] $strExt: Extension the script searches for
# Return:
# [int] 0: Success
# [int] 1: Error
Function Check-SourceExtension([string]$strExt)
{
If([string] $strExt -eq "")
{
$message = "No source extension is given. Standard extension $SOURCEEXTENSION will be used."
Write-Logfile $message
Write-Host $message
Return 0
}

return 0
}

Function Check-TargetExtension

This function works the same as the function Source-Extension but sets a different value in case of an error.

# Function Check-SourceExtension
# This function checks the the given extension
#
# Parameters:
# [string] $strExt: Extension the script will convert the images to
# Return:
# [int] 0: Success
# [int] 1: Error
Function Check-TargetExtension([string]$strExt)
{
If([string] $strExt -eq "")
{
$message = "No target extension is given. Standard extension $TARGETEXTENSION will be used."
Write-Logfile $message
Write-Host $message
Return 0
}

return 0
}

Function Check-Dimensions

The following function checks if the dimensions given at the parameters are right.

# Function Check-Dimensions
# This function checks the given dimensions voor the image resizing
#
# Parameters:
# [int] $sizeimage: Size of large image
# [int] $sizethumb: Size of thumbnail
# Return:
# [int] 0: Success
# [int] 1: Error
Function Check-Dimensions($sizeimg, $sizethb)
{
# If the size of the resize is greater than the original
If($sizeimg -ne "" -and $sizeimg -lt $sizethb)
{
$message = "Size of the large image is greater than the thumbnail size."
Write-Logfile $message
Write-Host $message
Return 1
}

# If no parameter is given for the larger image
If($sizeimg -eq "" -or 0)
{
$message = "No size given for the image."
Write-Logfile $message
Write-Host $message

Return 1
}
# If no parameter is given for the thimbnail image
If($sizethb -eq "" -or 0)
{
$message = "No size is given or the thumbnail."
Write-Logfile $message
Write-Host $message

Return 1
}

return 0
}

Function Check-Directories

This function checks if a directory value is given or if it exists.

# Function Check-Directories
# This function checks the directory is given or exists
# doorgegeven variabele een waarde bevat
#
# Parameters:
# [string] $strDir: Directory
# Return:
# [int] 0: Success
# [int] 1: Error
Function Check-Directories([string]$strDir)
{
#If no directory is given
If([string] $strDir -eq "")
{
$message = "No directory given!"
Write-Logfile $message
Write-Host $message
Return 1
}
#If directory doesn't exist
If(!(Test-Path $strDir))
{
$message = "Directory $strDir doesn't exist!"
Write-Logfile $message
Write-Host $message
Return 1
}

Return 0
}

The next part is to get the image format of the target extension:

# Get the image format
$imageFormat = Get-Imageformat $targetext

The code for getting the image format looks like this:

# Function Get-Imageformat
# Get the image format based on the parameter
#
# Parameters:
# [string] $strExt: String with extension
# Return:
# [string] $imageFormat: String with image format
Function Get-Imageformat([string]$strExt)
{
$imageFormat = "";
# Controleer de extensie
Switch($strExt)
{
# Set the extension
"gif" {$imageFormat = "gif"}
"GIF" {$imageFormat = "gif"}
"jpg" {$imageFormat = "jpeg"}
"jpeg" {$imageFormat = "jpeg"}
"tiff" {$imageFormat = "tiff"}
"TIFF" {$imageFormat = "tiff"}
"png" {$imageFormat = "png"}
"PNG" {$imageFormat = "png"}

# If the given parameter does not ccontain a right image format
default
{
$message = "The extension os no known image format!"
Write-Logfile $message
Write-Host $message
exit 0
}
}
# Return the image format
Return $imageFormat
}

The next part is to get all the files and folders from the given directory.

# Get alle the files and directories
$arraySourcedir = Get-Sourcedirs $source $sourceext

The code to get all the files and folders looks like this:
[cc lang="powershell"]
# Function Get-Sourcedirs
# This function returns all the subdirectories and files from the given source directory
#
# Parameters:
# [string] $s
# Resturns
# [array] $y: Array with all subdirectories and files
Function Get-Sourcedirs([string]$s, [string]$e)
{
# Execute the Get-Child-Item function
$y = Get-ChildItem -r $s -include $e | % { $_.FullName }

# Return results
return $y
}

The next part is to start converting the images

# If there any results in the sourcearray
If($arraySourcedir.length -gt 0)
{
# Loop through the functions for image conversion
Foreach($item In $arraySourcedir)
{
Convert-Image $item $destimage $sizeimage $targetext $imageFormat
Convert-Image $item $destthumb $sizethumb $targetext $imageFormat
Move-Image $item $archive
}
}
Else
{
# Write error in logfile
$message = "No images found in given directory!"
Write-Host $message
Write-Logfile $message
}
}

In this part of code another function is called named “Move-Image”. This function moves the original file to the given archive directory. The code looks like this:

# Function Move-Image
# This function moves the original image.
# It checks if the target directory exists. If not it creates the directory
#
# Parameters:
# [string] $imageLocation: Original location of the image
# [string] $to: Target location of the image
Function Move-Image([string]$imageLocation, [string] $to)
{
# Create a new path for the file
$newImageLocation = Replace-String $source $to $imageLocation
# Get the directory from the path
$newFolder = [System.IO.Path]::GetDirectoryName($newImageLocation)

# Test if the target directory exists
If(!(Test-Path $newFolder))
{
# If not create it
New-Item $newFolder -type directory
}

# Move the image
Move-Item $imageLocation $newImageLocation -force
}

The above code uses the function “Replace-String” that is also used in the function “Convert-Image”.

The last part of the code starts the reorganization and cleaning of the directory:

# Reorganize directories
Reorganise-Folders $sourceorg $destorg

# If the "cleanfolder" parameter is set
If($cleanfolder)
{
If($cleanfolder.length -gt 0)
{
Clean-Folder $cleanfolder
}
}

The function for reorganizing the directory by selecting the first three characters from the image’s directory. Creating an new directory and move the images to the archive:

# Function Reorganise-Folders
# This function reorganises the directories by selecting the first three characters
# from the original directory.
# Example: 1020567 will be replaced with 102
#
# Parameters:
# [array] $s: Source directory
# [array] $d: Target directory
Function Reorganise-Folders($s, $d)
{
# Check if the directories are correct and exist
If($s.length -and $d.length -gt 0)
{
For($i = 0; $i -lt $s.length; $i++)
{
If((Check-Directories $s[$i]) -ne 0){ exit 0}
If((Check-Directories $d[$i]) -ne 0){ exit 0}

# Get alle directories
$arraySourcedirs = Get-ChildItem $s[$i] | Where { $_.attributes -eq "Directory" }

# Loop through the array
If($arraySourcedirs.length -gt 0)
{
Foreach($item in $arraySourcedirs)
{
# Convert the item in the array to string
[string] $dir = $item

# Check if the length of the directory is no greater than 3
If($dir.length -gt 3)
{
# Create the new name
$newName = $dir.Substring(0,3);

# Create the old en new full paths
$oldDir = $s[$i] + "\" + $dir
$newDir = $d[$i] + "\" + $newName + "\" + $dir

# Test if the new path exists
If(!(Test-Path $newDir))
{
# If not create it
New-Item $newDir -type directory
}

# Move the images from the directory to the new one
#Move-Item $oldDir $newDir -force -verbose
Write-Host "robocopy.exe $olddir $newdir /E /S /R:10 /MOVE"
robocopy.exe $olddir $newdir /E /R:10 /MOVE /IS
}
}
}
}
}

An example for an archive folder would be:

  • C:\Imageconversion\Archive\104\10453678\

Where the original directory would be:

  • C:\Imageconversion\Source\10453678\

The last function cleans all the folder given. The original images are moved away from the source directory what leaves empty directories. To clean them the following function is used:

# Function Clean-Folders
# This function makes it possible to clean up the empty directories
#
# Parameters:
# [array] $folder: Location of the folder that need to be cleaned
Function Clean-Folder($folder)
{
Foreach($i In $folder)
{
# Get all the subdirectories
$f = Get-ChildItem $i | Where-Object {$_.PSIsContainer -eq $True}

# Check the length of the results
If($f.length -gt 0)
{
# Loop door de items heen
Foreach ($item in $f)
{
# Check if the directory doesn't contain any files
If($item.GetFiles().Count -eq 0)
{
# Create path to dir
$d = $i + "\" + $item
# Check if the path still exists
If(Test-Path $d)
{
# Remove dir
Remove-Item $d -Recurse
}
}
}
}
}
}

I hope you enjoyed the article. It contained a lot functionality what created the solution. If you have any recommendation or remarks about the article leave a comment.

The full script can be downloaded from here.

Powershell Image Conversion script – Part 3: Configuration functionality

Standard

Because I’m one of the type of people that is lazy as hell as it comes to IT, I created functionality that enabled me to create and read a configuration file for the script.

The first part of the article will explain the proces of creating a configuration file. The second part will show how the script works wih reading the configuration file.

In case of configuration files you have a lot of choices what type of file you use. There is a simple text file with the settings separated by comma of a new line . You can choose to use a Microsoft Access DB of any other database type but that will make you rely on a third-party product.
The reason why I chose XML is because it a good standard for setting op data. There are a lot of articles around that show you how to create and read config file. Further I like XML because it just look real clean.

Creating the configuration file

Like I mentioned before  I don’t like to do things by hand too much and I wanted other people to be able to implement the script in a way that you don’t have to be a rocket scientist to set it up.
With the use of input boxes  you have an easy way to let users input values that we can use to create the configuration file.

Powershell doesn’t support input boxes but Visual Basic does. The following code show the function:

# Function Show-Inputbox
# This function generates a inputbox where values can be inserted
#
# Parameters:
# [string] $question: Question in screen
# [string] $title: Title of the input box
# [string] $default: Default value if the cancel button is clicked
Function Show-Inputbox([string]$question, [string]$title, [string]$default)
{
[reflection.assembly]::loadwithpartialname("microsoft.visualbasic") | Out-Null
[microsoft.visualbasic.interaction]::InputBox($question, $title, $default)
}

The reason I created this function is because the function that will create the configuration file will use this functionality multiple times.

The following code will set up the part where the settings are picked up from the user:

# Function Create-Config
# This function makes it possible to create a coniguration file
#
# Parameters:
# [array] $loc: Full path where the configuration will be created
Function Create-Config($loc)
{
# Get the settings
$var1 = Show-Inputbox "Source directory images" "Source directory Images" ""
$var2 = Show-Inputbox "Source directory for reorganisation" "Source directory Reorganisation" ""
$var3 = Show-Inputbox "Target directory for images" "Target directory images" ""
$var4 = Show-Inputbox "Target directory for thumbnails" "Target directory thumbnails" ""
$var5 = Show-Inputbox "Target directory for reorganisation" "Target directory Reorganisation" ""
$var6 = Show-Inputbox "Archive directory for images" "Archive directory" ""
$var7 = Show-Inputbox "Size for images" "Size images" ""
$var8 = Show-Inputbox "Size for thumbnails" "Size thumbnails" ""
$var9 = Show-Inputbox "Source extension" "Source extension" ""
$var10 = Show-Inputbox "Target extension" "Target Extension" ""
$var11 = Show-Inputbox "Directory for logfiles" "Log directory" ""
$var12 = Show-Inputbox "Dierctory to clean up" "Directory for cleaning" ""

As you can see the function for the input box is called 12 times to get all the settings.

The next part will show how the XML file is set up and saved:

# Setup XML data
$xmlconfig = [xml]"
$var1
$var2
$var3
$var4
$var5
$var6
$var7
$var8
$var9
$var10
$var11
$var12
"

# Save the data
If(!(Test-Path $loc))
{
New-Item $loc -type file
}
$xmlconfig.save($loc)
}

The function can be used in the following way:

Create-Config "E:\Imageconversion\MyConfiguration.xml"

Reading the configuration file

Now we have a way to reate a configuration file. But how do we read the data from it. This can be done with the following code:

$xmlconfig = [xml]( Get-Content $config)
$source = $xmlconfig.Configuration.SourceImages

$sourceorg = @()
Foreach($s In $xmlconfig.Configuration.SourceReorganisation.Source)
{
$sourceorg = $sourceorg + $s
}

$destimage = $xmlconfig.Configuration.DestinationImage
$destthumb = $xmlconfig.Configuration.DestinationThumb

$destorg = @()
Foreach($d In $xmlconfig.Configuration.DestinationReorganisation.Destination)
{
$destorg = $destorg + $d
}

$archive = $xmlconfig.Configuration.ImageArchive
$sizeimage = $xmlconfig.Configuration.SizeImage
$sizethumb = $xmlconfig.Configuration.SizeThumb
$sourceext = $xmlconfig.Configuration.SourceExtension
$targetext = $xmlconfig.Configuration.TargetExtension
$logdir = $xmlconfig.Configuration.Logdirectory

$cleanfolder = @()
Foreach($c In $xmlconfig.Configuration.CleanFolder.Folder)
{
$cleanfolder = $cleanfolder + $c
}

As you can see some variables in the script can handle multiple directories. In the Create-Config function I didn’t implement the feature to add multiple values because in my case that wasn’t an issue.
The above code uses the global variables created in the beginning of our first script by creating parameters for Powershell.

I didn’t make a function for reading the configuration file because later on it’ll only be used once during the execution of the script.