Friday, March 11, 2005

Relating Mailbox sizes to Mailbox quotas

Somebody asked last week about how to query mailbox sizes and mailbox quota information for a user. This is a interesting question there’s are a lot of different methods for querying mailboxes sizes most of these are listed in this KB . With Exchange 2003 you can use WMI to pull the mailbox size from the Exchange_mailbox class there’s a sample in the Exchange SDK here. With mailbox quotas what quota information will apply to your mailbox will differ depending on how quotas have been applied to the mailbox. Quotas can come from 1 of 3 locations, from a policy applied to the mail-store, by setting the quotas on the mail store itself and from quotas set on the mailbox directly. So to work out what quotas apply to a user you first need to query the user object in AD and look at the mDBUseDefaults property (which relates to the use store defaults check box in AD Users and Computers). If this is set to true (which is default) then you need to look at the mail-store object to see if quotas have been applied to the store. After you have the mailbox quota information you can query the server via WMI to work out the mailbox size and then using a little math you can determine the percentage of the quota that’s been used for each user.

I came up with a couple of scripts that could do this based on the email address of the user. These scripts are designed to run with the users email address as a commandline parameter it does a lookup of Active directory based on the email address. Then checks the mDBUseDefaults property. If this is set to true it opens the mail-store object this mailbox is located on using the homemdb property and reads the quota information of the mail-store. If the mDBUseDefaults is set to false the quota information is read from the user object. The script then uses WMI to workout the mailbox size this is where the two scripts differ. The first one use’s a select statement with a Where clause and the second use the 5 key fields for the exchange_mailbox WMI class to do a direct connection via WMI. I thought the second method may have been quicker but it still a little slower then I’d like. The information is then echo'd out to the command-line and a little math I used to work out the percentage of the Quota used. These scripts are okay for one user but if your trying to do multiple users (like all the users on a server) this method is pretty slow. I started looking into ways to make this faster and one method that seemed to work although it has taken me most of the week to get it going is to use ADO data shaping to combine the data from the multiple datasources into one ADO recordset. I’m still working on this example but I should have something that’s post worthy by the next week. I’ve posted both the script I’ve talked about here as well as the slow server example.

26 comments:

cehupper said...

Hi, this is great, I got it working for my env. A few of your echos failed because of type issues, namely the on with the quid. I'm kind of new to this, can you explain why you have to use winMgmts? Why aren't these fields just available via ldap/adsi? Thanks

N Vickers said...

Glen, Can you please clarify what rights are required for this to work? I have made use of this but can't get it to work for restricted user accounts (Exchange View Only).

cehupper said...

Hi again. I'm running this as an admin. domain, exchange and local.
I have not tried to minimize this requirement. Sorry, not much help here.

Glen said...

I'll try and answer both of your questions.

Why am i using WMI, the only thing i use WMI for is to get the mailbox size (this will only work on a 2003 box).Basically you can't get this information anywhere else without logging onto the mailbox and using CDO,Exoledb or WebDAV to calculate it (which is what you have to do on a Ex2000 box).

In regards to permissions, with the MicrosoftExchangev2 WMI namespace by default this namespace is only remotable by Administrators. You can change this however on every server where you want users other these administrators to connect. Have a read of http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/setting_namespace_security.asp and http://redmondmag.com/columns/article.asp?EditorialsID=703 .

The namespace you want to modify is MicrosoftExchangev2 and you need to assign the RemoteEnable right to the group of users that you want to have this access. Note this only give these users the right to connect to the namespace they will still need View only Admin to query it.

cehupper said...

If CDO can get this info, then I'm assuming extended mapi can as well.

Do you know how to get the Mail Store quotas via MAPI? I can't figure out how to do it. As it is, I've been trying, poorly, to get this script to work in C++. Thanks

Glen said...

Sorry I think you miss understood I was talking about retrieving the mailbox size with CDO which you can do by looking at the size of each folder. The quota information is held in Active Directory so you need to use LDAP to access this information. Mailbox size however is always stored in the Exchange store so you need to use an API that can access the Exchange Store. eg WMI(*), CDO, MAPI, EXMAPI, Exoledb or WebDAV. For public folders this is different the quota info is stored in the Exchange Store

