Hi everyone,

What i'm trying to do is create a CSV file that compares two groups of objects - one an AD group, the other a Dynamic distribution group in Exchange.  I want to get a column heading with the AD or Exchange group, and a listing of who is in each of those groups below it.  I can then make sure that the group members all tally up.

I'm having troubles exporting a hash table to a CSV though, the CSV either comes out looking like:
#TYPE System.Collections.Hashtable
"IsReadOnly","IsFixedSize","IsSynchronized","Keys","Values","SyncRoot","Count"
"False","False","False","System.Collections.Hashtable+KeyCollection","System.Collections.Hashtable+ValueCollection","System.Object","148"

Or like this if I do a Select -expand values function before exporting to CSV.
#TYPE System.Object[]
"Count","Length","LongLength","Rank","SyncRoot","IsReadOnly","IsFixedSize","IsSynchronized"
"29","29","29","1","System.Object[]","False","True","False"
"8","8","8","1","System.Object[]","False","True","False"
"55","55","55","1","System.Object[]","False","True","False"
,,,,,,,
"12","12","12","1","System.Object[]","False","True","False"

Here's the code anyway, hope someone can assist - I've heard a custom object might solve my issue but am at my wits end trying to get it working!

$DistroList = @{}

$QueryDG | ForEach-Object {
        $DistroGroupDN = ("CN=" + $_.'GroupName' + ",OU=Targets,OU=Diego Independents,DC=Replaced,DC=com")
        $ADGroup = $_.'Groupname'
        $EXDistroGroup = $_.'Groupname'.Replace("Stores ","Stores - ")

        $ADGroupMembers = Get-QADGroupMember -Identity $DistroGroupDN | select-object Name
        $EXDistroSearch = Get-DynamicDistributionGroup -Identity $EXDistroGroup
        $EXDistroMembers = get-recipient -filter $EXDistroSearch.recipientfilter | Select-object Name
      
        #$DistroList.Add($ADGroup, $ADGRoupMembers)
        #$DistroList.Add($EXDistroGroup, $ExDistroMembers)
        $DistroList[$ADGroup] = $ADGroupMembers
        $DistroList[$EXDistroGroup] = $ExDistroMembers
}

Thanks,
Josh.

Yeah, I think using a custom object is the way to go here. I haven't tested, but try this (insert after your existing code):

$DistroList.GetEnumerator() | ForEach-Object { New-Object PSObject -Property @{ Group = $_.Key; Members = $_.Value } | Select-Object -Property Group, Members } | Export-CSV -Path "file.csv" -NoTypeInformation

Hi again GW, thanks for your help.. This is the output of the CSV file:

"Group","Members"
"Stores - Non-Bannered by Warehouse TN","System.Object[]"
"Stores - Bannered by Warehouse WA","System.Object[]"

We only seem to be getting one System.Object[] for each row, rather than all returned values. And I suppose the values aren't coming through, just what looks like the member attributes.

Doing a $DistroList gives me the following output (just a sample of it)
Name Value
---- -----
Stores - Non-Bannered by Wa... {@{Name=XXX Associate 1520}, @{Name=YYY Associate 1532}, @{Name=GGG Associate 1538}, @{Name=ZZZ Associate 1587}...}
Stores - Bannered by Wareho... {@{Name=PPP Diego 1900}, @{Name=HHH Diego 1902}, @{Name=BBB Diego 1903}, @{Name=AAA Diego 1905}...}

Thanks again,
Josh.

Hmmm yes I had a feeling that might happen. What is the output you are expecting?

Well, I was hoping on just the names of the people in the groups.. Something like:
"Stores - Non-Bannered by Warehouse TN", "Stores - Bannered by Warehouse WA"
"XXX Associate 1520","PPP Diego 1900"
"YYY Associate 1532","HHH Diego 1902"
"GGG Associate 1538","BBB Diego 1903"
,"AAA Diego 1905"

Or simplisticly,
Groupname1,Groupname2
member1,member5
member2,member6
member3,

etc..

I've managed to take out the System.object part of the export-csv now with the following command, but it's probably not the correct way to do it. 

$obj = ""
$obj = New-Object PSObject
$Obj | Add-Member NoteProperty -Name $ADGroup -Value ([string](($ADGroupMembers | %{$_.Name})))


This guy is after the same thing as I am, but in my case I'll need to increment the $Obj by one for each Group, and increment the Value by one for each member.
http://social.technet.microsoft.com...7ce40b6c15

Thanks again,
Josh.

To be honest, after reading your post again, what you're trying to do here doesn't really make sense. The columns in a CSV or any data structure that models itself after a database table is supposed to define the structure of a row. This would be the correct way of exporting this data to a CSV:

function New-Member
{
    param (
        [String]$Group,
        [String]$Member
    )
   
    return New-Object PSObject -Property @{
        Group = $Group
        Member = $Member
    }
}

$allMembers = @()

$ADGroupMembers | ForEach-Object { $allMembers += New-Member -Group $ADGroup -Member $_ }
$ExDistroMembers | ForEach-Object { $allMembers += New-Member -Group $ExDistroGroup -Member $_ }

$allMembers | Export-CSV -Path "file.csv" -NoTypeInformation

Could you elaborate a little more on what you mean by "tallying up" the members? Maybe you're going about this the wrong way.

Hi,

Your right - this has all got a bit out of control, so I might start from the top again.  I have a list of users within an AD Group (ADGroup1).  I also have an Exchange group (EXGroup1).  I'm trying to compare the members of ADGroup1 with the members of EXGroup1. 

I have approx. 30 or so of these groups that I need to compare.  ADGroup1 members to EXGroup1 members.  ADGroup2 members to EXGroup2 members. 

With the code you've just given me I think we're on the right track, but instead of having one member listed besides one group, I would like the column heading to be the Group, and have the members of that group listed below it. 

This is the output of the CSV at the moment:
"Member","Group"
"DG1595-GRP","Stores Bannered ALL"
"DG1591-GRP","Stores Bannered ALL"
"DG0159-GRP","Stores Bannered ALL"
"DG1589-GRP","Stores Bannered ALL"

I would love to have either:
ADGroup1,EXGroup1,ADGroup2,EXGroup2,ADGroup3,EXGroup3
member1,member1,member1,member1,member1,member1
member2,member2,member2,member2,member2,member2
member3,member3,member3,member3,member3,member3
member4,member4,member4,member4,member4,member4
member5,member5,member5,member5,member5,member5
member6,,member6,,,member6
member7,,,,,member7
member8,,,,,member8
member9,,,,,

Or if it would be simpler, the group name in the left most column, then the members off to the right.

Hope that clears up what I'm hoping for, and thanks for all your help again..

Josh

Extremely ugly and ungeneric, but I think its something to start with:

function New-Row
{
    param (
        [String]$ADGroup1Member,
        [String]$ExGroup1Member,
        [String]$ADGroup2Member,
        [String]$ExGroup2Member
    )
   
    return New-Object PSObject -Property @{
        ADGroup1 = $ADGroup1Member
        ExGroup1 = $ExGroup1Member
        ADGroup2 = $ADGroup2Member
        ExGroup2 = $ExGroup2Member
    }
}

$longest = 0

$ADGroup1Members, $ExGroup1Members, $ADGroup2Members, $ExGroup2Members | ForEach-Object {
    if ($longest -lt $_.Length) { $longest = $_.Length }
}

$rows = @()

for ($i = 0; $i -lt $longest; $i++)
{
    if ($i -lt $ADGroup1Members.Length) { $ADGroup1Member = $ADGroup1Members[$i] } else { $ADGroup1Member = $null }
    if ($i -lt $ExGroup1Members.Length) { $ExGroup1Member = $ExGroup1Members[$i] } else { $ExGroup1Member = $null }
    if ($i -lt $ADGroup2Members.Length) { $ADGroup2Member = $ADGroup2Members[$i] } else { $ADGroup2Member = $null }
    if ($i -lt $ExGroup2Members.Length) { $ExGroup2Member = $ExGroup2Members[$i] } else { $ExGroup2Member = $null }
   
    $rows += New-Row -ADGroup1Member $ADGroup1Member -ExGroup1Member $ExGroup1Member -ADGroup2Member $ADGroup2Member -ExGroup2Member $ExGroup2Member
}

$rows | Export-CSV -Path "file.csv" -NoTypeInformation

Better way, but haven't tested:

function New-Template
{
    param (
        [System.Collections.HashTable]$HashTable
    )
   
    $obj = New-Object PSObject
   
    $HashTable.Keys | ForEach-Object { $obj | Add-Member -MemberType NoteProperty -Name $_ -Value $null }
   
    return $obj
}

function ExportHashTo-CSV
{
    param (
        [System.Collections.HashTable]$HashTable
    )
   
    $longest = $HashTable.Values | Sort-Object -Property Length -Descending | Select-Object -ExpandProperty Length -First 1
   
    $rows = @()
   
    for ($i = 0; $i -lt $longest; $i++)
    {
        $template = New-Template -HashTable $HashTable
       
        $HashTable.GetEnumerator() | ForEach-Object {
            if ($_.Value.Length -lt $longest)
            {       
                $template."$($_.Key)" = $_.Value[$i]
            }
        }
       
        $rows += $template
    }
   
    $rows | Export-CSV -Path "file.csv" -NoTypeInformation
}

