###############################################################################################################################################################################################

# Create a report for Exchange Online and Office365

#

# Used the following to put it together:

# http://social.technet.microsoft.com/Forums/scriptcenter/en-US/9d324f75-1430-4797-a89e-4d2385cc0e74/powershell-combine-output-from-multiple-cmdlets-in-a-csv-file

# http://blogs.technet.com/b/heyscriptingguy/archive/2013/02/27/get-exchange-online-mailbox-size-in-gb.aspx

# http://blog.concurrency.com/infrastructure/automate-mailbox-size-reporting-in-office-365/

# credit to mjolinor, Brian T. Jackett, and Michael Epping

#

# To run this script as a scheduled task uncomment the Connect365 function on line 99, create a password hash file and set it up to run using the Task Scheduler.

# run the command "Read-Host -AsSecureString “Enter your Office 365 password” | ConvertFrom-SecureString | Out-File <path to file>\Office365cred.txt"

# and use the same path that you used for the $passhash variable

###############################################################################################################################################################################################

# variables that need to be set for each enviroment

$adminaccount = "admin@domain.com"

$mailto = "exchangereports@domain.com"

$mailfrom = "exchangereports@domain.com"

$smtpserver = "your.smtpserver.domain.com"

$emailsubject = "Office365 Report"

$passhash = "<path to file>\Office365cred.txt"

Function Connect365 {

$password = Get-Content $passhash | ConvertTo-SecureString

$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminaccount , $password

if ( Get - PSSession ) { Get - PSSession | ? { $_ .ConfigurationName -like "*Exchange*" } | Remove - PSSession }

$session = New - PSSession - ConfigurationName Microsoft.Exchange - ConnectionUri https: // ps .outlook.com / powershell - Authentication Basic - AllowRedirection -Credential $cred

Import - PSSession $session - AllowClobber

Connect - MsolService -credential $cred

}

Function DataCollection {

# Splat the properties to values for the Mailbox Collection

$mbxprops = {

@ {

Name = $mbx .name

Type = $mbx .RecipientTypeDetails

SizeInMB = $size

ItemCount = $stats .itemcount

ArchiveSizeInMB = $archsize

ArchiveItemCount = $archive .itemcount

IsLicensed = $msol .isLicensed

LastAccess = $stats .lastlogontime

Hidden = $mbx .HiddenFromAddressListsEnabled

ArchiveEnabled = $mbx .ArchiveStatus

ItemRecoveryEnabled = $mbx .SingleItemRecoveryEnabled

}

}

# Regex that makes the properties appear in the same order each time

$mbxproplist = $mbxprops .tostring ( ) .split ( " `n " ) -match "^\s*(\S+)\s*=.+$" -replace "^\s*(\S+)\s*=.+$" , '$1'

$script :mbxcol = @ ( )

# Starts by getting Mailbox Statistics of all mailboxes except for the Discovery Mailbox and then loops

foreach ( $stats in ( get - mailbox - filter { RecipientTypeDetails -ne "DiscoveryMailbox" } | Get - MailboxStatistics -ErrorAction silentlycontinue ) ) {

# gets Mailbox object

$mbx = get - mailbox $stats .identity.tostring ( )

# get Office365 user object

$msol = $mbx | get - msoluser

# not all mailboxes have an archive but if they do this gets some info on the archive mailbox

if ( $mbx .ArchiveStatus -like "Active" ) {

$archive = Get - MailboxStatistics $stats .identity.tostring ( ) - archive -ErrorAction silentlycontinue

if ( $archive -ne $null ) {

$archsize = [ math ] ::Round ( $archive .TotalItemSize.ToString ( ) .Split ( "(" ) [ 1 ] .Split ( " " ) [ 0 ] .Replace ( "," , "" ) / 1MB )

}

}

else {

$archive = $null

$archsize = $null

}

# some math to set all of the sizes to MB

$size = [ math ] ::Round ( $stats .TotalItemSize.ToString ( ) .Split ( "(" ) [ 1 ] .Split ( " " ) [ 0 ] .Replace ( "," , "" ) / 1MB )

# adds each user and data to custom PSObject

$script :mbxcol += new-object psobject -property ( & $mbxprops ) | select $mbxproplist

}

# Map the properties to values for the Group Collection

$dstprops = {

@ {

Name = $dst .name

MemberCount = $membercount

Type = $dst .RecipientTypeDetails

Hidden = $dst .HiddenFromAddressListsEnabled

InternalOnly = $dst .requiresenderauthenticationenabled

}

}

# Regex that makes the properties appear in the same order each time

$dstproplist = $dstprops .tostring ( ) .split ( " `n " ) -match "^\s*(\S+)\s*=.+$" -replace "^\s*(\S+)\s*=.+$" , '$1'

$script :dstcol = @ ( )

# Loops through starts by getting Distribution Groups

foreach ( $dst in ( Get - DistributionGroup -ErrorAction silentlycontinue ) ) {

# Counts number of members in each group

$membercount = ( Get - DistributionGroupMember $dst .name ) .name.count

# adds each group and data to PSObject

$script :dstcol += new-object psobject -property ( & $dstprops ) | select $dstproplist

}

}

