Script to export Distribution list tree to CSV or Excel

spiceuser-lvtva · 2020-02-03T10:21:02.000Z

Im trying to export a recursive list of Distribution List members (including groups) in a particular format.

Ive found a few sample scripts to export recursively but im struggling with getting these into a format I am after… They usually just give me all members in a single flat list.
What im hoping to do is present the list in “levels”. For example, say the top level DL column1, then everything it contains is column2, then everything the Column2 DL Contains in column3.
Ultimately I want to use Excel to create a pivot table of the info to show the DL structure in a collapsible tree format.
Ive attached a sample xls of what im hoping to achieve via powershell.
Im thinking i need to somehow fill out an array, with a variable for each level… but I just cant seem to get my head around the logic.
Any thoughts around how I might achieve this?

Sample_Export.xlsx (15.1 KB)

DoctorDNS · 2020-02-03T11:54:16.000Z

Hi and Welcome to Spiceworks - thanks for your first post here. Take a look at this: PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! which describes how we work here! I hope this is helpful.

Importing and exporting from Excel can be done using a CSV file. The CSV file is essentially a table of objects, preceded by a header to name each column. In the CSV, each row after the first represents an object occurrences properties. If you have a simple object, then exporting to CSV just gives you a row with the values as in the original object. WIth Complex objects, is ones where a property of the object is actually an object that has properties (which can be objects that have properties). An obvious example is (Get-Process | Select -skip 1 -First 1). Modules - Each process object has a Modules property. That property is an array of objects each with properties. This makes it more complex to build your excel spreadsheet. The problem is that when PowerShell takes an object array (ie what you get back from Get-ADGroup) it can not export the objects in the format you want.

SO, you can do this, but it is more wok. What you have to do is to run through the nested groups and build the relevant objects with the relevant properties via a set of next for each. It could certainly be done. You would need to Get the top level lists, and for each member spit out the users at that level, then get the level 1 groups. For each of those, spit out the users at that level and the Level 2 groups. FOr each of those, spit out the users at that level plus the level three groups. And for each of them spit out the users at level 4.

You can start by getting the objects at the top level:

