Generate an HTML Report for Exchange Distribution Group Members

Summary: Use Windows PowerShell to generate an HTML report for Exchange Server 2012 Distribution groups.

Microsoft Scripting Guy, Ed Wilson, is here. Today, I would like to introduce a new guest blogger: Serkan Varoğlu.

Serkan Varoğlu is a Windows PowerShell enthusiast and a Microsoft Exchange Server fanatic. He describes himself as: “A regular IT pro who loves sharing what he learns with the community.” He blogs about Microsoft Exchange Server in Turkish on his personal blog (http://www.get-mailbox.org) and tweets in English @SRKNVRGL. Feel free to contact him.

Creating Exchange reports

I always hated exporting data to create manually reports. I am sure that I am not the only one out there who dislikes spending a long time getting a report. As a consultant, I also know that if a client does not have enough budget to buy nice reporting software, the only thing I can do is help supply them with manually created reports. One of the most requested reports is to get distribution group members for certain highly nested groups, and let’s face it, Microsoft Exchange Server does not have a built-in reporting tool even though it is the best messaging platform out there. So, I thought I should write a Windows PowerShell script to help clients with this task.

I wrote the Report-DistributionGroupMember PowerShell script, which outputs a nice HTML report. You can find it in TechNet Script Repository under Exchange 2010 Category.

I wanted to provide the following data in the report:

• If a distribution group has another distribution group as a member, then which group is a member of which group?
• Any Empty Groups in the membership.
• Duplicate Recipients that come from different group memberships.
• And recipient list that shows all unique members that will receive an email if sent to this distribution group.

I was thinking of going into a single loop to get everything, but this made things a lot more complicated. Instead, I chose to write a function that will collect all the group names in members, and then go into each group and search other group memberships until I get all the group names that are nested under the parent group.

With this function I added data to two hash tables: $NestedGroup and $NestedGroups. The main reason for this is $NestedGroup will get overwritten in the loop, but $NestedGroups will keep the data, which I will set as global at the end. This means after running the script, I can still access the data in the same session, and also, if I want more information than is shown in the report, I can access it afterwards.

function collect($DistGroupMembers)

{

$NestedGroup = @{}

if ($DistGroupMembers)

{   

foreach ($member in $DistGroupMembers)

{

if(($member.RecipientTypeDetails -like “*Group*”) -and ($member.RecipientTypeDetails -notlike “*Dynamic*”))

{

$name = $member.SamAccountName.ToString()

if ($NestedGroup.ContainsKey($name) -eq $false)

{

$NestedGroup.Add($name,$member.DisplayName.ToString())

$NestedGroups.Add($name,$DistGroup.Name.ToString())

}

}   

}

}

if($NestedGroup.Values.Count -gt 0)

{

foreach ($NestedGroup in $NestedGroup.values)

{

$DistGroup = Get-DistributionGroup $NestedGroup

$Nest=Get-DistributionGroupMember $NestedGroup

collect $Nest

}

}

$global:NestedGroups=$NestedGroups

}

There is a downside of this function. If somehow the distribution group has more than one membership to the parent group (said group is member of several other child groups), the script will not be able to add it to the $NestedGroups hashtable. But, hey, I don’t want the same group twice anyway, so this is actually good for me. I’m still on the right track. In addition, I used ErroractionPrefence to silently continue in case of an error for this Windows PowerShell script, so the user running this command will not see the red error text for this event.

As I have my function ready, I now need the name of the parent distribution group. For that, I depend on user input and then use the input as a parameter in this function.

param ( [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true,HelpMessage=’Distribution Group Name’)][string]$Name)

$DistGroup = $ADistGroup = Get-DistributionGroup $Name -ErrorAction “silentlycontinue”

if(!$DistGroup)

{

“Could not retrieve any information from your input. Please make sure Distribution Group Name is correct.”

exit

}

$DistGroupMembers = Get-DistributionGroupMember $DistGroup -Resultsize Unlimited

collect $DistGroupMembers

 

Now, I need to get SamAccountName of each group in $NestedGroups and append that information to each member of $NestedGroups.  Also, I need to find and record whether the individual group is empty.

foreach ($DistGroup in $NestedGroups.keys)

