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!

Powershell Recipient Count for Exchange 2007

The other day I wanted to get a quick count of how many recipients I had in the enterprise.  Of course, I wanted to be able to run a powershell command instead of going into the EMC.  I fooled around with the Get-MailboxStatistics command until I got what I wanted and figured it would be a nice little one liner to share:

Get-MailboxStatistics | group MailboxDatabase | format-table count

This is a very simple command that just outputs the count (number) of recipients you have on a given exchange mailbox server.  Of course, if you have more than one server you’ll need to specify that inside the command.  Since I only have one, it’s pretty simple.

UPDATED:  Please note that this returns ALL recipients…not just mailboxes (as I thought when I initially posted this).  That means contacts, distribution groups, etc.   For a way to find how many user mailboxes you have in each database check this post.  Thanks for reading!

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.

AllOutlookUsers Distribution List

One of the challenges at the hospital where I work is to duplicate the old mailing lists they’ve had around since Exchange came out.  Through each subsequent version the lists have been passed…and in some cases, they just won’t migrate or update and have to be recreated.

I found this specifically true for the “All Users” dynamic distribution list when migrating to Exchange 2007 from 2003.

Dynamic distribution lists just did not work when moved.  Period.  Luckily, there were only a handful of those in existence in our enterprise.  The most important of course is used daily to get announcements out to everyone.  When we migrated and things didn’t work, a major stick in the spokes of our hospital bike wheel was present.  Weeping and gnashing of teeth ensued.

There are two steps to get this resolved…one takes place on the Exchange front and the other takes place on the Client side of things.  It’s rather difficult on the client side of things because you’ll need to purge data from a cache file locally on each computer if it runs Outlook in cached mode.

First, we’ll go over how to recreate a dynamic distribution list to fit various needs and then we’ll go over what needs to happen client side to make sure the list works properly.

Server Side

On the server side of things, creation of dynamic distribution lists with variables for exclusion or inclusion are much easier using the Exchange Management Shell.  Using the console can be done in smaller environments where you might have mostly standard mailboxes, few contacts, and limited security groups, etc.  In my case however, we have over 50 contacts and around 100 system mailboxes that are not checked regularly for mail.  To limit these boxes from receiving mail, I used the Custom Attributes setting available to each mailbox when right clicking and selecting properties.  Rather than try and exclude by OU (which is possible…but since 2007 switches to OPATH from LDAP…it’s a bit more difficult to master the right query) I elected to exclude by this set attribute.  I’ve outlined the custom attribute button below on the right click properties of a mailbox in the Exchange Management Console.

After setting the customattribute1 to ‘exclude’ I can then create an exclusion rule for the Exchange Management Shell when creating a distribution list.  It could be anything recorded in the custom attribute…it doesn’t necessarily have to be ‘exclude’…it could be ‘hide’ or just an asterisk.  Determine what is best for you and use the shell command below to exclude those with the customattribute1 that you have set.

New-DynamicDistributionGroup -Name "AllOutlookUsers" -Recipientcontainer domain.org -RecipientFilter {((WindowsEmailAddress  -like '*@domain.org') -and -not(CustomAttribute1 -eq "exclude") -and -not(Name -like 'SystemMailbox{*') -and -not(Name -like 'CAS{*') -and (RecipientType -eq "UserMailbox"))}
  • -Recipientcontainer: tells us that the domain.org is the AD container where all users are found.  If yours differs, enclose it in quotes, i.e., “domain.org/users” .  Make sure you change it to your domain and don’t leave it as domain.org.
  • -RecipientFilter: is our criteria for filtering out information in AD.  In our instance above:
    • we match only @domain.org email addresses
    • we exclude system mailboxes
    • we exclude our customattribute1 = exclude
    • we exclude CAS system mailboxes
    • we match only UserMailboxes so that we don’t mail contacts on this list

You could change things up a bit and perhaps add -and -not(department -eq ‘specialString’)) somewhere in which special string might be a department you want to exclude.  For more information, open the exchange shell up and type get-help New-DynamicDistributionGroup | more (the more command appended to the end allows you to read one page at a time…spacebar moves forward a page) for more criteria you can add to the command.

Now that you’ve added in the distribution group, you’ll need to publish it and make it available to the masses.  You can wait until Exchange does this automatically the next maintenance window or you can push it out the the OAB (Offline Addressbook) manually.  In the Exchange Management Console, go to Organization Configuration >> Mailbox >> Offline Address Book tab.  Right click the OAB and choose “update”

Client Side

Here comes the difficult part. There are 2 parts to making this play nicely with clients.

Part 1: The first part is that if you want people to immediately be able to use the new distribution group you just updated, you’ll need them to click on the small arrow beside send/receive:

From there choose “Download Address Book” and select the address book you want to download and click “OK”.  This should be enabled for you but if it is grayed out, it may be prevented by group policy and thus you’ll need to see your group policy manager to re-enable.  When the progress window disappears (if it doesn’t, just click close) that client now has the most recent version of the address book you updated and this should include the new dynamic distribution list you just created.  However, there still can be problems as you’ll see in Part 2.

Part 2: Inside of Outlook, when you create a new email, there is an autocomplete history that is saved.  Inside this .n2k file is cached addresses that you’ve sent to before that will fill in when you begin to type a few letters.  While this saves time and effort…it wreaks havoc with the list you just created.  Why?

In Exchange 2003, I had a dynamic distribution list named “AllOutlookUsers(New)” that everyone used.  When updating to 2007 it was broke.  Now that we’ve created a new dynamic distribution list named “AllOutlookUsers” guess what everyone will choose to mail to?  That’s right, they’ll be getting “AllOutlookUsers(New)” from their Outlook client autocomplete history cache…which will completely bomb if they try to email to it.

How does one fix this?  You can do one of two things.  You can visit each client machine and delete the entry OR you can attempt to use group policy to delete the entire cache the next time they login.  The second option you’ll find many people don’t like because ALL of their autocomplete stuff will be erased.  The first option requires that you visit each individual outlook client…and of course, that’s going to give Administrators a horrible time.

To delete an entry, open up a new email and begin typing “all” in the address To: field.  When the autocomplete selection pops below, use the arrow keys to highlight the old address…in my case “AllOutlookUsers(New)”…and then press the delete key.  You’re done!  That entry has been removed from the autocomplete cache for that individual user.

For those wanting to manage this via group policy, the file is named Outlook.NK2 by default and is located in the %APPDATAA\Microsoft\Outlook folder.  Scripting it wouldn’t be too difficult.  If you’re like me though you have your AllUsers distribution list controlled by a security group so that only a few people can email to it…which means I didn’t have to visit too many desktops to make sure they weren’t using the old address.

Hopefully, this helps a few of you out there to stomp out problems that might pop up when you go to create new dynamic distribution lists after migrating from Exchange 2003 to Exchange 2007.