Mar 03

Determine If Distribution Group is Being Used in 365 Exchange

“What distribution groups are in use?” and “How many emails are sent to a specific distribution group per month?” are common questions I receive with 365 Exchange or Exchange.  Unfortunately, there is nothing built in that tracks how many emails on sent to a distribution group.  However we can use Get-MessageTrace to count the number of messages sent to a distribution group for a time range with the max being 30 days.  Also note, the by default PageSize returns 1000 items but you can increase the PageSize to 5000 items.  For example, to get the number of emails sent to the distribution group everyone@domain.com for a single day we can use:

$DGCount = Get-MessageTrace -PageSize 5000 -RecipientAddress "everyone@domain.com" -StartDate ([DateTime]::Today.AddDays(-1)) -EndDate ([DateTime]::Today) | ForEach-Object {$count++}
$DGCount

Using this method, we can count the number of emails sent to each distribution group each day and store the results in an output file.  We can then query those output files and create a report.  In my example, the report will show the total emails sent to each distribution group by month and go back 12 months.  Now without further ado, let’s get to the two scripts needed.

365_DGCounter.ps1

#365_DGCounter.ps1
# ------ SCRIPT CONFIGURATION ------
#Log File
$LogFile = $MyInvocation.MyCommand.Path.Replace($MyInvocation.MyCommand.Name,"Logs\") + ((Get-Date).AddDays(-1).ToString('yyyy_MM_dd')) + ".xml"
#Table Name
$TableName = "DG_Emails_Received"
# ------ END CONFIGURATION ------

#Confirm Logs Directory Exists
$LogPath = $MyInvocation.MyCommand.Path.Replace($MyInvocation.MyCommand.Name,"Logs\")
If (!(Test-Path $LogPath)){
	New-Item -ItemType Directory -Force -Path $LogPath
}

#Import Modules and Connect to Office 365
import-module ActiveDirectory
Try {
	$UserCredential = Get-Credential
	$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $UserCredential -Authentication Basic -AllowRedirection
	Import-PSSession $Session
}
Catch {
	#Error Connecting to Office365
}

#Create Data Table
$CounterTable = New-Object system.Data.DataTable $TableName
$CounterCol1 = New-Object system.Data.DataColumn Date,([datetime])
$CounterCol2 = New-Object system.Data.DataColumn Email,([string])
$CounterCol3 = New-Object system.Data.DataColumn Count,([int])
$CounterTable.columns.add($CounterCol1)
$CounterTable.columns.add($CounterCol2)
$CounterTable.columns.add($CounterCol3)

#Get DG's and Add to Table
$DG = Get-DistributionGroup -Resultsize Unlimited | select Displayname, Primarysmtpaddress
$DG | ForEach-Object {
	$count = 0
	#Note: [DateTime]::Today returns 12:00 AM of the Current Date 
	#      Let's assume the date is currently 3/3/2016, the below returns the range 3/2/2016 12:00 AM to 3/3/2016 12:00 AM
	Get-MessageTrace -PageSize 5000 -RecipientAddress $_.Primarysmtpaddress -StartDate ([DateTime]::Today.AddDays(-1)) -EndDate ([DateTime]::Today) | ForEach-Object {$count++}
	$CounterRow = $CounterTable.NewRow()
	$CounterRow.Date = [datetime](Get-Date).AddDays(-1)
	$CounterRow.Email = $_.Primarysmtpaddress
	$CounterRow.Count = $count
	$CounterTable.Rows.Add($CounterRow)
}

#Output Results
$CounterTable.WriteXml($LogFile)
$CounterTable.WriteXmlSchema($LogFile.replace(".xml",".xsd"))
$CounterTable | format-table -AutoSize

#Disconnect From Office365
Remove-PSSession $Session

Note: [DateTime]::Today returns 12:00 AM of the Current Date.  Let’s assume the date is currently 3/3/2016.  The script above would return the range 3/2/2016 12:00 AM to 3/3/2016 12:00 AM.  Running this script will actually return the email counts from yesterday since today has not ended.

365_DGCounterReport.ps1

#365_DGCounterReport.ps1
# ------ SCRIPT CONFIGURATION ------
#Log File Path
$LogPath = $MyInvocation.MyCommand.Path.Replace($MyInvocation.MyCommand.Name,"Logs\")
#Table Names
$MasterTableName = "DG_Emails_Received"
$ResultTableName = "DG_Emails_Received"
# ------ END CONFIGURATION ------

#Import XML Files to Master Table
$MasterTable = New-Object system.Data.DataTable $MasterTableName
$MasterCol1 = New-Object system.Data.DataColumn Date,([datetime])
$MasterCol2 = New-Object system.Data.DataColumn Email,([string])
$MasterCol3 = New-Object system.Data.DataColumn Count,([int])
$MasterTable.columns.add($MasterCol1)
$MasterTable.columns.add($MasterCol2)
$MasterTable.columns.add($MasterCol3)
Get-ChildItem $LogPath -Filter "*.xml" | Sort-Object Name | ForEach-Object {
	$XmlDocument = [XML](Get-Content -Path $_.FullName)
	$XmlDocument.DocumentElement.$MasterTableName | ForEach-Object {
		$MasterRow = $MasterTable.NewRow()
		$MasterRow.Date = [datetime]$_.Date
		$MasterRow.Email = $_.Email
		$MasterRow.Count = [int]$_.Count
		$MasterTable.Rows.Add($MasterRow)
	}
}

#Get List of Unique Distribution Groups from Master Table
$DGs = @{}
$MasterTable | ForEach-Object {
	If (!($DGs.ContainsKey($_.Email))){
		$DGs.Add($_.Email,$_.Email)
	}
}
$DGs = $DGs.GetEnumerator() | Sort-Object Name

#Count Emails By Month
$ResultTable = New-Object system.Data.DataTable $ResultTableName
$ResultCol1 = New-Object system.Data.DataColumn Email,([string])
$ResultCol2 = New-Object system.Data.DataColumn Mon,([int])
$ResultCol3 = New-Object system.Data.DataColumn Mon_1,([int])
$ResultCol4 = New-Object system.Data.DataColumn Mon_2,([int])
$ResultCol5 = New-Object system.Data.DataColumn Mon_3,([int])
$ResultCol6 = New-Object system.Data.DataColumn Mon_4,([int])
$ResultCol7 = New-Object system.Data.DataColumn Mon_5,([int])
$ResultCol8 = New-Object system.Data.DataColumn Mon_6,([int])
$ResultCol9 = New-Object system.Data.DataColumn Mon_7,([int])
$ResultCol10 = New-Object system.Data.DataColumn Mon_8,([int])
$ResultCol11 = New-Object system.Data.DataColumn Mon_9,([int])
$ResultCol12 = New-Object system.Data.DataColumn Mon_10,([int])
$ResultCol13 = New-Object system.Data.DataColumn Mon_11,([int])
$ResultCol14 = New-Object system.Data.DataColumn Total,([int])
$ResultTable.columns.add($ResultCol1)
$ResultTable.columns.add($ResultCol2)
$ResultTable.columns.add($ResultCol3)
$ResultTable.columns.add($ResultCol4)
$ResultTable.columns.add($ResultCol5)
$ResultTable.columns.add($ResultCol6)
$ResultTable.columns.add($ResultCol7)
$ResultTable.columns.add($ResultCol8)
$ResultTable.columns.add($ResultCol9)
$ResultTable.columns.add($ResultCol10)
$ResultTable.columns.add($ResultCol11)
$ResultTable.columns.add($ResultCol12)
$ResultTable.columns.add($ResultCol13)
$ResultTable.columns.add($ResultCol14)
$CurDate = Get-Date
$DGs | ForEach-Object{
	$DG = $_.Name
	$ResultRow = $ResultTable.NewRow()
	$ResultRow.Email = $DG
	$ResultRow.Mon = 0
	$ResultRow.Mon_1 = 0
	$ResultRow.Mon_2 = 0
	$ResultRow.Mon_3 = 0
	$ResultRow.Mon_4 = 0
	$ResultRow.Mon_5 = 0
	$ResultRow.Mon_6 = 0
	$ResultRow.Mon_7 = 0
	$ResultRow.Mon_8 = 0
	$ResultRow.Mon_9 = 0
	$ResultRow.Mon_10 = 0
	$ResultRow.Mon_11 = 0
	$ResultRow.Total = 0
	$MasterTable | ForEach-Object {
		If ($DG -eq $_.Email){
			#Current Month
			If ($_.Date.Month -eq $CurDate.Month){
				$ResultRow.Mon += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-1
			If ($_.Date.Month -eq $CurDate.AddMonths(-1).Month){
				$ResultRow.Mon_1 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-2
			If ($_.Date.Month -eq $CurDate.AddMonths(-2).Month){
				$ResultRow.Mon_2 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-3
			If ($_.Date.Month -eq $CurDate.AddMonths(-3).Month){
				$ResultRow.Mon_3 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-4
			If ($_.Date.Month -eq $CurDate.AddMonths(-4).Month){
				$ResultRow.Mon_4 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-5
			If ($_.Date.Month -eq $CurDate.AddMonths(-5).Month){
				$ResultRow.Mon_5 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-6
			If ($_.Date.Month -eq $CurDate.AddMonths(-6).Month){
				$ResultRow.Mon_6 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-7
			If ($_.Date.Month -eq $CurDate.AddMonths(-7).Month){
				$ResultRow.Mon_7 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-8
			If ($_.Date.Month -eq $CurDate.AddMonths(-8).Month){
				$ResultRow.Mon_8 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-9
			If ($_.Date.Month -eq $CurDate.AddMonths(-9).Month){
				$ResultRow.Mon_9 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-10
			If ($_.Date.Month -eq $CurDate.AddMonths(-10).Month){
				$ResultRow.Mon_10 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-11
			If ($_.Date.Month -eq $CurDate.AddMonths(-11).Month){
				$ResultRow.Mon_11 += $_.Count
				$ResultRow.Total += $_.Count
			}
		}
	}
	$ResultTable.Rows.Add($ResultRow)
}

#Rewrite Column Names as Months
For ($i=0; $i -lt 12; $i++) {
	If ($i -eq 0){
		$ResultTable.columns["Mon"].ColumnName = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($CurDate.Month)) $($CurDate.Year)"
	}Else{
		$ResultTable.columns["Mon_$($i)"].ColumnName = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($CurDate.AddMonths($i * -1).Month)) $($CurDate.AddMonths($i * -1).Year)"
	}
}

#Output Results
$ResultTable | format-table -AutoSize
$ResultTable | Export-CSV Report.csv -notypeinformation
Apr 04

365Licenses.ps1

#=========================================================================
# 365Licenses.ps1
# VERSION: 1.0
# AUTHOR: Brian Steinmeyer
# EMAIL: sigkill@sigkillit.com
# WEB: http://sigkillit.com
# DATE: 4/4/20114
# REQUIREMENTS:
# 1) Microsoft Online Services Sign-In Assistant for IT Professionals
# -(http://www.microsoft.com/en-gb/download/details.aspx?id=28177)
# 2) Windows Azure Active Directory Module for Windows PowerShell
# -(http://technet.microsoft.com/en-us/library/jj151815.aspx#bkmk_installmodule)
# COMMENTS: This script is intended to retrieve Office 365 licensing information
# by accepted domains. It will provide the active license count by domain,
# estimate the cost per domain, and provide the number of unused licenses.
#=========================================================================

# ------ SCRIPT CONFIGURATION ------
# Define License Unit Cost
$intCost = 3.88
# ------ END CONFIGURATION ------

# Connect to Microsoft Online
write-host "Connecting to Office 365..."
Import-Module MSOnline
Try {
Connect-MsolService -ErrorAction Stop
} Catch {
Write-Host $error[0].Exception -ForegroundColor Red -BackgroundColor Black
Write-Host "Error Connecting to Office 365... Quitting Script!" -ForegroundColor Red -BackgroundColor Black
Break
}

# Get Office 365 Accepted Domains and Active User Licenses
Try {
$arrDomains = @(Get-MsolDomain)
} Catch {
Write-Host "Error Retrieving Office 365 Accepted Domains... Quitting Script!" -ForegroundColor Red -BackgroundColor Black
Break
}
$arrCompany = @()
$TotalLicenses = 0
$TotalCost = 0
foreach ($d in $arrDomains){
$domain = $d.Name
write-host ("PROCESSING: " + $domain.ToUpper())
$users = Get-MsolUser -All | where {$_.isLicensed -eq "True" -and $_.UserPrincipalName.Contains($domain)} | Select DisplayName, UserPrincipalName -ExpandProperty Licenses | Select DisplayName, UserPrincipalName, AccountSkuID
If ($users.count -ne $null){
$i = $users.count
$users | format-table
$users | Export-Csv ("365_Licenses_" + $domain.replace(".","_") + ".csv")
}
Else{
$i = 0
Write-Host "0 Licenses<code>n</code>n"
}
$objCompany = New-Object -TypeName PSObject
$objCompany | Add-Member -Name 'Domain' -MemberType Noteproperty -Value $domain
$objCompany | Add-Member -Name 'Licenses' -MemberType Noteproperty -Value $i
$objCompany | Add-Member -Name 'Cost' -MemberType Noteproperty -Value ("{0:C2}" -f ($i * $intCost))
$arrCompany += $objCompany
$TotalLicenses += $i
$TotalCost += ($i * $intCost)

}

# Get Company Licensing Info
Try {
$companyLicenses = Get-MsolAccountSku | Select AccountSkuId, ActiveUnits, WarningUnits, ConsumedUnits
} Catch {
Write-Host $error[0].Exception -ForegroundColor Red -BackgroundColor Black
Write-Host "Error Retrieving Office 365 Account Info... Quitting Script!" -ForegroundColor Red -BackgroundColor Black
Break
}
$objCompany = New-Object -TypeName PSObject
$objCompany | Add-Member -Name 'Domain' -MemberType Noteproperty -Value "TOTAL ACTIVE LICENSES"
$objCompany | Add-Member -Name 'Licenses' -MemberType Noteproperty -Value $TotalLicenses
$objCompany | Add-Member -Name 'Cost' -MemberType Noteproperty -Value ("{0:C2}" -f $TotalCost)
$arrCompany += $objCompany

$unusedLicenses = ($companyLicenses.ActiveUnits - $companyLicenses.ConsumedUnits)
$unusedCost = ($unusedLicenses * $intCost)
$objCompany = New-Object -TypeName PSObject
$objCompany | Add-Member -Name 'Domain' -MemberType Noteproperty -Value "TOTAL UNUSED LICENSES"
$objCompany | Add-Member -Name 'Licenses' -MemberType Noteproperty -Value $unusedLicenses
$objCompany | Add-Member -Name 'Cost' -MemberType Noteproperty -Value ("{0:C2}" -f $unusedCost)
$arrCompany += $objCompany

$objCompany = New-Object -TypeName PSObject
$objCompany | Add-Member -Name 'Domain' -MemberType Noteproperty -Value "GRAND TOTAL LICENSES"
$objCompany | Add-Member -Name 'Licenses' -MemberType Noteproperty -Value $companyLicenses.ActiveUnits
$objCompany | Add-Member -Name 'Cost' -MemberType Noteproperty -Value ("{0:C2}" -f ($companyLicenses.ActiveUnits * $intCost))
$arrCompany += $objCompany

# Display Statistics
$companyLicenses | Format-Table -Auto
$arrCompany | Format-Table -Auto