none
Export AD Group Membership using CSV RRS feed

  • Question

  • Hi 

    I've looking for a way to get the group memberships from a list of users in AD

    I have used PowerShell to export a list of disabled users into a CSV

    The next thing I would like to do is query each users group membership and ideally append them to the existing CSV or to an alternate CSV using ideally the UserPrincipalName but can be the SamAccountName

    I am having some issues once I try and append the data to the existing CSV and wondered if anyone knows of an efficient way to do this

    I did find a useful script (https://gallery.technet.microsoft.com/scriptcenter/PowerShell-Get-All-Group-167a9ce7/view/Discussions) on the Script Center, but ideally I could do with it being in a CSV for data analysis and filtering

    Import-Csv -Path 'C:\Temp\test.csv' | 
        ForEach-Object {
            get-aduser -filter "UserPrincipalName -eq '$($_.UserPrincipalName)'" -Properties UserPrincipalName|Get-ADPrincipalGroupMembership | Select-Object name,groupcategory | Export-Csv "C:\Temp\test.csv" -NoTypeInformation -Append -force
     
        }

    If anyone can assist that would be great

    Thanks

    Dean

    Wednesday, March 13, 2019 3:26 PM

Answers

  • Many thanks, this is now working and giving me the expected outcome

    Many thanks

    Dean

    • Marked as answer by exchinfo Thursday, March 14, 2019 1:39 PM
    Thursday, March 14, 2019 1:39 PM

All replies

  • Close.  but you cannot output to the same file you are reading from. Also the filtering and exporting need to be done at the end of a pipeline.

    Import-Csv C:\Temp\test.csv |
        ForEach-Object {
            get-aduser -filter "UserPrincipalName -eq '$($_.UserPrincipalName)'" 
                Get-ADPrincipalGroupMembership   
        } |
        Select-Object name, groupcategory | 
        Export-Csv C:\Temp\results.csv -NoTypeInformation 


    \_(ツ)_/

    Wednesday, March 13, 2019 4:18 PM
  • You will need to then modify this method to append the names that you want to the query.

    This will give you the SamAccountName:

    Import-Csv C:\Temp\test.csv |
        ForEach-Object {
            get-aduser -filter "UserPrincipalName -eq '$($_.UserPrincipalName)'" -PipelineVariable usr |
                Get-ADPrincipalGroupMembership   
        } |
        Select-Object @{n='SamaccountName';e={$usr.SamAccountName}},name, groupcategory | 
        Export-Csv C:\Temp\results.csv -NoTypeInformation


    \_(ツ)_/


    Wednesday, March 13, 2019 4:23 PM
  • To get separate files we can just use the pipeline object $psitem which now contains the SamAccountName after the "Select-Object". The new object can be used like this:

    Import-Csv C:\Temp\test.csv |
        ForEach-Object {
            get-aduser -filter "UserPrincipalName -eq '$($_.UserPrincipalName)'" -PipelineVariable usr |
                Get-ADPrincipalGroupMembership   
        } |
        Select-Object @{n='SamaccountName';e={$usr.SamAccountName}},name, groupcategory | 
        ForEach-Object{ $_|Export-Csv "C:\Temp\$($psitem.SamAccountName).csv" -NoTypeInformation}

    Or we could have used the $usr variable which also contains the name.


    \_(ツ)_/




    • Edited by jrv Thursday, March 14, 2019 12:35 PM
    Wednesday, March 13, 2019 4:33 PM
  • Many Thanks for the support,

    I'll work with the information you have provided me and feedback

    Kind Regards

    Dean


    Thursday, March 14, 2019 8:33 AM
  • Import-Csv C:\Temp\test.csv |
        ForEach-Object {
            get-aduser -filter "UserPrincipalName -eq '$($_.UserPrincipalName)'" -PipelineVariable usr
                Get-ADPrincipalGroupMembership   
        } |
        Select-Object @{n='SamaccountName';e={$usr.SamAccountName}},name, groupcategory | 
        Export-Csv C:\Temp\results.csv -NoTypeInformation

    I used the code from the 2nd example as I'm interested in getting all users added to one CSV file so we can analyze the data. When I run the code block the script does prompt for an identity, creates the results.csv but does not generate the data as expected

    "UserPrincipalName","name","groupcategory"
    "User@example.com,"user","Microsoft.ActiveDirectory.Management.ADPropertyValueCollection"

    To troubleshoot a little further I ran the following section of the code:

    Import-Csv C:\Temp\test.csv |
        ForEach-Object {
            get-aduser -filter "UserPrincipalName -eq '$($_.UserPrincipalName)'" -PipelineVariable usr
                Get-ADPrincipalGroupMembership   
        } 

    This code displays information about the user in the CSV

    DistinguishedName : User Details
    Enabled           : True
    GivenName         : User1
    Name              : user1
    ObjectClass       : user
    ObjectGUID        : 123445678910
    SamAccountName    : user1
    SID               : S-1-5-21-2083669633-569522783-1767988939-1420497
    Surname           : 
    UserPrincipalName : user1@example.com

    The code appears to want an input for the ADPrincipalGroupMembership:

    cmdlet Get-ADPrincipalGroupMembership at command pipeline position 1
    Supply values for the following parameters:
    (Type !? for Help.)
    Identity: 

    Any thoughts on what may need to be modified here



    Thursday, March 14, 2019 9:47 AM
  • And this code?

    Import-Csv C:\Temp\test.csv |
        ForEach-Object {
            get-aduser -filter "UserPrincipalName -eq '$($_.UserPrincipalName)'" -PipelineVariable usr |
                Get-ADPrincipalGroupMembership   
        } |
        Select-Object @{n='SamaccountName';e={$usr.SamAccountName}},name, groupcategory
    The original was missing a "|" which I fixed.


    \_(ツ)_/



    • Edited by jrv Thursday, March 14, 2019 12:35 PM
    Thursday, March 14, 2019 12:33 PM
  • Many thanks, this is now working and giving me the expected outcome

    Many thanks

    Dean

    • Marked as answer by exchinfo Thursday, March 14, 2019 1:39 PM
    Thursday, March 14, 2019 1:39 PM
  • Many thanks, this is now working and giving me the expected outcome

    Many thanks

    Dean

    Mark the post that answered your question and not your own response.


    \_(ツ)_/

    Thursday, March 14, 2019 1:56 PM