Tuesday, April 17, 2007

Showing the percentage of Store being used by a User (or Users) on Exchange 2003 and 2007

If you haven’t been able to implement Mailbox quotas in your Exchange organization then you will probably find some users are using a large percentage of the used (or allocated space) in your Exchange database when compared to other users. If you work out this on a percentage basis this information can come in handy if you want to try to charge back to certain groups the cost of storing that information. E.g. you use this percentage of the resource so you pay this percentage of the cost (tell him he’s dreaming). I’ve put together a couple of scripts to help show this information as well as some other basic information about the user. The first couple of scripts show all the important information about a particular user. It takes one parameter which is the samaccountname and then does a AD Lookup and returns information about what Server the users mailbox is on what version of Exchange that mailserver is running what mailstore the mailbox is located in and what storage group that mailstore is located in. It then uses WMI to connect to the server in question and get the size of the database files on 2003 this includes the EDB and STM file which it adds together to get the combined file size. It then uses the Exchange_Mailbox class on Exchange 2003 to then retrieve the mailbox size of the user and the using a few divisions to get the percentage of store space the user is using. It then displays the results back to the user. The Exchange 2007 version does pretty much the same thing except that it uses the Exchange Powershell cmdlets from the Exchange Management Shell to get the same information. Most of the information can be returned with the get-mailboxstatistics cmdlet so the size of this script is a little smaller when compared to the Exchange 2003 version.

I’ve also put together a version of the script that returns the percentage used for every user in the domain that has a mailbox. This is where you can see a clear difference in why its easier to script against 2007 compared to 2003. The same script which took 50 lines in powershell in 2003 (okay I could have drop the GUID stuff and just used the legdn but that would have only saved about 4 lines) I could do the same thing in 10 lines in 2007 using the Exchange 2007 cmdlets with a big reduction in the complexity of the script.

In both these scripts I’ve used hashtables to store information and objects which can later be referenced as needed when trying to access the information across a number of servers and mail stores. In 2003 you need to do a bit of backtracking in AD to get the information about the mailboxes you are after. Eg on the user account you have DN of the mailstore the mailbox is located in the homemdb property you can take this and get the mailstore object from AD. On the mailstore object you have the msExchOwningServer property which has the DN of the server object in the configuration partition which you can then retrieve the netbios server name from which you can then use in your WMI query to get the mailbox size. Because you don’t really want to query the mailserver for each mailbox its easier to just grab all the mailbox sizes first and store then in a Hashtable and the just grab the size from the hashtable when needed.

I’ve put a downloadable copy of all the scripts here to show the difference in complexities between the 2003 version and 2007 version for the all users version the following is the 2007 version

