Users not part of office distribution lists PowerShell

%3CLINGO-SUB%20id%3D%22lingo-sub-3274029%22%20slang%3D%22en-US%22%3EUsers%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3274029%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI'm%20looking%20to%20find%20office365%20users%20whose%20primary%20SMTP%20is%20our%20domain.com%20that%20are%20not%20part%20of%20certain%20office365%20distribution%20lists%20we%20have.%20For%20example%20Dist1%2C%20Dist2%2C%20Dist3%2C%20Dist4%20and%20export%20the%20results%20to%20a%20csv.%20I'm%20hoping%20someone%20already%20created%20such%20a%20script.%20Any%20is%20appreciated.%20Thank%20you%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3274029%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281631%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281631%22%20slang%3D%22en-US%22%3E%3CP%3EDid%20it%20work%20for%20you%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3278239%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278239%22%20slang%3D%22en-US%22%3E%3CP%3ENo%20problem%2C%20let%20us%20know%20if%20it%20works%20out%20for%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3278226%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1209009%22%20target%3D%22_blank%22%3E%40Harm_Veenstra%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much.%20I'll%20test%20it%20out%20and%20get%20back%20to%20you%20but%20this%20looks%20like%20it%20might%20do%20it%20for%20me!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3274270%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3274270%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1154475%22%20target%3D%22_blank%22%3E%40Sam240%3C%2FA%3E%26nbsp%3BI%20created%20this%20one%20in%20my%20test%20tenant%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EScript%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3EConnect-ExchangeOnline%0A%24total%20%3D%20%40()%0A%24users%20%3D%20Get-Mailbox%20%7C%20Where-Object%20PrimarySmtpAddress%20-Match%20'M365x537152.OnMicrosoft.com'%20%7C%20where-object%20PrimarySmtpAddress%20-NotMatch%20'DiscoverySearchMailbox'%20%7C%20Sort-Object%20UserPrincipalName%0A%24distributiongroups%20%3D%20%22Executives%22%2C%20%22Sales%20Team%22%2C%20%22Tailspin%20Toys%22%0Aforeach%20(%24distributiongroup%20in%20%24distributiongroups)%20%7B%0A%20%20%20%20%24distributiongroupmembers%20%3D%20Get-DistributionGroupMember%20-Identity%20%24distributiongroup%0A%20%20%20%20foreach%20(%24user%20in%20%24users)%20%7B%0A%20%20%20%20%20%20%20%20if%20(-not%20(%24distributiongroupmembers%20%7C%20Select-String%20%24user.name))%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%24output%20%3D%20%5Bpscustomobject%5D%40%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22DistributiongroupName%22%20%20%20%20%20%20%20%20%20%20%3D%20%24distributiongroup%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22UserName%20which%20is%20not%20a%20Member%22%20%3D%20%24user.UserPrincipalName%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%24total%20%2B%3D%20%24output%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%7D%0A%0A%24total%20%7C%20Export-Csv%20-Path%20c%3A%5Ctemp%5Cdistributiongroups.csv%20-NoTypeInformation%20-Delimiter%20'%3B'%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECSV%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Harm_Veenstra_1-1648894579417.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F360722iEE2936ABF0BB1D31%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Harm_Veenstra_1-1648894579417.png%22%20alt%3D%22Harm_Veenstra_1-1648894579417.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375017%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375017%22%20slang%3D%22en-US%22%3EPlease%20mark%20my%20answer%20as%20solution%20to%20mark%20it%20as%20solved%20if%20it%20worked%20out%20for%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3379950%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3379950%22%20slang%3D%22en-US%22%3EWhy%20the%20two%20client%20side%20filters%20in%20the%20Get-Mailbox%20line%3F%20Performance%20wise%20that%20looks%20strange.%3CBR%20%2F%3ECan't%20we%20use%20a%20server%20side%20filter%20on%20Get-Mailbox%20for%20the%20two%20PrimarySmtpAddress%20elements%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3379967%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3379967%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20use%20whatever%20filter%20you%20want%20%3B)%3C%2Fimg%3E%20I%20added%20the%20discoverymailbox%20because%20it%20was%20also%20evaluated%2C%20you%20can%20leave%20that%20out%20if%20you%20want..%20Performance%20wise%20there%20was%20no%20impact%20in%20my%20test%20environment%2C%20very%20few%20users%2C%20but%20in%20larger%20environments%20it%20could%20take%20somewhat%20longer.%20But%20is%20this%20something%20you%20run%20once%20in%20a%20while%20or%20daily%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShorter%20version%3A%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EGet-Mailbox%3C%2FSPAN%3E%3CSPAN%3E%20%7C%20%3C%2FSPAN%3E%3CSPAN%3EWhere-Object%3C%2FSPAN%3E%3CSPAN%3E%20%7B%3C%2FSPAN%3E%3CSPAN%3E%24_%3C%2FSPAN%3E%3CSPAN%3E.PrimarySmtpAddress%3C%2FSPAN%3E%3CSPAN%3E%20-Match%20%3C%2FSPAN%3E%3CSPAN%3E'M365x537152.OnMicrosoft.com'%3C%2FSPAN%3E%3CSPAN%3E%20-and%20%3C%2FSPAN%3E%3CSPAN%3E%24_%3C%2FSPAN%3E%3CSPAN%3E.PrimarySmtpAddress%3C%2FSPAN%3E%3CSPAN%3E%20-NotMatch%20%3C%2FSPAN%3E%3CSPAN%3E'DiscoverySearchMailbox'%3C%2FSPAN%3E%3CSPAN%3E%7D%20%7C%20%3C%2FSPAN%3E%3CSPAN%3ESort-Object%3C%2FSPAN%3E%3CSPAN%3E%20UserPrincipalName%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3387156%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3387156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1209009%22%20target%3D%22_blank%22%3E%40Harm_Veenstra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan't%20this%20all%20be%20done%20server%20side.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EGet-Mailbox%20-Filter%20%7B%24_.PrimarySmtpAddress%20-Match%20'M365x537152.OnMicrosoft.com'%20-and%20%24_.PrimarySmtpAddress%20-NotMatch%20'DiscoverySearchMailbox'%7D%20%7C%0A%20%20%20%20Sort-Object%20UserPrincipalName%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3387167%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3387167%22%20slang%3D%22en-US%22%3ENot%20sure%20what%20you%20mean%20with%20server%20side%3F%20You%20connect%20Exchange%20Online%20and%20run%20the%20commands%20directory%20on%20that...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3387233%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3387233%22%20slang%3D%22en-US%22%3EIn%20your%20example%2C%20you%20are%20piping%20the%20Get-Mailbox%20account%20into%20Where-Object.%20This%20means%20it%20has%20to%20get%20all%20of%20the%20mailboxes%20first%20and%20then%20filter%20client%20side.%20The%20processing%20of%20this%20is%20done%20on%20the%20machine%20running%20the%20shell.%20(Client%20side).%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20use%20the%20-Filter%20on%20the%20Get-Mailbox%20command%2C%20this%20is%20then%20processed%20server%20side.%20Usually%20improving%20the%20performance%20of%20the%20script%2C%20particularly%20if%20there%20is%20a%20lot%20of%20data%20to%20filter%20which%20there%20would%20be%20in%20this%20case%20as%20you%20are%20looking%20at%20all%20mailboxes.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fskype-for-business-blog%2Ffilter-vs-where-object%2Fba-p%2F618442%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fskype-for-business-blog%2Ffilter-vs-where-object%2Fba-p%2F618442%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsid-500.com%2F2017%2F06%2F24%2Fpowershell-filtering-objects-vs-where-object%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsid-500.com%2F2017%2F06%2F24%2Fpowershell-filtering-objects-vs-where-object%2F%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3387269%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3387269%22%20slang%3D%22en-US%22%3E%3CP%3EAh%2C%20yes!%20You're%20right!%20But%20PrimarySmtpAddress%20doesn't%20look%20like%20something%20you%20can%20filter%20regarding%20Primary%20address%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fexchange%2Frecipientfilter-properties%3Fview%3Dexchange-ps%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EFilterable%20properties%20for%20the%20RecipientFilter%20parameter%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3388173%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3388173%22%20slang%3D%22en-US%22%3EIt's%20in%20that%20list.%20It%20doesn't%20have%20a%20LDAP%20Display%20Name%20but%20it%20says%20%22string%20(wildcards%20accepted)%22.%3CBR%20%2F%3EThe%20following%20works%20in%20my%20org.%20Need%20to%20use%20-eq%20or%20-like.%20(Match%20is%20not%20a%20supported%20operator).%3CBR%20%2F%3EGet-Mailbox%20-Filter%20%7BPrimarySmtpAddress%20-like%20'testuser*'%7D%3CBR%20%2F%3E%3CBR%20%2F%3EThere%20are%20always%20some%20attributes%5Ccommands%20where%20its%20better%20or%20easier%20to%20use%20client%20side%20filtering%2C%20but%20I%20always%20try%5Ccheck%20if%20it%20can%20be%20done%20server%20side%20where%20possible.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3388489%22%20slang%3D%22en-US%22%3ERe%3A%20Users%20not%20part%20of%20office%20distribution%20lists%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3388489%22%20slang%3D%22en-US%22%3EIt%20also%20says%20in%20the%20comments%20column%2C%20scroll%20right%20on%20page%3A%3CBR%20%2F%3E%3CBR%20%2F%3EDon't%20use%20the%20PrimarySmtpAddress%20property%3B%20use%20the%20EmailAddresses%20property%20instead.%20Any%20filter%20that%20uses%20the%20PrimarySmtpAddress%20property%20will%20also%20search%20values%20in%20the%20EmailAddresses%20property.%20For%20example%2C%20if%20a%20mailbox%20has%20the%20primary%20email%20address%20dario%40contoso.com%2C%20and%20the%20additional%20proxy%20addresses%20dario2%40contoso.com%20and%20dario3%40contoso.com%2C%20all%20of%20the%20following%20filters%20will%20return%20that%20mailbox%20in%20the%20result%3A%20%22PrimarySmtpAddress%20-eq%20'dario%40contoso.com'%22%2C%20%22PrimarySmtpAddress%20-eq%20'dario2%40contoso.com'%22%2C%20or%20%22PrimarySmtpAddress%20-eq%20'dario3%40contoso.com'%22.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20have%20to%20specify%20email%20addresses%2C%20I%20was%20searching%20for%20Domains%20which%20were%20set%20as%20PrimarySMTPAddress%3C%2FLINGO-BODY%3E
New Contributor

