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.

# Initialize function name.
Function Get-DagDbCount {
	# Return the status of all mailbox database copies.
	$DAGDbs = Get-MailboxDatabaseCopyStatus *
	# Return the DAG in the organization
	$Dag = Get-DatabaseAvailabilityGroup
	# Create empty array to hold DAG data.
	$DagTable = @()
	# Pipe the DAG members to a foreach-object loop to count mounted Dbs for each server. Then add
	#  results to array.
	$Dag.servers | foreach {
		$DagServerName = $_.Name
		$DagObject = New-Object PSObject
		$DagObject | Add-Member -type NoteProperty -Name "Server" -Value $DagServerName
		$DagObject | Add-Member -type NoteProperty -Name "MountedDbCount" `
		-Value (($DAGDbs | where {$_.mailboxserver -eq $DagServerName -and $_.status -eq "mounted"}) | measure).count
		$DagTable += $DagObject
	}
	# Show results to screen.
	$DagTable
}

 

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.

Search-Mailbox

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 `r`n.  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.

$HTMLHead = "`r`n"
$HTMLHead += "`r`n"
$HTMLHead += "</pre>`r`n"
$HTMLHead += "TABLE{border: 1px solid black; border-collapse: collapse; font-family: Arial, Helvetica, sans-serif; font-size: 8pt;}`r`n"
$HTMLHead += "TH{border: 1px solid black; background: #dddddd; padding: 5px; color: #000000;}`r`n"
$HTMLHead += "TD{border: 1px solid black; padding: 5px;}`r`n"
$HTMLHead += "H3{text-align:left; font-family: Arial, Helvetica, sans-serif; font-size: 15pt;}`r`n"
$HTMLHead += "<pre>`r`n"
$HTMLHead += "`r`n"

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.

$HTMLProcesses = $Processes | convertto-html -Fragment @{Label="Process Name";Expression={$_.ProcessName}}, `
@{Label="CPU"; Expression = {$_.CPU}},@{Label="Process ID"; Expression = {$_.ID}}

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.

$MailMessage= "
$HTMLHead

</pre>
<center>
<h3><b>Table Header</b></h3>
</center>
<pre>
$HTMLProcesses"

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

The example script is below:

<!--

Code highlighting produced by Actipro SyntaxEditor
http://www.ActiproSoftware.com/Products/DotNet/

--><span style="color: #008000;" data-mce-style="color: #008000;">#</span><span style="color: #008000;" data-mce-style="color: #008000;"> Get the currently running processes and only store svchost processes.</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$Processes</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span> <span style="color: #5f9ea0; font-weight: bold;" data-mce-style="color: #5f9ea0; font-weight: bold;">Get-Process</span><span style="color: #000000;" data-mce-style="color: #000000;"> | </span><span style="color: #5f9ea0; font-weight: bold;" data-mce-style="color: #5f9ea0; font-weight: bold;">Where-Object</span><span style="color: #000000;" data-mce-style="color: #000000;">{</span><span style="color: #800080;" data-mce-style="color: #800080;">$_</span><span style="color: #000000;" data-mce-style="color: #000000;">.ProcessName </span><span style="color: #ff0000;" data-mce-style="color: #ff0000;">-eq</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">svchost</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #000000;" data-mce-style="color: #000000;">}

</span><span style="color: #008000;" data-mce-style="color: #008000;">#</span><span style="color: #008000;" data-mce-style="color: #008000;"> Generate the HTML head section and include style elements.</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLHead</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;"><Html xmlns=`"http://www.w3.org/1999/xhtml`">`r`n</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLHead</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">+=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;"><Head>`r`n</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLHead</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">+=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;"><Style>`r`n</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLHead</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">+=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">TABLE{border: 1px solid black; border-collapse: collapse; font-family: Arial, Helvetica, sans-serif; font-size: 8pt;}`r`n</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLHead</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">+=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">TH{border: 1px solid black; background: #dddddd; padding: 5px; color: #000000;}`r`n</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLHead</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">+=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">TD{border: 1px solid black; padding: 5px;}`r`n</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLHead</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">+=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">H3{text-align:left; font-family: Arial, Helvetica, sans-serif; font-size: 15pt;}`r`n</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLHead</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">+=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;"></Style>`r`n</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLHead</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">+=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;"></Head>`r`n</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>

<span style="color: #008000;" data-mce-style="color: #008000;">#</span><span style="color: #008000;" data-mce-style="color: #008000;"> Convert the processes stored in $Processes with the -fragment switch and only</span><span style="color: #008000;" data-mce-style="color: #008000;">
#</span><span style="color: #008000;" data-mce-style="color: #008000;">  select a few key parts of the output.</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$HTMLProcesses</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span> <span style="color: #800080;" data-mce-style="color: #800080;">$Processes</span><span style="color: #000000;" data-mce-style="color: #000000;"> | </span><span style="color: #5f9ea0; font-weight: bold;" data-mce-style="color: #5f9ea0; font-weight: bold;">convertto-html</span> <span style="color: #5f9ea0; font-style: italic;" data-mce-style="color: #5f9ea0; font-style: italic;">-Fragment</span><span style="color: #000000;" data-mce-style="color: #000000;"> @{Label</span><span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">Process Name</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #000000;" data-mce-style="color: #000000;">;Expression</span><span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span><span style="color: #000000;" data-mce-style="color: #000000;">{</span><span style="color: #800080;" data-mce-style="color: #800080;">$_</span><span style="color: #000000;" data-mce-style="color: #000000;">.ProcessName}}, </span><span style="color: #5f9ea0; font-weight: bold;" data-mce-style="color: #5f9ea0; font-weight: bold;">`</span><span style="color: #000000;" data-mce-style="color: #000000;">
    @{Label</span><span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">CPU</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #000000;" data-mce-style="color: #000000;">; Expression </span><span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span><span style="color: #000000;" data-mce-style="color: #000000;"> {</span><span style="color: #800080;" data-mce-style="color: #800080;">$_</span><span style="color: #000000;" data-mce-style="color: #000000;">.CPU}},@{Label</span><span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">Process ID</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #000000;" data-mce-style="color: #000000;">; Expression </span><span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span><span style="color: #000000;" data-mce-style="color: #000000;"> {</span><span style="color: #800080;" data-mce-style="color: #800080;">$_</span><span style="color: #000000;" data-mce-style="color: #000000;">.ID}}

</span><span style="color: #008000;" data-mce-style="color: #008000;">#</span><span style="color: #008000;" data-mce-style="color: #008000;"> Create HTML email body by adding all the head section and $HTMLProcess table from above.</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$MailMessage</span><span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">
$HTMLHead
 <body>
     <center>
        <h3><b>Table Header</b></h3>
    </center>
    $HTMLProcesses
 </body>
</html>
</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>

<span style="color: #008000;" data-mce-style="color: #008000;">#</span><span style="color: #008000;" data-mce-style="color: #008000;"> Get the date and time.</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$DateTime</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span> <span style="color: #5f9ea0; font-weight: bold;" data-mce-style="color: #5f9ea0; font-weight: bold;">Get-Date</span> <span style="color: #5f9ea0; font-style: italic;" data-mce-style="color: #5f9ea0; font-style: italic;">-Format</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">ddd MM/dd/yyyy h:mm tt</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
<span style="color: #008000;" data-mce-style="color: #008000;">#</span><span style="color: #008000;" data-mce-style="color: #008000;"> Set subject line to include the $DateTime variable.</span>
<span style="color: #800080;" data-mce-style="color: #800080;">$EmailSubject</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">Send HTML email with PowerShell</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">+</span> <span style="color: #800080;" data-mce-style="color: #800080;">$DateTime</span>

<span style="color: #008000;" data-mce-style="color: #008000;">#</span><span style="color: #008000;" data-mce-style="color: #008000;"> Send an email with all the compiled data.</span>
<span style="color: #000000;" data-mce-style="color: #000000;">[</span><span style="color: #008080;" data-mce-style="color: #008080;">string</span><span style="color: #000000;" data-mce-style="color: #000000;">[]] </span><span style="color: #800080;" data-mce-style="color: #800080;">$EmailTo</span> <span style="color: #ff0000;" data-mce-style="color: #ff0000;">=</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">babcockscott@get-mailbox.net</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>
  <span style="color: #5f9ea0; font-weight: bold;" data-mce-style="color: #5f9ea0; font-weight: bold;">Send-MailMessage</span> <span style="color: #5f9ea0; font-style: italic;" data-mce-style="color: #5f9ea0; font-style: italic;">-To</span> <span style="color: #800080;" data-mce-style="color: #800080;">$EmailTo</span> <span style="color: #5f9ea0; font-weight: bold;" data-mce-style="color: #5f9ea0; font-weight: bold;">`</span>
  <span style="color: #5f9ea0; font-style: italic;" data-mce-style="color: #5f9ea0; font-style: italic;">-Subject</span> <span style="color: #800080;" data-mce-style="color: #800080;">$EmailSubject</span> <span style="color: #5f9ea0; font-style: italic;" data-mce-style="color: #5f9ea0; font-style: italic;">-From</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">Reporting@get-mailbox.net</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span> <span style="color: #5f9ea0; font-weight: bold;" data-mce-style="color: #5f9ea0; font-weight: bold;">`</span>
  <span style="color: #5f9ea0; font-style: italic;" data-mce-style="color: #5f9ea0; font-style: italic;">-Body</span> <span style="color: #800080;" data-mce-style="color: #800080;">$MailMessage</span> <span style="color: #5f9ea0; font-style: italic;" data-mce-style="color: #5f9ea0; font-style: italic;">-BodyasHTML</span> <span style="color: #5f9ea0; font-weight: bold;" data-mce-style="color: #5f9ea0; font-weight: bold;">`</span>
  <span style="color: #5f9ea0; font-style: italic;" data-mce-style="color: #5f9ea0; font-style: italic;">-SmtpServer</span> <span style="color: #800000;" data-mce-style="color: #800000;">"</span><span style="color: #800000;" data-mce-style="color: #800000;">MailRelayServer</span><span style="color: #800000;" data-mce-style="color: #800000;">"</span>

 

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

# Query all mailbox databases that are members of a DAG and that are not marked as
#  recovery databases.
$DAGDbs = Get-MailboxDatabase | Where-Object { $_.mastertype -eq "DatabaseAvailabilityGroup" -and $_.recovery -ne "true" }

# Use a Foreach loop to process each database.
ForEach ($DAGDb in $DAGDbs) {
	
	# Check the running status of jobs generated by this script to help throttle the
	#  concurrent jobs.
	$CheckJobs = Get-Job -Name GetMailboxes* | Where-Object { $_.State -eq 'Running' }
	
	# Use the If statement to throttle the number of concurrent jobs to four.
	if ($CheckJobs.count -le 3) {
		
		# When the number of running jobs is less than or equal to three, start
		#  another job using the DAG databases currently being processed within
		#  the ForEach loop.
		Start-Job -Name "GetMailboxes $DAGDb" -InitializationScript { Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010 } `
		-ArgumentList ($DAGDb.name) -ScriptBlock {
			Get-Mailbox -Database $Args[0] -ResultSize Unlimited
		}
		
		# Process the else statement if more than four jobs are running.
	} else {
		# Check the jobs and wait until one of them finishes.  Then kick off
		#  another Start-Job.
		$CheckJobs | Wait-Job -Any
		
		# Start a job with the DAG database currently being processed.  This job
		#  ensures that the current pipeline object is not skipped.
		Start-Job -Name "GetMailboxes $DAGDb" -InitializationScript { Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010 } `
		-ArgumentList ($DAGDb.name) -ScriptBlock {
			Get-Mailbox -Database $Args[0] -ResultSize unlimited
		}
	}
}

# Dump all the jobs from this script into the $Mailboxes variable.
$Mailboxes = Get-Job -Name GetMailboxes* | Receive-Job
# Clean up the jobs from this session.
do {
	$CheckJobs = Get-Job -Name GetMailboxes* | Where-Object { $_.State -eq 'Running' }
} until ($CheckJobs -eq $null)

Get-Job -Name GetMailboxes* | Remove-Job

 

 

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.
# Record the last time that WSUS was syncronized with updates from Microsoft.
$LastSync = ($wsus.GetSubscription()).LastSynchronizationTime

# Rewrite the array and eliminate servers that have 0 for needed updates.
$SummaryStatus = $SummaryStatus | where { $_.neededcount -ne 0 } | sort server

# List a summary of changes in a special table leveraging the "First" table class style listed above.
$WSUSHead += "<table class=`"First`">`r`n"
# Note the LastSync time.
$WSUSHead += "<tr><td class=`"First`"><b>Last Sync:</b></td><td class=`"First`"> " + `
$LastSync + "</td></tr>`r`n"
$WSUSHead += "</Body>`r`n"
$WSUSHead += "</Style>`r`n"
$WSUSHead += "</Head>`r`n"

# Create a generic HTML Header to use throughout the script for the body of the `
#  email message with table styles to control the formatting of any tables present it it.
$HTMLHead = "<Html xmlns=`"http://www.w3.org/1999/xhtml`">`r`n"
$HTMLHead += "<Head>`r`n"
$HTMLHead += "<Style>`r`n"
$HTMLHead += "TABLE{border-width: 1px;border-style: outset;border-color: `
black;border-spacing: 1px;border-collapse: separate;}`r`n"
$HTMLHead += "TH{border-width: 1px;padding: 1px;border-style:
inset;border-color: black;}`r`n"
$HTMLHead += "TD{border-width: 1px;padding-left: 3px;padding-right: `
3px;border-style: inset;border-color: black;}`r`n"
$HTMLHead += "TABLE.First{border-style: none;}`r`n"
$HTMLHead += "TD.First{border-style: none;}`r`n"
$HTMLHead += "</Style>`r`n"
$HTMLHead += "</Head>`r`n"
$HTMLHead += "<Body>`r`n"

