#######################################################################################################################
#Title - WhiteSpace and Free disk space of Exchange Mailbox Databases script
# This script sends the report in tabular format to the recipients mentioned in $recipients variable
#######################################################################################################################
# Block of code to send emails using the SMTP relay server
$recipients = "user1@adp.com","user2@domain.com"
#Function declaration for sending emails
function SendMail($recipients)
{
$date = (get-date).getdatetimeformats()
$Today = $date[1]
$SmtpClient = new-object system.net.mail.smtpClient
$MailMessage = New-Object system.net.mail.mailmessage
$SmtpClient.Host = "SMTPHostName"
$mailmessage.from = "Fromemailaddress"
foreach ($i in $recipients) {
$mailmessage.To.add($i)
}
$mailmessage.Subject = "Exchange Database Statistics - $Today"
$mailmessage.IsBodyHTML = $true
$mailmessage.Body = $body
$smtpclient.Send($mailmessage)
}
#Get the list of all clustered mailbox servers
$Error = $null
$MBCluster = Get-MailboxServer | where {$_.ClusteredStorageType -eq "NonShared"} | sort -Property name
$WhiteSpaceObject = @()
$BeginDate=[System.Management.ManagementDateTimeConverter]::ToDMTFDateTime((get-date).AddDays(-1))
foreach ($Server in $MBCluster)
{
$ClusRes = Get-WmiObject -Namespace root\mscluster -Class MSCluster_NodeToActiveGroup -ComputerName $Server | where {$_.PartComponent -like ("*"+$Server+'"')}
#Get the Active node details and declare variables, arrays and hash tables to be used
$MBServer = ($ClusRes.Groupcomponent.split("=")[1]).trim('"')
$WhiteSpaceSummary = @{}
$SGarray = @()
$DBArray = @()
$DBSizeArray = @()
$TotalLUNSizeArray = @()
$TotalLUNFreeArray = @()
$TotalLUNUsedArray = @()
$PercentFreeArray = @()
$WhiteSpaceArray = @()
$SortedWS = @{}
$i=0
$k=0
#Check the event logs on active node for event 1221 - This event ID shows the whitespace of the database
$logs = Get-WmiObject -class win32_ntlogevent -ComputerName $MBServer -Filter "(EventCode=1221) and (LogFile='application') and (TimeGenerated >'$BeginDate')"
foreach ($log in $logs) {
$MBName = $log.message.split("`"")
$WSEndString = $mbname[2].indexof("megabytes ")-6
$WhitespaceMB = $mbname[2].Substring(5,$WSEndString)/1024
$Formatting = "{0:N2}" -f $WhiteSpaceMB
$WhiteSpaceGB = $Formatting
$WhiteSpaceArray += $WhiteSpaceGB
$Sgname = $mbname[1].split("\")
$DBArray += $Sgname[1]
}
$length = $DBArray.length
$WhiteSpaceSummary.Add($DBArray[$i], $WhiteSpaceArray[$i])
for ($i=1; $i -le $length; $i++)
{
for ($j = $i-1; $j -ge 0; $j--)
{
if([string]$DBArray[$j] -ne [string]$DBArray[$i])
{
$WhiteSpaceSummary.Add($DBArray[$j], $WhiteSpaceArray[$j])
}
else
{
$Error = $WhiteSpaceSummary.Add($DBArray[$j], $WhiteSpaceArray[$j])
}
}
}
$SortedWS = [collections.sortedlist]$WhiteSpaceSummary
[array]$SortedDBArray = $SortedWS.Keys
[array]$SortedWSArray = $SortedWS.Values
#Extract the list of Storage Groups within the server. Use this only if you have to report SG names as well in the report
$StorageGroup = Get-StorageGroup -Server $Server | where {$_.Name -notlike "RSG"}
foreach ($SG in $StorageGroup){
$SGarray += $SG.Name
}
[Array]::Sort([array]$SGArray)
#Extract the list of databases, their EDB file location and the size of the EDB File.
$DatabaseList = Get-MailboxDatabase -Server $Server | where {$_.Recovery -notlike "True"} | Sort-Object Name
foreach($DB in $DatabaseList){
$edbfile = $DB.EdbFilePath
$edbfilepath = "`\`\"+$MBServer+"`\"+$edbfile.pathname.Replace(':','$')
$DBSize = Get-ChildItem $edbfilepath
$Size = $DBSize.Length/1024/1024/1024
$DBSize = "{0:N2}" -f $Size
$DBSizeArray += $DBSize
$LUNStartstring = $edbfile.pathname.indexof("\D")+1
$LUN = $edbfile.pathname.substring(0,$LUNStartstring)
#Extract the usage details of the drive where EDB file is present.
#Use this command if your data drives are hosted on LUNs. You can use the class Win32_Logicaldisk instead of Win32_Volume if you have local hard disks.
$LUNDetails = Get-WmiObject -Class Win32_Volume -ComputerName $MBServer | Where-Object {$_.Caption -like $LUN}
$TotalLUNSize = "{0:F2}" -f $($LUnDetails.Capacity / 1GB)
$TotalLUNSizeArray += $TotalLUNSize
$TotalLUNFree = "{0:F2}" -f $($LUnDetails.FreeSpace / 1GB)
$TotalLUNFreeArray += $TotalLUNFree
$TotalLUNUsed = "{0:F2}" -f $(($LUnDetails.Capacity-$LUnDetails.FreeSpace) / 1GB)
$TotalLUNUsedArray += $TotalLUNUsed
$PercentFree = "{0:F0}" -f $(([int64]$LUNDetails.FreeSpace / [int64]$LUNDetails.Capacity)*100)
$PercentFreeArray += $PercentFree
}
for ($k=0; $k -lt 10; $k++)
{
$WhiteSpaceStatus = New-Object System.Object
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $MBServer -Name Server
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $SGArray[$k] -Name StorageGroup
$WhiteSpaceStatus| Add-Member -MemberType NoteProperty -value $SortedDBArray[$k] -Name Database
$WhiteSpaceStatus| Add-Member -MemberType NoteProperty -value $TotalLUNSizeArray[$k] -Name TotalLUNSize
$WhiteSpaceStatus| Add-Member -MemberType NoteProperty -value $TotalLUNUsedArray[$k] -Name TotalLUNUsed
$WhiteSpaceStatus| Add-Member -MemberType NoteProperty -value $TotalLUNFreeArray[$k] -Name TotalLUNFree
[decimal]$EDBFileSize = "{0:N2}" -f $DBSizeArray[$k]
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $EDBFileSize -Name EDBSize
[decimal]$Whitespace = "{0:N2}" -f $SortedWSArray[$k]
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $Whitespace -Name WhiteSpace
[decimal]$ActualDBSize = "{0:F2}" -f $($EDBFileSize - $WhiteSpace)
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $ActualDBSize -Name ActualDBSize
$WhiteSpaceStatus | Add-Member -MemberType NoteProperty -value $PercentFreeArray[$k] -Name PercentFree
$WhiteSpaceObject += $WhiteSpaceStatus
}
}
#This section composes the body of email in HTML Table format.
$h = "<style>"
$h = $h + "BODY{background-color:#FFFFFF;}"
$h = $h + "TABLE{border-width: 1px;border-style: double;border-color: black;border-collapse: collapse;}"
$h = $h + "TH{border-width: 1px;padding: 0px;border-style: double;border-color: black;background-color: #000000}"
$h = $h + "TD{border-width: 1px;padding: 0px;border-style: double;border-color: black;background-color: #000000}"
$h = $h + "</style>"
$a = $WhiteSpaceObject | Sort-Object -Property Database | Foreach-Object -Begin {
$h
"<br><b>Mailbox Database Statistics</b><br>"
"<table>"
"<tr><th>Server Name</th><th>SG Name</th><th>Database</th><th>TotalLUNSize</th><th>TotalLUNUsed</th><th>TotalLUNFree</th><th>EDBSize</th><th>WhiteSpace</th><th>DBSize</th><th>%LUNFree</th></tr>"
$row = 0
} -Process {
$style = "style='background-color: #CCCCCC;text-align: center;'";
if ( $row % 2 -eq 0 )
{
$style = "style='background-color: white;text-align: center;'";
}
[string]$StrLUNSize = $_.TotalLUNSize
$TotalLUNSizeGB = $StrLUNSize +" GB"
[string]$StrLUNUsed = $_.TotalLUNUsed
$TotalLUNUsedGB = $StrLUNUsed +" GB"
[string]$StrLUNFree = $_.TotalLUNFree
$TotalLUNFreeGB = $StrLUNFree +" GB"
[string]$String = $_.WhiteSpace
$WhitespaceGB = $String +" GB"
[string]$StrEDBSize = $_.EDBSize
$EDBSizeGB = $StrEDBSize +" GB"
[string]$StrActualDBSize = $_.ActualDBSize
$ActualDBSizeGB = $StrActualDBSize +" GB"
"<tr><td {0}>{1}</td><td {0}>{2}</td><td {0}>{3}</td><td {0}>{4}</td><td {0}>{5}</td><td {0}>{6}</td><td {0}>{7}</td><td {0}>{8}</td><td {0}>{9}</td><td {0}>{10}</td></tr>" -f $style, $_.Server,$_.StorageGroup,$_.Database,$TotalLUNSizeGB,$TotalLUNUsedGB,$TotalLUNFreeGB,$EDBSizeGB,$WhiteSpaceGB,$ActualDBSizeGB,$_.PercentFree
$row++
} -End {
"</table>"
}
$body = ($a)
sendmail ($recipients)
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (4)
Commented:
I need to modify slightly for a non-clustered environment, with many databases on it.
Can you tell me which parts to change, or advise if there is another version for non-clustered servers?
I tried knocking out the cluster part and declaring the $MBServer as a static value but I ended up with an emailed report with no info in it.
Thanks heaps!
Author
Commented:Instead of running the entire script you can try running single commands on your powershell to find out if the desired results are shown.
If you get any output for the below command that means your whitespace details are gathered.
$logs = Get-WmiObject -class win32_ntlogevent -ComputerName $MBServer -Filter "(EventCode=1221) and (LogFile='application') and (TimeGenerated >'$BeginDate')"
So, this way you may have to do for each single component.
Author
Commented:Open in new window
Commented:
Do you have the modified powershell code for Exchange Server 2010 ?