Office 365 Distribution Group Cleanup

This script is in its infancy and I am releasing it as a 1.0 script. It is heavily based of my Exchange 2010 and 2013/2016 version of my Distribution Group Cleanup script. The script performs the same actions as my other scripts. The main difference is that this one requires more reconfiguration on your part, which I will go through in a bit, as well as it will connect to Office 365 and Exchange remotely to query message logs. In Office 365, PowerShell will query the Message Trace logs for the past 30 days (which I believe is the maximum that can be queried). Once the Exchange Server Message Tracking logs have been queried, any active groups will be combined onto one list.

Caveats

In order to be successful in using this script there are some caveats that need to be spelled out:

  • Use the script in a Hybrid environment
  • Run from a preconfigured workstation (see below)
  • A group in Office 365 cannot be tracked directly at this point
  • Only groups that are synched from AD can be correctly worked with in this version of this script
  • Dynamic Distribution Groups cannot be tracked yet

In the next version or two, there will be some accountability for groups that are solely in Office 365. There will also be an inclusion made for dynamic distribution groups, which are not yet included in the search at this point.

Pre-Configuration

To run this script properly you need to configure a computer with the proper requirements / prerequisites. Particularly, we need access to different PowerShell plugins as well as additional Operating System components. So far this script has only been tested on a Windows 7 workstation in a lab environment. While I realize that windows 7 is a bit old, it is also what I currently have for my test environment and I consider it a most reliable Windows OS. As I can make time I will validate and update this section for Windows 8.x and Windows 10.

Windows 7

In order for the script to run properly or to be scheduled to run on a regular basis, I determined that these are the base requirements:

  • Windows Azure PowerShell Module
  • Single Sign On Assistant**

** Please note that installing the RTM version of this software may not allow the Windows Azure PowerShell Module to install. A registry fix may be needed in order for this to work. This is well documented on the web. Check out this solution HERE (as I did not find the original solution).

In addition, I had originally put on Active Directory and Exchange 2016 PowerShell Modules, but that turned out to be overkill as I could accomplish my configuration with remote PowerShell sessions. In order to be able to remotely work with Exchange, I had to make sure that the PowerShell Virtual Directory was properly configured as well as enabling PowerShell remoting.

Operational Differences

The main difference between this script and the Exchange Server only version of the script is that remote connections will need to be made to Office 365 as well Exchange Server 201x. This requires stored credentials in the script as well as files on the server running the script. Lastly, any actively used group lists need to be combined in order to form one list to compare again all groups in Active Directory.

Office 365 Credentials
O365Credentials
Not the reference to a secure file, this file was created previous to the cleanup script running. Also note that I have hard coded the user name as well.

Exchange Server Credentials
ExchangeServerCredentials
Just like office 365 we have a password file and hard coded login name.

30 Day Limit in Office 365
For the Office 365 part, I also removed the time limits of Start and End for the logs to examine. I did this because only 30 days are retained or are qccessible with PowerShell:

30dayLinit-MessageTrace

Script

Here is the code, which I’ve also posted to the TechNet Gallery for anyone to download.

<#  
.SYNOPSIS  
	Distribution Group Cleanup Script - Office 365 Hybrid with Exchange 2013 or 2016
 
.DESCRIPTION  
	This script will cleanup all distribution groups that have not been used within a certain timeframe, it is designed to work with orgnizations
     that are running in a Hyrid mode with groups synched from an on-premises Active Directory to Office 365.  The script will check
     message logs in Office 365 to see what activity there is on certain groups.  This information will then be used to disable or
     hide on-premises groups.
 
.NOTES  
    Version				: 1.0
    Date Created		: 05/01/2016
    Change Log	 		: 1.0 - Script first set up, modified version of Exchange 2010, 2013 and 2016 version(s)
    Wish list			: HTML Report to IT?
    Rights Required		: Local admin on server
    Sched Task Req'd	: No, BUT it should be scheduled
    Exchange Version	: Office 365 and Exchange 2013/2016
    Author				: Damian Scoles
    Dedicated Blog		: https://justaucguy.wordpress.com/
    Disclaimer			: You are on your own.  This was not written by, support by, or endorsed by Microsoft.
    Code stolen from	: None

.EXAMPLE
		.\Office365-DistributionGroupCleanup.ps1
		
	To be run once per month as a recurring task
 
.INPUTS
                None. You cannot pipe objects to this script, at this time.
