locked
Export AD structure to CSV with OU breakdown RRS feed

  • Question

  • Hello,

    I'm just learning PS and I've been able to export our AD structure into a CSV, but I need to format it into a structure management will understand.

    Output now looks like this:

    OU=Special_Security,OU=Admins,OU=Infrastructure,OU=HQ,OU=Corporate,OU=Users,DC=Domain,DC=com
    
    OU=Admins,OU=Infrastructure,OU=HQ,OU=Corporate,OU=Users,DC=Domain,DC=com
    
    OU=HelpDesk,OU=HQ,OU=Corporate,OU=Users,DC=Domain,DC=com 
    
    OU=Finance,OU=HQ,OU=Corporate,OU=Users,DC=Domain,DC=com 
    
    etc.



    I would like it to format like this:

    Domain FIELD2 OU OU OU OU OU User Count
    Domain . Com Users 0
    Domain . Com Users Corporate HQ 5
    Domain . Com Users Corporate HQ Finance 12
    Domain . Com Users Corporate HQ IT 6
    Domain . Com Users Corporate HQ IT HelpDesk 8
    Domain . Com Users Corporate HQ IT Infrastructure 3

    I know it will probably require scripting against the CSV file rather than trying to format the output directly from AD, but I really don't know where to start.

    Background:

    There are over 150 top level OUs plus their sub-OUs so reformatting by hand will take forever. We are trying to clean up the mess that 3 former "Subject Matter Experts" created by attempting to restructure AD. (Each with their own "best way to do it")

    I need to make regular progress reports and I think a visible comparison of what is there now will have more impact than just "I'm 10% done", "I'm 25% done", etc.

    Many Thanks!
    • Edited by dot19408 Wednesday, March 7, 2018 2:22 PM say thank you young man!
    Wednesday, March 7, 2018 2:21 PM

Answers

  • If u wanna clean-up empty OUs, have User count on each OU, i would suggest this way

    <snip>

    Ah.. u need to start from domain name.. then

    Get-ADOrganizationalUnit -Filter * -properties canonicalname |select canonicalname

    U can export the list into CSV, then use Delimiter "/" to get your "breakdowns"

    OOOOHHH
    Thank you!

    so short and elegant...

    The end goal is to clean-up empty OUs, and re-organize AD, but I have to make the current spaghetti monster mess understandable to upper management in a 10 minute presentation...


    • Marked as answer by dot19408 Wednesday, March 7, 2018 7:25 PM
    Wednesday, March 7, 2018 4:10 PM
  • Or this:

    Get-ADOrganizationalUnit  -Properties canonicalname -Filter *|
        ForEach-Object{
            [pscustomobject]@{
                OUName = Split-Path $_.CanonicalName -Leaf
                CanonicalName = $_.CanonicalName
                UserCount = (Get-AdUser -Filter * -SearchBase $_.DistinguishedName -SearchScope OneLevel).Count
            }
        }
    


    \_(ツ)_/

    • Marked as answer by dot19408 Wednesday, March 7, 2018 7:26 PM
    Wednesday, March 7, 2018 5:37 PM

