Find Number of Mailboxes per Database with Powershell

I previously posted about how to count total number of what I thought was mailboxes on any given server…and today I realized that when I used the command from that post I was coming up with a number just a bit too high for what I was looking for.  I did some research and found out that this command finds any entry for any recipient on the server you’re running it and reports back.  For example, I have just over 2000 objects in Recipient Configuration in the Exchange Management Console (EMC).  This is reported back if I use that command.  What I really wanted to know though was how many users mailboxes I have per database.

Of course, powershell is the easiest way to accomplish this.  Powershell is POWERFULL…but sometimes you just need to do simple things with it and instead of having a simple powershell command, you have a complex one.  It’s not the fault of powershell of course…it’s just how things happen to work.  Just the same, here is the command that you can use to get a nice readout of how many users you have in each database:

Get-MailboxDatabase | Select Server, StorageGroupName, Name, @{Name="Number Of Mailboxes";expression={(Get-Mailbox -Database $_.Identity | Measure-Object).Count}} | Format-Table -AutoSize

Please note this command is for a single mailbox server environment…if you have clustered or multiple mailbox database servers the command will probably be different.  Breaking down the command…with Get-MailboxDatabase we’re selecting all databases in our environment.  Next we’re selecting a few columns of data…server, storage group name.  Next we’re selecting a column titled Number Of Mailboxes and we’re defining an expression.  The expression grabs the identity of the single database and then does a count of each individual mailbox…it then returns that value under the name “Number Of Mailboxes”.  The last bits format the table and autoresize it to fit on your powershell screen.

You could output this to CSV relatively easy as well and you could even incorporate this into a nightly report if you really wanted to.  I know the command isn’t very simple…which is odd considering that it should be much simpler to find out the number of people on a single database.  If there are easier ways to do this…I haven’t found them.  You can use EMC to select the column and then sort and highlight the number of people for a quick and easy way…but I prefer powershell.  I hope this helps someone out!  I know it is a command I can’t live without!

User Not Appearing in GAL 2007 Fix

I had a problem since our migration from Exchange 2003.  The problem was a handful of users were not appearing on our Global Address List.

Tracking down what was going on has taken me quite a long time despite Microsoft KB articles published on the matter.  The solution posted by Microsoft in this instance just didn’t work for me.  I ran that powershell command until I was blue in the face and it returned zero results.  To find out how this happened and why they’re not displaying, I had to dig around for many weeks and create a custom powershell scriptblock until I FINALLY found the answer.  Hopefully, passing on this info here will save readers time and effort in their search for resolution of the same thing.

Why Is It Happening?

The reason this is happening is that when you first move from Exchange 2003 to Exchange 2007 or 2010, you have a “Default Policy” in Email Address Policy that should be automatically created (expecially if you run in tandem like I did).  This policy is carried over from Exchange 2003.  Microsoft changed the way 2007 filters work…they no longer use LDAP as they did with Exchange 2003, they now use OPATH.  For a more detailed explanation on this, see this blog post.  This default policy can be found in the Exchange Management Console >> Organization Configuration >> Hub Transport.  The default policy will place the ‘user alias’ in front of the domain that you have set for your receive connector.  When you first apply the policy, it appears like this:

default policy

Where can you view the screen pictured?  You can click edit on the default policy found in the location above and press next a couple of times and then cancel out  (you don’t want to apply the policy another time do you?).

This information lets us know that the default policy sets up our domain (edited to show domain.com in the picture) with the user network name, aka alias, so that users can immediately send and receive based on login names.  Often times, if you are in a small organization, users may login with firstname.lastname or firstinitial.lastname and this may be their email address as well.  In this case, you wouldn’t implement a secondary email address policy like I had to do.

Our organization wanted to receive email with firstname.lastname.  They’ve implemented a network name (aka alias) policy of first two initials of first and last name followed by last three of employee number.  So if my name were John Smith and my employee number were 12345, the network name would become josm345.  This is great for not having repeat names on our network but it’s bad for emailing people.  Enter my secondary email policy.

secondary policy

The secondary email address policy I created after we switched off Exchange 2003 set up the domain to be able to send and receive with firstname.lastname@domain.com.  I then applied this secondary policy and the SMTP address it created as the primary SMTP address for all users and gave this policy a higher priority over the default policy noted above.  You can see from the picture the policy takes firstname and lastname in front of the domain.

I also had to update all address lists as shown in this helpful blog post at MXExchangeteam.com.  After this, I updated the offline address book by going to Organization Configuration >> Mailbox >> Offline Address Book >> Right-click Update.  If you need to create a new global address list, you can’t do it from the EMC, you have to do it via Exchange Management Shell.

It’s a long process, but once you know that you have TWO email address policies that are applied, it makes sense that if a user doesn’t have both of the criteria to match these policies they will not appear on the Global Address List nor the Offline Address Book.  So now we have found out who this has happened to and then we’ll know why users are not showing up in these lists.  We also need to know what we need to do in order to fix it.

Finding Users Without Both SMTP Addresses

