A follow up to my previous post.
A colleague pointed out an oversight in my strategy. Email addresses don’t necessarily begin with a-z and 0-9, they can have other characters too. So he was concerned whether my strategy was exhaustive enough.
Fair enough. Email addresses aren’t limited to the characters I was searching on. I suppose I could expand the characters (but I am not sure what all to include), or maybe I could filter on some other property. Name, Display Name, etc. seemed like good candidates, but they have the same issue. So I thought let me filter on the Guid. This can either be the ExchangeGuid
property or just the Guid
property.
Seemed like a sensible idea, but it didn’t go well. For some reason I wasn’t getting all mailboxes. I don’t think its my filter, because if I get a list of all my mailboxes and extract the first character of their Guids it does fall in the a-z, 0-9 range – so something else was the matter.
Not knowing what to do I finally struck upon another idea! Yes, email addresses can start with characters other than a-z, 0-9, but I can use Graph to get me a list of all email addresses in my firm, extract their first and second characters, and use that to filter in Exchange Online. It doesn’t matter what the absolute range of email addresses are, all I care about is what’s present in my organization! :)
Getting email addresses via Graph is easy.
1 2 3 |
Select-MgProfile beta $graphUsers = Get-MgUser -All -Filter "(mail ne null) and (userType ne 'Guest')" -ConsistencyLevel Eventual -CountVariable userCount -ErrorAction Stop -Select mail |
I don’t care about Guest users that’s why I am excluding them too above. Also, I think I need to use the beta profile but I could be wrong. Thanks to this and this blog post for pointing me the correct way. The above takes about 1 min in my organization, and we have some 20,000+ users.
I can now extract the first letters. I do this by extracting the first and second characters and putting these into hash-tables. The keys, sorted, give me the range of characters!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$firstCharHash = @{} $secondCharHash = @{} $graphUsers.mail | ForEach-Object { $firstChar = $_.Substring(0,1) $firstCharHash[$firstChar.toLower()] = "x" $secondChar = $_.Substring(1,1) $secondCharHash[$secondChar.toLower()] = "x" } $firstCharArray = [array]$firstCharHash.Keys | Sort-Object $secondCharArray = [array]$secondCharHash.Keys | Sort-Object Remove-Variable firstCharHash Remove-Variable secondCharHash |
And that’s it, now my previous loop becomes:
1 2 3 4 5 6 7 |
foreach ($letter1 in $firstCharArray) { foreach ($letter2 in $secondCharArray) { $mailboxes = @(Get-Mailbox -ResultSize Unlimited -Filter "PrimarySmtpAddress -like '${letter1}${letter2}*'" -ErrorAction Stop) # do something with $mailboxes } } |
And this works too! To be doubly sure I did a comparison of the number of results I get by the above method vs Get-Mailbox -ResultSize Unlimited
and the numbers match.