Exchange 2007 reporting
The Exchange 2007 - Powershell pair works very well, and is even mandatory for some tasks (for instance there is no GUI in order to handle Public Folder client permission!)
You can also use Powershell to make lots of administrative tasks, and even reporting.
For instance, in Exchange 2003, if we wanted to get a full list of consumed mailbox size and quota for some users, we had to use 2 separate scripts, the first which will get the consumed mailbox size (WMI query) and the other that’ll get the quota (LDAP query). Then we had to match both with a common field (for instance samAccountName).
This worked, but it was time-consumer, specially with a large platform.
In Exchange 2007, we can use Powershell in order to do that. And in this case, the same result can be achieved with a single line of code. Sure the line is complex, but it shows the power of Powershell.
Here is a sample script used for mailbox report on a Exchange 2007 / HMC 4.5 platform :
# Reporting total des mailbox
# Export au format csv des fichiers reporting
#
# Anciennes variables pour export CSV
$rootPath = "D:\Reporting\MailboxFull\"
$cheminCSV = $rootPath + "ReportingMailboxFull+" + $dateExport + ".csv"
$dateExport = Get-Date -uformat "%Y%m%d%H00"
$SMTPSRV = "xxx"
$EmailFrom = "administrator@xxx"
$EmailTo = "administrator@xxx"
# Fonction d'envoi de mail
function Send-SMTPmail($to, $from, $subject, $smtpserver, $body) {
$mailer = new-object Net.Mail.SMTPclient($smtpserver)
$msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
$msg.IsBodyHTML = $false
$mailer.send($msg)
}
# Récupération des infos de BAL
$dataMailbox = Get-Mailbox -ResultSize Unlimited | where { $_.OrganizationalUnit -ne "xxx/Users" -AND $_.OrganizationalUnit -notlike "xxx/ServiceAccounts/*" -AND $_.OrganizationalUnit -notlike "xxx/Hosting/xxx/Recette*" } | Select-Object DisplayName,Name,samAccountName, @{Name="Revendeur";Expression={$_.OrganizationalUnit.Split("/")[2]}}, @{Name="Client";Expression={$_.OrganizationalUnit.Split("/")[3]}}, @{Name="Forfait";Expression={if($_.ProhibitSendQuota -ge 500MB){"PCWEB" + $_.ProhibitSendQuota}Else{"POP" + $_.ProhibitSendQuota}}}, @{Name="WhenCreated";Expression={Get-Date $_.WhenCreated -Format d}},@{Name="TailleMB";Expression={if($stats=(Get-MailboxStatistics $_ -WarningAction SilentlyContinue){($stats.TotalItemSize.Value.ToMB()+1)}Else{"0"}}}, serverName
# Export en CSV
#$dataMailbox | Export-Csv -NoTypeInformation -Encoding UTF8 -Path $cheminCSV -Delimiter ";"
# Export en base SQL
$connexionSQL = New-Object System.Data.SqlClient.SqlConnection("Data Source=xxx;Initial Catalog=xxx;Persist Security Info=True;User ID=xxx;pwd=xxx")
$connexionSQL.Open()
$cmd = $connexionSQL.CreateCommand()
$dataMailbox | ForEach-Object {
$tmpDate = $dateExport
$tmpDate = $tmpDate.Insert(10,":")
$tmpDate = $tmpDate.Insert(8," ")
$tmpDate = $tmpDate.Insert(6,"-")
$tmpDate = $tmpDate.Insert(4,"-")
$cmd.CommandText ="INSERT INTO xxx (dateStat, displayName, userPrincipalName, samAccountName, revendeur, client, forfait, whenCreated, tailleMB, serverName) VALUES ( '" + $tmpDate + "', '" + $_.DisplayName.Replace("'","''") + "', '" + $_.Name + "', '" + $_.samAccountName + "', '" + $_.Revendeur + "', '" + $_.Client + "', '" + $_.Forfait + "', '" + (Get-Date($_.WhenCreated) + "', '" + $_.TailleMB + "', '" + $_.serverName + "')"
$cmd.ExecuteNonQuery()
$cmd.CommandText
}
$connexionSQL.Close()
# Si erreur, on envoi un mail
if ($tmpErr) { Send-SMTPmail $EmailTo $EmailFrom "[ERR] reportingMailboxFull $dateExport" $SMTPSRV $tmpErr }