ExportHashTo-CSV -HashTable $DistroList

Hi,

Sorry about the delay in getting back to you - the task got put on the back burner for a little while trying to figure this out.  If I'm honest I got a little bit confused about where all of my existing code needs to go inside of your code, and with the amount of changes I'd made (and subsequently not got right) I thought I'd try a different approach. 

As you said earlier, each row should have a key, and values after it, so I thought I'd make the Group name the key, and the members of that group seperate values beside that key.  Then combine all of the values and export.

$ObjArray = ""

$QueryDG | ForEach-Object {
        $DistroGroupDN = ("CN=" + $_.'GroupName' + ",OU=Targets,OU=Diego Independents,DC=aur,DC=com,DC=au")
        $ADGroup = $_.'Groupname'
        $EXGroup = $_.'Groupname'.Replace("Stores ","Stores - ")

        $tmpObject = New-Object PSObject
        $tmpObject | Add-Member -MemberType NoteProperty -Name "Name" -Value $EXGroup

        #$ADGroupMembers = Get-QADGroupMember -Identity $DistroGroupDN
        $EXDistroSearch = Get-DynamicDistributionGroup -Identity $EXGroup
        $EXGroupMembers = get-recipient -filter $EXDistroSearch.recipientfilter | select-object $_.Displayname
       
        $Member = 1
       
        $EXGroupMembers | foreach-object {
           
            $TmpObject |  Add-Member NoteProperty -Name ($Member++) -Value ($_.DisplayName)
       
            }       
       
        $objArray += $tmpObject
       
        }


$TmpObject now gives me the following if outputted to the console, and export-csv even works off the back of it.

Name : Stores - Bannered by State NSW 8 Page
1    : LAKE ALBERT Diego 1551
2    : WAGGA WAGGA Diego 1583
3    : EDEN Diego 2033
4    : GLOUCESTER Diego 2137
5    : MARRICKVILLE Diego 2141

Export-csv
#TYPE System.Management.Automation.PSCustomObject
"Name","1","2","3","4","5"
"Stores - Bannered by State NSW 8 Page","LAKE ALBERT Diego 1551","WAGGA WAGGA Diego 1583","EDEN Diego 2033","GLOUCESTER Diego 2137","MARRICKVILLE Diego 2141"


$objarray on the other hand, gives me
@{Name=Stores - Bannered by State NSW 8 Page; 1=LAKE ALBERT Diego 1551; 2=WAGGA WAGGA Diego 1583; 3=EDEN Diego 2033; 4=GLOUCESTER Diego 2137; 5=MARRICKVILLE Diego 214
1

Export-csv:
#TYPE System.String
"Length"
"2348"

When I'm pushing $tmpobject through to $objarray, is this changing the properties of the fields to something not outputtable by export-csv ?

Thanks again,

Hi All,

I've got it going finally.  GW, big thanks for putting me on the right path!  The reason the above code wasn't working is because of those first few lines of code -
ObjArray = ""

Doing that was turning the ObjArray into a System.String object, where as I needed it to be a customobject.  Shows what a mislabelled variable can do! 

So all I really needed to do was change that into an array to hold all of my TmpObjects, and it now rolls through quite nicely.  Exports to CSV, etc seems to be fine as well.  I've still got to plug in the AD lookup, but so far this will give you an exportable CSV of the Dynamic distribution groups on Exchange:

#Setup an Array to hold all the goodies
$ObjArray = @()

#For each Distriubution Group get a list of the members
$QueryDG | ForEach-Object {
        $DistroGroupDN = ("CN=" + $_.'GroupName' + ",OU=Targets,OU=Diego Independents,DC=bob,DC=com")
        #$ADGroup = $_.'Groupname'
        $EXGroup = $_.'Groupname'.Replace("Stores ","Stores - ")
       
        #Store the name of the group in $TmpObject
        $tmpObject = New-Object PSObject
        $tmpObject | Add-Member -MemberType NoteProperty -Name "Name" -Value $EXGroup
       
        #$ADGroupMembers = Get-QADGroupMember -Identity $DistroGroupDN
        $EXDistroSearch = Get-DynamicDistributionGroup -Identity $EXGroup
        $EXGroupMembers = get-recipient -filter $EXDistroSearch.recipientfilter | select-object $_.Displayname
       
        #Setup our value incrementer
        $Member = 1
               
        $EXGroupMembers | foreach-object {
           
            #Add each member of the Dynamic distribution group to it's own numbered column
            $TmpObject |  Add-Member NoteProperty -Name ($Member++) -Value ($_.DisplayName)
                   
            }       
       
        #Output the $TmpObject to an array
        $objArray += $tmpObject
       
        }
       
$ObjArray | Export-csv Test.csv

Thanks again,
Mitsu.