If we find users without both SMTP Addresses present on their mailbox, we will find the users who are NOT appearing in our Global Address List.  These users either didn’t have both email address policies applied to them or they had one SMTP address deleted.  Either way, if both policies (and thus both SMTP addresses) were not applied to them they will not be included.  You can fix this by adding in the SMTP address that is lacking making sure to appropriately set the one you want to be default.

I tried to use Microsoft’s KB article to find the users without both addresses but the query they wanted me to run via Exchange Management Shell returned nothing back for me.  I looked up what the query was doing and rewrote it how I thought it should be written.  Here’s the finished product and I’ll go through what it’s doing below:

Get-Mailbox -ResultSize Unlimited |Select-Object DisplayName,PrimarySmtpAddress, @{Name=“EmailAddresses”;Expression={$_.EmailAddresses |Where-Object {$_.PrefixString -ceq “smtp”} | ForEach-Object {$_.SmtpAddress}}} | export-csv c:\export.csv –NoTypeInformation

First, with ‘Get-Mailbox -ResultSize Unlimited’ we are getting ALL mailboxes in the enterprise.  We’re piping the results using the ‘|’ and then selecting an object based on criteria.  The object criteria we are selecting:  DisplayName so we can tell who the person is, Primary SMTP address and then we’re creating something called a scriptblock with the third ‘Select-Object’ criteria…I’ve taken it out of the command above to help explain it below:

@{Name=“EmailAddresses”;Expression={$_.EmailAddresses |Where-Object {$_.PrefixString -ceq “smtp”} | ForEach-Object {$_.SmtpAddress}}} | export-csv c:\export.csv –NoTypeInformation

This command grabs the email address and matches it to the DisplayName we selected previously.  It then searches through each Object and ‘Where-Object’ matches the string “smtp” it displays that information.  Notice it is in lowercase.  The primary SMTP address are stored in AD as capital letters and the secondary SMTP address (or third or fourth, etc) are stored in lower case…so we’re matching ANY other SMTP address (lowercase) that appears for DisplayName.  The results should display similar to the following:

DisplayName    ServerName    PrimarySmtpAddress                EmailAddresses
Nologist, Tek  ExchServer    tekn.nologist@teknologist.net     teno321@teknologist.net

The last portion of the code, ‘export-csv c:\export.csv‘, exports the report to the C: drive as a CSV so we can manipulate it in Excel.  The -NoTypeInformation removes the type information line from the top of our results…mainly because it’s not needed.

Using this query to find the users that are not appearing on your GAL is now as easy as finding blank entries in a CSV 🙂  Scan that CSV and where an SMTP address cell is missing (primary as noted in ‘PrimarySmtpAddrss’ or secondary as noted under ‘EmailAddresses’) you can note the DisplayName as a user who is not appearing in the GAL.

After you’re done with the list, you will need to add in the missing SMTP information on each of the users that you noted.  If you’re wondering WHERE to add it in, open the Exchange Management Console >>Recipient Configuration >> Mailbox >> Right-click user >> Properties >> Email Addresses Tab.  From there, you should only see one SMTP address…if you have two policies, you’ll need to add in another SMTP address that matches said policy.  As you can see displayed in the picture, some of my users had only my secondary policy applied.  So when I went to the Email Addresses Tab, some users appeared to only have firstname.lastname@domain.com. I needed them to also have alias@domain.com to match the default policy I carried over from 2003.  To do this, just click the “Add” button and create another SMTP address that matches the user alias@domain.com.  Make sure the checkbox at the bottom is checked so that any lists containing this user are updated.

When I first discovered the solution to this, I thought “Why do I need that default policy after I’ve powered down my Exchange 2003 server?”  Then it hit me!  Users probably still receive mail sent to their alias!  Some of the employees have been here for 30+ years and their alias is still a first initial and last name…who’s to say they don’t still give that out as their email address?  So I had to keep this policy in play whether I wanted to or not.

After you have found all the users that needed another SMTP server added to their Email Addresses area, you’ll need to go back and update the Offline Address Book again (shown above when I spoke about address lists).  The change should be instantaneous for all your OWA users but Outlook users may have to wait for a day before they appear depending on how you have Outlook 2007 setup.  If you’d like your Outlook users to immediately have the change, click on the “Send/Receive” arrow and choose “Download address Book”.  Select the appropriate address book and click “Ok”  Allow it to update and then check to make sure the users that did NOT appear before are actually appearing now.  Congratulations!  You’ve fixed the problem!

I hope that this post does a good job explaining WHY this occurs and how you can find those users and how you can get them to appear in the GAL and OAB again.

Migration Tip – XML Logs

When migrating from Exchange 2003 to Exchange 2007, I had problems with disk space on one of our network drives.  For the life of me, I couldn’t figure out what the problem was until we had about 100MB left.  Then I realized that every single mailbox move was being logged!  Try running a script that moves hundreds over at a time and then think of the log files!

I ran across a blog post that details just this and what to do about it.  Hopefully you won’t end up like me finding out after the fact!

http://www.telnetport25.com/component/content/article/11-exchmig/320-exchange-2007-tracking-and-analysing-large-mailbox-move-reports-using-the-exchangemovemailboxtaskviewer-tool.html