Exchange 2010 mailbox usage and edb size

Follow

Hi,
I want to share my solutions for displaying user's mailbox trends on exchange servers..
As Labtech does not store this information I did it on another mysql server. A bit out of the scope of this forum, but ends up with brightgauge datasets :)
So here is the steps for whoever wants to achieve this :

1.Spin up a mysql in Azure or wherever
2.Connect this mysql as a new datasource in brightgauge.
3.Create a database in this server named : "collector"
4.Save the following as a .sql file and import it using phpmyadmin :
+++++start file import.sql+++++
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
CREATE TABLE IF NOT EXISTS edbdata (
id int(11) NOT NULL,
server varchar(255) NOT NULL,
dbname varchar(255) NOT NULL,
dbsize int(11) NOT NULL,
timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
Client varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS ExchangeData (
id int(11) NOT NULL,
Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
Alias varchar(255) NOT NULL,
ItemCount int(255) NOT NULL,
TotalItemSize int(255) NOT NULL,
Client varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=325 DEFAULT CHARSET=latin1;
ALTER TABLE edbdata
ADD PRIMARY KEY (id);
ALTER TABLE ExchangeData
ADD PRIMARY KEY (id), ADD UNIQUE KEY id (id);
ALTER TABLE edbdata
MODIFY id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7;
ALTER TABLE ExchangeData
MODIFY id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=325;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+++++end file import.sql+++++

5.Create a folder on the server (C drive) : "collector"
6.Place in the folder, 4 files
7.File 1 is MySql.Data.dll You will find it here : http://dev.mysql.com/downloads/connector/net/1.0.html or single download on my server at : http://tools.upg.gr/exchcollector/
8.File 2 is grabbing the edb sizes. "edbsizes.ps1" :

+++++start file edbsizes.ps1+++++
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010
. $env:ExchangeInstallPath\bin\RemoteExchange.ps1
Connect-ExchangeServer -auto
[void][system.reflection.Assembly]::LoadFrom(“C:\nroicollector\MySQL.Data.dll”)
import-module activedirectory
$servers = Get-MailboxServer
foreach($server in $servers)
{ $mailboxes = Get-MailboxDatabase -server $server -Status
foreach ($m in $mailboxes)
{ $stats = Get-MailboxDatabase $m -status | Select ServerName,Name,DatabaseSize
$server = $m.Server
$dbname = $m.name
$size = $stats.DatabaseSize.ToGB()
$client = (Get-ADDomain -Identity (Get-WmiObject Win32_ComputerSystem).Domain).NetBIOSName
$dbbonnect = New-Object MySql.Data.MySqlClient.MySqlConnection
$dbconnect.ConnectionString = "server=YOURMYSQLSERVERFQDN;port=3306;uid=YOURUSERNAME;pwd=YOURPASSWORD;database=YOURDATABASENAME;"
$dbconnect.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $dbconnect
$sql.CommandText = "INSERT INTO edbdata (server,dbname,dbsize,Client) values ('" + $server + "','" + $dbname + "','" + $size + "','" + $client + "')"
$sql.ExecuteNonQuery()
$dbconnect.Close()
}
}
+++++end file edbsizes.ps1+++++

1. File 3 is grabbing the mailboxsizes : "mailboxsizes.ps1" :

+++++start file mailboxsizes.ps1+++++
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010
. $env:ExchangeInstallPath\bin\RemoteExchange.ps1
Connect-ExchangeServer -auto
[void][system.reflection.Assembly]::LoadFrom(“C:\nroicollector\MySQL.Data.dll”)
import-module activedirectory
$servers = Get-MailboxServer
foreach($server in $servers)
{ $mailboxes = Get-Mailbox -server $server -ResultSize unlimited
foreach ($m in $mailboxes)
{ $stats = Get-MailboxStatistics $m | Select ItemCount,TotalItemSize
$alias = $m.Alias
$items = $stats.ItemCount
$size = $stats.TotalItemSize.Value.ToMB()
$client = (Get-ADDomain -Identity (Get-WmiObject Win32_ComputerSystem).Domain).NetBIOSName
$dbconnect = New-Object MySql.Data.MySqlClient.MySqlConnection
$dbconnect.ConnectionString = "server=YOURMYSQLSERVERFQDN;port=3306;uid=YOURUSERNAME;pwd=YOURPASSWORD;database=YOURDATABASENAME;"
$dbconnect.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $dbconnect
$sql.CommandText = "INSERT INTO ExchangeData (Alias,ItemCount,TotalItemSize,Client) values ('" + $alias + "','" + $items + "','" + $size + "','" + $client + "')"
$sql.ExecuteNonQuery()
$dbconnect.Close()
}
}
+++++end file mailboxsizes.ps1+++++

10.File 4 is running the above scripts : "start.bat" :

+++++start file start.bat+++++
Powershell.exe -executionpolicy remotesigned -File C:\collector\edbsizes.ps1
Powershell.exe -executionpolicy remotesigned -File C:\collector\mailboxsizes.ps1
+++++end file start.bat+++++

11.Schedule start.bat to run once a month, using windows scheduler or labtech
12.In the ps1 files above, configure the parameters , YOURUSERNAME,YOURPASSWORD,YOURDATABASENAME,YOURMYSQLSERVERFQDN
13.Once all those are done, you aready to grab the data into brighgauge..

exchdata dataset :

Select
ExchangeData.id,
ExchangeData.Timestamp,
ExchangeData.Alias,
ExchangeData.TotalItemSize,
ExchangeData.Client
From
ExchangeData
WHERE
(Timestamp BETWEEN DATE_SUB(CURDATE(),INTERVAL (DAY(CURDATE())-1) DAY) AND LAST_DAY(NOW()))
Order By
ExchangeData.TotalItemSize Desc
LIMIT 10

edbsizes dataset :

select * from edbdata