I'm looking to find office365 users whose primary SMTP is our domain.com that are not part of certain office365 distribution lists we have. For example Dist1, Dist2, Dist3, Dist4 and export the results to a csv. I'm hoping someone already created such a script. Any is appreciated. Thank you

13 Replies

@Sam240 I created this one in my test tenant:

 

Script:

 

Connect-ExchangeOnline
$total = @()
$users = Get-Mailbox | Where-Object PrimarySmtpAddress -Match 'M365x537152.OnMicrosoft.com' | where-object PrimarySmtpAddress -NotMatch 'DiscoverySearchMailbox' | Sort-Object UserPrincipalName
$distributiongroups = "Executives", "Sales Team", "Tailspin Toys"
foreach ($distributiongroup in $distributiongroups) {
    $distributiongroupmembers = Get-DistributionGroupMember -Identity $distributiongroup
    foreach ($user in $users) {
        if (-not ($distributiongroupmembers | Select-String $user.name)) {
            $output = [pscustomobject]@{
                "DistributiongroupName"          = $distributiongroup
                "UserName which is not a Member" = $user.UserPrincipalName
            }
            $total += $output
        }
    }
}

$total | Export-Csv -Path c:\temp\distributiongroups.csv -NoTypeInformation -Delimiter ';'

 

 