* WMI is not technically an API to access the store but it’s an interface to retrieve management information such as mailbox size (im not sure what it uses under the covers to access the store my guess is ExMAPI)

Johnnie Miami said...

Hi Glen!
I have just started doing work with Exchange and .NET and continually find myself back at your blog. I am working on gathering current email usage statistics but would like to get this information directly though vb.net. Is it possile to use WMI in .NET (i.e. convert your vb scripts to .NET)?

Thanks!

Glen said...

Sure in .NET you can use the System.Management class to access WMI. I've posted a few C# samples a while ago http://gsexdev.blogspot.com/2005/03/c-wmi-exchange-samples.html .If you want to do data shaping in .NET this is easy with ADO.NET this allows you to create a datarealtion between datatables within a dataset. If you want to use any of the WMI methods (not that there are many for the exchange class's) you need to use Invoke.

Anonymous said...

Glen in one of your vbs examples you have a function called Octenttohex. Do you have that in vb.net or c#? Now that I can get the mailbox size (thank you btw) I want to use the msExchMailboxGUID from AD to find the appropriate mailbox in Exchange. However, when looking at the guid in AD its in a format like (90 F0 E7 55....) but in Exchange its {55E7f090-....}. I haven't had any luck finding a ascb function in .net.

Glen said...

You can try something like this in VB.NET same thing should work in C# as well


Console.WriteLine(ConvertToExGuid(de.Properties("msExchMailboxGUID").Value))


Function ConvertToExGuid(ByVal values As Byte()) As String
Return "{" & values(3).ToString("x2") & values(2).ToString("x2") & values(1).ToString("x2") & _
values(0).ToString("x2") & "-" & values(5).ToString("x2") & values(4).ToString("x2") & _
"-" & values(7).ToString("x2") & values(6).ToString("x2") & "-" & values(8).ToString("x2") & _
values(9).ToString("x2") & "-" & values(10).ToString("x2") & values(11).ToString("x2") & _
values(12).ToString("x2") & values(13).ToString("x2") & values(14).ToString("x2") & values(15).ToString("x2") & "}"
End Function

Anonymous said...

Glen, Great scripts - they have been very helpful. As expected, when using CDO or WebDAV in E2K, ESM reports last logged on user as the account that last ran the script.

The WMI option that E2K3 provides obviously does not do this since it doesn't log on to the user's mailbox.

I dislike leaving this type of trail and was wondering if there was any way to avoid doing this on E2K? Thanks!

Glen said...

I dont think you can do this using Script. Using Exmapi and C++ its possible have a look at the exmapi method outlinded in http://support.microsoft.com/default.aspx?scid=KB;en-us;q320071#XSLTH3120121122120121120120 . I'm pretty sure this is what the WMI class is wrappering in Exchange 2003

Deb said...

Did you ever update this script?

Glen said...

Yep sure did have a look at http://gsexdev.blogspot.com/2005/03/data-shaping-adsi-and-exchange-wmi.html

Cheers
Glen

Anonymous said...

Hi Glen, thanks for helping others atchieving their goals. since i'm totally new to scripting and i'm asked to create a script to query the mailbox size in comparison with the Mailbox Quota BUT!! for each OU and Sub-OUs.

Please Help or Advice!!

Glen said...

You need to keep in mind that quotas aren't applied at the OU level there always at the user object or Store/System Policy. So the scripts here and in http://gsexdev.blogspot.com/2005/03/data-shaping-adsi-and-exchange-wmi.html
will output all the information you need to create your report. If you want to group the information by OU what you need to do is parse the OU information out of the Users distinguishedname include this in the CSV file and them maybe feed it into something like Crystal Reports when you can create a nice grouped report from CSV data. We it comes to mailbox size its depends on what version of Exchange you are running as to what methods you an use eg if you running Exchange 2003 then using WMI is the easiest method if your running exchange 2000 then you need to look at CDO 1.2 or WebDAV/ADO to do this. If your new to scripting this is a pretty advanced script (im not sure what else i can really say).

Cheers
Glen

Anonymous said...

