Monday, January 11, 2010

Script to pull database stats and e-mail results

With some help from a coworker (irundis.com), I got this script put together for pulling database stats, and then e-mailing the results to an e-mail recipient. The idea is to just attach this to a scheduled task and let it run. It is a good thing I got help or I'm positive it wouldn't be quite this pretty.

Scheduled Task:

Program/Task:

C:\windows\system32\windowspowershell\v1.0\powershell.exe

Arguments:

-PSConsoleFile "C:\Program Files\Microsoft\Exchange Server\bin\exshell.psc1" -noexit -command ". C:\DBStats.ps1

-------------------------------------------------------------
Script (be careful about returns if you copy/paste)
-------------------------------------------------------------

function Get-MXDBData() {
PROCESS {

$identity = $_.Name
$FullID = $_.identity
$FilePath = $_.EdbFilePath.PathName
$SizeMB = [long]([long]((Get-ChildItem $FilePath).length) / 1048576)
$SizeGB = [long]($SizeMB / 1024)
$MBXCount = (Get-Mailbox -database $FullID).count

#Create the new custom object and add new members to the object
$obj = New-Object PSObject
$obj | Add-Member NoteProperty Database $identity
$obj | Add-Member NoteProperty "Size in GB" $SizeGB
$obj | Add-Member NoteProperty "Size in MB" $SizeMB
$obj | Add-Member NoteProperty "Mailbox Count" $MBXCount

#Output the object to gain access to all of its properties for later in the pipeline.
Write-Output $obj
}
}

Get-MailboxDatabase -Server SERVER | Get-MXDBData | export-csv -path c:\DBStats.csv -NoTypeInformation

################
# SMTP Mail Alert
################

$file = "c:\DBStats.csv"

#Create new .NET object and assign to variable
$mail = New-Object System.Net.Mail.MailMessage

#Sender Address
$mail.From = "sender@domain.com";

#Recipient Address
$mail.To.Add("recipient@domain.com");

#Message Subject
$mail.Subject = "Database Statistics Report";

#Message Body
$mail.Body = "Please see attached CSV for database size and mailbox count.";

#Connect to your Hub Transport (FQDN or NLB alias)
$smtp = New-Object System.Net.Mail.SmtpClient("fqdn.domain.com");

#Attach CSV
$attachment = new-object Net.Mail.Attachment($file)
$mail.Attachments.Add($attachment)

#Uncomment if no anonymous relay (best practice to have separate receive connector)
# $smtp.Credentials = New-Object System.Net.NetworkCredential("username", "passwd");

#Send Email
$smtp.Send($mail);

$attachment.Dispose()


Example of output before CSV:


10 comments:

Jeremy Phillips said...

It could be better if you dumped it into a SQL DB and had a web based front end to the results.

Jeremy Phillips said...

Oh, and shouldn't it say "Connect to your hub transport" instead of "Connect to your mail server"?

Kevinm said...

I really like webfront ends. they make things so much more pretty and simpler for me to use.

Jeremy Phillips said...

See!!! It's not just me! EVERYONE likes web front ends. When are you going to get started on that?

Joshua Raymond said...

I'll make you a front end that requires you to save the first fake princess in Super Mario Bros before it displays.

*Clarified mail server role.

Jeremy Phillips said...

DUDE! That's awesome! I love Super Mario Bros! The NES version, right? Can you do all the graphics in PowerShell?

Jeremy Phillips said...

Also, shouldn't you make a recommendation for *how often* this is run as a scheduled task? For heavily loaded servers with lots of mailboxes this could be rather resource intensive.

Joshua Raymond said...

Nope, no recommendations.

The frequency that I run it, however, is once per week on Monday at 7:30am. This is so that the admins can get a report just before their work week starts, and they will know where to place new users.

..and duh..of course the NES version; not that it matters really. All of Nintendo's games are the exact same thing, no matter the system, right?

Joshua Raymond said...

If my babelfish translation is close enough, then yes are correct, jost. In fact, irundis.com has a good template to that very end.

Jeremy said...

I think i figured out how to speed up the delay when you go to do the size calculation. Try replacing

$SizeMB = [long]([long]((Get-ChildItem $FilePath).length) / 1048576)

with

$SizeMB = [system:math]::Truncate([long]([long]((Get-ChildItem $FilePath).length) / 1048576))

This should be faster than dynamically casting the variable as an integer, but I did not really test it yet. Let me know.