#>

# ---- NOTES ON THE SCRIP ----
# Testing script for login credentials as well as opening and closing a session
# Make sure to install the Windows Azure PowerShell module from - http://go.microsoft.com/fwlink/p/?linkid=236297. 

# Note, before does this step, make sure to create the securestring.txt file (see the next line for instructions)
# read-host -assecurestring | convertfrom-securestring | out-file C:\securestring.txt
# Place the file you create in a folder on the server where the scrip truns and then change the location below

# ---- VARIABLES ----
# Set time range for the message trace query
$onemonth = ((get-date).adddays(-30))
$current = get-date

# Arrays
$activegroups2 = @()
$activegroups = @()
$inactivegroups = @()
$allgroups = @()
$smtp = @()
$activeGroupsOP2 = @()
$activeGroupsOP = @()

# Other Variables
$from = "Notifications@16-lg.local"
$SMTPServer = "192.168.0.190"
$to = "damian@16-lg.local"
$AdminAddress = "damian@16-lg.local"

# ---- GATHER CREDENTIALS FOR OFFICE 365 and ONPREMISES EXCHANGE ----
# Create secure credentials to be used by the PowerShell session to Office 365 [change file location as needed)

# Office 365 User Name and Password
$username = "damian8192@scoles.onmicrosoft.com"
$password = cat C:\securestring-scolesfamily.txt | convertto-securestring
$LiveCred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password

# Onpremises User name and Password
$username = "16-lg.local\administrator"
$password = cat C:\securestring2.txt | convertto-securestring
$LocalCred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password

# Load the PowerShell module needed for this
import-module msonline
import-module activedirectory

# ---- FUNCTIONS ----

function mail-managerhidden ($groupsmtpaddress) {
	$manager = ((get-distributiongroup $groupsmtpaddress).managedby)
	$manager | foreach {
		$mgr = $_.name
		$smtp4 = (get-mailbox $mgr).emailaddresses
		$smtp4 | foreach {
			$smtp3 = $_.smtpaddress
			$smtp3
		}
		$smtp += @($smtp3)
	}
	$DLName =  (get-distributiongroup $groupsmtpaddress).displayname
	[string] $body = "<strong>NOTIFICATION</strong><BR><BR>As a part of regular maintanence, IT has decided to monitor the usage of Distribution # Lists.<BR><BR>This email is a notification that an Email Distribution Group that you manage has been inactive for 6 months. Because of this level of inactivity, the group has been hidden from the Global Address List. Please check this list to see if it is still valid or not.<BR><BR>Please send an email to $AdminAddress if the list is no longer needed.   Thanks for you assistance with this matter."
	foreach ($line in $smtp) {
   	     $messageParameters = @{
		Subject = "Distribution Group Manager Alert - Inactive Distribution Group - $DLName"
		Body = $body
		From = $from
		To = $line
		SmtpServer = $SMTPServer
             }
                Send-MailMessage @messageParameters –BodyAsHtml
	}
}

function mail-managerdisabled ($groupsmtpaddress) {
	$manager = ((get-distributiongroup $groupsmtpaddress).managedby)
	$manager | foreach {
		$mgr = $_.name
		$smtp2 = (get-mailbox $mgr).emailaddresses
		$smtp2 | foreach {
			$smtp3 = $_.smtpaddress
		}
		$smtp += @($smtp3)
	}
	$DLName =  (get-distributiongroup $groupsmtpaddress).displayname
	[string] $body = "<strong>NOTIFICATION</strong><BR><BR>As a part of regular maintanence, IT has decided to monitor the usage of Distribution # Lists.<BR><BR>This email is a notification that an Email Distribution Group that you manage has been inactive for over 12 months. This Distribiution group has been deleted.<BR><BR>Please send an email to $AdminAddress if you have any questions.   Thanks for you assistance with this matter."
	foreach ($line in $smtp) {
 	     $messageParameters = @{
  		Subject = "Distribution Group Manager Alert - Removed Distribution Group - $DLName"
		Body = $body
		From = $from
		To = $line
		SmtpServer = $SMTPServer
                }
                Send-MailMessage @messageParameters –BodyAsHtml
	     }
}

