none
Same Results Returned of ExpandProperty in AD RRS feed

  • Question

  • So I have the following script that fills in the array $allAD with all the active users in ActiveDirectory.  Then I'm looping through those users in the array to get their AD Group Members shipped on a single line for import into a something else

    Each Line is basically (each membership with the SamAccountName on the front).

    @{SamAccountName=JPublic};CN=Internet Gen Access,CN=Users,DC=corporate,DC=domain,DC=local

    If I remark out the Get-ADUSER -FILTER to get all users and just do the simple array of 3 users it works fine, when I try to grab all it just keeps returning the same group membership for every user.  Like it's filled in $naa array of the ExpandedProperty from one person and just keeps feeding that to me as all the users.

    $allAD = Get-ADUser -Filter {Enabled -eq $true} -Properties SamAccountName | select-object SamAccountName
    
    #$allAD = "user1","user2","user3"
    
    for ($i=0; $i -lt $allAD.length; $i++) {
        $naa = Get-ADUSER -Identity $allAD[$i] -Properties memberof |  Select-Object -ExpandProperty memberof
        for ($a=0;$a -lt $naa.length; $a++) {
        	   Write-Output "$($allAD[$i]);$($naa[$a])"
        }
    }

    I'm an amateur at this obviously, there are better ways to do this, but I just can't figure out why this doesn't work with all users and works with a small subset.

    Thanks,

    Bob



    • Edited by STLBob Monday, April 10, 2017 6:28 PM
    Monday, April 10, 2017 6:17 PM

Answers

  • This is how PowerShell does this:

    Get-ADUser -Filter { Enabled -eq $true } -PipelineVariable user | 
    	ForEach-Object{ $_ | Get-ADPrincipalGroupMembership } | 
    	Select-Object @{n='UserName';e={$user.Name}}, @{n='GroupName';e={$_.Name}}


    \_(ツ)_/

    • Marked as answer by STLBob Monday, April 10, 2017 7:52 PM
    Monday, April 10, 2017 7:42 PM

All replies

  • This is how PowerShell does this:

    Get-ADUser -Filter { Enabled -eq $true } -PipelineVariable user | 
    	ForEach-Object{ $_ | Get-ADPrincipalGroupMembership } | 
    	Select-Object @{n='UserName';e={$user.Name}}, @{n='GroupName';e={$_.Name}}


    \_(ツ)_/

    • Marked as answer by STLBob Monday, April 10, 2017 7:52 PM
    Monday, April 10, 2017 7:42 PM
  • Now you can do this to get a nice report:

    Get-ADUser -Filter { Enabled -eq $true } -PipelineVariable user | 
    	ForEach-Object{ $_ | Get-ADPrincipalGroupMembership } | 
    	Select-Object @{n='UserName';e={$user.Name}}, @{n='GroupName';e={$_.Name}} |
    	Format-Table -GroupBy UserName -Property GroupName


    \_(ツ)_/


    • Edited by jrv Monday, April 10, 2017 7:46 PM
    Monday, April 10, 2017 7:45 PM
  • You can even pretty-it-up like this:

    Get-ADUser -Filter { Enabled -eq $true } -PipelineVariable user | 
    	ForEach-Object{ $_ | Get-ADPrincipalGroupMembership } | 
    	Select-Object @{n='UserName';e={$user.Name}}, @{n='GroupName';e={"`t`t$($_.Name)"}} |
    	Format-Table -GroupBy UserName -Property GroupName

    .... or we can create a custom formatter that outputs in any format we need.


    \_(ツ)_/


    • Edited by jrv Monday, April 10, 2017 7:51 PM
    Monday, April 10, 2017 7:51 PM
  • Thanks! I can work with that. We are trying to basically create some data to import into a database, which is why I was trying the single line with the fully qualified name.

    Monday, April 10, 2017 7:54 PM
  • Thanks! I can work with that. We are trying to basically create some data to import into a database, which is why I was trying the single line with the fully qualified name.

    The you want the first output as a CSV. Use the SamAccountNames and not the names.

    Get-ADUser -Filter { Enabled -eq $true } -PipelineVariable user | 
    	ForEach-Object{ $_ | Get-ADPrincipalGroupMembership } | 
    	Select-Object @{n='UserSamName';e={$user.SamAccountName}}, @{n='GroupSamName';e={"`t`t$($_.SamAccountName)"}} |
    	Export-Csv UserGroups.csv -NoType

    SQLServer can directly link to AD and import SQL selects on AD.


    \_(ツ)_/



    • Edited by jrv Monday, April 10, 2017 8:00 PM
    Monday, April 10, 2017 7:59 PM
  • https://docs.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-ole-db-provider-for-microsoft-active-directory-service

    https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/

    This would make  the data in SQL always current.  If you want history just snapshot a view on AD.  Contact you database DBA for assistance.  Thins will work with nearly any database vendor including Oracle and DB2.


    \_(ツ)_/

    Monday, April 10, 2017 8:07 PM
  • Getting errors at the end of the run, "Get-ADUser : The server has returned the following error: invalid enumeration context" which I've seen before when the command tries to process too many records, which is kind of why I try to pass them as variables to each command at a time using loops, which kept the arrays smaller. Arg!
    Monday, April 10, 2017 8:57 PM
  • If you are trying to do this with a remote connection it won't work.  The connection will be serialized and behave differently.

    The following should work on any local copy of AD:

    Get-ADUser -Filter { Enabled -eq $true } -PipelineVariable user | 
    	ForEach-Object{ $_ | Get-ADPrincipalGroupMembership } | 
    	Select-Object @{n='UserSamName';e={$user.SamAccountName}}, @{n='GroupSamName';e={"`t`t$($_.SamAccountName)"}}
    It must be PowerShell V3 (WMF 3) or later.


    \_(ツ)_/

    Monday, April 10, 2017 9:25 PM