$mbStores = @{ }
Get-mailboxdatabase | foreach {
$ffEdbFileFilter = "name='" + $_.edbfilepath.ToString().Replace("\","\\") + "'"
$mbEdbSize = get-wmiobject CIM_Datafile -filter $ffEdbFileFilter -ComputerName $_.ServerName

get-mailboxstatistics | foreach{
$divval = $mbStores[$_.Database]/100
$pcStore = ($_.TotalItemSize.Value/$divval)/100
$_.DisplayName + "," + $_.TotalItemSize.Value.ToMB() + "," + "{0:P1}" -f $pcStore

And this is the 2003 version

$snServerNames = @{ }
$mbSizes = @{ }
$mbStores = @{ }
$root = [ADSI]'LDAP://RootDSE'
$cfConfigRootpath = "LDAP://" + $root.ConfigurationNamingContext.tostring()
$configRoot = [ADSI]$cfConfigRootpath
$searcher = new-object System.DirectoryServices.DirectorySearcher($configRoot)
$searcher.Filter = '(objectCategory=msExchExchangeServer)'
$searcher1 = $searcher.FindAll()
#Get Server Objects
foreach($server in $searcher1){
$soServerObject = $server.getDirectoryEntry()
#Get Mailbox Sizes
$qrQueryresults = get-wmiobject -class Exchange_Mailbox -Namespace ROOT\MicrosoftExchangev2 -ComputerName $soServerObject.Name
foreach ($mbMailbox in $qrQueryresults){
$searcher.Filter = '(objectCategory=msExchPrivateMDB)'
$searcher2 = $searcher.FindAll()
foreach ($mailstore in $searcher2){
$moMailStoreObject = $mailstore.getDirectoryEntry()
$soServer = $snServerNames[[String]$moMailStoreObject.msExchOwningServer]
$ffEdbFileFilter = "name='" + $moMailStoreObject.msExchEDBFile.ToString().replace("\","\\") + "'"
$ffStmFileFilter = "name='" + $moMailStoreObject.msExchSLVFile.ToString().replace("\","\\") + "'"
$mbEdbSize =get-wmiobject CIM_Datafile -filter $ffEdbFileFilter -ComputerName $soServer.Name
$mbStmSize =get-wmiobject CIM_Datafile -filter $ffStmFileFilter -ComputerName $soServer.Name
[int64]$csCombinedSize = [double]$mbEdbSize.FileSize + [int64]$mbStmSize.FileSize

$dfDefaultRootPath = "LDAP://" + $root.DefaultNamingContext.tostring()
$dfRoot = [ADSI]$dfDefaultRootPath
$gfGALQueryFilter = "(&(&(&(& (mailnickname=*)(objectCategory=person)(objectClass=user)(msExchHomeServerName=*)))))"
$dfsearcher = new-object System.DirectoryServices.DirectorySearcher($dfRoot)
$dfsearcher.Filter = $gfGALQueryFilter
$searcher2 = $dfsearcher.FindAll()
foreach ($uaUsers in $searcher2){
$uaUserAccount = New-Object System.DirectoryServices.directoryentry
$uaUserAccount = $uaUsers.GetDirectoryEntry()
$gaGuidArray = $uaUserAccount.msExchMailboxGuid.value
$adGuid = "{" + $gaGuidArray[3].ToString("X2") + $gaGuidArray[2].ToString("X2") + $gaGuidArray[1].ToString("X2") + $gaGuidArray[0].ToString("X2") + "-" +
$gaGuidArray[5].ToString("X2") + $gaGuidArray[4].ToString("X2") + "-" + $gaGuidArray[7].ToString("X2") + $gaGuidArray[6].ToString("X2") + "-" +
$gaGuidArray[8].ToString("X2") + $gaGuidArray[9].ToString("X2") + "-" + $gaGuidArray[10].ToString("X2") + $gaGuidArray[11].ToString("X2") +
$gaGuidArray[12].ToString("X2") + $gaGuidArray[13].ToString("X2") + $gaGuidArray[14].ToString("X2") + $gaGuidArray[15].ToString("X2") + "}"
$mbsize = [double]$mbSizes[$adGuid].Size
$divval = ($mbStores[$uaUserAccount.HomeMDB][0]/1024)/100
$pcStore = ($mbsize/$divval)/100
$uaUserAccount.Name.ToString() + "," + "{0:#.00}" -f ($mbsize/1KB) + "," + "{0:P1}" -f $pcStore


Rob Campbell said...

Nice. I did much the same thing (but did use the legacydn) split out by mail store.

For the output file, I used the following to convert the user's DN to a somewhat "friendlier" format:

$User_dn -match "^(CN=.+?),(OU|CN)=(.+),DC=.+,DC=.+"
$User_CN = $matches[1]

$ad_path = "," + ($($matches[3]) -replace "OU=", "")

$ad_pathlist = @($($ad_path).split(","))
$ad_rpathlist = @()
$ad_pathcount=($($ad_pathlist).count) -1

for ($i=$ad_pathcount; $i -gt -1 ; $i --) {$ad_rpathlist += "$($ad_pathlist[$i])"}

if ($ad_pathcount -gt 1){$ad_rpath = [string]::join("." , $ad_rpathlist)}

else {$ad_rpath = $ad_rpathlist}

This converts the DN to a dot separated root-to-leaf form, sans the domain portion that's easier to sort/parse in Excel, and more intuitive for someone used to ADUC.

Glen said...

Nice Thanks Rob for sharing