I have a .csv file where it contains Mailbox delegation Permissions for entire shared mailboxes. File will contain two columns with email addresses mentioned. I.e. who is having Permissions on what mailboxes.
E.g. ColA = shared mailboxes and ColB = User mailboxes.
ColA. --- ColB
S1 --- User1
S1 --- User2
S1 --- User3
S2 --- User1
S2 --- User4
S3 --- User3
User1 --- S3
S4 --- User5
S4 --- User6
S5 --- User5
User6 --- S5
Requirement is to name them in Batches by creating a one more column.
E.g.
ColA --- ColB. ---ColC
S1 --- User1 ---Batch1
S1 --- User2 ---Batch1
S1 --- User3 ---Batch1
S2 --- User1 ---Batch1
S2 --- User4 ---Batch1
S3 --- User3 ---Batch1
User1 --- S3 ---Batch1
S4 --- User5 ---Batch3
S4 --- User6 ---Batch3
S5 --- User5 ---Batch3
User6 --- S5 ---Batch3
Naming in the Batches like Batch1, 2,3 & so on is based on the logic explained below.
Looking at above e.g. User1,2,3 have access to S1 so it will be Batch1.
User1 again have access to S2 so it will go in Batch1 coz it was already exist in Batch1.
User4 have access to S2 so it will go in Batch1 coz S2 previously exist in Batch1
In the same way all others users have access to mailboxes and should be Batched by creating the above logic.
I tried to write a code by using loops and storing them into variable and then using a compare-object. But its not helping me.
I have written a half way code as per my understanding as below.
$csv = import-csv c:\permlist.csv
$compare = compare-object $csv.colA
$csv.ColB -includeequal Foreach ($x in $compare.name) if
($compare.sideindicator -eq "=>")
{write-output "Exist in column B"} else {write-output "exist in column A"}
I was bored, and curious how I'd manage this, so I went ahead and mocked up your file (and added a bit to it to make sure that I would get successive groups into the same batch so long as there was any connected users):
$FileIn = @'
ColA,ColB
S1,User1
S1,User2
S1,User3
S2,User1
S2,User4
S3,User3
User1,S3
S4,User5
S4,User6
S5,User5
User6,S5
S6,User4
S6,User7
S7,User7
S7,User8
'@ -split '[\r\n]+'|convertfrom-csv
Then figured out how to sort it into batches, making sure that I looped enough to get any connected group of users into the same batch.
$AccessTo = @{}
$FileIn|Group ColA|%{$AccessTo.add($_.Name,$_.Group.ColB)}
$i=1
ForEach($Box in $AccessTo.Keys){
ForEach($User in $AccessTo[$Box]){
If(($FileIn.ColB|?{$_ -eq $User}|Select -First 1).Batch){Continue}
$BatchUsers = $AccessTo.Keys|?{$AccessTo[$_] -contains $User -or $_ -eq $User}|%{$AccessTo[$_]}|Select -Unique
Do{For($x=0;$x -lt $BatchUsers.Count;$x++){$AccessTo.Keys|?{$AccessTo[$_] -contains $BatchUsers[$x]}|%{$AccessTo[$_]}|?{$_ -notin $BatchUsers}|%{$BatchUsers+=$_}}}Until($x -eq $BatchUsers.count)
$FileIn | ?{$_.ColB -in $BatchUsers -and !$_.Batch}|%{Add-Member -InputObject $_ -NotePropertyName 'Batch' -NotePropertyValue $i}
}
$i=($FileIn.batch|sort|select -last 1)+1
}
In the end I get:
PS C:\Users\TMTech> $FileIn
ColA ColB Batch
---- ---- -----
S1 User1 1
S1 User2 1
S1 User3 1
S2 User1 1
S2 User4 1
S3 User3 1
User1 S3 1
S4 User5 2
S4 User6 2
S5 User5 2
User6 S5 2
S6 User4 1
S6 User7 1
S7 User7 1
S7 User8 1
Edit: Ok, a breakdown of the code was requested, so here we go. Please note that I use some aliases, mainly: ?{...} is short for Where{...}, and %{...} is short for ForEach-Object{...}.
I start by getting the data into PowerShell, and saving it to a variable named $FileIn. You will be importing a csv, so I'll just move on. Next I make an empty hashtable.
$AccessTo = @{}
Then I take the data that was imported, group it up by the value in ColA, and for each grouping I add an item to the hashtable where the value in ColA is the Key, and the Value is an array of strings from ColB. For example, the first item in the hashtable has a Key of S1, and a Value of User1,User2,User3. There is an item like that for each unique value in ColA.
Once I have the mailboxes, and the users that have access to those mailboxes organized and grouped up I move on to trying to batch them. I start with Batch 1, so I set a variable to that.
$i=1
Next I loop through each $Box (i.e. each key in the hashtable). This is the outer loop that I will call the Mailbox Loop.
ForEach($Box in $AccessTo.Keys){
Within the Mailbox Loop I have an inner loop that iterates through each $User associated with that mailbox. I'll call that the User Loop.
ForEach($User in $AccessTo[$Box]){
Using the sample data again the first mailbox is S1, and the users associated with that are User1,User2, and User3. So within the inner loop I start by checking if the current user already has a batch assigned to them. If they do, I continue to the next user.
If(($FileIn.ColB|?{$_ -eq $User}|Select -First 1).Batch){Continue}
Then I look at each item in the hashtable, and if the current $User is in the list of users for any given mailbox I output all users for that mailbox. That gets piped to a Select command that gets me only unique values, and is captured in $BatchUsers.
$BatchUsers = $AccessTo.Keys|?{$AccessTo[$_] -contains $User -or $_ -eq $User}|%{$AccessTo[$_]}|Select -Unique
At this point we have a good basis of users that belong in the batch. Now I just need to make sure that those users aren't in other groups that don't include the current $User, and if they are I need to iterate the users from those additional groups too, and I need to keep doing that until I stop getting more users. I'll break this down with formatting that's a little easier to read:
Do{
For($x=0;$x -lt $BatchUsers.Count;$x++){
$AccessTo.Keys|
?{$AccessTo[$_] -contains $BatchUsers[$x]}|
%{$AccessTo[$_]}|
?{$_ -notin $BatchUsers}|
%{$BatchUsers+=$_}
}
}Until($x -eq $BatchUsers.count)
I start with a Do/Until loop that will run itself until $BatchUsers.Count equals the number of users that we looked at the last iteration of the loop (so it loops until it does not find additional users). Inside that loop I run a For loop that starts at $x=0, and loops until $x equals the current value of $BatchUsers.Count. I specify "the current value" because we add things to $BatchUsers inside the loop.
For each iteration of the loop we look at all of the items in the $AccessTo hashtable again
For($x=0;$x -lt $BatchUsers.Count;$x++){
$AccessTo.Keys|
We check if the $xth item in $BatchUsers is in that item of the hashtable.
?{$AccessTo[$_] -contains $BatchUsers[$x]}|
If it is we expand all the users in that item of the hashtable.
%{$AccessTo[$_]}|
Then filter out the ones that are already in $BatchUsers.
?{$_ -notin $BatchUsers}|
For each one that isn't already in $BatchUsers we add it to $BatchUsers.
%{$BatchUsers+=$_}
After the Do/Until loop is done looking for users we go to our original data stashed in $FileIn. We iterate through that, looking at each record to see if the value in ColB is in $BatchUsers, and make sure that it doesn't already have a batch assigned.
$FileIn | ?{$_.ColB -in $BatchUsers -and !$_.Batch}
We pipe those records to a ForEach-Object loop, and for each of them we add a property named Batch, with a value of $i (which we were using to keep track of the current batch)
$FileIn | ?{$_.ColB -in $BatchUsers -and !$_.Batch}|%{Add-Member -InputObject $_ -NotePropertyName 'Batch' -NotePropertyValue $i}
Now that everybody in the current batch has a Batch value, we set $i to be one higher than the highest batch number. I originally just iterated $i to be one more for each cycle of the Mailbox Loop, but there were multiple boxes included in one batch, so I ended up with Batch 1, and Batch 4, which doesn't make a lot of sense to me. So, yeah, set the next batch to be one higher than the last batch:
$i=($FileIn.batch|sort|select -last 1)+1
That's it. If you have specific questions please feel free to ask.