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:


Friday, January 8, 2010

Prevent Double-Booking Rooms in Exchange 2007

My new responsibilities leave me little time to post, so I figured I'd dump a quick PS execution for admins quickly searching for a way to avoid double booking in their environment.


get-mailbox | where {$_.ResourceType -eq "Room" } | Set-MailboxCalendarSettings -
AutomateProcessing AutoAccept -AllBookInPolicy:$true -AllowConflicts:$false

ResourceType can be substituted for other types than Room as well.