Get DAG Database Distribution Function

PowerShell is great for repeatable administration shortcuts. I had created a script to a while ago that provided the mounted database distribution on each Database Availability Group server as part of a larger routine status reporting script. I found that this particular task of know the database distribution during the business day was helpful so I converted it to a function and included into my PowerShell profile. That way I could just run the Get-DagDbCount function without hunting around my various scripts directories trying find it and run it in script form.

It is really quite simple and makes use of an empty array populated with custom PSObjects with just server name and the number of mounted databases on it. Comments in the script should make everything clear on what is happening.


Using Search-Mailbox -SearchQuery

I had recently been given the task to search all mailboxes in an Exchange 2013 on premises organization for a specific keyword phrase. Naturally I attempted to use the eDiscovery and Litigation hold feature of the EAC but was severely limited by the number of mailboxes I could search at one time. As always I’m looking for a way to automate time consuming tasks. The PowerShell cmdlet New-MailboxSearch also didn’t help much either. After submitting a new search I attempted to view the status and I get a Watson error.


Enter the older but tried and true Search-Mailbox cmdlet. It took me a while to figure out how to use the -SearchQuery switch though. I had checked through the documentation on how to do Exchange Search using the Advanced Query Syntax (AQS) but nothing was very clear on my problem.

Read moreUsing Search-Mailbox -SearchQuery

Select A Random Transport Service

I’ve recently created a simple function to select a random transport service on Exchange 2013. My thoughts are that the cmdlet Send-MailMessage only allows specifying a single server and this has been bothering me for a while now. I have many scripts that provide output via email. So what if that server I’ve specified with Send-MailMessage is not available? The script is dead in the water when it comes to reporting. The ability to select a random transport service is nice if your messaging environment does not provide any other means (load balanced relay for example).

Read moreSelect A Random Transport Service

Send HTML email with PowerShell

Quite a few Exchange administrator like to send HTML email with PowerShell scripts for reporting to a group of people, and I’m no different. The code below is my solution to sending HTML email messages using the CSS style element to control all the formatting.  This includes font family, font size, table borders, and table header background color. The goal of the HTML design decisions here are an attempt to improve readability on smart phones by using a  smaller font size and color in the table headers to add some dimension.

Below is the head section of the HTML message and there a few things to take note of.  You will see at the end of each line there is an rn.  These are special characters used for formatting which creates a carriage return and new line.  On line 7 you will see the H3 tag which includes style for any H3 tag used in the HMTL messages.

When using the ConvertTo-HTML cmdlet and the -fragment tag you are only creating a table. Unfortunately there is no way to control the formatting of the HTML output and it is a bit of a mess. You can find additional information about the ConvertTo-HTML cmdlet on technet.

Finally you’ll want to put together various HTML sections and form the final HTML document that will be sent as a report. Be careful to make sure that your HTML document is well formed. As the complexity of your reports increases it become more likely you’ll make the mistake of adding extra HTML code that will cause some formatting headaches.

The body of the email message will appear as below:
PowerShell HTML table

The example script is below:


Get DAG Mailboxes Faster with Start-Job

Have you ever needed to increase the speed of the get-mailbox cmdlet?   This Exchange PowerShell code is a building block that provides a way to get DAG mailboxes faster with start-job.  It will query the Database Availability Group within your Exchange organization and return all the mailbox databases.  Each mailbox database has an instance of get-mailbox run against it.

I’ve used the ForEach loop to process all mailbox databases individually and throttle how many jobs run at once.  This can be tuned to your liking and to the limits of what your environment can handle.  I’ve set the throttling to only run four jobs at a time in the code below.

The trick in this script is the IF and ELSE statements and the placement of the two start-job cmdlets.  The start-job cmdlet is necessary in the ELSE statement block otherwise the current pipeline object would get discarded before being processed once there are four jobs running.  Additionally, the wait-job -any cmdlet and switch will immediately start a new job when any currently running job is completed.

The very end of the script contains some cleanup code and uses the DO UNTIL statements to wait for all jobs to be completed before cleanup.  So just how much faster is this bit of code?  I’ve done a comparison between my code below and a single get-mailbox cmdlet that returns all mailboxes within the same DAG.

Get DAG Mailboxes Faster with Start-Job
Single Get-Mailbox cmdlet takes 10 minutes
Get DAG Mailboxes Faster with Start-Job
Start-Job takes three and a half minutes!

PowerGUI Script Editor



WSUS Reporting with Powershell Part 3

Edit: Find the complete script here.

This is the final installation that concludes WSUS reporting with PowerShell.  In part 1 I explained the basics of connecting to WSUS with PowerShell and making a few basic queries.  In Part 2 I covered using a specific group in WSUS and creating a summary of the updates that those group members needed.  In this post I’ll explain the following:

  • Filter the results to trim down the report.
  • Use HTML formatting to prepare an email.
  • Send the report via an HTML formatted email.

An example of the WSUS Reporting HTML email output is below:

WSUS Reporting HTML Email

WSUS Reporting with Powershell Part 2

Edit: Find the complete script here.

In my previous post I provided the basics of connecting to WSUS with PowerShell and making a few basic queries that will be used in a WSUS reporting script.  This is the second post about WSUS reporting and covers how to query a group in WSUS and provide a summary of computers that need updates from the most recent synchronization.

This script will provide an output in PowerShell but it is intended to be used in conjunction with an email alert.  The report has a very nice feature that takes the needed updates and converts them into a hyperlink with the text being the KB article number.

The result of the $SummaryStatus variable with the select-object filters above results in the following:

WSUS Reporting

The final section of this series involves filtering the returned data a little further and then using some HTML formatting to send an acceptable looking report.

WSUS Reporting with Powershell Part 1

Edit: Find the complete script here.

If you have ever used WSUS for pushing Microsoft patches then you’ll know that getting a quick report of the patching status can be cumbersome. This is the first post about WSUS reporting that will lay out the basics of using PowerShell to connect to WSUS and make some queries about the status of your environment. In this post I will explain the following.

  • How to load the WSUS assembly.
  • Make a connection to WSUS.
  • Create a computer scope and update scope, which is needed for making queries.
  • Generate a few basic queries that will be used in the WSUS reporting script.

The very first thing that must be done is to load the WSUS assembly.

The next thing to do is to make a connection to WSUS and set the connection into a variable for future use. At the end of the command substitute the WSUS server name, use $true to force a connection via SSL and finally enter the port number.

In order to make some queries within WSUS we’ll be using the GetSummariesPerComputerTarget method. This method requires a computer scope and additionally we will be using an update scope to refine the query. First create a default update scope and then modify it only include the latest approved revisions.

Next create the computer scope, no need to make a modification.

Now that we have this bit established we can take a look at a few things. Run the following to return the groups that you have established in the WSUS console:

Taking things a step further let’s identify one of the group ids and use Where-Object to filter a specific group and set it into a variable. Then enumerate all the members of the group:

You will now have detailed information about each computer object that is a member of your WSUS group stored in the $MemberOfGroup variable.  We’ll use this in the next section two find out what updates are needed for the members of this group and build upon the WSUS reporting script.

Redistribute Mailboxes Across an Exchange 2010 DAG

I have put together a script to redistribute mailboxes across an Exchange 2010 DAG and wanted to share my work with the Exchange community.  Over time some of the databases in an Exchange 2010 DAG  will have an uneven distribution of mailboxes.  The script in this post automates the assignment of mailboxes for redistribution based on a couple of criteria, standard mailbox count, large mailbox count and total mailbox count.  These definition of standard and large mailbox sizes are different in just about every environment.  Therefore, I’m making the assumption that if a mailbox in your environment is using the default database quotas, it is a standard mailbox.  Otherwise, the mailbox is not using the default database quotas and has a different, larger quota that is uniform across the board.  The high level steps are:

  • Gather all DAG databases.
  • Gather mailboxes on each database and categorize as standard or large.
  • Make calculations about each mailbox type per DAG database so that we can get accurate tallies and then evenly redistribute mailboxes.
  • Make new-moverequest assignments and track the changes that will occur on the mailbox count per database
  • Initiate the new-moverequest for mailboxes selected.

