Restricted Distribution Group access Excel Report

Sharing a script that have been recently written by one of my colleague & that has been reviewed & enhanced further by me.

There was a requirement to extract the Send To access List for a set of Distribution lists. DL Names should appear as sheet Names & Access list identities should show in the sheet as tabular format.

Here is the SAMPLE:

Download & extract the script from below link, update the Distribution Lists in Dls.txt

https://gallery.technet.microsoft.com/scriptcenter/Restricted-Distribution-f63de63f

Update the location to save the excel file

$FinalExcelLocation =”C:\Scripts\ManualScripts\RestrictionReportXL\restrictedgroupsreport.xlsx”

update the customattribute as per your environment , I have used 10 as per the environment in question for employeeid extraction.

Run the script & it will lauch the excel & start updating the results 🙂

It will also extract the members from the groups that are inserted in send to access.

Prerequisites: Exchange 2010 Management Shell & Quest Active Directory Shell

############################################################################ 
#        Author: Prabhat Tiwari 
#        Reviewer: Vikas Sukhija 
#        Date: 3/24/2016 
#        Modified: 3/28/2016 
#        Modified: 3/30/3016 
#        Update: Logic is updated to use DNs instead of display names 
#        Update: Include gorup members 
#        Description: Report of Send to Restrictions in Excel format/ 
#        Multiple seets 
############################################################################ 
 
#--------For Log Files-------------- 
 
$date = get-date -format d  
$date = $date.ToString().Replace(“/”, “-”)  
$time = get-date -format t  
$time = $time.ToString().Replace(":", "-")  
$time = $time.ToString().Replace(" ", "")  
 
$logs = ".\Logs" + "\" + "Restricted_DL_Details" + $date + "_" + $time + "_.txt" 
 
###################import modules########################### 
 
If ((Get-PSSnapin | where {$_.Name -match "Microsoft.Exchange.Management.PowerShell.E2010"}) -eq $null) 
{ 
    Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010 
} 
 
If ((Get-PSSnapin | where {$_.Name -match "Quest.ActiveRoles.ADManagement"}) -eq $null) 
{ 
    Add-PSSnapin Quest.ActiveRoles.ADManagement 
} 
 
 
Start-Transcript -Path $logs 
######################################################################### 
 
function truncate-string([string]$value, [int]$length) 
{ 
    if ($value.Length -gt $length) { $value.Substring(0, $length) } 
    else { $value } 
} 
 
$DLs = Get-Content ".\DLs.txt" 
$xl = New-Object -ComObject Excel.Application 
$FinalExcelLocation ="C:\Scripts\RestrictionReportXL\restrictedgroupsreport.xlsx" 
$wb=$xl.Workbooks.Add() 
$sh= $wb.Worksheets.Item(1) 
$ws=$wb.ActiveSheet 
$xl.Visible=$true 
$cells=$ws.Cells 
$row=1 
$column=1 
$i=1 
 
foreach($DL in $DLs) 
{ 
        $SName = "Sheet" + $i 
                
        if($i -gt 3) 
        { 
        $sh = $wb.WorkSheets.Add() 
        $wc = $wb.Worksheets.Item($SName) 
        $wc.Activate() 
        $tdl = truncate-string $DL 31 
        $wc.Name= $tdl 
        } 
        Else 
        { 
        $wc = $wb.Worksheets.Item($i) 
        $wc.Activate() 
        $tdl = truncate-string $DL 31 
        $wc.Name= $tdl 
        } 
        $i++ 
        $row =1 
        $column=1 
 
        $wc.cells.Item($row,$column) = "Type" 
 
        $wc.cells.Item($row,$column+1) = "UserName" 
 
        $wc.cells.Item($row,$column+2) = "DisplayName" 
 
        $wc.cells.Item($row,$column+3) = "EmployeeID" 
 
        $wc.cells.Item($row,$column+4) = "EmailID" 
 
        Write-host "processing......$DL" -foregroundcolor green 
        $resusers= (Get-DistributionGroup -Identity $DL).AcceptMessagesOnlyFromSendersorMembers | %{$_.distinguishedname} 
         
foreach($resuser in $resusers) 
        { 
         
    $chkmbx = get-mailbox $resuser -ea silentlycontinue 
 
    if($chkmbx){ $resmbx = $chkmbx 
 
    $resmbxemail = $resmbx.PrimarySmtpAddress.Local + "@" + $resmbx.PrimarySmtpAddress.Domain 
         $wc.cells.Item($row+1,$column) = "Mailbox"     
         $wc.cells.Item($row+1,$column+1) = $resmbx.Name 
         $wc.cells.Item($row+1,$column+2) = $resmbx.DisplayName 
         $wc.cells.Item($row+1,$column+3) = $resmbx.Customattribute10 
         $wc.cells.Item($row+1,$column+4) = $resmbxemail 
     $row=$row +1} 
     
    $chkdl = get-distributiongroup $resuser -ea silentlycontinue 
     
    if($chkdl){ $resdl = $chkdl 
 
        $dlemail = $resdl.PrimarySmtpAddress.Local + "@" + $resdl.PrimarySmtpAddress.Domain 
                $wc.cells.Item($row+1,$column) = "DL" 
                $wc.cells.Item($row+1,$column+1) = $resdl.Name 
                $wc.cells.Item($row+1,$column+2) = $resdl.DisplayName 
                $wc.cells.Item($row+1,$column+4) = $dlemail 
                $row=$row +1 
 
    $qadmems = get-qadgroupmember -identity $resdl.samaccountname -indirect -sizelimit 0 | where{$_.type -eq "user"}     
     
    foreach($qadmem in $qadmems){ 
          
    $chkmbx1 = get-mailbox $qadmem.Name -ea silentlycontinue 
     
    if($chkmbx1){ $resmbx1 = $chkmbx1 
     $resmbxemail1 = $resmbx1.PrimarySmtpAddress.Local + "@" + $resmbx1.PrimarySmtpAddress.Domain 
         $wc.cells.Item($row+1,$column) = "Mailbox"     
         $wc.cells.Item($row+1,$column+1) = $resmbx1.Name 
         $wc.cells.Item($row+1,$column+2) = $resmbx1.DisplayName 
         $wc.cells.Item($row+1,$column+3) = $resmbx1.Customattribute10 
         $wc.cells.Item($row+1,$column+4) = $resmbxemail1 
     $row=$row +1} 
    } 
                 
      } 
 
} 
$wc.Columns.Item(1).columnWidth = 24 
$wc.Columns.Item(2).columnWidth = 24 
$wc.Columns.Item(3).columnWidth = 24 
$wc.Columns.Item(4).columnWidth = 24 
$wc.Columns.HorizontalAlignment = -4131 
$wc.rows.item(1).Font.Bold = $True 
 
$row =1 
$column=$column+1 
 
} 
 
$wb.saveas($FinalExcelLocation) 
$xl.Workbooks.close() 
$xl.quit() 
 
 
Stop-Transcript 
######################################################################################