Export AD Users from OU to CSV and check for group membership

Your PowerGUI Community: Simplify your PowerShell related GUI and script editing needs.


Export AD Users from OU to CSV and check for group membership

This question is answered
Hello,

i want to export all users from one single OU to a csv. And while doing this i want to check if this user is memeber if one group. If so i want to have a value 1 if not a value 0.

Exporting the Users from the AD is not a problem, but i have to check for the group and all my trys took very long and i did not have success.

Maybe i should note that i am new to powershell scripting but not to using powershell for basic administration.

Can someone help me?
Verified Answer
  • Two cmdlets are your best friends: Get-Member and Get-Help. For example, to find all of the attribues that are returned by Get-QADUser (so you can discover if the user is enabled or not) do this:

    Get-QADUser -SizeLimit 1 | Get-Member

    This will expose the attributes AccountIsExpired and AccountIsDisabled that you will probably want to add to your Select property list.

    You probably already know that the "@{}" format indicates a hash table. You can find all the cmdlets that have explicit hash table parameters by doing this:

    Get-Help * -Parameter *hash*

    Also try Get-Help About_*hash*

    You can probably guess from the examples that Select accepts a hash table as a property. You can find more about that by entering:

    Get-Help Select-Object -Parameter Property

    Which returns:
    -Property <Object[]>
        Specifies the properties to select. Wildcards are permitted.
       
        The value of the Property parameter can be a new calculated property. To create a calculated, property, use a hash table. Valid keys are:
        -- Name (or Label) <string>
        -- Expression <string> or <script block>
       
        Required?                    false
        Position?                    1
        Default value               
        Accept pipeline input?       false
        Accept wildcard characters?  true
       

    You'll probably want to follow that up with Get-Help Select-Object -Examples
    and check out the Scripting Guys Blog because they usually have lots more examples and practical applications of the various techniques.
All Replies
  • Hi there,

    You can try something like the below:

    Get-QADUser -SearchRoot 'OU_DN' -SizeLimit 0 |  Select Name,DN,@{n='MemberOfGroupA';e={$_.memberof | Select-String -Pattern '^cn=GroupA,' -Quiet}} | Export-Csv OUUsers.csv

    Just change the GroupA to the name of the group that you want to check to see if the user is a member of.

    Hope this helps

    Sean
  • Damn, and i was already at a script with more than 10 lines :-)

    Yes it is near to perfect. But it gives a True or False Value. Unfortunately it has to be 1/0 is there some way to do this directly, or do i have to replace every True and false ?


    Edit1:
    So this is also solved. But there occured one new problem :-( i need do have 3 empty columns between 2 of the columns. How is this possible?


    Edit2:
    Ok setting 1/0 Works with this code:
    Import-CSV -Path "C:\user2.csv" | ForEach-Object {
    if($_.Fuhrungskrafte -match 'True')
    {
    $_.Fuhrungskrafte = 1;$_
    }
    else
    {
    $_.Fuhrungskrafte =0; $_
    }}| Export-CSV -Path "C:\user3.csv" -NoTypeInformation

    Message was edited by: mg11

    Message was edited by: mg11
  • You can easily add empty columns by including parameter names that don't exist into the Select property list. (This is a common trick that can be used to avoid using Add-Member when adding computed values to an object.)

    Get-QADUser -SearchRoot 'OU_DN' -SizeLimit 0 | Select Name,DN,EmptyColumn1,EmptyColumn2,EmptyColumn3,@{n='MemberOfGroupA';e={$_.memberof | Select-String -Pattern '^cn=GroupA,' -Quiet}} | Export-Csv OUUsers.csv
  • Now i feel realy stupid. I should definitely put more time into learning powershell. So one last question then i am definitively done :-)

    Is it possible to check if the user is in the same row that is stated above?
  • I'm not entirely sure what you mean. There should only be a single row per user. If you want to know what row each user is in, then a hash table is probably your answer. If there are multiple rows with the same user name, then there will be an error adding to the hash table for the duplicates. The error message will give you the names.


    $Users = Import-CSV OUUsers.csv
    $UserRow = @{}
    0..($Users.Count-1) | % {$UserRow.Add($Users[$_].Name,$_ + 2)}

    $UserRow['someUser']

    Note that this will give you the row number in the spreadsheet and not the index number of the user in the $Users array.
  • Oh sorry i just checked my post, there is missing a sentence.

    What i want to accomplish is to check if the user account is enabled or disabled. And i wanted asked if it is possible to do it in the same powershell row: Get-QADUser -SearchRoot 'OU_DN' -SizeLimit 0 | Select Name,DN,EmptyColumn1,EmptyColumn2,EmptyColumn3,@{n='MemberOfGroupA';e={$_.memberof | Select-String -Pattern '^cn=GroupA,' -Quiet}} | Export-Csv OUUsers.csv

    Like the part where the group membership is checked.

    By the way where can i read about this kind of powershell syntax? I don't even understand whats happening (i mean: @{n='MemberOfGroupA';e={$_.memberof | Select-String -Pattern '^cn=GroupA,' -Quiet}} ) Idea[/i]
  • Two cmdlets are your best friends: Get-Member and Get-Help. For example, to find all of the attribues that are returned by Get-QADUser (so you can discover if the user is enabled or not) do this:

    Get-QADUser -SizeLimit 1 | Get-Member

    This will expose the attributes AccountIsExpired and AccountIsDisabled that you will probably want to add to your Select property list.

    You probably already know that the "@{}" format indicates a hash table. You can find all the cmdlets that have explicit hash table parameters by doing this:

    Get-Help * -Parameter *hash*

    Also try Get-Help About_*hash*

    You can probably guess from the examples that Select accepts a hash table as a property. You can find more about that by entering:

    Get-Help Select-Object -Parameter Property

    Which returns:
    -Property <Object[]>
        Specifies the properties to select. Wildcards are permitted.
       
        The value of the Property parameter can be a new calculated property. To create a calculated, property, use a hash table. Valid keys are:
        -- Name (or Label) <string>
        -- Expression <string> or <script block>
       
        Required?                    false
        Position?                    1
        Default value               
        Accept pipeline input?       false
        Accept wildcard characters?  true
       

    You'll probably want to follow that up with Get-Help Select-Object -Examples
    and check out the Scripting Guys Blog because they usually have lots more examples and practical applications of the various techniques.