This script will require the Exchange 2010 snapin.

Set a variable for the new-moverequest batch name.

Get the databases within the DAG but skip recovery databases. Hopefully this makes the database gather process as generic as possible.

We’ll use $DBCountArray for tracking the mailbox count on each database. $MBXArray will be used to store every mailbox in your organization and leveraged to make each new-moverequest.

In this next code block we are taking each DAG database and then enumerating the mailboxes it contains.  This will take some time to execute so a progress bar is helpful here.  There are two variables used as counters (line 15 and 16) which are set to zero.  In the second foreach loop the script examines the current mailbox and determines if it is standard or large based on it using default database quotas or not.  The MBXObject variable records basic data about the mailbox and puts this data into the MBXArray.  At the end of the first foreach loop the script records each mailbox processed and tallies the count for each database.

We now take the $DBCountArray and pipe the collected counts to measure-object to get an average number for each of the three attributes; totalmailboxes, largemailbox, and standardmailbox.  Secondly use [Math] in order to round the averages to a whole number.

We’ll use all the calculated variables to continue building out what the database counts are within $DBCountArray.

In this block the script matches the current database in the foreach loop with where-object and makes a calculation on each criteria by subtracting the average value from the total value and adding the property to the $DBCountArray.  This lets us know where each database is in relation to the average.  The calculated number will be positive integer, negative integer, or will be zero.  This will be the basis on which database to move mailboxes from and where to move them to.

This section handles new-moverequest assignments for large mailboxes and tracking the database counts for each large mailbox.  The source databases are selected by sorting the totmbxdiff property using the descending switch.  Selecting the first one will always grab the database in the list that is farthest out of skew from the computed average.  I’ve devised a non elegant way to add and subtract each property in the array.  By using the do until code block the script nulls out the source and target during each iteration and will continue until either to source or target databases have reached zero, or in other words, until it runs out of mailboxes to move or a place to move them to.

This next section of code is rinse and repeat for the standard mailboxes.

If you have made it this far then take a peek at what has been generated in the $DBCountArray

Taking a look at the contents of the $MBXArray you will see something similar.  This example list shows standard mailboxes but the mailboxes are not assigned a database for a move request.  The $MBXArray will be used with the new-moverequest cmdlet to direct mailboxes to their new databases.

The final step is to initiate the new-move request.  This is accomplished by taking the $MBXArray and trimming out any mailboxes without the moverequest property populated using where-object.  The $MoveRequest variable contains all the mailboxes that will be moved so simply pipe that variable into a ForEach loop that creates a new move request.

Here is the script in it’s entirety:

Get IronPort Delivery Status With PowerShell

The Cisco IronPort E-mail Security Appliance (ESA) makes various statuses available in an XML format and you can use PowerShell to parse the XML data and get some useful information.  With this technique you can create a report about the IronPort delivery status in your environment.  Forget about trying to force PowerShell into using some sort of SSH connection method.  I’ve tried it and it isn’t pretty.  There is a security concern with the following method but it is a proof of concept that may work well in your environment.  Here are the ingredients to get you started:

  • A Cisco IronPort E-mail Security Appliance (ESA) and a need to access the status pages.
  • An account that can authenticate to the ESA, preferably with the guest role.
  • A locked down environment that will reduce attack surfaces within your organization.

The methodology is rather simple, use PowerShell to grab an XML status page and parse it into an object that can be massaged for your benefit.  Think of having a script send you a periodic report about the IronPort delivery status of your appliance, awesome!  To illustrate, I’ll be looking at the tophosts status since I really want to know about my partner organizations that could be having problems.  Why not automate and be proactive at the same time?

Read moreGet IronPort Delivery Status With PowerShell