function emailIT-Groupremoval ($groupsmtpaddress) {
	$DLName =  (get-distributiongroup $groupsmtpaddress).displayname
	[string] $body = "<strong>NOTIFICATION</strong><BR><BR>As a part of regular maintanence this group was disabled.  The group has been inactive for 12 months per the DL Cleanup Script. Confirm that this list can be deleted and remove it from AD."
	$messageParameters = @{
		Subject = "Distribution Group - IT Alert - Removed Distribution Group - $DLName"
		Body = $body
		From = $from
		to = $to
		SmtpServer = $SMTPServer
                }
	Send-MailMessage @messageParameters –BodyAsHtml
}

# ---- SCRIPT BODY ----

# Connect to Office 365
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $LiveCred -Authentication Basic -AllowRedirection
Import-PSSession $Session

# Review Message Trace for recent distribution list email sent and expanded
# $Active = Get-MessageTrace -status Expanded | Sort-Object RelatedRecipientAddress | Group-Object RecipientAddress | Sort-Object Name | select-object name
$activegroups2 += (Get-MessageTrace -status Expanded | Sort-Object RelatedRecipientAddress | Group-Object RecipientAddress | Sort-Object Name | select-object name)

$activegroups2 = $activegroups2 | sort-object name | group-object name
foreach ($line in $activegroups2) {
    $activegroups += $line.name
}

# Closing all active PowerShell Sessions
get-pssession | remove-pssession

# Connect to Local Exchange Server
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://mail.16-lg.local/powershell/ -Credential $LocalCred -Authentication Basic -AllowRedirection
Import-PSSession $Session

# ---- RUN ON THE ONPREMISES EXCHANGE SERVER(S) ----

# Get a list of the active groups - from the Exchange 201x Server(s)
$servers = get-transportservice
foreach ($name in $servers) {
     $ActiveGroupsOP2 += (Get-MessageTrackingLog -Server $name.name -EventId Expand -ResultSize Unlimited -start $onemonth -end $current | Sort-Object RelatedRecipientAddress | Group-Object RelatedRecipientAddress | Sort-Object Name | select-object name)
}

# Process the active groups found on the Exchange 201x Server(2)
$ActiveGroupsOP2 = $ActiveGroupsOP2 | sort-object name | group-object name
foreach ($line in $ActiveGroupsOP2) {
	$ActiveGroupsOP += $line.name
}

# Get a list of Active Groups from Office 365
$ActiveGroups2 = $ActiveGroups2 | sort-object name | group-object name
foreach ($line in $ActiveGroups2) {
	$ActiveGroupsO365 += $line.name
}

# Combine both lists - Office 365 and Exchange 201x Server(s)
$ActiveGroups = $ActiveGroupsO365 + $ActiveGroupsOP

# Get a list of all groups list in Active Directory (should be onprem and O365)
$allgroups2 = get-distributiongroup -resultsize unlimited | Select-Object -Property @{Label="Name";Expression={$_.PrimarySmtpAddress}}
foreach ($line in $allgroups2) {
	$allgroups += $line.name
}

# Find inactive groups by comparing active groups to all groups
$InactiveGroups2 = Compare-Object $activegroups $allgroups
foreach ($line in $inactivegroups2) {
	$smtp2=$line.inputobject
    $address=$smtp2
	$inactivegroups += $address
}

# Set custom attribute 10 for active groups to 0
foreach ($line in $ActiveGroups){
 	set-distributiongroup -identity $line -CustomAttribute10 0 -warningaction silentlycontinue
}

# Set custom attribute 10 for inactive groups - increase by 1
# Hide or disable group
foreach ($line in $InactiveGroups){
		[string]$email = $line
		[int]$number = (get-distributiongroup -identity $email).CustomAttribute10
		$number += 1

		set-distributiongroup -identity $email -CustomAttribute10 $number 
		if ($number -eq 6) {
			$notes = "$current - Hidden from address list due to inactive use."
			Set-Group -identity $email -notes $notes
			Set-DistributionGroup -identity $email -HiddenFromAddressListsEnabled $true
			# Email manager - group hidden
			mail-managerhidden $email
		}

		if ($number -eq 12) {
			# Email manager and IT of group removal
			mail-managerdisabled $email
			emailIT-Groupremoval $email
			# Disable the group
			$notes = "$current - No longer Mail Enabled due to inactive use."
			Set-Group -identity $email -notes $notes
			Disable-DistributionGroup -identity $email -Confirm:$false
		}
}
Advertisements

One thought on “Office 365 Distribution Group Cleanup

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s