Author Posts

December 9, 2013 at 9:30 am

How can I export each OU to separate .CSV/xls files? I would like each file to be named the following example. OU=Service Accounts,OU=Corp Objects,DC=corp,DC=domain,DC=com. This means Each file will be will take the name of its' own distinctive OU. How can I go about accomplishing this? This is the PowerShell Script that I am using:

Script-;

Import-Module ActiveDirectory

# This array will hold the report output.
$report = @()

# Hide the errors for a couple duplicate hash table keys.
$schemaIDGUID = @{}
$ErrorActionPreference = 'SilentlyContinue'
Get-ADObject -SearchBase (Get-ADRootDSE).schemaNamingContext -LDAPFilter '(schemaIDGUID=*)' -Properties name, schemaIDGUID |
ForEach-Object {$schemaIDGUID.add([System.GUID]$_.schemaIDGUID,$_.name)}
Get-ADObject -SearchBase "CN=Extended-Rights,$((Get-ADRootDSE).configurationNamingContext)" -LDAPFilter '(objectClass=controlAccessRight)' -Properties name, rightsGUID |
ForEach-Object {$schemaIDGUID.add([System.GUID]$_.rightsGUID,$_.name)}
$ErrorActionPreference = 'Continue'

# Get a list of all OUs. Add in the root containers for good measure (users, computers, etc.).
$OUs = Get-ADOrganizationalUnit -Filter * | Select-Object -ExpandProperty DistinguishedName
$OUs += Get-ADObject -SearchBase (Get-ADDomain).DistinguishedName -SearchScope OneLevel -LDAPFilter '(objectClass=container)' | Select-Object -ExpandProperty DistinguishedName

# Loop through each of the OUs and retrieve their permissions.
# Add report columns to contain the OU path and string names of the ObjectTypes.
ForEach ($OU in $OUs) {
$report += Get-Acl -Path "AD:\$OU" |
Select-Object -ExpandProperty Access |
Select-Object @{name='organizationalUnit';expression={$OU}}, `
@{name='objectTypeName';expression={if ($_.objectType.ToString() -eq '00000000-0000-0000-0000-000000000000') {'All'} Else {$schemaIDGUID.Item($_.objectType)}}}, `
@{name='inheritedObjectTypeName';expression={$schemaIDGUID.Item($_.inheritedObjectType)}}, `
*
}

# Dump the raw report out to a CSV file for analysis in Excel.
$report | Export-Csv ".\OU_Permissions.csv" -NoTypeInformation
Start-Process ".\OU_Permissions.csv"

December 9, 2013 at 10:09 am

You just need to move your call to Export-Csv inside the loop, instead of generating one giant array of results and dumping it into a single CSV. For example:

# Loop through each of the OUs and retrieve their permissions.
# Add report columns to contain the OU path and string names of the ObjectTypes.

$properties = @(
    @{name='organizationalUnit';expression={$OU}}
    @{name='objectTypeName';expression={if ($_.objectType.ToString() -eq '00000000-0000-0000-0000-000000000000') {'All'} Else {$schemaIDGUID.Item($_.objectType)}}}
    @{name='inheritedObjectTypeName';expression={$schemaIDGUID.Item($_.inheritedObjectType)}}
    '*'
)

ForEach ($OU in $OUs) {
    Get-Acl -Path "AD:\$OU" |
    Select-Object -ExpandProperty Access |
    Select-Object -Property $properties |
    Export-Csv -Path ".\$OU.csv" -NoTypeInformation
}

You may run into situations where the distinguished name of an OU contains characters that aren't legal in Windows file names; if so, you'll have to add a little bit of extra string manipulation code to create your filename.

December 10, 2013 at 7:49 am

Dave: That works very well. Is there a way to also have just one single column with this script? What do I need to change? I don't meed to individual columns in the Excel spreadsheet.

Touchy2k

December 10, 2013 at 7:53 am

Sure, but which column do you want to keep? All you have to do is modify the $properties array so it only contains one element.

December 10, 2013 at 9:41 am

All I need to show is whether the permission is unique to the OU, or if it's being inherited.

December 10, 2013 at 10:32 am

Do you think it would be better if use dsacl to would be something like:
foreach-object {dsacls $_ |ft identityreference, accesscontroltype -autosize > c:\$_.csv}