AD Security Group matrix

‘Sup PSHomies,

So last blog was about adding members to a security group efficiently. Which got me thinking, can I reverse this process? If given the security groups with specified members, can I recreate the csv? I do love me a challenge!

So to pick up where we left off, I’ll be using the $addADGroupMembers to repopulate the csv…

#Security Matrix
$Groups = $addADGroupMembers.Keys
function Convert-ArrayToHash($a){
    begin { $hash = @{} }
    process { $hash[$_] = $null }
    end { return $hash }
}

$template = [PSCustomObject]([Ordered]@{UserID=$null} + $($Groups | Convert-ArrayToHash))

$addADGroupMembers has all the group names we need. I’m converting the group names into an empty hashtable. The $template variable is a custom object I’ll be using to move things  along, I’ll explain as we go…

Now for the tricky part…

$arrMatrix = @()

$Groups |
ForEach-Object{
   $GroupName = $_
   if($addADGroupMembers.$_){
      $addADGroupMembers.$_ |
      ForEach-Object{
         if($arrMatrix.Count -eq 0) {
            $newItem = $template.PSObject.Copy()
            $newItem.UserID = $_
            $newItem.$GroupName = '1'
            $arrMatrix += $newItem
         }
         else{
            if($arrMatrix.UserID.contains($($_))){
               $index = [array]::IndexOf($arrMatrix.UserID, $_)
               $arrMatrix[$index].$GroupName = '1'
            }
            else{
               $newItem = $template.PSObject.Copy()
               $newItem.UserID = $_
               $newItem.$GroupName = '1'
               $arrMatrix += $newItem
            }
         }
      }
   }
}

First we have an array to save the results. We only need to worry about groups with members. The $template has been initialized with $null. The first time it runs, $arrMatrix.Count will be zero, so just add this group to get things started. Here’s where it gets interesting, in order to add a newItem to the array I have to clone it first. Adding and saving this way makes sure I have a row with a unique UserID. Truth be told I had to google to figure this one out. Modifying $template and then assigning it to $newItem will only assign the reference. Change the value once more and every item in the array changes! I read it somewhere, it was fun to stumble on this… The more you know…

The next trick was to find the index of a UserID already saved. Google to the rescue! I found this neat trick of using [array]::IndexOf(). This will give you the first index with that value. Lucky for me, my UserIDs are unique 😉
Once I have my index I can add a value of ‘1’ to the group if the UserID is a member. If I can’t find a UserID then a new unique UserID is added to the $arrMatrix