CSV:

Harm_Veenstra_1-1648894579417.png

 

 

@Harm_Veenstra Thank you so much. I'll test it out and get back to you but this looks like it might do it for me!!!

No problem, let us know if it works out for you!

Did it work for you ?

Please mark my answer as solution to mark it as solved if it worked out for you.
Why the two client side filters in the Get-Mailbox line? Performance wise that looks strange.
Can't we use a server side filter on Get-Mailbox for the two PrimarySmtpAddress elements?

You can use whatever filter you want ;) I added the discoverymailbox because it was also evaluated, you can leave that out if you want.. Performance wise there was no impact in my test environment, very few users, but in larger environments it could take somewhat longer. But is this something you run once in a while or daily?

 

Shorter version:

Get-Mailbox | Where-Object {$_.PrimarySmtpAddress -Match 'M365x537152.OnMicrosoft.com' -and $_.PrimarySmtpAddress -NotMatch 'DiscoverySearchMailbox'} | Sort-Object UserPrincipalName

@Harm_Veenstra 

 

Can't this all be done server side.

 

Get-Mailbox -Filter {$_.PrimarySmtpAddress -Match 'M365x537152.OnMicrosoft.com' -and $_.PrimarySmtpAddress -NotMatch 'DiscoverySearchMailbox'} |
    Sort-Object UserPrincipalName
Not sure what you mean with server side? You connect Exchange Online and run the commands directory on that...
In your example, you are piping the Get-Mailbox account into Where-Object. This means it has to get all of the mailboxes first and then filter client side. The processing of this is done on the machine running the shell. (Client side).

If you use the -Filter on the Get-Mailbox command, this is then processed server side. Usually improving the performance of the script, particularly if there is a lot of data to filter which there would be in this case as you are looking at all mailboxes.

https://techcommunity.microsoft.com/t5/skype-for-business-blog/filter-vs-where-object/ba-p/618442

https://sid-500.com/2017/06/24/powershell-filtering-objects-vs-where-object/

Ah, yes! You're right! But PrimarySmtpAddress doesn't look like something you can filter regarding Primary address Filterable properties for the RecipientFilter parameter | Microsoft Docs

It's in that list. It doesn't have a LDAP Display Name but it says "string (wildcards accepted)".
The following works in my org. Need to use -eq or -like. (Match is not a supported operator).
Get-Mailbox -Filter {PrimarySmtpAddress -like 'testuser*'}

There are always some attributes\commands where its better or easier to use client side filtering, but I always try\check if it can be done server side where possible.
It also says in the comments column, scroll right on page:

Don't use the PrimarySmtpAddress property; use the EmailAddresses property instead. Any filter that uses the PrimarySmtpAddress property will also search values in the EmailAddresses property. For example, if a mailbox has the primary email address dario@contoso.com, and the additional proxy addresses dario2@contoso.com and dario3@contoso.com, all of the following filters will return that mailbox in the result: "PrimarySmtpAddress -eq 'dario@contoso.com'", "PrimarySmtpAddress -eq 'dario2@contoso.com'", or "PrimarySmtpAddress -eq 'dario3@contoso.com'".

You have to specify email addresses, I was searching for Domains which were set as PrimarySMTPAddress