Bulk group modification

This topic contains 12 replies, has 3 voices, and was last updated by  edwin arlington 6 days, 17 hours ago.

  • Author
    Posts
  • #81299

    Jon
    Participant

    We are in the process of redoing our sharepoint group permissions (yay) and part of that is
    1) Bulk group creation
    2) Removing users from groups
    3) Adding groups/users to groups

    I have taken care of bulk group creation with this:

    gc C:\Scripts\sp\sp.txt | ForEach-Object {New-adgroup -Groupscope Global -Name $_ -path "OU=SHAREPOINT,OU=blah,DC=blah,DC=blah,DC=blah,DC=blah"}

    Where sp.txt just contains the group names

    I have taken care of emptying the current groups with:

    gc C:\scripts\sp\sp2.txt | % {Remove-ADGroupMember -Identity $_ -Members (Get-ADGroupMember $_ ) -confirm:$false}

    Where sp2.txt contains the group names

    Where I am a little fuzzy is how to handle the group modification. Currently Sharepoint team gives me a spreadsheet with two columns. One is the group to be modified, and the other is the groups/users that need to be added to that group. Currently they are putting all the users/groups to be added to the group in one cell. I'm not sure of a way to read all the users in the cell into the group.

    I know I could format the data like below, and do a for each statement. But that would require me going into the spreadsheet and modifying the columns, I want to do this as automated as possible 🙂

    group,user
    group1,user1
    group1,user2
    group1,user3

    Any thoughts?

  • #81302

    Simon B
    Participant

    How are they separating the username in the cell ?

  • #81305

    Simon B
    Participant
  • #81310

    Jon
    Participant
  • #81314

    Simon B
    Participant

    That link I posted seems to work with your data format

    • #81317

      Jon
      Participant

      Thanks, that it does. For some reason it outputs the csv with just a bunch of "F"'s. Not sure why, but I am going to dig into it and see what I can figure out.

  • #81320

    Simon B
    Participant

    This is how my test data came out

    Group useradd
    group1 user3
    group2 user5
    group3 user27
    group4 user15
    group5 user56
    group6 user11
    group6 user15
    group6 user99
    group6

    • #81368

      Jon
      Participant

      It worked, problem was not enough coffee 🙂

      Real problem:
      The test spreadsheet I was using had multiple tabs, and the data I was looking to modify wasn't the first tab. I created a new spreadsheet with only one tab and the data and it was fine.

      Thank you for your help.

  • #81376

    Simon B
    Participant

    Glad it worked in the end coffee soles everything 🙂

  • #81385

    Simon B
    Participant

    Just a further update if you change the line $ws = $wb.Sheets.Item(1) to the number or name of the tab you will not have to create a new spread sheet each time (assuming the data is always on the same tab wit the same name 🙂 the example below had 2 tabs with the second one called thisone

    function ImportAndCrossJoin($path){
    $xls = New-Object -ComObject Excel.Application
    $xls.Visible = $false
    $wb = $xls.Workbooks.Open($path)
    $ws = $wb.Sheets.Item("thisone")

  • #81386

    Simon B
    Participant

    one more mod, I have changed the function so you can pass it the tab name

    function ImportAndCrossJoin($path,$tab){
    $xls = New-Object -ComObject Excel.Application
    $xls.Visible = $false
    $wb = $xls.Workbooks.Open($path)
    $ws = $wb.Sheets.Item($tab)
    $lastRow = ($ws.UsedRange.Rows).Count
    $lastCol = ($ws.UsedRange.Columns).Count
    foreach ($row in (2..$lastRow)){
    $ht = [ordered]@{}
    foreach ($col in (1..$lastCol)){
    $heading = $ws.Cells.Item(1,$col).value2
    #split the cell by crlf or comma
    $parts = ($ws.Cells.Item($row,$col).value2 -split ",|`n").Trim()
    $ht."$heading" = @($parts)
    }
    CartesianProduct $ht
    }
    $wb.Close()
    $xls.Quit()
    }

    ImportAndCrossJoin C:\test\test.xlsx "tabname" | Export-CSV -NoTypeInformation -path C:\test\seperatedwithtabs.csv
    ii C:\test\seperatedwithtabs.csv

  • #81475

    Jon
    Participant

    Thank you Simon! You are a great resource here.

  • #81899

    edwin arlington
    Participant

    This can be done through PowerShell

    $DNOfManager=dsquery user -o dn -name "Testing Tester"
    $GroupList=dsquery group DC=ad,dc=example,dc=local -limit 600
    Foreach ($group in $grouplist) {
    set-adgroup -Identity $Group -ManagedBy $DNOfManager
    add-adpermission -Identity $Group -user $DNOfManager -AccessRights ReadProperty, WriteProperty -Properties 'Member'
    }

    For more information, visit here.

You must be logged in to reply to this topic.