none
[PowerShell] Compare AD user's memberOf attribute against a list of excluded groups RRS feed

  • Question

  • I am making a script that will grab a list of users out of Active Directory and dump it to CSV, but I want it to only grab the users if they are not members of one or more Active Directory groups.  In the script, I've created an array of strings that represents the excluded groups' SAM account names, but I'm having trouble figuring out how I can leverage it against each user account.

    It seems that I'm having trouble tacking the problem of comparing two arrays with one another and excluding the user account from the CSV report if even one  of the exclude groups' names shows up in the memberOf attribute for any particular user.

    Any assistance would be welcome.  Thanks!

    Monday, August 22, 2016 6:16 PM

Answers

  • The ideal result would be a filter that excludes users that are members of any of the groups. For this you need the distinguishedNames of the groups. The memberOf attribute is an array of group DN's. Then you can use code similar to:

    Get-ADUser -LDAPFilter "(&(!(memberOf=cn=Group 1,ou=West,dc=Domain,dc=com))(!(memberOf=cn=Group 2,ou=West,dc=Domain,dc=com))(!(memberOf=cn=Group 3,ou=West,dc=Domain,dc=com))(!(memberOf=cn=Group 4,ou=West,dc=Domain,dc=com)))

    The filter will be long, but that is fine. The filter can include as many DN's as required. You can hard code the group DN's, or code to create the filter from code that converts the sAMAccountNames into distinguishedNames. The former is easier. At the end, of course, use the Export-Csv cmdlet.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Monday, August 22, 2016 6:45 PM
    Moderator

All replies

  • Post a short example script that contains only the minimum amount of code needed to reproduce the problem. Tell the expected and actual results, and also any error messages.

    -- Bill Stewart [Bill_Stewart]

    Monday, August 22, 2016 6:22 PM
    Moderator
  • Here is a code sample:

    [array]$ExcludedGroups = @(
        'C-GenericMailboxAccounts',
        'C-GuestAccounts',
        'C-KioskAccounts',
        'C-ServiceAccounts',
        'C-TestAccounts',
        'C-TrainingAccounts'
    )
        
    Function STOF-Get-ADParent([string]$DistinguishedName)
    {
         [array]$DnParts = $DistinguishedName -split '(?<![\\]),'
         $DnParts[1..$($DnParts.Count-1)] -join ','
    }
        
    Import-Module ActiveDirectory
        
    $Users = Get-ADUser -Filter * -SearchScope Subtree -Properties DisplayName, `
        Enabled,GivenName,Surname,EmployeeId,EmailAddress,MemberOf,Title,Department, `
        Created,LastLogonDate,PasswordNeverExpires -ErrorAction Stop | Select-Object `
        DisplayName,EmployeeId,GivenName,Surname,EmailAddress,Title,Department, `
        Enabled,PasswordNeverExpires,Created,LastLogonDate,@{Name="MemberOf"; `
        Expression={$_.MemberOf -Join '|'}},Name,@{Name="ParentContainerDN"; `
        Expression={STOF-Get-ADParent($_.DistinguishedName)}},DistinguishedName, `
        SamAccountName,UserPrincipalName,SID -ErrorAction Stop | Where-Object {( `
        $_.PasswordNeverExpires -eq $true)}
    
    $Users | Export-Csv -NoTypeInformation -Path 'C:\Temp\userreport.csv'



    The sample does not include the code I'm having trouble with (comparing a user accounts' memberOf attribute to $ExcludedGruops) because I don't know how to write said code.

    Actual results (these relate to the script as written): User accounts are included in the report whether or not they are members of one of the groups listed in the memberOf attribute.

    Expected results (once the actual filter code exists): User accounts that are members of one or more of the groups whose SAM account name is listed in the $ExcludedGroups array variable are not contained in the $Users variable and hence not present in the final CSV report.
    Monday, August 22, 2016 6:35 PM
  • The ideal result would be a filter that excludes users that are members of any of the groups. For this you need the distinguishedNames of the groups. The memberOf attribute is an array of group DN's. Then you can use code similar to:

    Get-ADUser -LDAPFilter "(&(!(memberOf=cn=Group 1,ou=West,dc=Domain,dc=com))(!(memberOf=cn=Group 2,ou=West,dc=Domain,dc=com))(!(memberOf=cn=Group 3,ou=West,dc=Domain,dc=com))(!(memberOf=cn=Group 4,ou=West,dc=Domain,dc=com)))

    The filter will be long, but that is fine. The filter can include as many DN's as required. You can hard code the group DN's, or code to create the filter from code that converts the sAMAccountNames into distinguishedNames. The former is easier. At the end, of course, use the Export-Csv cmdlet.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Monday, August 22, 2016 6:45 PM
    Moderator
  • I hadn't thought of building an LDAP filter.  That's a good idea.

    I like to make my scripts easy to modify, so I'd like to keep the simple-to-understand and simple-to-edit array of group SAM account names.  I should be able to come up with the code that builds the filter dynamically.

    Thanks!

    Edit: Yep!

    # Build LDAP filter items for excluded groups
    If ($ExcludedGroups.Count -ge 1)
    {
    	ForEach ($ExcludedGroup in $ExcludedGroups)
    	{
    		$LDAPFilterItems += "(!(memberOf=$(Get-ADGroup -Identity $ExcludedGroup | Select-Object -Expand DistinguishedName)))"
    	}
    }
    
    # Build the LDAP filter
    [string]$LDAPFilter = "(&$LDAPFilterItems)"

    • Edited by Scott W. Sander Monday, August 22, 2016 7:43 PM Added code snippet
    Monday, August 22, 2016 7:15 PM
  • An easy way to accomplish this would be the following:

    get-aduser -filter * -properties * | where {($_.memberof -notlike "*group1,*") -or ($_.memberof -notlike "*group2,*") -or ($_.memberof -notlike "*group3,*")} 

    Add additional ($_.memberof -notlike "*<group>*") for each group you want to check. Doing that will cause the script to go through all users, then only return the users that don't belong to any of the groups that you have in the query. You would then pipe that output to select and return the properties you want to display.

    The memberof property in AD (as returned in a variable for AD) is always a single string value that contains the full DN of whatever groups the user belongs to. When you do $_.memberof -notlike "*group1,*" you are basically telling the system to look through the memberof value for "group1" anywhere in the string, and with the , at the end it will make sure to not include any groups that are, for example, named "group1 is cool" or "i hate group1".

    Monday, August 22, 2016 7:26 PM
  • Adam - it is generally very bad practice to use -Properties * when you're not interested in every single property.

    Monday, August 22, 2016 7:29 PM
  • Yes Scott, you got it. The filter is efficient because you only retrieve the objects you need from the domain controller, rather than all users.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Monday, August 22, 2016 9:39 PM
    Moderator