Home > Scripts > Script: Exchange Mailbox Statistics Report

Script: Exchange Mailbox Statistics Report


A colleague asked me if I had any script in my repository that will create him a detailed report of users, mailboxes and their quota limits. I didn’t have one, so I told him I’d write it for him.

The first thing that came into my mind was the Get-MailboxStatistics PowerShell cmdlet. But then he said that the environment he needed the script for, was a Windows 2003 Domain with Exchange 2003. So I decided I’d do it VBS style.

The details he needed for the report were not only from the Exchange Mailbox but also from the Active Directory:

Property Where to get it from
Account Name Active Directory: samAccountName
User Principal Name Active Directory: userPrincipalName
Display Name Active Directory: displayName
Email Address Active Directory: mail
Issue Warning Active Directory: mDBStorageQuota *
Prohibit Send Active Directory: mDBOverQuotaLimit *
Prohibit Send and Receive Active Directory: mDBOverHardQuotaLimit *
Limit Status Exchange: StorageLimitInfo
Mailbox Size Exchange: Size
Total Items Exchange: TotalItems
Mailbox Location Exchange: ServerName + StorageGroupName + StoreName

 

So I started with an ADSI query to the configurationNamingContext to get the Exchange Servers listed in Active Directory.

(&(objectCategory=msExchExchangeServer)(objectClass=msExchExchangeServer))

For each server, a WMI query to the Exchange_Mailbox Class under the  /root/MicrosoftExchangeV2 namespace to get the StorageLimitInfo, Size, TotalItems, ServerName, StorageGroupName, StoreName and the MailboxDisplayName.

And for each mailbox, query the Active Directory for the additional required details (samAccountName, userPrincipalName, displayName, mail, mDBStorageQuota, mDBOverQuotaLimit and the mDBOverHardQuotaLimit). I used the legacyExchangeDN to match the mailbox to the user account in Active Directory.

(&(ObjectClass=user)(ObjectCategory=person)(legacyExchangeDN=" & legacyExchangeDN & "))

* But then, It got to me that the user may not have specific quota limits set to his user in the Active Directory, and that those settings would be inherited from the mailbox store.

So I added an ADSI query to get the information from the Mailbox Stores,

(&(objectClass=msExchPrivateMDB)(!objectClass=msExchPrivateMDBPolicy))

and put the needed values (mDBStorageQuota, mDBOverQuotaLimit and mDBOverHardQuotaLimit) into to a key-paired Dictionary Object (like a Hashtable). Then, when a user had the mDBUseDefaults set to true, I’d pull the information from the dictionary using his homeMDB property. Actually what I used was the value of:

GetObject("LDAP://" & oRs.Fields("homeMDB")).cn

 

After a few dry runs, I came across mailboxes that failed to be fully reported. I did some debugging (wscript.echo this and wscript.echo that), and noted that I forgot to handle disconnected mailboxes. So by checking if the DateDiscoveredAbsentInDS property had a value I was able to separate the “connected” from the “disconnected” mailboxes.

The script could still be tweaked for better performance and could use a bit more of logging, but I think it’s good enough to share here and definitely meets my colleague needs.

You can download the full script from here or here.

Just remember to run it using the cscript engine:

cscript //NoLogo ExchMailBoxStats.vbs

 

Notes:

  • You will need administrative rights on the Exchange Server to connect to it using WMI.
  • The CSV report will be created in the format of ExchMailBoxStats.yyyyMMdd.csv and located on the same folder as the ExchMailBoxStats.vbs is on.
Advertisements
  1. D-Rock
    December 7, 2011 at 06:30

    Hello Martin,

    I receive the following error when attempting to run

    Unable to connect to the EROS/root/MicrosoftExchangeV2 namespace.
    Unable to connect to the HELIOS/root/MicrosoftExchangeV2 namespace.
    Unable to connect to the SAGITTARIUS/root/MicrosoftExchangeV2 namespace

    A perms issue?

    Thanks,
    D.

    • December 8, 2011 at 13:58

      It might be a permissions issue, or a problem with the MicrosoftExchangeV2 WMI namepsace.
      try using wbemtest.exe to connect to the root/MicrosoftExchangeV2 namespace on the exchange box.

      • D-Rock
        December 9, 2011 at 06:27

        No dice. The wbemtest worked (I could connect to namespace on the exchange server).
        I ran a simple WMI query on the Exchange box under the same credentials I ran the script in

        strComputer = “.”
        Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\MicrosoftExchangeV2”)
        Set colItems = objWMIService.ExecQuery( _
        “SELECT * FROM Exchange_Mailbox”,,48)
        For Each objItem in colItems
        Wscript.Echo “———————————–”
        Wscript.Echo “Exchange_Mailbox instance”
        Wscript.Echo “———————————–”
        Wscript.Echo “Size: ” & objItem.Size
        Next

        and this worked fine so it must be some issue with the way it’s being called.

        Thanks.

      • December 9, 2011 at 13:33

        Then it’s a WMI security issue.
        If the code you wrote works for you, you can omit the impersonation part of the WMI connection (line 134) in the script.
        change it from:
        Set oWMIExchange = GetObject(“winmgmts:{impersonationLevel=impersonate}!//” & _
        to:
        Set oWMIExchange = GetObject(“winmgmts://” & _

        HTH,
        Martin.

      • D-Rock
        December 12, 2011 at 07:41

        Hi Martin,

        I changed that line of code. Still getting the same error. It’s searching our entire domain also. How can I restrict to a child domain?

        Thanks,
        Darren.

  2. SMS
    December 16, 2011 at 17:40

    I’m getting the same error as D-Rock. Did anyone come up with the solution to that yet?

    • D-Rock
      December 20, 2011 at 03:20

      Success! The line 137

      If Err.Number 0 Then

      I changes this to

      If Err.Number = 0 Then

      And this has got the script reporting! Fantastic!

  3. D-Rock
    December 20, 2011 at 03:20

    yipee!

  4. SMS
    December 20, 2011 at 16:41

    I made the Err.Number change, and that didn’t change my results. I still get the same error.

  5. SMS
    December 20, 2011 at 16:42

    SMS :
    I made the Err.Number change, and that didn’t change my results. I still get the same error.

    If Err.Number = 0 Then
    WScript.Echo “Unable to connect to the ” & sServer & _
    “/root/MicrosoftExchangeV2 namespace.”

  6. D-Rock
    December 20, 2011 at 23:01

    hmm…I’m no programmer. I pasted a simple WMI script in the comments above. Try this one and see if you get a successful result.

  7. SMS
    December 21, 2011 at 18:22

    Your simple test script worked. I’ll keep trying to figure it out. Thanks

  8. James
    May 25, 2012 at 10:35

    Wow. Fantastic – thank you very much for this – it worked a treat!!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: