Skip to main content

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
$mbStores.add($_.Identity,$mbEdbSize.FileSize)

}
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()
$snServerNames.add([String]$soServerObject.distinguishedName,$soServerObject)
#Get Mailbox Sizes
$qrQueryresults = get-wmiobject -class Exchange_Mailbox -Namespace ROOT\MicrosoftExchangev2 -ComputerName $soServerObject.Name
foreach ($mbMailbox in $qrQueryresults){
$mbSizes.add([String]$mbMailbox.MailboxGUID,$mbMailbox)
}
}
$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
$mbStores.add([String]$moMailStoreObject.distinguishedName,[int64]$csCombinedSize)
}

$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
}

Popular posts from this blog

Downloading a shared file from Onedrive for business using Powershell

I thought I'd quickly share this script I came up with to download a file that was shared using One Drive for Business (which is SharePoint under the covers) with Powershell. The following script takes a OneDrive for business URL which would look like https://mydom-my.sharepoint.com/personal/gscales_domain_com/Documents/Email%20attachments/filename.txt This script is pretty simple it uses the SharePoint CSOM (Client side object Model) which it loads in the first line. It uses the URI object to separate the host and relative URL which the CSOM requires and also the SharePointOnlineCredentials object to handle the Office365 SharePoint online authentication. The following script is a function that take the OneDrive URL, Credentials for Office365 and path you want to download the file to and downloads the file. eg to run the script you would use something like ./spdownload.ps1 ' https://mydom-my.sharepoint.com/personal/gscales_domain_com/Documents/Email%20attachments/filena

A walk-though using the Graph API Mailbox reports in Powershell

Quite recently the Reporting side of the Graph API has moved in GA from beta, there are quite a number of reports that can be run across various Office365 surfaces but in this post I'm going to focus on the Mailbox related ones. Accessing Office365 Reports using Powershell is nothing new and has been available in the previous reporting endpoint  https://msdn.microsoft.com/en-us/library/office/jj984326.aspx however from the end of January many of these cmdlets are now being depreciated in favour of the Graph API  https://msdn.microsoft.com/en-us/library/office/dn387059.aspx . Prerequisites  In comparison to using the Remote PowerShell cmdlets where only the correct Office365 Admin permissions where needed, to use the new Graph API reports endpoint you need to use OAuth for authentication so this requires an Application Registration  https://developer.microsoft.com/en-us/graph/docs/concepts/auth_overview  that is then given the correct oAuth Grants to use the Reports EndPoin

How to test SMTP using Opportunistic TLS with Powershell and grab the public certificate a SMTP server is using

Most email services these day employ Opportunistic TLS when trying to send Messages which means that wherever possible the Messages will be encrypted rather then the plain text legacy of SMTP.  This method was defined in RFC 3207 "SMTP Service Extension for Secure SMTP over Transport Layer Security" and  there's a quite a good explanation of Opportunistic TLS on Wikipedia  https://en.wikipedia.org/wiki/Opportunistic_TLS .  This is used for both Server to Server (eg MTA to MTA) and Client to server (Eg a Message client like Outlook which acts as a MSA) the later being generally Authenticated. Basically it allows you to have a normal plain text SMTP conversation that is then upgraded to TLS using the STARTTLS verb. Not all servers will support this verb so if its not supported then a message is just sent as Plain text. TLS relies on PKI certificates and the administrative issue s that come around certificate management like expired certificates which is why I wrote th
All sample scripts and source code is provided by for illustrative purposes only. All examples are untested in different environments and therefore, I cannot guarantee or imply reliability, serviceability, or function of these programs.

All code contained herein is provided to you "AS IS" without any warranties of any kind. The implied warranties of non-infringement, merchantability and fitness for a particular purpose are expressly disclaimed.