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.

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.

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:

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

With the start date and end date

The Code

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:

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:

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:

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:

The code can be seen below:

 

 

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.

0

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:

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.

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:

Function Check-Logdir

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

Function Check-SourceExtension

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

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-Dimensions

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

Function Check-Directories

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

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

The code for getting the image format looks like this:

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

The next part is to start converting the images

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:

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:

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:

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:

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:

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:

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:

The function can be used in the following way:

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:

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.