# Build a variable with HTML for sending a report.
$UpdatesHTML = $HTMLHead
# Continue building HTML with the updates needed
$UpdatesHTML += $SummaryStatus | convertto-html -Fragment `
@{ Label = "Server"; Expression = { $_.server } }, @{ Label = "Needed Count"; Expression = { $_.NeededCount } }, @{ Label = "Not Installed"; Expression = { $_.NotInstalledCount } }, `
@{ Label = "Downloaded"; Expression = { $_.DownloadedCount } }, @{ Label = "Pending Reboot"; Expression = { $_.InstalledPendingRebootCount } }, @{ Label = "Failed Updates"; Expression = { $_.FailedCount } }, `
@{ Label = "Needed"; Expression = { $_.Needed } }

# Add an assembly to fix up powershell HTML markup. Ensures all special characters
# are converted correctly.
Add-Type -AssemblyName System.Web
$UpdatesHTML = [System.Web.HttpUtility]::HtmlDecode($UpdatesHTML)

# Create HTML email by adding all the various HTML sections from above.
$MailMessage = "
<html>
<body>
$WSUSHead
$UpdatesHTML
</body>
</html>
"

# Get the date and time.
$DateTime = Get-Date -Format "ddd MM/dd/yyyy h:mm tt"
# Set subject line to include the $DateTime variable.
$EmailSubject = "Update Status for " + $DateTime

# Send an email with all the compiled data.
[string[]]$EmailTo = "babcockscott@get-mailbox.net"
Send-MailMessage -To $EmailTo `
-Subject $EmailSubject -From "WSUS Reporting@get-mailbox.net" `
-Body $MailMessage -BodyasHTML `
-SmtpServer "MailRelayServer"

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.

