How can I export each OU to separate .CSV files

This topic contains 5 replies, has 2 voices, and was last updated by Profile photo of touchy2k touchy2k 3 years, 6 months ago.

  • Author
    Posts
  • #11870
    Profile photo of touchy2k
    touchy2k
    Participant

    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"

  • #11873
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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.

  • #11906
    Profile photo of touchy2k
    touchy2k
    Participant

    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

  • #11907
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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.

  • #11916
    Profile photo of touchy2k
    touchy2k
    Participant

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

  • #11919
    Profile photo of touchy2k
    touchy2k
    Participant

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

You must be logged in to reply to this topic.