$ArrayOfUsers = @()   # you ad to this
Function Add-User { <adds the user to the array}  # fill in the blanks
$TopLevel = Get-AdObject -Filter * -SearchScope OneLevel -SearchBase 'ou=cookhamhq,dc=cookham,dc=net'
Foreach ($Obj in $TopLevel) 
  If (! (object is a user or a group) continue # skip over this
  If <object is user>  {add-uuser $obj -Level 1}     # add this user to the array of user
  Else  {   #here to process a level 1 group
    $Level2objes = Get-Adobject -Filter * =searchscope ONeLevel -Searchbase <work it out from $obj> 
    Foreach (L2obj in $Level2objs)
     -skip over non group/users
     =add a L2 user> 
     <process L2 groups>
    etc

I hope this gets you started.

I haven’t looked closely enough, but you might be able to get some help from the ImportExcel module which you can download from the PS Gallery. See PowerShell Gallery | ImportExcel 5.4.2

Have a go, post what you come up with for more help.

furicle · 2020-02-03T14:08:06.000Z

CSV is a 2D output.

You could try export and import from JSON, which handles the ‘nesting’ for you.

Carrying on with the example above, look at the output of

 (Get-Process | select -skip 1 -first 1) | ConvertTo-Json

spiceuser-lvtva · 2020-02-04T08:51:21.000Z

Hi Guys,

Thanks so much for the input… I think I may have gotten close to what I was trying to achieve.

Its crude, with no error handling and a a large number of nested foreach statements… but it gets me what I want in an array, up to 9 levels deep.

The challenge I have now, is when I export-csv on the array, each entry comes up as a System.Object

Dont suppose you would have any ideas why that would happen?

$Level0DL = "DL TO EXPORT"
$Level1DL = (Get-DistributionGroupMember $Level0DL | Where-Object {$_.RecipientType -notmatch 'UserMailbox'} | Select Name)
$levels = "level0","level1","level2","level3","level4","level5","level6","level7","level8","level9"
$DLTable =@()
$DLTable = "" | select $levels

    foreach ($DL1 in $Level1DL){
        $Level1Table =@()
        $Level1Table = "" | select $levels
        [array]$Level1Table.level0 = $level0DL
        [array]$Level1Table.level1 = $DL1.Name
        [array]$DLTable += $Level1Table
        $Level2DL = (Get-DistributionGroupMember $DL1.Name | Where-Object {$_.RecipientType -notmatch 'UserMailbox'} | Select Name)
            foreach ($DL2 in $Level2DL){
                $Level2Table =@()
                $Level2Table = "" | select $levels
                [array]$Level2Table.level0 = $level0DL
                [array]$Level2Table.level1 = $DL1.Name
                [array]$Level2Table.level2 = $DL2.Name
                [array]$DLTable += $Level2Table
                $Level3DL = (Get-DistributionGroupMember $DL2.Name | Where-Object {$_.RecipientType -notmatch 'UserMailbox'} | Select Name)
                    foreach ($DL3 in $Level3DL){
                        $Level3Table =@()
                        $Level3Table = "" | select $levels
                        [array]$Level3Table.level0 = $level0DL
                        [array]$Level3Table.level1 = $DL1.Name
                        [array]$Level3Table.level2 = $DL2.Name
                        [array]$Level3Table.level3 = $DL3.Name
                        [array]$DLTable += $Level3Table
                        $Level4DL = (Get-DistributionGroupMember $DL3.Name | Where-Object {$_.RecipientType -notmatch 'UserMailbox'}| Select Name)
                            foreach ($DL4 in $Level4DL){
                                $Level4Table =@()
                                $Level4Table = "" | select $levels
                                [array]$Level4Table.level0 = $level0DL
                                [array]$Level4Table.level1 = $DL1.Name
                                [array]$Level4Table.level2 = $DL2.Name
                                [array]$Level4Table.level3 = $DL3.Name
                                [array]$Level4Table.level4 = $DL4.Name
                                [array]$DLTable += $Level4Table
                                $Level5DL = (Get-DistributionGroupMember $DL4.Name | Where-Object {$_.RecipientType -notmatch 'UserMailbox'}| Select Name)
                                    foreach ($DL5 in $Level5DL){
                                        $Level5Table =@()
                                        $Level5Table = "" | select $levels
                                        [array]$Level5Table.level0 = $level0DL
                                        [array]$Level5Table.level1 = $DL1.Name
                                        [array]$Level5Table.level2 = $DL2.Name
                                        [array]$Level5Table.level3 = $DL3.Name
                                        [array]$Level5Table.level4 = $DL4.Name
                                        [array]$Level5Table.level5 = $DL5.Name
                                        [array]$DLTable += $Level5Table
                                        $Level6DL = (Get-DistributionGroupMember $DL5.Name | Where-Object {$_.RecipientType -notmatch 'UserMailbox'}| Select Name)
                                            foreach ($DL6 in $Level6DL){
                                                $Level6Table =@()
                                                $Level6Table = "" | select $levels
                                                [array]$Level6Table.level0 = $level0DL
                                                [array]$Level6Table.level1 = $DL1.Name
                                                [array]$Level6Table.level2 = $DL2.Name
                                                [array]$Level6Table.level3 = $DL3.Name
                                                [array]$Level6Table.level4 = $DL4.Name
                                                [array]$Level6Table.level5 = $DL5.Name
                                                [array]$Level6Table.level6 = $DL6.Name
                                                [array]$DLTable += $Level6Table
                                                $Level7DL = (Get-DistributionGroupMember $DL6.Name | Where-Object {$_.RecipientType -notmatch 'UserMailbox'}| Select Name)
                                                    foreach ($DL7 in $Level7DL){
                                                        $Level7Table =@()
                                                        $Level7Table = "" | select $levels
                                                        [array]$Level7Table.level0 = $level0DL
                                                        [array]$Level7Table.level1 = $DL1.Name
                                                        [array]$Level7Table.level2 = $DL2.Name
                                                        [array]$Level7Table.level3 = $DL3.Name
                                                        [array]$Level7Table.level4 = $DL4.Name
                                                        [array]$Level7Table.level5 = $DL5.Name
                                                        [array]$Level7Table.level6 = $DL6.Name
                                                        [array]$Level7Table.level7 = $DL7.Name
                                                        [array]$DLTable += $Level7Table
                                                        $Level8DL = (Get-DistributionGroupMember $DL7.Name | Where-Object {$_.RecipientType -notmatch 'UserMailbox'}| Select Name)
                                                            foreach ($DL8 in $Level8DL){
                                                                $Level8Table =@()
                                                                $Level8Table = "" | select $levels
                                                                [array]$Level8Table.level0 = $level0DL
                                                                [array]$Level8Table.level1 = $DL1.Name
                                                                [array]$Level8Table.level2 = $DL2.Name
                                                                [array]$Level8Table.level3 = $DL3.Name
                                                                [array]$Level8Table.level4 = $DL4.Name
                                                                [array]$Level8Table.level5 = $DL5.Name
                                                                [array]$Level8Table.level6 = $DL6.Name
                                                                [array]$Level8Table.level7 = $DL7.Name
                                                                [array]$Level8Table.level8 = $DL8.Name
                                                                [array]$DLTable += $Level8Table
                                                                $Level9DL = (Get-DistributionGroupMember $DL8.Name | Where-Object {$_.RecipientType -notmatch 'UserMailbox'}| Select Name)
                                                                    foreach ($DL9 in $Level9DL){
                                                                        $Level9Table =@()
                                                                        $Level9Table = "" | select $levels
                                                                        [array]$Level9Table.level0 = $level0DL
                                                                        [array]$Level9Table.level1 = $DL1.Name
                                                                        [array]$Level9Table.level2 = $DL2.Name
                                                                        [array]$Level9Table.level3 = $DL3.Name
                                                                        [array]$Level9Table.level4 = $DL4.Name
                                                                        [array]$Level9Table.level5 = $DL5.Name
                                                                        [array]$Level9Table.level6 = $DL6.Name
                                                                        [array]$Level9Table.level7 = $DL7.Name
                                                                        [array]$Level9Table.level8 = $DL8.Name
                                                                        [array]$Level9Table.level9 = $DL9.Name
                                                                        [array]$DLTable += $Level9Table
                                                                        
                                                                        }
                                                                }
                                                        }
                                                }
                                        }
                                }
                        }
                }
        }

        $DLTable |Export-Csv C:\Script\DLTree.csv

DoctorDNS · 2020-02-05T11:18:51.000Z

Glad you got to an answer.

Please ensure you mark helpful and best answers - thanks!