Thanks Glen for your comment, and i forgot to mention that i'm working in a hosted exchange env. which means as you know that everything is provisioned through the MPS, and each OU is representing a seperate domain users. and to get a report for the mailbox size for each domain, i need to query each OU Get each user mailbox Size then calculate it somehow and finally echo it.
and regarding the Assigned Quota do i need to query the MPS DB since nothing to do with AD nor Exchange, am i right?

Anonymous said...

sorry! it's me again but after reading the post found that it's not well explained:), anyway, here is the logic behind what i'm willing to do exactly:

1-query every OU and subs for each domain (OU)and display the OU Name
2- Query the Assigned Quota for every Domain (OU)
3-Query and calculate the mailbox size for each user per every Domain (OU.
4-Display the Output in a chart view, or pass it to any reporting tool like what you mentioned CR

my question is: is that possible to happen? & How ?

Glen said...

I dont work in the hosted Exchange space so my experince with this is limited. No matter what fancy provisioning stuff that is on top the underlying Exchange/Active Directory functionality will be the same. I don’t mean to harp back to this but you wont get quota information from an OU mailbox quotas are applied directly to a user objects or to the store object. In a hosted environment where users maybe sharing the same store and need different quotas the provisioning tool would probably be applying the quotas to the user object. The one problem i see unless you have a really tight environment you may have an OU called Sales with users A,B,C at the time of creation these users may have been assigned a 70 MB quotas. User B hits his quotas but because he's someone important admin comes along and changes his quota to 100 MB to shut him up. In this case what do you say the quota is for the OU ?. The other complications are maybe the user objects don’t have quotas applied and they are all set to use the store defaults except for one user that for some reason need a larger quotas then the store had.

But if you’re in a shared environment this might be all redundant eg can you use ADSI to query Active Directory, can you use WMI to query Exchange. Both these API's generally will ignore any partitioning that’s inplace and just return everything.

1-query every OU and subs for each domain (OU)and display the OU Name

Are you talking Active Directory domains in multiple domain forest or SMTP domains?


3-Query and calculate the mailbox size for each user per every Domain (OU.

Depends what API's you can use and what rights you have. eg if you cant get Exchange Administration rights they you wont be able to use WMI. And if you can’t get an account that has rights to every users mailbox then you won’t be able to use CDO or WebDAV to query the mailbox to work out the size.

4-Display the Output in a chart view, or pass it to any reporting tool like what you mentioned CR

You can’t do charting in VBS if you’re looking for something simple try Excel or look at an ASP page instead and use an Active X charting plug in

I’ve used the word can’t a lot here but the underlying stuff is not that hard to extract once you have the rights. Formatting it the way you want takes a bit of effort but if you have to present this report to management etc then your better of using a package like Crystal where you can use the functionality of crystal to do any extra filtering or sorting that you might need.

localhost said...

Hi,

My boss asked me to look for an already existing addin that shows a realtime quota indicator, just like someone else asked in http://www.outlookcode.com/threads.aspx?forumid=4&messageid=13187

I already found a solution for OWA: http://www.leederbyshire.com/info.asp and http://www.mmmug.co.uk/forums/thread/531.aspx
I only need to find something for Outlook 2003.

If such a beast does not yet exist, how can the ideas you posted be used to create my own outlook addin? I need to provide this information to an actual developer who will then write it according to specs.

Glen said...

The easiest method would be to write a webservice somewhere that did the calculations for you at the backend and then just write a plug in that consumed this webservice , requested information for the user in question and displayed the result.

Cheers
Glen

Anonymous said...

Hi

I tried your script but didn't work and got the error message " subscript out of range" line 1 char 1.

I look forward your reply

tks

roy

Glen said...

These scripts are designed to run with the users email address as a commandline parameter eg cscript scritp.vbs user@domain.com

hohosaint said...

Hello Glen,
Was wondering if executing the script with wild card @ domain.com. Is it possible?

hohosaint said...

Hello Glen,

Can I use a wild card @ domain.com for mass users query?

lovinit said...

I'm looking for a script to override the default settings for a LIST of users. Users with exceptions to the default limits. Is this possible?