Gather information of distribution groups and format them to show in HTML

Hi,

I’m trying to gather all distribution groups (Office 365) in my organization and then add their members.
Example of what I need as result:

List: List - 1
Members: {John Doe, Alan Wake,etc}

List: List - Abc
Members: {Shakira, Madonna}

Then I should have a table with all my distribution groups and their members to convert it to an HTML file.

What I was trying:

$DistributionGroup = Get-DistributionGroup -ResultSize Unlimited | sort DisplayName
foreach ($DG in $DistributionGroup){
$Results += [PSCustomObject]@{
   DG = $DG.DisplayName
   Members = $Members | ForEach-Object{
      if ($_.RecipientType -like "*MailContact*"){
         $_.Name
         } else {
            $_.DisplayName
            }
         }
      }
   }

Obviously it isn’t working =/
This is the first step… Then I’ll try to convert it to HTML =p

$members doesn’t reference anything, we need the full script. You’ll have to show us where you are joining the members into a single string. The object only holds one property. Try not scripting inside the psobject, it can be confusing for beginners.

+= is annoying :slight_smile:

$results = foreach ($DG in $DistributionGroup){
[PSCustomObject]@{…}}

Sorry,

Forgot that line =/

$DistributionGroup = Get-DistributionGroup -ResultSize Unlimited | sort DisplayName
foreach ($DG in $DistributionGroup){
$Members = Get-DistributionGroupMember $DG
   $Results = [PSCustomObject]@{
      DG = $DG.DisplayName
      Members = $Members | ForEach-Object{
         if ($_.RecipientType -like "*MailContact*"){
            $_.Name
            } else {
               $_.DisplayName
               }
         }
      }
   }

Using that with only one DG (removing the first two lines of the script and setting $DG), when I type $Results.DGS and $Results.Members it shows what I want… Want to add them all in a single variable to be able to export it…

$DG = Get-DistributionGroup support
$Members = Get-DistributionGroupMember $DG.DisplayName
   $Results = [PSCustomObject]@{
      DG = $DG.DisplayName
      Members = $Members | ForEach-Object{
         if ($_.RecipientType -like "*MailContact*"){
            $_.Name
            } else {
               $_.DisplayName
               }
         }
      }

Image with the results

#when troubleshooting add this  | select -first 1

$DistributionGroup = Get-DistributionGroup -ResultSize Unlimited | sort DisplayName | select -first 1

foreach ($DG in $DistributionGroup) {
	
	$Members = Get-DistributionGroupMember $DG | ForEach-Object{
		if ($_.RecipientType -like "*MailContact*") { $_.Name } else { $_.DisplayName }
	}
	
	$string = $members -join '; '
		
		$Results = [PSCustomObject]@{
			DG = $DG.DisplayName
			Members = $string
	}
	
}


You could even place the $results = in front of the first foreach.

Dan Potter,

Thanks! But still it doesn’t work for more than 1 DG.
It only keep the “last” DG processed…
How to keep them all inside $Results?

Making me go look for an exchange server costs money :slight_smile:

$DistributionGroup = Get-DistributionGroup -ResultSize Unlimited | sort DisplayName | select -first 1

$Results = foreach ($DG in $DistributionGroup) {
	
	$Members = Get-DistributionGroupMember $DG | ForEach-Object{
		if ($_.RecipientType -like "*MailContact*") { $_.Name } else { $_.DisplayName }
	}
	
	$string = $members -join '; '
		
		 [PSCustomObject]@{
			DG = $DG.DisplayName
			Members = $string
	}


	
}


$results

=p

Worked perfectly! Many thanks!

NP!

btw, I was wrong in the |select -first 1 The warning for exchange management shell threw me off course. Seems there has been no development for it since 2007!

|select -first 1 works great in plain PS when enumerating a lot of objects but slower than a prius in EMS.

I’m using Exchange Online… It worked for me =p

I could have done that as well but I’m too lazy to save the connection string =D Maybe next year when I use it again.

Lol

Ok… That was the first part =p

Now I’m trying to send an e-mail with that info, using this:

$Body = ""

foreach ($item in $Results) {
  $Body+="$($item.DG)"
  $Body+= $item.Members |
  Convertto-Html -Fragment -As Table
}

Send-MailMessage -SmtpServer "server" -From "teste@mail.com" -To "mymail@organization.com" -Subject "DGs" -Body $Body -BodyAsHtml -Encoding UTF8

It returns:

