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

This topic contains 30 replies, has 3 voices, and was last updated by Profile photo of Vandrey Trindade Vandrey Trindade 4 months, 3 weeks ago.

  • Author
    Posts
  • #46264
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    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

  • #46273
    Profile photo of Dan Potter
    Dan Potter
    Participant

    $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 🙂

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

  • #46281
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    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

  • #46290
    Profile photo of Dan Potter
    Dan Potter
    Participant
    
    #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
    	}
    	
    }
    
    
    
    • #46294
      Profile photo of Vandrey Trindade
      Vandrey Trindade
      Participant

      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?

  • #46292
    Profile photo of Dan Potter
    Dan Potter
    Participant

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

  • #46296
    Profile photo of Dan Potter
    Dan Potter
    Participant

    Making me go look for an exchange server costs money 🙂

    $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
    
  • #46310
    Profile photo of Dan Potter
    Dan Potter
    Participant

    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.

  • #46312
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    I'm using Exchange Online... It worked for me =p

  • #46314
    Profile photo of Dan Potter
    Dan Potter
    Participant

    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.

  • #46318
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    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...

  • #46330
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    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...

  • #46332
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    
    • This reply was modified 5 months ago by Profile photo of Rob Simmers Rob Simmers.
  • #46352
    Profile photo of Dan Potter
    Dan Potter
    Participant

    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.

  • #46466
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    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...

  • #46470
    Profile photo of Dan Potter
    Dan Potter
    Participant

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

  • #46472
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    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/
    [url=https://postimg.org/image/hm9r4odhd/][img]https://s32.postimg.org/hm9r4odhd/Image1.png[/img][/url]
    [url=https://postimg.org/image/6b73gb6m9/][img]https://s32.postimg.org/6b73gb6m9/Image2.png[/img][/url]

  • #46475
    Profile photo of Dan Potter
    Dan Potter
    Participant

    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
    
    
  • #46477
    Profile photo of Dan Potter
    Dan Potter
    Participant

    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 "

  • #46482
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    Dan,

    Tried to add that to the $Body variable and the mail sent was a mess =/

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

    Actually I feel ashamed of asking that kind of things here... Someone may tell me: "search for the solution, stop asking"
    I'll try to read more about PowerShell HTML formatting... Don't get it why it throws that numbers on the table...

  • #46485
    Profile photo of Dan Potter
    Dan Potter
    Participant

    select first one. did you properly add the html breaks?

  • #46487
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    Dan,

    Here are some prints...
    First one – code
    Second one – mail received
    http://postimg.org/gallery/26a0fjcxk/

  • #46489
    Profile photo of Dan Potter
    Dan Potter
    Participant

    copy the header html stuff from here, halfway down the page.

    https://technet.microsoft.com/en-us/library/ff730936.aspx?f=255&MSPPError=-2147217396

  • #46493
    Profile photo of Vandrey Trindade
    Vandrey Trindade
    Participant

    Dan,

    Using like this:

    $results |select -first 2 | ConvertTo-Html -head $a | Out-File D:\Temp\Test.html
    

    I was able to get it inside a table with the headers (DG and Members) and the data correctly filled.
    I think that the problem of not getting the members on each separated line is the type of the variable $Results.Members inside the PSCustomObject... Will try to not set it as [string].

    [Update] Tried that... Removing this line:

    $String = $Members -join "; "
    

    When I try to convert it to HTML, it shows an * for the DG field and a number for the members field.
    When I try to convert each single line to string, it returns "System.Object[]"

    • #46501
      Profile photo of Vandrey Trindade
      Vandrey Trindade
      Participant

      I was looking for a PowerShell HTML output with multiple lines value and didn't find any...
      I suppose that it isn't supported then, maybe that's why it throws that numbers instead =/

  • #46510
    Profile photo of Dan Potter
    Dan Potter
    Participant

    What you are trying to do or what I think you are trying to do doesn't make sense. If you want the name of the group and then the members on a second line it doesn't make sense to even create a table in the first place nor convert it to html.

    $services = get-service | ConvertTo-Html -Body Listofservices
    $services | out-file services.html
    .\services.html

    • This reply was modified 5 months ago by Profile photo of Dan Potter Dan Potter.
  • #46540
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    The format you are asking for is possible in HTML, you can use nested tables. I used the code posted last time and took the results and sent it to a function to build a report. This example is dumping it to an HTML file (you'll need to update the path) and will open it in your default browser so you can see what the rendered HTML looks like. Keep in mind if you are sending this as an email, you may need to adjust and use inline CSS to get it to render the same as a browser.

    • This reply was modified 5 months ago by Profile photo of Rob Simmers Rob Simmers.
    • #46955
      Profile photo of Vandrey Trindade
      Vandrey Trindade
      Participant

      Rob Simmers,

      Sorry for taking so long to reply. I was busy with another thing at work...
      I've tried like this:

      $DistributionGroup = Get-DistributionGroup -ResultSize Unlimited | sort DisplayName
      
      $DistributionGroup = $DistributionGroup | select -First 2
      
      $Results = foreach ($DG in $DistributionGroup) {
      	
      	$Members = Get-DistributionGroupMember $DG.DisplayName | ForEach-Object{
      		if ($_.RecipientType -like "*MailContact*") { $_.Name } else { $_.DisplayName }
      	}
      	
      	$String = $Members -join "; "
      		
      		 [PSCustomObject]@{
      			DistributionGroup = $DG.DisplayName
                  Members = $String
      	}
      
      
      	
      }
      
      $Results
      
      
      #YOUR CODE HERE
      #It didn't fit =/
      
      
      $htmlReport = Out-MyReport -obj $results
      $htmlReport | Out-File "C:\Reports\Testreport.html"
      ii "C:\Reports\Testreport.html"
      

      The results were this:

      https://postimg.org/image/c9ld9q3w1/

    • #46963
      Profile photo of Vandrey Trindade
      Vandrey Trindade
      Participant

      FINALLY!

      I got this working using a tip from Dan Potter (don't know if this is the best way of doing it...).
      I will sent as an image because the forum removes the HTML code part.

      https://postimg.org/image/4jajlgag1/

      The $css code I've got from this EqualLogic report: https://virtualisedreality.com/2011/01/18/equallogic-configuration-reporting-powershell-script/

      Thanks for Dan Potter and Rob Simmers for your time and attention!

You must be logged in to reply to this topic.