# Create empty arrays to contain collected data.
$UpdateStatus = @()
$SummaryStatus = @()

# Use a foreach loop to process summaries per computer for each
# member of the "Email Servers" group. Then populate an array
# with a updates needed.
Foreach ($Object in $wsus.GetSummariesPerComputerTarget($updatescope, $computerscope)) {
	# Use a nested foreach to process the Mail Servers members.
	Foreach ($object1 in $MemberOfGroup) {
		# Use an if statement to match the wsus objects that contains update summaries with
		#  the members of the Mail servers members.
		If ($object.computertargetid -match $object1.id) {
			# Set the fulldomain name of the Mail Server member in a variable.
			$ComputerTargetToUpdate = $wsus.GetComputerTargetByName($object1.FullDomainName)
			# Filter the server for updates that are marked for install with the state
			#  being either downloaded or notinstalled.  These are updates that are needed.
			$NeededUpdate = $ComputerTargetToUpdate.GetUpdateInstallationInfoPerUpdate() `
			| where {
				($_.UpdateApprovalAction -eq "install") -and `
				(($_.UpdateInstallationState -eq "downloaded") -or `
				($_.UpdateInstallationState -eq "notinstalled"))
			}
			
			# Null out the following variables so that they don't contaminate
			#  op_addition variables in the below nested foreach loop.
			$FailedUpdateReport = $null
			$NeededUpdateReport = $null
			# Use a nested foreach loop to accumulate and convert the needed updates to `
			# the KB number with URL in an HTML format.
			if ($NeededUpdate -ne $null) {
				foreach ($Update in $NeededUpdate) {
					
					$myObject2 = New-Object -TypeName PSObject
					$myObject2 | add-member -type Noteproperty -Name Server `
					-Value (($object1 | select -ExpandProperty FullDomainName) `
					-replace ".get-mailbox.net", "")
					$myObject2 | add-member -type Noteproperty -Name Update `
					-Value ('<a>' + (($wsus.GetUpdate([Guid]$update.updateid)).title) + '<' + '/' + 'a' + '>')
					$UpdateStatus += $myObject2
					
					if ($Update.UpdateInstallationState -eq "Failed") {
						$FailedUpdateReport += ('</a><a>' + "(" + (($wsus.GetUpdate([Guid]$update.updateid)).KnowledgebaseArticles) + ") " + '<' + '/' + 'a' + '>')
					}
					if ($Update.UpdateInstallationState -eq "Notinstalled" -or $Update.UpdateInstallationState -eq "Downloaded") {
						$NeededUpdateReport += ('</a><a>' + "(" + (($wsus.GetUpdate([Guid]$update.updateid)).KnowledgebaseArticles) + ") " + '<' + '/' + 'a' + '>')
					}
				}
			}
			# Create a custom PSObject to contain summary data about each server and updates needed.
			$myObject1 = New-Object -TypeName PSObject
			$myObject1 | add-member -type Noteproperty -Name Server -Value (($object1 | select -ExpandProperty FullDomainName) -replace ".get-mailbox.net", "")
			$myObject1 | add-member -type Noteproperty -Name UnkownCount -Value $object.UnknownCount
			$myObject1 | add-member -type Noteproperty -Name NotInstalledCount -Value $object.NotInstalledCount
			$myObject1 | add-member -type Noteproperty -Name NotApplicable -Value $object.NotApplicableCount
			$myObject1 | add-member -type Noteproperty -Name DownloadedCount -Value $object.DownloadedCount
			$myObject1 | add-member -type Noteproperty -Name InstalledCount -Value $object.InstalledCount
			$myObject1 | add-member -type Noteproperty -Name InstalledPendingRebootCount -Value $object.InstalledPendingRebootCount
			$myObject1 | add-member -type Noteproperty -Name FailedCount -Value $object.FailedCount
			$myObject1 | add-member -type Noteproperty -Name ComputerTargetId -Value $object.ComputerTargetId
			$myObject1 | add-member -type Noteproperty -Name NeededCount -Value ($NeededUpdate | measure).count
			$myObject1 | add-member -type Noteproperty -Name Failed -Value $FailedUpdateReport
			$myObject1 | add-member -type Noteproperty -Name Needed -Value $NeededUpdateReport
			$SummaryStatus += $myObject1
		}
	}
}

# Only return the values that are most interesting.
$SummaryStatus | select server, notinstalled*, downloaded*, installedpending*, failed*, needed* | ft

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.

[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | Out-Null

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.

$WSUS = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer("wsus.get-mailbox.net",$true,8531)

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.

$UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
$UpdateScope.ApprovedStates = [Microsoft.UpdateServices.Administration.ApprovedStates]::LatestRevisionApproved

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

$ComputerScope = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope

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:

$WSUS.GetComputerTargetGroups()

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:

$ComputerTargetGroups = $WSUS.GetComputerTargetGroups() `
| Where {$_.id -eq 'aa60f906-b555-4b81-bd3b-a050fde63ce8'}
$MemberOfGroup = $wsus.getComputerTargetGroup($ComputerTargetGroups.Id).GetComputerTargets()

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.

 Microsoft.Exchange.Management.PowerShell.E2010 -ErrorAction "SilentlyContinue"

Set a variable for the new-moverequest batch name.

$BatchName = RebalanceDAG

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

# Get all the DAG databases
$TarDatabases = Get-MailboxDatabase | where {$_.mastertype `
-eq "DatabaseAvailabilityGroup" -and $_.Recovery -eq $False}

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.

# Create empty arrays to store sorting data.  One for Database counts and one `
#  for mailbox types and move request assignments.
$DBCountArray = @()
$MBXArray = @()

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.

# Build the list of databases with associated counts for the various criteria used during sorting.
Foreach ($TarDatabase in $TarDatabases) {
# Show a status bar for progress while data is collected.
$PercentComplete = [Math]::Round(($LoopCount++ / $TarDatabases.Count * 100),1)
$CurrentDB = $TarDatabase.Name
Write-Progress -Activity "Mailbox Database Query in Progress" -PercentComplete $PercentComplete `
-Status "$PercentComplete% Complete" -CurrentOperation "Current Database: $CurrentDB"

# Query the current database in the foreach loop and get every mailbox.
$TarDBInfo = Get-Mailbox -Database $TarDatabase

# Set the initial values to 0 for each database being processed.
$TotalActiveSTDMBs = 0
$TotalActiveLRGMBs = 0
# Loop through each mailbox in the database checking to see if it is a large mailbox or not.
Foreach ($MBXInfo in $TarDBINfo) {
$MBXObject = $null
$MBXObject = New-Object system.Object
$MBXObject | Add-Member -type NoteProperty -Name "Database" -Value $MBXInfo.database.Name
$MBXObject | Add-Member -type NoteProperty -Name "Name" -Value $MBXInfo.Name
$MBXObject | Add-Member -type NoteProperty -Name "PrimarySMTP" -Value $MBXInfo.PrimarySmtpAddress
If ($MBXInfo.UseDatabaseQuotaDefaults -eq $true) {
# It's a standard mailbox size so increment that number.
$TotalActiveSTDMBs++
$MBXObject | Add-Member -type NoteProperty -Name "MBXSize" -Value "Standard"
} Else {
# It's a large mailbox size so increment that number.
$TotalActiveLRGMBs++
$MBXObject | Add-Member -type NoteProperty -Name "MBXSize" -Value "Large"
}
$MBXObject | Add-Member -type NoteProperty -Name "MoveRequest" -Value $Null
$MBXArray += $MBXObject
# Store the collected data in the DBCountArray variable.
$DBObject = $Null
$DBObject = New-Object system.Object
$DBObject | Add-Member -type NoteProperty -Name "Database" -Value $TarDatabase
$DBObject | Add-Member -type NoteProperty -Name "StandardMailbox" -Value $TotalActiveSTDMBs
$DBObject | Add-Member -type NoteProperty -Name "LargeMailbox" -Value $TotalActiveLRGMBs
$DBObject | Add-Member -type NoteProperty -Name "TotalMailboxes" -Value ($TotalActiveSTDMBs + $TotalActiveLRGMBs)
$DBCountArray += $DBObject
}

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.

# Measure the criteria counts to obtain various data. Average will be used to judge
# what each database count should be.
$TotalMBs = $DBCountArray | Measure-Object "TotalMailboxes" -Average
$TotalLMBs = $DBCountArray | Measure-Object "LargeMailbox" -Average
$TotalStdMBs = $DBCountArray | Measure-Object "StandardMailbox" -Average

# Round the averages.
$TotalMBAvg = ([Math]::Round($TotalMBs.average))
$TotalLMBAvg = ([Math]::Round($TotalLMBs.average))
$TotalStdMBAvg = ([Math]::Round($TotalStdMBs.average))

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.

# Using the averages obtained get the variance on the current database counts.
Foreach ($DB in $DBCountArray){
$DBCountArray | where {$_.database.name -eq $DB.database.Name} `
| Add-Member -type NoteProperty -Name "StdMbxDiff" -Value ($DB.Standardmailbox - $TotalStdMBAvg)
$DBCountArray | where {$_.database.name -eq $DB.database.Name} `
| Add-Member -type NoteProperty -Name "LrgMbxDiff" -Value ($DB.Largemailbox - $TotalLMBAvg)
$DBCountArray | where {$_.database.name -eq $DB.database.Name} `
| Add-Member -type NoteProperty -Name "TotMbxDiff" -Value ($DB.TotalMailboxes - $TotalMBAvg)
}

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.

# Loop through and assess the count on the Database count, increment the various criteria and make new move assignments.
Write-Host "Assigning Large Mailboxes" -ForegroundColor Yellow
do {
# initialiaze/null out the variables to prevent contaminating the loop.
$DBsource = $null
$DBtarget = $null
# Get the large mailbox count source and target databases
$DBsource = $DBCountArray | where {$_.lrgmbxdiff -gt 0} | sort totmbxdiff -Descending | select -First 1
$DBtarget = $DBCountArray | where {$_.lrgmbxdiff -lt 0} `
| sort totmbxdiff | select -First 1
# If they aren't null then do the work.
if ($DBsource -ne $null -and $DBtarget -ne $null){
# decrement the source databases
($DBCountArray | where {$_.database.name -eq $DBsource.database}).largemailbox = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).largemailbox -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).totalmailboxes = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).totalmailboxes -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).lrgmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).lrgmbxdiff -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).totmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).totmbxdiff -1
# increment the target databases
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).largemailbox = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).largemailbox +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totalmailboxes = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totalmailboxes +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).lrgmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).lrgmbxdiff +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totmbxdiff +1
# Access the list of mailboxes and make an assigment based on the target identified in $DBtarget
($MBXArray | where {$_.database -eq $DBsource.Database -and $_.mbxsize -eq "large" -and $_.moverequest -eq $null} | select -First 1).moverequest = $DBtarget.Database.Name
}
}
# Keep doing the loop until either variable runs out of databases.
until ($null -eq $DBtarget -or $null -eq $DBsource)

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

# Loop through and assess the count on the Database count, increment the various criteria and make new move assignments.
Write-Host "Assigning Standard Mailboxes" -ForegroundColor Yellow
do {
# initialiaze/null out the variables to prevent contaminating the loop.
$DBsource = $null
$DBtarget = $null
# Get the standard mailbox count source and target databases
$DBsource = $DBCountArray | where {$_.stdmbxdiff -gt 0} | sort totmbxdiff -Descending | select -First 1
$DBtarget = $DBCountArray | where {$_.stdmbxdiff -lt 0} | sort totmbxdiff | select -First 1
# If they aren't null then do the work.
if ($DBsource -ne $null -and $DBtarget -ne $null){
# decrement the source databases
($DBCountArray | where {$_.database.name -eq $DBsource.database}).standardmailbox = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).standardmailbox -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).totalmailboxes = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).totalmailboxes -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).stdmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).stdmbxdiff -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).totmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).totmbxdiff -1
# increment the target databases
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).standardmailbox = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).standardmailbox +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totalmailboxes = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totalmailboxes +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).stdmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).stdmbxdiff +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totmbxdiff +1
# access the list of mailboxes and make an assignment based on the target identified in $DBtarget
($MBXArray | where {$_.database -eq $DBsource.Database -and $_.mbxsize -eq "standard" -and $_.moverequest -eq $null} | select -First 1).moverequest = $DBtarget.Database.Name
}
}
# Keep doing the loop until either variable runs out of databases.
until ($null -eq $DBtarget -or $null -eq $DBsource)

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

$DBCountArray | ft -AutoSize

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.

$MBXArray

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.

# New-Move request to initiate seeding of mailboxes to databases as assigned in $MailboxData table.
$MoveRequest = $MBXArray | where {$_.moverequest -ne $null}
$MoveRequest | ForEach {
$TargetDB = $_.DatabaseName
$ID = $_.PrimarySMTPAddress
New-MoveRequest -identity $ID -TargetDatabase $TargetDB -BadItemLimit 50 -Batchname $BatchName -SuspendWhenReadyToComplete -Confirm:$false
}

Here is the script in it’s entirety:

# Get all the DAG databases
$TarDatabases = Get-MailboxDatabase | where {$_.mastertype -eq "DatabaseAvailabilityGroup" -and $_.Recovery -eq $False}

# Create empty arrays to store sorting data.  One for Database counts and one `
#  for mailbox types and move request assignments.
$DBCountArray = @()
$MBXArray = @()

# Build the list of databases with associated counts for the various criterea `
#  used durning sorting.
Foreach ($TarDatabase in $TarDatabases) {
# Show a status bar for progress while data is collected.
$PercentComplete = [Math]::Round(($LoopCount++ / $TarDatabases.Count * 100),1)
$CurrentDB = $TarDatabase.Name
Write-Progress -Activity "Mailbox Database Query in Progress" -PercentComplete $PercentComplete `
-Status "$PercentComplete% Complete" -CurrentOperation "Current Database: $CurrentDB"

# Define the query which which finds every mailbox in the database.
$TarDBInfo = Get-Mailbox -Database $TarDatabase
# Set the initial values to 0 for each database being processed.
$TotalActiveSTDMBs = 0
$TotalActiveLRGMBs = 0
# Loop through each mailbox in the database checking to see if it is a large mailbox or not
Foreach ($MBXInfo in $TarDBINfo) {
$MBXObject = $null
$MBXObject = New-Object system.Object
$MBXObject | Add-Member -type NoteProperty -Name "Database" -Value $MBXInfo.database.Name
$MBXObject | Add-Member -type NoteProperty -Name "Name" -Value $MBXInfo.Name
$MBXObject | Add-Member -type NoteProperty -Name "PrimarySMTP" -Value $MBXInfo.PrimarySmtpAddress

If ($MBXInfo.UseDatabaseQuotaDefaults -eq $true) {
# It's a standard mailbox size so increment that number.
$TotalActiveSTDMBs++
$MBXObject | Add-Member -type NoteProperty -Name "MBXSize" -Value "Standard"
} Else {
# It's a large mailbox size so increment that number.
$TotalActiveLRGMBs++
$MBXObject | Add-Member -type NoteProperty -Name "MBXSize" -Value "Large"
}
$MBXObject | Add-Member -type NoteProperty -Name "MoveRequest" -Value $Null
$MBXArray += $MBXObject
}
# Store the collected date in the DBCountArray variable.
$DBObject = $Null
$DBObject = New-Object system.Object
$DBObject | Add-Member -type NoteProperty -Name "Database" -Value $TarDatabase
$DBObject | Add-Member -type NoteProperty -Name "StandardMailbox" -Value $TotalActiveSTDMBs
$DBObject | Add-Member -type NoteProperty -Name "LargeMailbox" -Value $TotalActiveLRGMBs
$DBObject | Add-Member -type NoteProperty -Name "TotalMailboxes" -Value ($TotalActiveSTDMBs + $TotalActiveLRGMBs)
$DBCountArray += $DBObject
}

# Measure the critera counts to obtain various data. Average will be used to judge `
#  what each database count should be.
$TotalMBs = $DBCountArray | Measure-Object "TotalMailboxes" -Average -Maximum -Minimum -Sum
$TotalLMBs = $DBCountArray | Measure-Object "LargeMailbox" -Average -Maximum -Minimum -Sum
$TotalStdMBs = $DBCountArray | Measure-Object "StandardMailbox" -Average -Maximum -Minimum -Sum

# Round the averages.
$TotalMBAvg = ([Math]::Round($TotalMBs.average))
$TotalLMBAvg = ([Math]::Round($TotalLMBs.average))
$TotalStdMBAvg = ([Math]::Round($TotalStdMBs.average))

# Using the averages obtained get the variance on the current database counts.
Foreach ($DB in $DBCountArray){
# Blackberry and Large Mailbox
$DBCountArray | where {$_.database.name -eq $DB.database.Name} | Add-Member -type NoteProperty -Name "StdMbxDiff" -Value ($DB.Standardmailbox - $TotalStdMBAvg)
$DBCountArray | where {$_.database.name -eq $DB.database.Name} | Add-Member -type NoteProperty -Name "LrgMbxDiff" -Value ($DB.Largemailbox - $TotalLMBAvg)
$DBCountArray | where {$_.database.name -eq $DB.database.Name} | Add-Member -type NoteProperty -Name "TotMbxDiff" -Value ($DB.TotalMailboxes - $TotalMBAvg)
}

$DBCountArray | ft -AutoSize

# Loop through and assess the count on the Database count, increment the various `
#  criteria and make new move assignments.
Write-Host "Assigning Large Mailboxes" -ForegroundColor Yellow
do {
# initialiaze/null out the variables to prevent contaminating the loop.
$DBsource = $null
$DBtarget = $null
# Get the large mailbox count source and target databases
$DBsource = $DBCountArray | where {$_.lrgmbxdiff -gt 0} | sort totmbxdiff -Descending | select -First 1
$DBtarget = $DBCountArray | where {$_.lrgmbxdiff -lt 0} | sort totmbxdiff | select -First 1
# If they aren't null then do the work.
if ($DBsource -ne $null -and $DBtarget -ne $null){
# decrement the source databases
($DBCountArray | where {$_.database.name -eq $DBsource.database}).largemailbox = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).largemailbox -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).totalmailboxes = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).totalmailboxes -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).lrgmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).lrgmbxdiff -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).totmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).totmbxdiff -1
# increment the target databases
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).largemailbox = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).largemailbox +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totalmailboxes = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totalmailboxes +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).lrgmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).lrgmbxdiff +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totmbxdiff +1
# access the list of mailboxes and make an assigment based on the target identified in $DBtarget
($MBXArray | where {$_.database -eq $DBsource.Database -and $_.mbxsize -eq "large" -and $_.moverequest -eq $null} | select -First 1).moverequest = $DBtarget.Database.Name
}
}
# Keep doing the loop until either variable runs out of databases.
until ($null -eq $DBtarget -or $null -eq $DBsource)