{

$DistGroup = Get-DistributionGroup $DistGroup

$AllDistGroups += $DistGroup

$srknvrgl=Get-DistributionGroupMember $DistGroup -Resultsize Unlimited

if ($srknvrgl)

{

            foreach ($srkn in $srknvrgl)

            {

            $srkn | Add-Member -type NoteProperty -name GroupName -value $DistGroup.SamAccountName

            $srkn | foreach {$MemberCount[$_.SamAccountName] += 1}

            }

}

else

{

$EmptyGroup+=$DistGroup

}

$AllMembers+=$srknvrgl

 

At this point I have:

• Hashtable that holds all the Distribution Group Names and their Parent Group Name as Value. ($NestedGroups)
• Empty Groups. ($EmptyGroup)
• Another hashtable that holds the count of each member directly or indirectly member of the parent group. ($MemberCount)
• And also All Members ($AllMembers). But one downside: In $AllMembers, I also have distribution groups if they are in second-level membership. I need to get rid of them while reporting.

I also want to report any duplicate recipients in this report, so I need to check $AllMembers for each key in $MemberCount that has a value greater than 1. If it is greater than 1, I have the same recipient more than once.

foreach ($DuplicateMember in $MemberCount.keys)

{

            if ($MemberCount.$DuplicateMember -gt 1)

            {

            $DuplicateMemberGroup+=$AllMembers | ?{$_.SamAccountName -like $DuplicateMember}

            }

}

 

Done. I have all the information that I need now. It is time to prepare the report.

Writing HTML is pretty simple. I used only one variable ($Output) to keep all the HTML data as I already had all the information I needed. I added to this variable as I go along. All I had to do was populate it so HTML will not lose style. I am not going to explain all HTML code in this Windows PowerShell script because it is more than 180 lines, but I will try to show you how I easily set the styles. I used if-else and foreach in HTML tables. If you take a close look at the code, you will see that most of it is just styling.

In summary, what I have done is simple; I created tables with HTML and used foreach to populate each row from the data I collected before. If the variable has any data and if it has the value I am looking for, I set the style to the style I choose. If not, I will set it to the general style. Here is an example from the script:

Note For each group, I am checking MemberJoinRestriction information. If the value is “Open,” background of the cell will be Green. If “Approval” is needed, it will be Orange. In any other case, there won’t be any background color on this cell. Also, if MemberDepartRestriction value is “Open,” I will have a red dotted border around this cell. First, I set my cases for styling to get my $bgcolor and $border that I will use to set background color and border style, respectively.

if ($groupdata.MemberJoinRestriction -like “Open”)

{$bgcolor=”bgcolor=””#A4FFA4″””}

elseif ($groupdata.MemberJoinRestriction -like “Approval*”)

{$bgcolor=”bgcolor=””#FFB366″””}

else

{$bgcolor=””}

 

if ($groupdata.MemberDepartRestriction -like “Open”)

{$border=”style=””border: 2px dotted red”””}

else

{$border=””}

 

I have the styles ready now, so I can go ahead and create the row in my table, as shown here.

$Output+=”<th $($bgcolor)>&nbsp;</th><th $($border)>&nbsp;</th>”

 

This is how I create easy styles for HTML tables with Windows PowerShell.

After all the styling, I have the $Output ready. It is time to get it out as an HTML file, and if the user did not supply any file name for $ReportName value as parameter, I am going to generate a unique name for it. The easiest way for me to provide a unique name is to use the current time in the file name, but I will not be too strict about it, so I will set it to minutes rather than seconds.

$t = Get-Date -UFormat %d%m%H%M

If (!$ReportName)

{

$HTMLReport = “$($ADistGroup.windowsemailaddress.local)-$($t).html”

}

Else

{

$HTMLReport=$ReportName

}

 

The Windows PowerShell script is ready. Sorry for not doing a line-by-line description; my aim is to give you a general idea of HTML reporting scripts that I can share with you all on the TechNet Script Repository under Exchange 2010 Category. Before leaving the floor to Ed, I also want to let you know that I did not add Dynamic Distribution Groups to this solution yet; they will get discarded in this report. I hope you enjoyed it. Please let me know if you have any questions via Twitter @SRKNVRGL.

Thank you, Serkan, for writing this blog and sharing.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy