Author Posts

October 5, 2017 at 12:22 pm

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?

October 5, 2017 at 12:30 pm

How are they separating the username in the cell ?

October 5, 2017 at 1:12 pm

That link I posted seems to work with your data format

October 5, 2017 at 1:28 pm

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.

October 5, 2017 at 1:31 pm

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

October 5, 2017 at 2:47 pm

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.

October 5, 2017 at 3:12 pm

Glad it worked in the end coffee soles everything 🙂

October 5, 2017 at 3:44 pm

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")

October 5, 2017 at 4:04 pm

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

October 6, 2017 at 2:14 pm

Thank you Simon! You are a great resource here.

October 12, 2017 at 11:35 am

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.
Bulk modifications using Set-AdUser