$DBCountArray | ft -AutoSize

# Loop through and assess the count on the Database count, increment the various `
#  criteria and make new move assignments.
Write-Host "Assigning Standard Mailboxes" -ForegroundColor Yellow
do {
# initialiaze/null out the variables to prevent contaminating the loop.
$DBsource = $null
$DBtarget = $null
# Get the standard mailbox count source and target databases
$DBsource = $DBCountArray | where {$_.stdmbxdiff -gt 0} | sort totmbxdiff -Descending | select -First 1
$DBtarget = $DBCountArray | where {$_.stdmbxdiff -lt 0} | sort totmbxdiff | select -First 1
# If they aren't null then do the work.
if ($DBsource -ne $null -and $DBtarget -ne $null){
# decrement the source databases
($DBCountArray | where {$_.database.name -eq $DBsource.database}).standardmailbox = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).standardmailbox -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).totalmailboxes = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).totalmailboxes -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).stdmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).stdmbxdiff -1
($DBCountArray | where {$_.database.name -eq $DBsource.database}).totmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBsource.database}).totmbxdiff -1
# increment the target databases
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).standardmailbox = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).standardmailbox +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totalmailboxes = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totalmailboxes +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).stdmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).stdmbxdiff +1
($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totmbxdiff = ($DBCountArray | where {$_.database.name -eq $DBtarget.database}).totmbxdiff +1
# access the list of mailboxes and make an assigment based on the target identified in $DBtarget
($MBXArray | where {$_.database -eq $DBsource.Database -and $_.mbxsize -eq "standard" -and $_.moverequest -eq $null} | select -First 1).moverequest = $DBtarget.Database.Name
}
}
# Keep doing the loop until either variable runs out of databases.
until ($null -eq $DBtarget -or $null -eq $DBsource)

# Uncomment to double check your arrays
# $DBCountArray | ft -AutoSize
# $MBXArray | where {$_.moverequest -ne $null}

# New-Move request to initiate seeding of mailboxes to databases as assigned in $MailboxData table.
### The new-moverequest loop is commented out so you don't accidently initiate a move request
<# $MoveRequest = $MBXArray | where {$_.moverequest -ne $null} $MoveRequest | ForEach { $TargetDB = $_.DatabaseName $ID = $_.PrimarySMTPAddress New-MoveRequest -identity $ID -TargetDatabase $TargetDB -BadItemLimit 50 -Batchname $BatchName -SuspendWhenReadyToComplete -Confirm:$false } #>

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