DG Name
*
109

¬¬

If I remove the “| Convertto-Html -Fragment -As Table” it shows the names like you formatted them to me “name; name2; name 3; name 4”
In a DG with a lot of members looks awful to read it…
Tried using “$Body+= $item.Members.Split(”;“)” but didn’t worked too…

Hi again,

That’s was the “first” part =p
Now I’m trying to send it by mail using this:

$Body = ""
foreach ($item in $Results) {
  $Body+="$($item.DG)"
  $Body+= $item.Members |
  Convertto-Html -Fragment -As Table
}

Send-MailMessage -SmtpServer "server" -From "abcdatmail.com" -To "abcdeatmail.com" -Subject "DGs -Body $Body -BodyAsHtml

But it returns something like this:

DG name
*
109

¬¬

When I remove the “| Convertto-Html -Fragment -As Table” it returns the DG name and all the members like “name; name1; name2; name3”. It looks awful to read when a DG has a lot of members
Tried to use “$Body+= $item.Members.Split(”;“)” but didn’t worked too…

Using Select -First 1 doesn’t really buy you a lot using it like that. You are still getting ALL of the Distribution Groups and then selecting the first 1. You should always filter as far left as possible, so using the -Filter or -Identity to select a smaller resultset to test your code. I use Select -First, but I’ll normally pull a large resultset once, keep it in memory and then remark it out, so something like this:

#$DistributionGroup = Get-DistributionGroup -ResultSize Unlimited | sort DisplayName
$DistributionGroup = $DistributionGroup | Select -First 3

Also, tested this as another possible solution:

$DistributionGroup = Get-DistributionGroup -ResultSize Unlimited | sort DisplayName | select -first 1

$results = foreach ($DG in $DistributionGroup) {
	Get-DistributionGroupMember $DG.Identity | Select Name, DisplayName, Id, @{Name="DistributionGroup";Expression={$DG}}
}

$results

Edit: To send as HTML, try something like this…

$resultsHTML = $results | ConvertTo-HTML -As Table

$mailParams = @{
    To = "abcdeatmail.com"
    From = "abcdatmail.com"
    Subject = "Report:  Distribution Group"
    Body = $resultsHTML
    BodyAsHtml = $true
    SmtpServer = "server"
}

Send-MailMessage @mailParams

Right, I merely use it as a troubleshooting option instead of rewriting the query twice.

In the shell I would do $DistributionGroup = $DistributionGroup | select -first 1 then copy and paste the foreach. Just a quick time saver.

Hi again,

That’s was the “first” part =p
Now I’m trying to send it by mail using this:

$Body = ""
foreach ($item in $Results) {
  $Body+="$($item.DG)"
  $Body+= $item.Members |
  Convertto-Html -Fragment -As Table
}

Send-MailMessage -SmtpServer "server" -From "abcdatmail.com" -To "abcdeatmail.com" -Subject "DGs -Body $Body -BodyAsHtml

But it returns something like this:

DG name
*
109

¬¬

When I remove the “| Convertto-Html -Fragment -As Table” it returns the DG name and all the members like “name; name1; name2; name3”. It looks awful to read when a DG has a lot of members
Tried to use “$Body+= $item.Members.Split(”;“)” but didn’t worked too…

$body = $results |converto-html |out-string

Hi Dan,

It sends the e-mail just like we see the $Results on PowerShell.
I wanted to change the mode it is displayed.

foreach ($item in $Results) {
  $Body+="$($item.DG)"
  $Body+= $item.Members
}

In this way I get the DG DisplayName in bold and bigger size than regular.
Then I get the members, but they are all in on line. I wanted one line for each member…

Images of the results: http://postimg.org/gallery/lwvl77du/

Try this and see what you get.

$a = ""
		$a = $a + "BODY{background-color:white;FONT-SIZE: 8pt; FONT-FAMILY: verdana}"
		$a = $a + "TABLE{width: 975px;border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
		$a = $a + "TH{width: 975px;border-width: 1px;padding: 3px;border-style: solid;border-color: black;color: Green;background-color:#CCCCCC}"
		$a = $a + "TD{width: 975px;border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color:white}"
		$a = $a + ""

$results |select -first 1 | ConvertTo-Html -head $a -body "$item.DG " | out-string

Well, this forum strips out html code so… look up formatting html headers powershell.

This part has html breaks after the $item.dg ConvertTo-Html -head $a -body "$item.DG lessthan br greaterthan "