Ok enough chit-chat here’s some code to play with

	#region Create GroupMember Hashtable
	$csvMatrix = @"
	UserID APP-MS Excel APP-MS Outlook APP-MS Powerpoint APP-MS Visio Viewer APP-MS Word APP-Adobe Reader
	ejboogers 1 1 1 1 1
	dlbouchlaghmi 1 1 1 1 1 1
	sideroij 1 1 1 1 1
	barnhoorn 1 1 1 1
	bofechter 1 1 1 1
	asschonewille 1 1 1 1
	rrchouiter 1 1 1 1
	mgragt 1 1 1 1 1
	tpggrimbergen 1 1 1
	bvanderhassel 1 1 1 1 1 1
	jvderwilk 1 1 1 1 1
	cdvanderheijden 1 1 1 1 1 1
	thvjanssen 1 1 1 1
	skalac-ivor 1 1 1 1 1
	nvanderspeklap 1 1 1 1
	mkrunder 1 1 1 1
	nkoelewijk 1 1 1 1 1
	jlekkerkernij 1 1 1 1
	"@ | ConvertFrom-Csv -Delimiter "`t"
	$csvMatrix |
	Sort-Object -Property UserID |
	Format-Table
	$Header = $csvMatrix |
	Get-Member -MemberType NoteProperty |
	Where-Object{$_.Name -ne 'UserID'} |
	Select-Object -ExpandProperty Name
	$addADGroupMembers = @{}
	$delADGroupMembers = @{}
	$Header |
	ForEach-Object{
	$Group = $_
	$addADGroupMembers.$Group = $csvMatrix.Where{$_.$Group -eq '1'} | Select-Object -ExpandProperty 'UserID'
	$delADGroupMembers.$Group = $csvMatrix.Where{$_.$Group -ne '1'} | Select-Object -ExpandProperty 'UserID'
	}
	#endregion
	#region Security Matrix
	$Groups = $addADGroupMembers.Keys
	function Convert-ArrayToHash($a){
	begin { $hash = @{} }
	process { $hash[$_] = $null }
	end { return $hash }
	}
	$template = [PSCustomObject]([Ordered]@{UserID=$null} + $($Groups | Convert-ArrayToHash))
	$arrMatrix = @()
	$Groups |
	ForEach-Object{
	$GroupName = $_
	if($addADGroupMembers.$_){
	$addADGroupMembers.$_ |
	ForEach-Object{
	if($arrMatrix.Count -eq 0) {
	$newItem = $template.PSObject.Copy()
	$newItem.UserID = $_
	$newItem.$GroupName = '1'
	$arrMatrix += $newItem
	}
	else{
	if($arrMatrix.UserID.contains($($_))){
	$index = [array]::IndexOf($arrMatrix.UserID, $_)
	$arrMatrix[$index].$GroupName = '1'
	}
	else{
	$newItem = $template.PSObject.Copy()
	$newItem.UserID = $_
	$newItem.$GroupName = '1'
	$arrMatrix += $newItem
	}
	}
	}
	}
	}
	$arrMatrix |
	Sort-Object -Property UserID |
	Select-Object 'UserID', 'APP-MS Excel','APP-MS OutLook','APP-MS Powerpoint','APP-MS Visio Viewer','APP-MS Word','APP-Adobe Reader' |
	Format-Table
	#endregion

view raw Demo-SecurityMatrix.ps1 hosted with ❤ by GitHub

This should be your endresult

No too shabby eh? 😛

Ok Urv that’s all good and well but when am I going to use this?

Why thank you for asking!

If you’ve ever been in charge of implementing Role Based Access Control then you could appreciate this. A security matrix like this is where I’d start, only now you don’t have to start from scratch… 😉

Here’s how it works…

I created a security group Rol-Consultant for RBAC purposes. This group is a member of all the APP-* groups giving any member access by way of group nesting. Users who are a member of Rol-Consultant don’t have to be a direct member for access. The down side of RBAC is it’s all or nothing, exceptions are real deal breakers…

I did a blog about reporting a user’s nested group membership. Let take user ‘dlbouchlaghmi’. This is what his effective user group membership looks like in list form

The security matrix makes it a bit more visual. Granted, it takes some getting use to but the information is there. Now you can ‘fix’ any issues and reapply the way you see fit! 😉

This has been on my radar for quite some time. Processing security groups this way, makes scripting, I wouldn’t say easier, but more easier to manipulate if you catch my drift…

Ok here’s the code to get the ADSecuirtyMatrix. Do be careful with groups with large memberships. I tried my hand at a group with more than 3800 member, took a couple of minutes, but it worked.

	#Security Matrix
	#Specify your security groups. try small groups first
	$Groups = @(
	'APP-MS Outlook','APP-Adobe Reader','APP-MS Word',
	'APP-MS Powerpoint','APP-MS Excel','APP-MS Visio Viewer','Rol-Consultant'
	)
	function Convert-ArrayToHash($a){
	begin { $hash = @{} }
	process { $hash[$_] = $null }
	end { return $hash }
	}
	$template = [PSCustomObject]([Ordered]@{UserID=$null} + $($Groups | Convert-ArrayToHash))
	$arrMatrix = @()
	#Get current Group memberships
	$SnapshotADGroupMembers = @{}
	$Groups |
	ForEach-Object{
	$SnapshotADGroupMembers.$($_) = Get-ADGroupMember -Identity $_ | Select-Object -ExpandProperty SamAccountName
	}
	$Groups |
	ForEach-Object{
	$GroupName = $_
	if($SnapshotADGroupMembers.$_){
	$SnapshotADGroupMembers.$_ |
	ForEach-Object{
	if($arrMatrix.Count -eq 0) {
	$newItem = $template.PSObject.Copy()
	$newItem.UserID = $_
	$newItem.$GroupName = '1'
	$arrMatrix += $newItem
	}
	else{
	if($arrMatrix.UserID.contains($($_))){
	$index = [array]::IndexOf($arrMatrix.UserID, $_)
	$arrMatrix[$index].$GroupName = '1'
	}
	else{
	$newItem = $template.PSObject.Copy()
	$newItem.UserID = $_
	$newItem.$GroupName = '1'
	$arrMatrix += $newItem
	}
	}
	}
	}
	}
	$arrMatrix |
	Select-Object 'UserID', 'APP-MS Outlook','APP-Adobe Reader','APP-MS Word',
	'APP-MS Powerpoint','APP-MS Excel','APP-MS Visio Viewer','Rol-Consultant' |
	Out-GridView

view raw Get-ADSecurityMatrix.ps1 hosted with ❤ by GitHub

I got one more use to go… Some Operation validation! Stay tuned…

Hope it’s worth something to you…

Ttyl,

Urv

Add Members to Group – a different approach

‘Sup PSHomies,

My Project Manager is slowly becoming a  true PowerShell believer! Of course he doesn’t have to do the actual scripting, that’s where I come in… 😉

So in walks the PM…

PM: “Say Urv, if I gave you an excel worksheet with the user/group relationship, think you’d be able to script it?”

Me:”Gee, this is so sudden, let me think about it and get back to you asap… (Grinning)”

Here’s an impression of the worksheet:

I’ve done this in past by just going through each user adding each group. I remember reading a post by Mike F. Robbins on how this could be done more efficiently! I very much like this approach. I’ve added a lil’ extra to the mix. Let’s dig in!

Well for starters I’ll just get the source directly from the excel file using D. Finke’s ImportExcel Module. No need to convert to CSV first.

$xlsxADGroupMembers = Import-Excel .\source\xlsx\$xlsxFile -WorkSheetname $WorkSheet

The other thing was retrieving the GroupNames from PSCustomObject by selecting MemberType ‘NoteProperty’, I got this as a tip on my own blog by Dirk. This way you’re not depending on the position of where the group names start in the header.

#Select Group names from Object
$Header = $xlsxADGroupMembers |
   Get-Member -MemberType NoteProperty |
   Where-Object{$_.Name -ne  'UserID'} |
   Select-Object -ExpandProperty Name

This makes it just a bit resilient.

I also decided to use hashtable to store the results first before processing the group membership. This way I can also export the results for future reference (Always keep a log)

#Create empty hashtables
$addADGroupMembers = @{}
$delADGroupMembers = @{}

#Get Group membership
$Header |
ForEach-Object{
   $Group = $_
   $addADGroupMembers.$Group  = $xlsxADGroupMembers.Where{$_.$Group -eq '1'} | Select-Object -ExpandProperty 'UserID'
   $delADGroupMembers.$Group = $xlsxADGroupMembers.Where{$_.$Group -ne '1'} | Select-Object -ExpandProperty 'UserID'
}

Now it’s time to add the members to specified groups

$Header |
ForEach-Object{
   if($addADGroupMembers.$_){
      try{
         Add-ADGroupMember -Identity $_ -Members $addADGroupMembers.$_
      }
      catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
         Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"
      }
   }

   if($delADGroupMembers.$_){
      try{
         Remove-ADGroupMember -Identity $_ -Members $delADGroupMembers.$_ -Confirm:$false
      }
      catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
         Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"
      }
   }
}

I’m using Try/Catch to catch any errors on AD Objects not existing. Without it you’d get quite a few errors if the AD objects don’t exists.

Here’s the full script:

	<#
	Author: I. Strachan
	Version:
	Version History:
	Purpose: Add group member from excel worksheet. Scripts is based on Mike Robin's work
	http://tinyurl.com/jfmw4o7
	#>
	[CmdletBinding()]
	param(
	$xlsxFile = 'Demo-Kruisjes.xlsx',
	$WorkSheet = 'Demo'
	)
	#region
	$exportDate = Get-Date -Format ddMMyyyy
	#endregion
	#Import Worksheet Using D. Finke's ImportExcel module http://tinyurl.com/lbhkhbd
	$xlsxADGroupMembers = Import-Excel .\source\xlsx\$xlsxFile -WorkSheetname $WorkSheet
	#Select Group names from Object
	$Header = $xlsxADGroupMembers |
	Get-Member -MemberType NoteProperty |
	Where-Object{$_.Name -ne 'UserID'} |
	Select-Object -ExpandProperty Name
	#Create empty hashtables
	$addADGroupMembers = @{}
	$delADGroupMembers = @{}
	#Get Group membership
	$Header |
	ForEach-Object{
	$Group = $_
	$addADGroupMembers.$Group = $xlsxADGroupMembers.Where{$_.$Group -eq '1'} | Select-Object -ExpandProperty 'UserID'
	$delADGroupMembers.$Group = $xlsxADGroupMembers.Where{$_.$Group -ne '1'} | Select-Object -ExpandProperty 'UserID'
	}
	#region Main. Add and remove users to/from groups
	$Header |
	ForEach-Object{
	if($addADGroupMembers.$_){
	try{
	Add-ADGroupMember -Identity $_ -Members $addADGroupMembers.$_
	}
	catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
	Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"
	}
	}
	if($delADGroupMembers.$_){
	try{
	Remove-ADGroupMember -Identity $_ -Members $delADGroupMembers.$_ -Confirm:$false
	}
	catch [Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException]{
	Write-Warning "AD Object $($Error[0].CategoryInfo.TargetName) not found"
	}
	}
	}
	#endregion
	#region Export for futher processing
	$GroupMembers =@{
	Groups = $Header | Get-ADGroup -Properties WhenCreated
	Added = $addADGroupMembers
	Revoked = $delADGroupMembers
	}
	$GroupMembers |
	Export-Clixml .\export\dsa\ADGroupMembers-$exportDate.xml -Encoding UTF8
	#endregion

view raw Add-MemberToADGroup.ps1 hosted with ❤ by GitHub

I like the fact that you can use the list to add, but also delete if it isn’t necessary. Of course if you fill it in wrong, then there is no fixing that. So be warned!

Another thing is that members can be added manually or another process, so don’t be surprised, when you’re evaulating the group membership….

Tip: Save the  GroupMembership just to be sure…

#Get current Group memberships
$SnapshotADGroupMembers = @{}

$Header |
ForEach-Object{
   $SnapshotADGroupMembers.$($_) = Get-ADGroupMember -Identity $_ | Select-Object -ExpandProperty SamAccountName
}

Next time I’ll blog about how we could to this in reverse… Stay tuned!

Hope it’s worth something to you,

Ttyl,

Urv