All replies

  • Where is the script you are using? You are able to query AD, then pass over to the Select-Object cmdlet where you can take care of all formatting needs, before passing over to Export-Csv to create your csv file.

    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful. (99,108,97,121,109,97,110,50,64,110,121,99,97,112,46,114,114,46,99,111,109|%{[char]$_})-join''

    Wednesday, March 7, 2018 2:34 PM
  • If u wanna clean-up empty OUs, have User count on each OU, i would suggest this way

    1. get user list via get-aduser   
    2. extract OU from their distinguishedname
    3. group distinguishedname to get count of users on each OU

    get-aduser -filter * -SearchBase "myOU" |
     select name,@{n="ou";e={$_.distinguishedname -replace '^.+?,(CN|OU.+)’,’$1'}} |
     group ou

    Ah.. u need to start from domain name.. then

    Get-ADOrganizationalUnit -Filter * -properties canonicalname |select canonicalname
    U can export the list into CSV, then use Delimiter "/" to get your "breakdowns"


    • Edited by Mekac Wednesday, March 7, 2018 3:17 PM
    Wednesday, March 7, 2018 3:04 PM
  • My command is:

    Get-ADOrganizationalUnit -filter * | select DistinguishedName | Export-csv -path d:\ADOUExport.csv -NoTypeInformation

    I'm not sure (read, I got no idea) how I would tell Select-Object to:

    READ:

    OU=Security,OU=IT,OU=HQ,OU=Corp Users,OU=ABC Users,DC=Company,DC=com
    
    Then reverse the order and seperate the OUs to:
    Company.com | ABC Users | Corp Users | HQ | IT | Security

    Wednesday, March 7, 2018 4:02 PM
  • You can use the CanonicalName to get the simple structure and split it on the separator.  The CanonicalName is already reversed in order.

    $path_elements = $_.CanonicalName -split '/'


    \_(ツ)_/

    Wednesday, March 7, 2018 4:10 PM
  • If u wanna clean-up empty OUs, have User count on each OU, i would suggest this way

    <snip>

    Ah.. u need to start from domain name.. then

    Get-ADOrganizationalUnit -Filter * -properties canonicalname |select canonicalname

    U can export the list into CSV, then use Delimiter "/" to get your "breakdowns"

    OOOOHHH
    Thank you!

    so short and elegant...

    The end goal is to clean-up empty OUs, and re-organize AD, but I have to make the current spaghetti monster mess understandable to upper management in a 10 minute presentation...


    • Marked as answer by dot19408 Wednesday, March 7, 2018 7:25 PM
    Wednesday, March 7, 2018 4:10 PM
  • Let me see if I understand what's happening...

    So "canonicalname" is a property of an OU object.

    Get-ADOrganizationalUnit -Filter *

    gets spit out like so:

    City                     :
    Country                  :
    DistinguishedName        : OU=Users,OU=Corp,DC=Company,DC=Com
    LinkedGroupPolicyObjects : {}
    ManagedBy                :
    Name                     : Corp
    ObjectClass              : organizationalUnit
    ObjectGUID               : aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
    PostalCode               :
    State                    :
    StreetAddress            :

    -Filter * is just "Give me everything"

    -Properties canonicalname means "I want that property too"

    CanonicalName            : Company.com/Corp
    City                     :
    Country                  :
    DistinguishedName        : OU=Corp,DC=Company,DC=com
    LinkedGroupPolicyObjects : {}
    ManagedBy                :
    Name                     : Corp
    ObjectClass              : organizationalUnit
    ObjectGUID               : b8bb5377-6d54-477d-aac2-95a66312c360
    PostalCode               :
    State                    :
    StreetAddress            :

    Then piping that to Select canonicalname means "Just show me that"

    Am I getting this right?

    If it's OK to ask, How would I then get the user and /or group count and add it to the output CSV at the end?

    I found this function on Stackoverflow

    $ous = Get-ADOrganizationalUnit -Filter * -SearchBase "ou=Users,ou=CMSG,dc=contoso,dc=com" | Select-Object -ExpandProperty DistinguishedName
    $ous | ForEach-Object{
        [psobject][ordered]@{
            OU = $_
            Count = (Get-ADUser -Filter * -SearchBase "$_").count
        }
    }

    
    

    and it outputs the DistinguishedName of the OUs:

    OU                             OU=People,DC=diversifiedCS,DC=local                                                                                                                                      
    Count                          291

    Can I add the property selection of canonacalname be added in there somehow? 



    • Edited by dot19408 Wednesday, March 7, 2018 5:25 PM cleanup
    Wednesday, March 7, 2018 5:22 PM
  • Get-ADOrganizationalUnit  -Properties canonicalname -Filter *|select Canonicalname

    \_(ツ)_/


    • Edited by jrv Wednesday, March 7, 2018 5:30 PM
    Wednesday, March 7, 2018 5:30 PM
  • Get-ADOrganizationalUnit  -Properties canonicalname -Filter *|
        ForEach-Object{
            [pscustomobject]@{
                CanonicalName = $_.CanonicalName
                UserCount = (Get-AdUser -Filter * -SearchBase $_.DistinguishedName -SearchScope OneLevel).Count
            }
        }


    \_(ツ)_/

    Wednesday, March 7, 2018 5:36 PM
  • Or this:

    Get-ADOrganizationalUnit  -Properties canonicalname -Filter *|
        ForEach-Object{
            [pscustomobject]@{
                OUName = Split-Path $_.CanonicalName -Leaf
                CanonicalName = $_.CanonicalName
                UserCount = (Get-AdUser -Filter * -SearchBase $_.DistinguishedName -SearchScope OneLevel).Count
            }
        }
    


    \_(ツ)_/

    • Marked as answer by dot19408 Wednesday, March 7, 2018 7:26 PM
    Wednesday, March 7, 2018 5:37 PM
  • Get-ADOrganizationalUnit  -Properties canonicalname -Filter *|
        ForEach-Object{
            [pscustomobject]@{
                CanonicalName = $_.CanonicalName
                UserCount = (Get-AdUser -Filter * -SearchBase $_.DistinguishedName -SearchScope OneLevel).Count
            }
        }


    \_(ツ)_/

    Thank you jrv !

    Just making sure I understand how this is working. (let me know where I'm wrong)

    Get-ADOrganizationalUnit  -Properties canonicalname -Filter *

    Get the properties of all OU objects and include the "canonicalname"

     ForEach-Object{
            [pscustomobject]@{
                CanonicalName = $_.CanonicalName

    For each OU object, create a custom object with all the standard properties and tack on the CanonicalName
    (please correct me if I've got the thinking wrong here)

     UserCount = (Get-AdUser -Filter * -SearchBase $_.DistinguishedName -SearchScope OneLevel).Count
            }
        }

    For each of the new custom objects, run Get-ADUser against its DistinguishedName.

    Search only the current level of the OU and return the count of user objects there.

    Wednesday, March 7, 2018 6:13 PM
  • Thanks again!

    I'm learnin' Ma!

    From what I'm seeing, the difference is just the addition of 

    OUName = Split-Path $_.CanonicalName -Leaf

    Comparing the output, it adds a column with just the current OU name to the output.

    OUName = Split-Path

    Splits each section of the object path into it's own piece.

    OUName = Split-Path $_.CanonicalName

    I'm guessing this is telling Split-Path to use the CanonicalName and -Leaf is the current end part of the path.

    Please let me know if I've got something wrong.

    MANY Thanks to all of you!

    Wednesday, March 7, 2018 6:26 PM
  • Use help to read what the CmdLets do and how they are used.


    \_(ツ)_/

    Wednesday, March 7, 2018 7:05 PM
  • Thank you very much for your help,

    I'm reading them and practicing, but I have trouble understanding without working examples. Things seem to stick better when I see something in action. Dissecting working examples and using HELP to see what each part does Is working OK too I think.

    I'm hip deep in The Scripting Guy blog too, but I still have trouble following the explanations on complex functions and scripts.

    I've found a few YouTube videos where some guy explains what happens at each step. I can comprehend that immediately, but there were only a few and they were very basic.

    I'm thinking of recording some of the scripts I'm working on and doing the same. Walking through it and explaining it fixes it permanently in my brain.

    Well, as permanent  as anything can be stuck in there...

    Wednesday, March 7, 2018 8:28 PM
  • Drop everything and do this tutorial first.  Do all examples and exercises.  You will come out as a very well trained code.

    https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276


    \_(ツ)_/

    Wednesday, March 7, 2018 8:54 PM