# Import MSOnline Powershell Module

get - module - listavailable | ? { $_ .name -like "msonline*" } | import - module

# Connect to Office 365 using the stored password hash. Uncomment to enable running as scheduled task

# connect365

# Run the function to populate the $mbxcol and $dstcol variables with all the data we want to work with

datacollection

# Creating HTML tables containing all the things I want to report on

$hidden = $mbxcol | ? { $_ .Hidden -like "True" } | select name , type | ConvertTo-Html - fragment

$big = $mbxcol | sort -Property sizeinmb -Descending | select name , sizeinmb , ItemCount -First 10 | ConvertTo-Html - Fragment

$inactive = $mbxcol | ? { ( $_ .Lastaccess -lt ( get-date ) .Adddays ( - 15 ) ) -and ( $_ . type -like "UserMailbox" ) } | ConvertTo-Html name , lastaccess - Fragment

$licensing = Get - MsolAccountSku | convertto-html accountskuid , activeunits , consumedunits - Fragment

$internalgroups = $dstcol | ? { $_ .internalonly -like "True" } | convertto-html -property name - Fragment

$hiddengroups = $dstcol | ? { $_ .Hidden -like "True" } | ConvertTo-Html -property name - fragment

$SharedLicensed = $mbxcol | ? { ( $_ . Type -like "SharedMailbox" ) -and ( $_ .IsLicensed -like "True" ) }

# A little extra logic here to return the String "None" if the variables are $null because I didn't want to have empty tables

if ( $SharedLicensed -eq $null ) {

$SharedLicense = "<table><tr><td>None</td></tr></table>"

}

else {

$SharedLicense = $SharedLicensed | ConvertTo-Html -property name - Fragment

}

$emptygroups = $dstcol | ? { $_ .membercount -eq 0 }

# A little extra logic here to return the String "None" if the variables are $null because I didn't want to have empty tables

if ( $emptygroups -eq $null ) {

$emptygroup = "<table><tr><td>None</td></tr></table>"

}

else {

$emptygroup = $emptygroups | ConvertTo-Html -property name - Fragment

}

# For these I just collect the data and the tables are hardcoded below because the Name never changes and I couldn't figure out how to get them all in the same Object to create a table on the fly

$usercount = ( $mbxcol | ? { $_ . Type -like "UserMailbox" } ) .count

$roomcount = ( $mbxcol | ? { $_ . Type -like "RoomMailbox" } ) .count

$sharedcount = ( $mbxcol | ? { $_ . Type -like "SharedMailbox" } ) .count

$rulecount = ( Get - TransportRule ) .count

# Combine everything into a single HTML document

$body = @ "

<html>

<head>

<style>

TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}

TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}

TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}

</style>

</head>

<H1>

Commdore Mail Report

</H1>

<body>

<p>

Licenses:</p></body>

$licensing

<body>

<p>

Mailbox and Rules Count:</p></body>

<table>

<colgroup><col/><col/></colgroup>

<tr><th>Name</th><th>Type</th></tr>

<tr><td>Users Mailboxes</td><td>$usercount</td></tr>

<tr><td>Room Mailboxes</td><td>$roomcount</td></tr>

<tr><td>Shared Mailboxes</td><td>$sharedcount</td></tr>

<tr><td>Transport Rules</td><td>$rulecount</td></tr>

</table>

<body>

<p>

Mailboxes Inactive for at least 15 days:</p></body>

$inactive

<body>

<p>

10 Largest Mailboxes:</p></body>

$big

<body>

<p>

Shared Mailboxes using a License:</p></body>

$SharedLicense

<body>

<p>

Distribution Groups Hidden from GAL:</p></body>

$hiddengroups

<body>

<p>

Empty Groups:</p></body>

$emptygroup

<body>

<p>

Internal Only Groups:</p></body>

$internalgroups

<body>

<p>

Mailboxes Hidden from GAL:</p></body>

$hidden

</html>

" @

# Email it to anyone you want

send - mailmessage - to $mailto - from $mailfrom - subject $emailsubject -body $body - BodyAsHtml - SmtpServer $smtpserver

#cleanup