locked
Add list of users to AD group based on headers in csv RRS feed

  • Question

  • Hi,

    I am trying to make a script to add users to the AD groups based on header, the csv looks like this

    Gruop1 Group2 Group3
    UserA UserC UserA
    UserB   UserC


    Is that possible for script to add users based on my format, and ignore error if user is already in the group?

    Thank in advance, and have a good day!

    PS. see Fred's reply for working solution.
    • Edited by Leon.12345 Thursday, September 28, 2017 1:45 AM
    Wednesday, September 27, 2017 8:15 AM

Answers

  • HI Leon,

    sorry, kinda messed up on the enumeration ... let's give this another try:

    $rows = Import-Csv ".\Bulk_Import.csv"
    $groups = @{}
    foreach ($header in $rows[0].PSObject.Properties.Name) { $groups[$header] = @() }
    foreach ($row in $rows)
    {
        [string[]]$keys = $groups.Keys
        foreach ($key in $keys)
        {
            if ($row.$key) { $groups[$key] += $row.$key }
        }
    }
    foreach ($key in $groups.Keys) { Add-ADGroupMember -Identity $key -Member $groups[$key] -ErrorAction Ignore }

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Wednesday, September 27, 2017 12:09 PM

All replies

  • Your csv file is not a correct csv format. It will be hard for you to accomplish what you're asking if you start with a "bad data source".

    A correct csv file format could be this:

    User,Group
    User1,Group1
    User1,Group2
    User2,Group2
    User2,Group3
    or this:
    User,Groups
    User1,Group1;Group2
    User2,Group2;Group3
    ... or the one you already posted ... This way it will be easy to use the normal cmdlets from Powershell.

    Grüße - Best regards

    PS:> (79,108,97,102|%{[char]$_})-join''



    • Edited by BOfH-666 Wednesday, September 27, 2017 9:22 AM
    Wednesday, September 27, 2017 9:20 AM
  • Hi Leon,

    welcome to Technet.
    This is totally possible:

    $rows = Import-Csv ".\Bulk_Import.csv"
    $groups = @{}
    foreach ($header in $rows[0].PSObject.Properties.Name) { $groups[$header] = @() }
    foreach ($row in $rows)
    {
        foreach ($key in $groups.Keys)
        {
            if ($row.$key) { $groups[$key] += $row.$key }
        }
    }
    foreach ($key in $groups.Keys) { Add-ADGroupMember -Identity $key -Member $groups[$key] -ErrorAction Ignore }
    1. Create hashtable to contain data
    2. Take the first entry to read the column names, create a list for each column
    3. Iterate over each object and collect the entries into the respective group list
    4. For each group, add all users in one bulk operation, ignoring errors

    Cheers,
    Fred

    PS: Be sure to test it first!


    There's no place like 127.0.0.1

    Wednesday, September 27, 2017 9:23 AM
  • Thanks Fred for the prompt reply, i appreciate your help.
    It is way advanced than my level, I shall spend some time to digest it item 3 that you mentioned.

    It returns me below error though :(

    Collection was modified; enumeration operation may not execute.
    At C:\ADGroup2.ps1:6 char:14
    +     foreach ($key in $groups.Keys)
    +              ~~~~
        + CategoryInfo          : OperationStopped: (:) [], InvalidOperationException
        + FullyQualifiedErrorId : System.InvalidOperationException

    Add-ADGroupMember : Cannot validate argument on parameter 'Members'. The argument is null, empty, or an element of the
    argument collection contains a null value. Supply a collection that does not contain any null values and then try the
    command again.
    At C:\ADGroup2.ps1:11 char:75
    + foreach ($key in $groups.Keys) { Add-ADGroupMember -Identity $key -Member $group ...
    +                                                                           ~~~~~~
        + CategoryInfo          : InvalidData: (:) [Add-ADGroupMember], ParameterBindingValidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.AddADGrou
       pMember


    • Edited by Leon.12345 Wednesday, September 27, 2017 11:50 AM
    Wednesday, September 27, 2017 11:46 AM
  • HI Leon,

    sorry, kinda messed up on the enumeration ... let's give this another try:

    $rows = Import-Csv ".\Bulk_Import.csv"
    $groups = @{}
    foreach ($header in $rows[0].PSObject.Properties.Name) { $groups[$header] = @() }
    foreach ($row in $rows)
    {
        [string[]]$keys = $groups.Keys
        foreach ($key in $keys)
        {
            if ($row.$key) { $groups[$key] += $row.$key }
        }
    }
    foreach ($key in $groups.Keys) { Add-ADGroupMember -Identity $key -Member $groups[$key] -ErrorAction Ignore }

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Wednesday, September 27, 2017 12:09 PM
  • Thanks Fred, it is perfect and I learned a completely new idea. 
    I spent hours and finally found out .key is a function in hash table.  


    Thursday, September 28, 2017 1:48 AM
  • Glad to have been of Assistance :)

    Keys is actually a property of a hashtable. It also has another useful property: Values (in case you want the stored values instead). I forgot to store away the contents of it before iterating over it and changing the hashtable (which is due to an internal way how foreach handles the keys property).


    There's no place like 127.0.0.1

    Thursday, September 28, 2017 7:40 AM