Moonshot365.psm1

function Connect-Office365
{
#Description: Connect to various Office 365 PowerShell services
<#
.NOTES
===========================================================================
Created on: 2/4/2019 10:42 PM
Created by: Bradley Wyatt
E-Mail: Brad@TheLazyAdministrator.com
GitHub: https://github.com/bwya77
Website: https://www.thelazyadministrator.com
Organization: Porcaro Stolarek Mete Partners; The Lazy Administrator
Filename: Connect-Office365.ps1
Version: 1.0.4
Contributors: /u/Sheppard_Ra
Changelog:
1.0.4
- Host title will add a service or services you are connected to. If unable to connect it will not display connection status until connection is valid
===========================================================================
.SYNOPSIS
Connect to Office 365 Services
.DESCRIPTION
 
.PARAMETER MFA
Description: Specifies MFA requirement to sign into Office 365 services. If set to $True it will use the Office 365 ExoPSSession Module to sign into Exchange & Compliance Center using MFA. Other modules support MFA without needing another external module
.PARAMETER Exchange
Description: Connect to Exchange Online
.PARAMETER SkypeForBusiness
Description: Connect to Skype for Business
.PARAMETER SharePoint
Description: Connect to SharePoint Online
.PARAMETER SecurityandCompliance
Description: Connect to Security and Compliance Center
.PARAMETER AzureAD
Description: Connect to Azure AD V2
.PARAMETER MSOnline
Type: Switch
Description: Connect to Azure AD V1
.PARAMETER Teams
Type: Switch
Description: Connect to Teams
.EXAMPLE
Description: Connect to SharePoint Online
C:\PS> Connect-Office365 -SharePoint
.EXAMPLE
Description: Connect to Exchange Online and Azure AD V1 (MSOnline)
C:\PS> Connect-Office365 -Service Exchange, MSOnline
.EXAMPLE
Description: Connect to Exchange Online and Azure AD V2 using Multi-Factor Authentication
C:\PS> Connect-Office365 -Service Exchange, MSOnline -MFA
.EXAMPLE
Description: Connect to Teams and Skype for Business
C:\PS> Connect-Office365 -Service Teams, SkypeForBusiness
.EXAMPLE
Description: Connect to SharePoint Online
C:\PS> Connect-Office365 -Service SharePoint -SharePointOrganizationName bwya77 -MFA
.LINK
 
#>

[OutputType()]
[CmdletBinding(DefaultParameterSetName)]
$ServicesIndex = ""
$MFAcheck = ""
Write-Host ""
Write-Host "==============================================" -ForegroundColor Yellow
Write-Host "Services to Connect To:" -ForegroundColor Yellow
Write-Host "1. AzureAD" -ForegroundColor Yellow
Write-Host "2. Exchange Online" -ForegroundColor Yellow
Write-Host "3. MSOnline (AzureAD v2)" -ForegroundColor Yellow
Write-Host "4. Teams" -ForegroundColor Yellow
Write-Host "5. SkypeForBusiness" -ForegroundColor Yellow
Write-Host "6. SharePoint" -ForegroundColor Yellow
Write-Host "7. Security and Compliance - Requires MFA" -ForegroundColor Yellow
Write-Host "==============================================" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Write-Host "Which services do you want to connect to? Type the numbers, separated by a comma." -ForegroundColor Yellow
$ServicesIndex = Read-Host "Services"
Write-Host ""
Write-Host "Connect via MFA?" -ForegroundColor Yellow
$MFAcheck = Read-Host "Yes or No"
Start-Sleep -seconds 1

$ServicesString = ""
$ConnectCall = "Connect-Office365 -Service "

if($ServicesIndex -like "*1*"){
$ServicesString = -join ($ServicesString,"AzureAD,")
}
if($ServicesIndex -like "*2*"){
$ServicesString = -join ($ServicesString,"ExchangeOnline,")
}
if($ServicesIndex -like "*3*"){
$ServicesString = -join ($ServicesString,"MSOnline,")
}
if($ServicesIndex -like "*4*"){
$ServicesString = -join ($ServicesString,"Teams,")
}
if($ServicesIndex -like "*5*"){
$ServicesString = -join ($ServicesString,"SkypeForBusiness,")
}
if($ServicesIndex -like "*7*"){
$ServicesString = -join ($ServicesString,"SecurityandCompliance,")
}
if($ServicesIndex -like "*6*"){
Write-Host ""
Write-Host "SharePoint:" -ForegroundColor Yellow
$SharePointOrganizationName = Read-Host "What is your tenant name? (<tenant name>.onmicrosoft.com)"
$ServicesString = -join ($ServicesString,"SharePoint,")
}
$ServicesString = $ServicesString.trimend(",")
$ServicesString = $ServicesString.trimstart(",")
$ServicesString = $ServicesString.trimstart(" ")
$ConnectCall = -join ($ConnectCall,$ServicesString)
$MFA = ""
if($MFAcheck -like "*yes*"){
$MFA = $True
}else{
$MFA = $False}
Start-Sleep -seconds 1
Write-Host ""
Write-Host ""
Write-Host "Calling connection script..." -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Start-Sleep -seconds 1

$Service = $ServicesString -split(',')
$getModuleSplat = @{
ListAvailable = $True
Verbose          = $False
}
If ($MFA -ne $True)
{
Write-Host "Gathering PSCredentials object for non MFA sign on"
$Credential = Get-Credential -Message "Please enter your Office 365 credentials"
}
ForEach ($Item in $Service)
{
Write-Host "Attempting connection to $Item"
Switch ($Item)
{
AzureAD {
If ($null -eq (Get-Module @getModuleSplat -Name "AzureAD"))
{
Write-Error "SkypeOnlineConnector Module is not present!"
continue
}
Else
{
If ($MFA -eq $True)
{
$Connect = Connect-AzureAD
$Connect
If ($Connect -ne $Null)
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: AzureAD"
}
Else
{
$host.ui.RawUI.WindowTitle += " - AzureAD"
}
}
}
Else
{
$Connect = Connect-AzureAD -Credential $Credential
$Connect
If ($Connect -ne $Null)
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: AzureAD"
}
Else
{
$host.ui.RawUI.WindowTitle += " - AzureAD"
}
}
}
}
continue
}
ExchangeOnline {
If ($MFA -eq $True)
{
$getChildItemSplat = @{
Path = "$Env:LOCALAPPDATA\Apps\2.0\*\CreateExoPSSession.ps1"
Recurse = $true
ErrorAction = 'SilentlyContinue'
Verbose = $false
}
$MFAExchangeModule = ((Get-ChildItem @getChildItemSplat | Select-Object -ExpandProperty Target -First 1).Replace("CreateExoPSSession.ps1", ""))
If ($null -eq $MFAExchangeModule)
{
Write-Error "The Exchange Online MFA Module was not found!
https://docs.microsoft.com/en-us/powershell/exchange/exchange-online/connect-to-exchange-online-powershell/mfa-connect-to-exchange-online-powershell?view=exchange-ps"

continue
}
Else
{
Write-Host "Importing Exchange MFA Module"
Import-Module -Name "$MFAExchangeModule\CreateExoPSSession.ps1" -Global
Write-Host "Connecting to Exchange Online"
$null = Connect-EXOPSSession -ConnectionUri "https://ps.outlook.com/powershell/" | Out-Null
$global:exchangeOnlineSession = (Get-PSSession | Where-Object { ($_.ConfigurationName -eq 'Microsoft.Exchange') -and ($_.State -eq 'Opened') })[0]
Import-Module -AsCustomObject (Import-PSSession $exchangeOnlineSession -AllowClobber) -Global

If ($Null -ne (Get-PSSession | Where-Object { $_.ConfigurationName -like "*Exchange*" }))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Exchange"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Exchange"
}
}
}
}
Else
{
$newPSSessionSplat = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri      = "https://ps.outlook.com/powershell/"
Authentication    = 'Basic'
Credential          = $Credential
AllowRedirection  = $true
}
Write-Host "Connecting to Exchange Online"
$EOSession = New-PSSession @newPSSessionSplat
Import-Module (Import-PSSession $EOSession -AllowClobber -DisableNameChecking) -Global
If ($Null -ne (Get-PSSession | Where-Object { $_.ConfigurationName -like "*Exchange*" }))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Exchange"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Exchange"
}
}
}
continue
}
MSOnline {
If ($null -eq (Get-Module @getModuleSplat -Name "MSOnline"))
{
Write-Error "MSOnline Module is not present!"
continue
}
Else
{
Write-Host "Connecting to MSOnline"
If ($MFA -eq $True)
{
Connect-MsolService
If ($Null -ne (Get-MsolCompanyInformation -ErrorAction SilentlyContinue))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: MSOnline"
}
Else
{
$host.ui.RawUI.WindowTitle += " - MSOnline"
}
}
}
Else
{
Connect-MsolService -Credential $Credential
If ($Null -ne (Get-MsolCompanyInformation -ErrorAction SilentlyContinue))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: MSOnline"
}
Else
{
$host.ui.RawUI.WindowTitle += " - MSOnline"
}
}
}
}
continue
}
SecurityAndCompliance {
If ($MFA -eq $True)
{
$getChildItemSplat = @{
Path = "$Env:LOCALAPPDATA\Apps\2.0\*\CreateExoPSSession.ps1"
Recurse = $true
ErrorAction = 'SilentlyContinue'
Verbose = $false
}
$MFAExchangeModule = ((Get-ChildItem @getChildItemSplat | Select-Object -ExpandProperty Target -First 1).Replace("CreateExoPSSession.ps1", ""))
If ($null -eq $MFAExchangeModule)
{
Write-Error "The Exchange Online MFA Module was not found!
https://docs.microsoft.com/en-us/powershell/exchange/exchange-online/connect-to-exchange-online-powershell/mfa-connect-to-exchange-online-powershell?view=exchange-ps"

continue
}
Else
{
Write-Host "Importing Exchange MFA Module (Required)"
. "$MFAExchangeModule\CreateExoPSSession.ps1"
Write-Host "Connecting to Security and Compliance Center"
Connect-IPPSSession
If ($Null -ne (Get-PSSession | Where-Object { $_.ConfigurationName -like "*Exchange*" }))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Security and Compliance Center"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Security and Compliance Center"
}
}
}
}
Else
{
$newPSSessionSplat = @{
ConfigurationName = 'Microsoft.SecurityAndCompliance'
ConnectionUri      = 'https://ps.compliance.protection.outlook.com/powershell-liveid/'
Authentication    = 'Basic'
Credential          = $Credential
AllowRedirection  = $true
}
$Session = New-PSSession @newPSSessionSplat
Write-Host "Connecting to SecurityAndCompliance"
Import-Module (Import-PSSession $Session -DisableNameChecking) -Global
If ($Null -ne (Get-PSSession | Where-Object { $_.ConfigurationName -like "*Exchange*" }))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Security and Compliance Center"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Security and Compliance Center"
}
}
}
continue
}
SharePoint {
If ($null -eq (Get-Module @getModuleSplat -Name Microsoft.Online.SharePoint.PowerShell))
{
Write-Error "Microsoft.Online.SharePoint.PowerShell Module is not present!"
continue
Write-Host "Installing SharePoint Module!"
Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Confirm:$False -Force
}
Else
{
If (!$SharePointOrganizationName)
{
Write-Error 'Please provide a valid SharePoint organization name with the -SharePointOrganizationName parameter.'
continue
}
$SharePointURL = "https://{0}-admin.sharepoint.com" -f $SharePointOrganizationName
Write-Host "Connecting to SharePoint at $SharePointURL"
If ($MFA -eq $True)
{
$SPOSession =  Connect-SPOService -Url https://$SharePointOrganizationName-admin.sharepoint.com -credential $credential
$SPOSession
If ($Null -ne (Get-SPOTenant))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: SharePoint Online"
}
Else
{
$host.ui.RawUI.WindowTitle += " - SharePoint Online"
}
}
}
Else
{
$SPOSession = Connect-SPOService -Url https://$SharePointOrganizationName-admin.sharepoint.com -credential $credential
$SPOSession
If ($Null -ne (Get-SPOTenant))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: SharePoint Online"
}
Else
{
$host.ui.RawUI.WindowTitle += " - SharePoint Online"
}
}
}
}
continue
}
SkypeForBusiness {
Write-Host "Connecting to SkypeForBusiness"
If ($null -eq (Get-Module @getModuleSplat -Name "SkypeOnlineConnector"))
{
Write-Error "SkypeOnlineConnector Module is not present!"
}
Else
{
# Skype for Business module
Import-Module SkypeOnlineConnector
If ($MFA -eq $True)
{
$CSSession = New-CsOnlineSession
Import-Module (Import-PSSession $CSSession -AllowClobber) -Global
If ($Null -ne (Get-CsOnlineDirectoryTenant))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Skype for Business"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Skype for Business"
}
}
}
Else
{
$CSSession = New-CsOnlineSession -Credential $Credential
Import-Module (Import-PSSession $CSSession -AllowClobber) -Global
If ($Null -ne (Get-CsOnlineDirectoryTenant))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Skype for Business"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Skype for Business"
}
}
}
}
continue
}
Teams {
If ($null -eq (Get-Module @getModuleSplat -Name "MicrosoftTeams"))
{
Write-Error "MicrosoftTeams Module is not present!"
}
Else
{
Write-Host "Connecting to Teams"
If ($MFA -eq $True)
{
$TeamsConnect = Connect-MicrosoftTeams
If ($Null -ne ($TeamsConnect))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Microsoft Teams"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Microsoft Teams"
}
}
}
Else
{
$TeamsConnect = Connect-MicrosoftTeams -Credential $Credential
If ($Null -ne ($TeamsConnect))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Microsoft Teams"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Microsoft Teams"
}
}
}
}
continue
}
Default { }
}
}
}
function Get-Office365Modules
{
#Description: Install AzureAD, MsOnline, and SharePoint modules
#Check if connected to a tenant
if (Get-Module -ListAvailable -Name AzureAD) {
Write-Host "AzureAD Module Installed!"
}
else {
Write-Host "Installing AzureAD Module!"
    Install-Module AzureAD -Confirm:$False -Force
}
if (Get-Module -ListAvailable -Name MsOnline) {
Write-Host "MsOnline Module Installed!"
}
else {
Write-Host "Installing MsOnline Module!"
    Install-Module MsOnline -Confirm:$False -Force
}
if (Get-Module -ListAvailable -Name Microsoft.Online.SharePoint.PowerShell) {
Write-Host "SharePoint Module Installed!"
}
else {
Write-Host "Installing SharePoint Module!"
    Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Confirm:$False -Force
}
}
function New-365Report
{
#Description: Report on O365 tenant objects & generate migration scripts
$Sku = @{
  "O365_BUSINESS_ESSENTIALS"      = "Office 365 Business Essentials"
  "O365_BUSINESS_PREMIUM"      = "Office 365 Business Premium"
  "DESKLESSPACK"      = "Office 365 (Plan K1)"
  "DESKLESSWOFFPACK"      = "Office 365 (Plan K2)"
  "LITEPACK"      = "Office 365 (Plan P1)"
  "EXCHANGESTANDARD"      = "Office 365 Exchange Online Only"
  "STANDARDPACK"      = "Enterprise Plan E1"
  "STANDARDWOFFPACK"      = "Office 365 (Plan E2)"
  "ENTERPRISEPACK" = "Enterprise Plan E3"
  "ENTERPRISEPACKLRG"      = "Enterprise Plan E3"
  "ENTERPRISEWITHSCAL" = "Enterprise Plan E4"
  "STANDARDPACK_STUDENT"      = "Office 365 (Plan A1) for Students"
  "STANDARDWOFFPACKPACK_STUDENT"      = "Office 365 (Plan A2) for Students"
  "ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students"
  "ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students"
  "STANDARDPACK_FACULTY"      = "Office 365 (Plan A1) for Faculty"
  "STANDARDWOFFPACKPACK_FACULTY"      = "Office 365 (Plan A2) for Faculty"
  "ENTERPRISEPACK_FACULTY" = "Office 365 (Plan A3) for Faculty"
  "ENTERPRISEWITHSCAL_FACULTY" = "Office 365 (Plan A4) for Faculty"
  "ENTERPRISEPACK_B_PILOT" = "Office 365 (Enterprise Preview)"
  "STANDARD_B_PILOT"      = "Office 365 (Small Business Preview)"
  "VISIOCLIENT"      = "Visio Pro Online"
  "POWER_BI_ADDON" = "Office 365 Power BI Addon"
  "POWER_BI_INDIVIDUAL_USE"      = "Power BI Individual User"
  "POWER_BI_STANDALONE"      = "Power BI Stand Alone"
  "POWER_BI_STANDARD"      = "Power-BI Standard"
  "PROJECTESSENTIALS"      = "Project Lite"
  "PROJECTCLIENT"      = "Project Professional"
  "PROJECTONLINE_PLAN_1"      = "Project Online"
  "PROJECTONLINE_PLAN_2"      = "Project Online and PRO"
  "ProjectPremium" = "Project Online Premium"
  "ECAL_SERVICES"      = "ECAL"
  "EMS"      = "Enterprise Mobility Suite"
  "RIGHTSMANAGEMENT_ADHOC" = "Windows Azure Rights Management"
  "MCOMEETADV" = "PSTN conferencing"
  "SHAREPOINTSTORAGE"      = "SharePoint storage"
  "PLANNERSTANDALONE"      = "Planner Standalone"
  "CRMIUR" = "CMRIUR"
  "BI_AZURE_P1"      = "Power BI Reporting and Analytics"
  "INTUNE_A"      = "Windows Intune Plan A"
  "PROJECTWORKMANAGEMENT"      = "Office 365 Planner Preview"
  "ATP_ENTERPRISE" = "Exchange Online Advanced Threat Protection"
  "EQUIVIO_ANALYTICS"      = "Office 365 Advanced eDiscovery"
  "AAD_BASIC"      = "Azure Active Directory Basic"
  "RMS_S_ENTERPRISE"      = "Azure Active Directory Rights Management"
  "AAD_PREMIUM"      = "Azure Active Directory Premium"
  "MFA_PREMIUM"      = "Azure Multi-Factor Authentication"
  "STANDARDPACK_GOV"      = "Microsoft Office 365 (Plan G1) for Government"
  "STANDARDWOFFPACK_GOV"      = "Microsoft Office 365 (Plan G2) for Government"
  "ENTERPRISEPACK_GOV" = "Microsoft Office 365 (Plan G3) for Government"
  "ENTERPRISEWITHSCAL_GOV" = "Microsoft Office 365 (Plan G4) for Government"
  "DESKLESSPACK_GOV"      = "Microsoft Office 365 (Plan K1) for Government"
  "ESKLESSWOFFPACK_GOV"      = "Microsoft Office 365 (Plan K2) for Government"
  "EXCHANGESTANDARD_GOV"      = "Microsoft Office 365 Exchange Online (Plan 1) only for Government"
  "EXCHANGEENTERPRISE_GOV" = "Microsoft Office 365 Exchange Online (Plan 2) only for Government"
  "SHAREPOINTDESKLESS_GOV" = "SharePoint Online Kiosk"
  "EXCHANGE_S_DESKLESS_GOV"      = "Exchange Kiosk"
  "RMS_S_ENTERPRISE_GOV"      = "Windows Azure Active Directory Rights Management"
  "OFFICESUBSCRIPTION_GOV" = "Office ProPlus"
  "MCOSTANDARD_GOV"      = "Lync Plan 2G"
  "SHAREPOINTWAC_GOV"      = "Office Online for Government"
  "SHAREPOINTENTERPRISE_GOV"      = "SharePoint Plan 2G"
  "EXCHANGE_S_ENTERPRISE_GOV"      = "Exchange Plan 2G"
  "EXCHANGE_S_ARCHIVE_ADDON_GOV"      = "Exchange Online Archiving"
  "EXCHANGE_S_DESKLESS"      = "Exchange Online Kiosk"
  "SHAREPOINTDESKLESS" = "SharePoint Online Kiosk"
  "SHAREPOINTWAC"      = "Office Online"
  "YAMMER_ENTERPRISE"      = "Yammer Enterprise"
  "EXCHANGE_L_STANDARD"      = "Exchange Online (Plan 1)"
  "MCOLITE"      = "Lync Online (Plan 1)"
  "SHAREPOINTLITE" = "SharePoint Online (Plan 1)"
  "OFFICE_PRO_PLUS_SUBSCRIPTION_SMBIZ" = "Office ProPlus"
  "EXCHANGE_S_STANDARD_MIDMARKET"      = "Exchange Online (Plan 1)"
  "MCOSTANDARD_MIDMARKET"      = "Lync Online (Plan 1)"
  "SHAREPOINTENTERPRISE_MIDMARKET" = "SharePoint Online (Plan 1)"
  "OFFICESUBSCRIPTION" = "Office ProPlus"
  "YAMMER_MIDSIZE" = "Yammer"
  "DYN365_ENTERPRISE_PLAN1"      = "Dynamics 365 Customer Engagement Plan Enterprise Edition"
  "ENTERPRISEPREMIUM_NOPSTNCONF"      = "Enterprise E5 (without Audio Conferencing)"
  "ENTERPRISEPREMIUM"      = "Enterprise E5 (with Audio Conferencing)"
  "MCOSTANDARD"      = "Skype for Business Online Standalone Plan 2"
  "PROJECT_MADEIRA_PREVIEW_IW_SKU" = "Dynamics 365 for Financials for IWs"
  "STANDARDWOFFPACK_IW_STUDENT"      = "Office 365 Education for Students"
  "STANDARDWOFFPACK_IW_FACULTY"      = "Office 365 Education for Faculty"
  "EOP_ENTERPRISE_FACULTY" = "Exchange Online Protection for Faculty"
  "EXCHANGESTANDARD_STUDENT"      = "Exchange Online (Plan 1) for Students"
  "OFFICESUBSCRIPTION_STUDENT" = "Office ProPlus Student Benefit"
  "STANDARDWOFFPACK_FACULTY"      = "Office 365 Education E1 for Faculty"
  "STANDARDWOFFPACK_STUDENT"      = "Microsoft Office 365 (Plan A2) for Students"
  "DYN365_FINANCIALS_BUSINESS_SKU" = "Dynamics 365 for Financials Business Edition"
  "DYN365_FINANCIALS_TEAM_MEMBERS_SKU" = "Dynamics 365 for Team Members Business Edition"
  "FLOW_FREE"      = "Microsoft Flow Free"
  "POWER_BI_PRO"      = "Power BI Pro"
  "O365_BUSINESS"      = "Office 365 Business"
  "DYN365_ENTERPRISE_SALES"      = "Dynamics Office 365 Enterprise Sales"
  "RIGHTSMANAGEMENT"      = "Rights Management"
  "PROJECTPROFESSIONAL"      = "Project Professional"
  "VISIOONLINE_PLAN1"      = "Visio Online Plan 1"
  "EXCHANGEENTERPRISE" = "Exchange Online Plan 2"
  "DYN365_ENTERPRISE_P1_IW"      = "Dynamics 365 P1 Trial for Information Workers"
  "DYN365_ENTERPRISE_TEAM_MEMBERS" = "Dynamics 365 For Team Members Enterprise Edition"
  "CRMSTANDARD"      = "Microsoft Dynamics CRM Online Professional"
  "EXCHANGEARCHIVE_ADDON"      = "Exchange Online Archiving For Exchange Online"
  "EXCHANGEDESKLESS"      = "Exchange Online Kiosk"
  "SPZA_IW"      = "App Connect"
  "WINDOWS_STORE"      = "Windows Store for Business"
  "MCOEV"      = "Microsoft Phone System"
  "VIDEO_INTEROP"      = "Polycom Skype Meeting Video Interop for Skype for Business"
  "SPE_E5" = "Microsoft 365 E5"
  "SPE_E3" = "Microsoft 365 E3"
  "ATA"      = "Advanced Threat Analytics"
  "MCOPSTN2"      = "Domestic and International Calling Plan"
  "FLOW_P1"      = "Microsoft Flow Plan 1"
  "FLOW_P2"      = "Microsoft Flow Plan 2"
  "CRMSTORAGE" = "Microsoft Dynamics CRM Online Additional Storage"
  "SMB_APPS"      = "Microsoft Business Apps"
  "MICROSOFT_BUSINESS_CENTER"      = "Microsoft Business Center"
  "DYN365_TEAM_MEMBERS"      = "Dynamics 365 Team Members"
  "STREAM" = "Microsoft Stream Trial"
  "EMSPREMIUM"                         = "ENTERPRISE MOBILITY + SECURITY E5"
  "SPB" = "Microsoft 365 Business"
  "MCOPSTN1" = "Domestic Calling Plan"
  "MEETING_ROOM" = "Teams Meeting Room"
  "POWERAPPS_PER_APP_IW" = "PowerApps Per App"
  "TEAMS_COMMERCIAL_TRIAL" = "Microsoft Teams"
  "POWERAPPS_PER_USER" = "PowerApps Per User"
  "FLOW_PER_USER" = "PowerAutomate Per User"
  "POWERFLOW_P1" = "Flow P1"
  
 }

#Check if connected to a tenant
if (Get-Module -ListAvailable -Name AzureAD) {
}
else {
    Install-Module AzureAD -Confirm:$False -Force
}
if (Get-Module -ListAvailable -Name MsOnline) {
}
else {
    Install-Module MsOnline -Confirm:$False -Force
}
Clear-Host
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Welcome to the Office 365 Report Builder!"
Write-Host " Starting module connection checks."
Write-Host "============================================"
try
{ $var = Get-AzureADTenantDetail }

catch [Microsoft.Open.Azure.AD.CommonLibrary.AadNeedAuthenticationException]
{$credential = Get-Credential -Message "Please enter your Office 365 credentials"
Import-Module MsOnline
Connect-AzureAD -Credential $credential
Connect-MsolService -Credential $credential
$exchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://outlook.office365.com/powershell-liveid/" -Credential $credential -Authentication "Basic" -AllowRedirection
Import-PSSession $exchangeSession -AllowClobber
}
$CreateUsers = ""
$CreateGroups = ""
$CreateShared = ""
$CreateResources = ""
$CreateDomains = ""
$CreateScripts = ""
$CreateAllReports = ""
$CreateContacts = ""
$MigrationTF = ""
$CheckOneDrive = ""
$CreateGroupScripts = ""
Write-Host ""
Write-Host ""
Write-Host "Connected to Azure and Exchange Online!"
Start-Sleep -Seconds 2

Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "========================================================"
Write-Host "Identify the Module Reports to generate."
Write-Host "========================================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "Generate All Reports?"
Write-Host "Including Users, Groups, Shared Mailboxes, Resource Mailboxes, Contacts, & Domains"
Write-Host ""
Write-Host "Type y for Yes, n for No"
$CreateAllReports = Read-Host "[y/n]"
If ($CreateAllReports -eq "y")
{
Write-Host ""
Write-Host "Check OneDrive Storage Usage? This will increase time when parsing Users."
Write-Host ""
Write-Host "Type y for Yes, n for No"
$CheckOneDrive = Read-Host "[y/n]"
Write-Host ""
Write-Host ""

if($CheckOneDrive -eq "y"){
  Write-Host "========================================================"
  Write-Host "Enter Tenant Name (Found in <Tenant Name>.onmicrosoft.com)"
  $TenantName = Read-Host "Name"
if (Get-Module -ListAvailable -Name Microsoft.Online.SharePoint.PowerShell) {
Write-Host "SharePoint Module Installed!"
}
else {
Write-Host "Installing SharePoint Module!"
    Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Confirm:$False -Force
}
try
{ $var = Get-SPOsite
Start-Sleep -Seconds 1
Write-Host ""
Write-Host ""
Write-Host "Connected to SharePoint Online!"
}
catch
{
  Write-Host ""
  Write-Host ""
  Write-Host "Connecting to SharePoint Online..."
  Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking
  Connect-SPOService -Url https://$TenantName-admin.sharepoint.com -credential $credential
  Write-Host ""
  Write-Host "Connected!"
  Write-Host ""
  Write-Host "========================================================"
}
}
$CreateUsers = "y"
$CreateGroups = "y"
$CreateShared = "y"
$CreateResources = "y"
$CreateDomains = "y"
$CreateScripts = ""
$CreateContacts = "y"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "Generate scripts to perform a migration to another Office 365 Tenant?"
Write-Host ""
Write-Host "Type y for Yes, n for No"
$CreateScripts = Read-Host "[y/n]"
Write-Host ""
If ($CreateScripts -eq "y")
{
  Write-Host "Please enter the @<domain>.onmicrosoft.com prefix name for destination Tenant"
  Write-Host "(ex: 'comp123' for comp123.onmicrosoft.com)"
  $MigrationOMSFT = Read-Host "Prefix Name"
  Write-Host ""
  Write-Host "Enter the full default email & username domain users should have in destination Tenant"
  Write-Host "(ex: company.com)"
  $MigrationPrimeDomain = Read-Host "Default Domain"
  $CreateGroupScripts = "y"
}
} else {
  $CreateUsers = ""
  $CreateGroups = ""
  $CreateShared = ""
  $CreateResources = ""
  $CreateDomains = ""
  $CreateScripts = ""
  $CreateContacts = ""
  $MigrationTF = ""
  $CheckOneDrive = ""
  $CreateGroupScripts = ""
  Write-Host ""
  Write-Host "========================================================"
  Write-Host "Specify Indivudal Module Reports"
  Write-Host "========================================================"
  Write-Host ""
  Write-Host "Generate report for Users?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateUsers = Read-Host "[y/n]"
  If($CreateUsers -eq "y")
  {
  Write-Host ""
  Write-Host "Check OneDrive Storage Usage? This will increase time when parsing Users."
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CheckOneDrive = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""

  if($CheckOneDrive -eq "y"){
    Write-Host "Enter Tenant Name (Found in <Tenant Name>.onmicrosoft.com)"
    $TenantName = Read-Host "Name"
  if (Get-Module -ListAvailable -Name Microsoft.Online.SharePoint.PowerShell) {
  Write-Host "SharePoint Module Installed!"
  }
  else {
  Write-Host "Installing SharePoint Module!"
      Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Confirm:$False -Force
  }
  try
  { $var = Get-SPOsite
  Start-Sleep -Seconds 1
  Write-Host ""
  Write-Host ""
  Write-Host "Connected to SharePoint Online!"
  }
  catch
  {
    Write-Host ""
    Write-Host ""
    Write-Host "Connecting to SharePoint Online..."
    Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking
    Connect-SPOService -Url https://$TenantName-admin.sharepoint.com -credential $credential
    Write-Host ""
    Write-Host "Connected!"
    Write-Host ""
    Write-Host "========================================================"
  }
  }
  }
  Write-Host ""
  Write-Host ""
  Write-Host "Check Archive Size?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CheckArchive = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Groups?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateGroups = Read-Host "[y/n]"
  if($CreateGroups -eq "y"){
    Write-Host ""
    Write-Host "Create Group Migration Scripts?"
    Write-Host ""
    Write-Host "Type y for Yes, n for No"
    $CreateGroupScripts = Read-Host "[y/n]"
    Write-Host ""
    Write-Host ""
  }
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Shared Mailboxes?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateShared = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Resource Mailboxes?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateResources = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Contacts?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateContacts = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Domains?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateDomains = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""
  Write-Host "Generate scripts to perform a migration to another Office 365 Tenant?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateScripts = Read-Host "[y/n]"
  Write-Host ""
  If ($CreateScripts -eq "y")
  {
    Write-Host "Please enter the @<domain>.onmicrosoft.com prefix name for destination Tenant."
    Write-Host "(ex: company123 for company123.onmicrosoft.com)"
    $MigrationOMSFT = Read-Host "Prefix Name"
    Write-Host ""
    Write-Host "Enter the full default email & username domain users should have in destination Tenant"
    Write-Host "(ex: company.com)"
    $MigrationPrimeDomain = Read-Host "Default Domain"
  }
}
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Start-Sleep -Seconds 2
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Creating Report File. Please Wait"
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Progress -Activity 'Creating Report File!' -Status "Please wait."
Start-Sleep -Seconds 2
$TenantDisplay = Get-AzureAdTenantDetail | Select -ExpandProperty DisplayName
$Date1 = Get-Date -Format "MM-dd-yyyy HH-mm"
$SelectedReports = ""
if($CreateUsers -eq "y"){
  $SelectedReports = -join ($SelectedReports," Users -")
}
if($CreateGroups -eq "y"){
  $SelectedReports = -join ($SelectedReports," Groups -")
}
if($CreateShared -eq "y"){
  $SelectedReports = -join ($SelectedReports," Shared -")
}
if($CreateResources -eq "y"){
  $SelectedReports = -join ($SelectedReports," Resources -")
}
if($CreateContacts -eq "y"){
  $SelectedReports = -join ($SelectedReports," Contacts -")
}
if($CreateDomains -eq "y"){
  $SelectedReports = -join ($SelectedReports," Domains -")
}
if($CreateScripts -eq "y"){
  $SelectedReports = -join ($SelectedReports," Scripts -")
}
$FileName = "$TenantDisplay -$SelectedReports $Date1.xlsx"
$OutputPath = "$home\Desktop\$FileName"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
#Create the workbook
$workbook = $excel.Workbooks.Add()
$workbook.SaveAs($OutputPath)
$InitalWorksheet = $workbook.Worksheets.Item(1)
$InitalWorksheet.Name = "Generating"
#$Worksheet = $Workbook.Worksheets.Add()
#$Worksheet.Name = "Public Folders"
if($CreateScripts -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Scripts"
}
if($CreateDomains -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Domains"
}
if($CreateResources -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Resources"
}
if($CreateContacts -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Contacts"
}
if($CreateShared -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Shared Mailboxes"
}
if($CreateGroups -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Groups"
}
if($CreateUsers -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Licensed Mailboxes"
}
if($CreateUsers -eq "y"){
$Sheet_LicensedMailboxes = $Workbook.Worksheets.Item("Licensed Mailboxes")
}
if($CreateGroups -eq "y"){
$Sheet_Group = $Workbook.Worksheets.Item("Groups")
}
if($CreateShared -eq "y"){
$Sheet_SharedMailboxes = $Workbook.Worksheets.Item("Shared Mailboxes")
}
if($CreateContacts -eq "y"){
$Sheet_Contacts = $Workbook.Worksheets.Item("Contacts")
}
if($CreateDomains -eq "y"){
$Sheet_Domains = $Workbook.Worksheets.Item("Domains")
}
if($CreateResources -eq "y"){
$Sheet_Resources = $Workbook.Worksheets.Item("Resources")
}
if($CreateScripts -eq "y"){
$Sheet_Scripts = $Workbook.Worksheets.Item("Scripts")
}
$Workbook.Worksheets.Item("Generating").Delete()

#Licensed Mailbox Worksheet
if($CreateUsers -eq "y"){
$Sheet_LicensedMailboxes.Activate()
$Sheet_LicensedMailboxes.ActiveSheet
$row = 1
$Column = 1
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Licensed Users'
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Size = 26
#$Sheet_LicensedMailboxes.Cells.Item($row,$column).Font.Bold=$True
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_LicensedMailboxes.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_LicensedMailboxes.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
#Create a Title for the first worksheet and adjust the font
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 1
$row = 2
$Column = 1
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = '#'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'User Account'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Office 365 Licenses'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 4
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Primary E-Mail Address'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 5
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Alias E-Mail Addresses'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 6
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Mailbox Size'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 7
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Archive Size'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 8
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'OneDrive Size'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$Users = Get-Msoluser | Where-Object { $_.IsLicensed -eq $True } | Sort-Object DisplayName
$UsersCount = $Users.count
$Sheet_LicensedMailboxes_Y = 3
$RowNumber = 0
$LicenseName = @()
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Users. Please Wait"
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Start-Sleep -Seconds 1
$Counter = 0
Foreach ($User in $Users)
{
$Row = $Sheet_LicensedMailboxes_Y++
$DisplayName = ($User).DisplayName
$RowNumber++
$Counter++
$EmailAddresses = @()
$primaryemailaddress = try{($User | Select-Object -ExpandProperty proxyaddresses | Where-Object { $_ -cmatch '^SMTP' }).Trim("SMTP:")}catch{}
$EmailAddresses = @(($User | Select-Object -ExpandProperty proxyaddresses | Where-Object { $_ -cnotmatch "^SMTP:$primaryemailaddress" }) -replace "smtp:")
$TotalSize = (Get-MailboxStatistics $DisplayName -ErrorAction SilentlyContinue | Select-Object -Property TotalItemSize -ErrorAction SilentlyContinue | ft -hidetableheaders | Out-String).Trim()
if($CheckArchive -eq "y"){
$ArchiveCheck = (Get-MailboxStatistics -Archive $DisplayName -ErrorAction SilentlyContinue | Select-Object -Property TotalItemSize -ErrorAction SilentlyContinue | ft -hidetableheaders | Out-String).Trim()
}else{
  $ArchiveCheck = ""
}
$SignInName = ($User).SignInName

Write-Progress -Activity 'Processing Users' -Status "User $counter of $UsersCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $UsersCount) * 100)

#Write-Host "Processing $DisplayName..."
#Write-Host "User $Counter of $UsersCount"
#Write-Host ""
#Write-Host ""
if($CheckOneDrive -eq "y")
{
if($SignInName.Contains('@'))
{
$SignInName=$SignInName.Replace('@','_')
$SignInName=$SignInName.Replace('.','_')
$SignInName=$SignInName.Replace('.','_')
$SignInName="https://$tenantname-my.sharepoint.com/personal/"+$SignInName
$OneDriveTotal = try{(Get-SPOSite -Identity $SignInName -ErrorAction SilentlyContinue | Select-Object -expandproperty StorageUsageCurrent).ToString('N0')}catch{$OneDriveTotal = "0"}
$OneDriveSize = "$OneDriveTotal MB"
}
}else{
  $OneDriveSize = ""
}
#Lookup for friendly license name
$Licenses = (($User).Licenses).AccountSkuID
If (($Licenses).Count -gt 1)
{
Foreach ($License in $Licenses)
{
$LicenseItem = $License -split ":" | Select-Object -Last 1
$TextLic = $Sku.Item("$LicenseItem")
If (!($TextLic))
{
$fallback_Licenses = $LicenseItem
$LicenseName += $fallback_Licenses
}
Else
{
$LicenseName += $TextLic
}
}
}
Else
{
$LicenseItem = $Licenses -split ":" | Select-Object -Last 1
$TextLic = $Sku.Item("$LicenseItem")
If (!($TextLic))
{
$LicenseName = $LicenseItem
}
Else
{
$LicenseName = $TextLic
}
}
$LicenseName = ($LicenseName | Out-String).TrimEnd().Trim()
$AllEmailAddresses = ($EmailAddresses | Out-String).Trim().TrimEnd()
$Sheet_LicensedMailboxes.Cells.Item($Row, 1) = $RowNumber
$Sheet_LicensedMailboxes.Cells.Item($row, 1).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, 1).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, 1).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, 1).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, 1).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, 1).Font.ThemeFont = 1
$Even = $RowNumber % 2
If ($Even -eq 0)
{
$Sheet_LicensedMailboxes.Cells.Item($Row, 2) = $DisplayName
$Sheet_LicensedMailboxes.Cells.Item($row, 2).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($Row, 2).VerticalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($Row, 3) = $LicenseName
$Sheet_LicensedMailboxes.Cells.Item($row, 3).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($Row, 4) = $primaryemailaddress
$Sheet_LicensedMailboxes.Cells.Item($row, 4).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($Row, 5) = $AllEmailAddresses
$Sheet_LicensedMailboxes.Cells.Item($row, 5).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($Row, 6) = $TotalSize
$Sheet_LicensedMailboxes.Cells.Item($row, 6).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($Row, 7) = $ArchiveCheck
$Sheet_LicensedMailboxes.Cells.Item($row, 7).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($Row, 8) = $OneDriveSize
$Sheet_LicensedMailboxes.Cells.Item($row, 8).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 8).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 9).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 10).VerticalAlignment = -4160
$LicenseName = @()
}
Else
{
$Sheet_LicensedMailboxes.Cells.Item($Row, 2) = $DisplayName
$Sheet_LicensedMailboxes.Cells.Item($Row, 2).VerticalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, 2).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($Row, 3) = $LicenseName
$Sheet_LicensedMailboxes.Cells.Item($row, 3).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($Row, 4) = $primaryemailaddress
$Sheet_LicensedMailboxes.Cells.Item($row, 4).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($Row, 5) = $AllEmailAddresses
$Sheet_LicensedMailboxes.Cells.Item($row, 5).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($Row, 6) = $TotalSize
$Sheet_LicensedMailboxes.Cells.Item($row, 6).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($Row, 7) = $ArchiveCheck
$Sheet_LicensedMailboxes.Cells.Item($row, 7).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($Row, 8) = $OneDriveSize
$Sheet_LicensedMailboxes.Cells.Item($row, 8).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 8).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 9).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 10).VerticalAlignment = -4160
$LicenseName = @()
}
$Sheet_LicensedMailboxes.Columns.AutoFit() | Out-Null
$Sheet_LicensedMailboxes.Rows.AutoFit() | Out-Null
}
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateGroups -eq "y"){
#Groups Worksheet
$Sheet_Group.Activate()
$Sheet_Group.ActiveSheet
#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$Sheet_Group.Cells.Item($row, $column) = 'Groups'
$Sheet_Group.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Group.Cells.Item($row,$column).Font.Bold=$True
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Group.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_Group.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_Group.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
$row = 2
$Column = 1
$Sheet_Group.Cells.Item($row, $column) = '#'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_Group.Cells.Item($row, $column) = 'Group Name'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_Group.Cells.Item($row, $column) = 'Group Type'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 4
$Sheet_Group.Cells.Item($row, $column) = 'E-Mail Address'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 5
$Sheet_Group.Cells.Item($row, $column) = 'Alias Address'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 6
$Sheet_Group.Cells.Item($row, $column) = 'Sync Status'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 7
$Sheet_Group.Cells.Item($row, $column) = 'Members'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
if($CreateGroupScripts -eq "y"){
  $row = 2
  $Column = 8
  $Sheet_Group.Cells.Item($row, $column) = 'Creation Script'
  $Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
  $Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
  $Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
  $Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
  $Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
  $Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
  $row = 2
  $Column = 9
  $Sheet_Group.Cells.Item($row, $column) = 'Members Script'
  $Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
  $Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
  $Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
  $Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
  $Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
  $Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
  $row = 2
  $Column = 10
  $Sheet_Group.Cells.Item($row, $column) = 'Group Settings Script'
  $Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
  $Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
  $Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
  $Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
  $Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
  $Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
}
$Groups = Get-MsolGroup | Sort-Object DisplayName
$UnifiedGroups = Get-UnifiedGroup | Sort-Object DisplayName
$UGlist = @()
foreach($UnifiedGroup in $UnifiedGroups){
  $UGlist += @($UnifiedGroup.DisplayName)
}
$GroupsCount = $Groups.count
$Sheet_Groups_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Groups. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($Group in $Groups)
{
$RowNumber++
$Counter++
$Row = $Sheet_Groups_Y++
$DisplayName = ($Group).DisplayName
Write-Progress -Activity 'Processing Groups' -Status "Group $counter of $GroupsCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $GroupsCount) * 100)
if($UGlist -contains ($Group).DisplayName){
$GroupType = "UnifiedGroup"
}else{
$GroupType = ($Group).GroupType
}

$GroupEmail = ($Group).EmailAddress
$Members = (Get-AzureADGroupMember -ObjectId $Group.ObjectID -ErrorAction SilentlyContinue | Sort-Object DisplayName | Select-Object -ExpandProperty DisplayName) -join ", "
$GetGroupDirSyncStatus = Get-AzureADGroupMember -ObjectId $Group.ObjectID -ErrorAction SilentlyContinue | Select-Object DirectorySynchronizationEnabled
if($GetGroupDirSyncStatus -eq $null){
  $GroupDirSyncStatus = "Cloud"
}else{
  $GroupDirSyncStatus = "Synced"
}
$GroupTypeString = $GroupType | Out-String
$GroupTypeStringTrimEnd = $GroupTypeString.TrimEnd()
$Aliases = @()
$Aliases = @(($Group | Select-Object -ExpandProperty ProxyAddresses | Where-Object { $_ -cnotmatch "^SMTP:$GroupEmail"}) -replace "smtp:")

#Group Script Creation
if($CreateGroupScripts -eq "y"){
  $GroupCreate = ""
  $GTypeStandard = ""
  $GroupSettingsUpdate = ""
  $GroupMemberAdd = ""
  $GroupProxyAddresses = ""
  $IndvMemberList = (Get-AzureADGroupMember -ObjectId $Group.ObjectID -ErrorAction SilentlyContinue | Sort-Object DisplayName)
  if($GroupType -eq "MailEnabledSecurity"){$GTypeStandard = "Security"}
  if($GroupType -eq "Security"){$GTypeStandard = "Security"}
  if($GroupType -eq "DistributionList"){$GTypeStandard = "Distribution"}
  if($GroupType -eq "UnifiedGroup"){$GTypeStandard = "Office 365"}
  if($GroupType -ne "Security"){
    $GroupProxyAddresses = '"' + (($Group | Select-Object -Expand ProxyAddresses) -join '","') + '"'
    Foreach ($IndvMember in $IndvMemberList)
    {
    $IndvMemberCreate = @()
    $IndvMemberCreate = @(($IndvMember | Select-Object -ExpandProperty DisplayName))
    $GroupMemberAdd = -join ($GroupMemberAdd," Add-DistributionGroupMember -Identity ""$DisplayName"" -Member ""$IndvMemberCreate"";")
    }
    if($GTypeStandard -eq "Office 365"){
      $UGMembs = '"' + ((@(Get-AzureADGroupMember -ObjectId $Group.ObjectID -ErrorAction SilentlyContinue | Select-Object -expandproperty DisplayName)) -join '","') + '"'
      $UGAliasstring = $GroupEmail | Out-String
      $UGAlias = ($UGAliasstring).split('@')[0]
      $GroupCreate = "New-UnifiedGroup -Displayname ""$DisplayName"" -Alias ""$UGAlias"""
      $GroupMemberAdd = "Add-UnifiedGroupLinks -Identity ""$DisplayName"" -LinkType Members -Links $UGMembs"
      $GroupSettingsUpdate = ""
    }else{
    $GroupSenderAuth = (Get-DistributionGroup $DisplayName).RequireSenderAuthenticationEnabled
    $GroupCreate = "New-DistributionGroup -Name ""$DisplayName"" -Type ""$GTypeStandard"""
    $GroupSettingsUpdate = "Set-DistributionGroup -Identity ""$DisplayName"" -RequireSenderAuthenticationEnabled:"+"$"+"$GroupSenderAuth -EmailAddresses $GroupProxyAddresses"
    }
  }else{
    Foreach ($IndvMember in $IndvMemberList)
    {
    $IndvMemberCreate = @()
    $IndvMemberCreate = @(($IndvMember | Select-Object -ExpandProperty DisplayName))
    $GroupMemberAdd = -join ($GroupMemberAdd," Add-DistributionGroupMember -Identity ""$DisplayName"" -Member ""$IndvMemberCreate"";")
    }
    $GroupCreate = "New-DistributionGroup -Name ""$DisplayName"" -Type ""Security"""
  }

}

$Sheet_Group.Cells.Item($Row, 1) = $RowNumber
$Sheet_Group.Cells.Item($row, 1).Font.Bold = $True
$Sheet_Group.Cells.Item($row, 1).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, 1).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, 1).VerticalAlignment = -4108
$Sheet_Group.Cells.Item($row, 1).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, 1).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, 1).Font.ThemeFont = 1
$Even = $RowNumber % 2
If ($Even -eq 0)
{
$Sheet_Group.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_Group.Cells.Item($Row, 2) = $DisplayName
$Sheet_Group.Cells.Item($row, 2).Interior.ColorIndex = 15
$Sheet_Group.Cells.Item($Row, 3) = $GroupTypeStringTrimEnd
$Sheet_Group.Cells.Item($row, 3).Interior.ColorIndex = 15
$Sheet_Group.Cells.Item($Row, 4) = $GroupEmail
$Sheet_Group.Cells.Item($row, 4).Interior.ColorIndex = 15
$Sheet_Group.Cells.Item($Row, 5) = $Aliases
$Sheet_Group.Cells.Item($row, 5).Interior.ColorIndex = 15
$Sheet_Group.Cells.Item($Row, 6) = $GroupDirSyncStatus
$Sheet_Group.Cells.Item($row, 6).Interior.ColorIndex = 15
$Sheet_Group.Cells.Item($Row, 7) = $Members
$Sheet_Group.Cells.Item($row, 7).Interior.ColorIndex = 15
if($CreateGroupScripts -eq "y")
{
  $Sheet_Group.Cells.Item($Row, 8) = $GroupCreate
  $Sheet_Group.Cells.Item($row, 8).Interior.ColorIndex = 15
  $Sheet_Group.Cells.Item($Row, 9) = $GroupMemberAdd
  $Sheet_Group.Cells.Item($row, 9).Interior.ColorIndex = 15
  $Sheet_Group.Cells.Item($Row, 10) = $GroupSettingsUpdate
  $Sheet_Group.Cells.Item($row, 10).Interior.ColorIndex = 15
}
$Sheet_Group.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 8).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 9).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 10).VerticalAlignment = -4160
$Sheet_Group.Columns.Item(7).WrapText = $True
$Sheet_Group.Rows.AutoFit() | Out-Null
$Sheet_Group.Columns.AutoFit() | Out-Null
$Sheet_Group.Columns.Item(7).columnWidth = 75
$Sheet_Group.Columns.Item(8).columnWidth = 30
$Sheet_Group.Columns.Item(9).columnWidth = 30
$Sheet_Group.Columns.Item(10).columnWidth = 30
}
Else
{
$Sheet_Group.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_Group.Cells.Item($Row, 2) = $DisplayName
$Sheet_Group.Cells.Item($row, 2).Interior.ColorIndex = 2
$Sheet_Group.Cells.Item($Row, 3) = $GroupTypeStringTrimEnd
$Sheet_Group.Cells.Item($row, 3).Interior.ColorIndex = 2
$Sheet_Group.Cells.Item($Row, 4) = $GroupEmail
$Sheet_Group.Cells.Item($row, 4).Interior.ColorIndex = 2
$Sheet_Group.Cells.Item($Row, 5) = $Aliases
$Sheet_Group.Cells.Item($row, 5).Interior.ColorIndex = 2
$Sheet_Group.Cells.Item($Row, 6) = $GroupDirSyncStatus
$Sheet_Group.Cells.Item($row, 6).Interior.ColorIndex = 2
$Sheet_Group.Cells.Item($Row, 7) = $Members
$Sheet_Group.Cells.Item($row, 7).Interior.ColorIndex = 2
if($CreateGroupScripts -eq "y")
{
  $Sheet_Group.Cells.Item($Row, 8) = $GroupCreate
  $Sheet_Group.Cells.Item($row, 8).Interior.ColorIndex = 2
  $Sheet_Group.Cells.Item($Row, 9) = $GroupMemberAdd
  $Sheet_Group.Cells.Item($row, 9).Interior.ColorIndex = 2
  $Sheet_Group.Cells.Item($Row, 10) = $GroupSettingsUpdate
  $Sheet_Group.Cells.Item($row, 10).Interior.ColorIndex = 2
}
$Sheet_Group.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 8).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 9).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 10).VerticalAlignment = -4160
$Sheet_Group.Columns.Item(7).WrapText = $True
$Sheet_Group.Rows.AutoFit() | Out-Null
$Sheet_Group.Columns.AutoFit() | Out-Null
$Sheet_Group.Columns.Item(7).columnWidth = 75
$Sheet_Group.Columns.Item(8).columnWidth = 30
$Sheet_Group.Columns.Item(9).columnWidth = 30
$Sheet_Group.Columns.Item(10).columnWidth = 30
}
}
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateShared -eq "y"){
#Shared Mailboxes Sheet
$Sheet_SharedMailboxes.Activate()
$Sheet_SharedMailboxes.ActiveSheet
#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Shared Mailboxes'
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Size = 26
#$Sheet_SharedMailboxes.Cells.Item($row,$column).Font.Bold=$True
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_SharedMailboxes.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_SharedMailboxes.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
$row = 2
$Column = 1
$Sheet_SharedMailboxes.Cells.Item($row, $column) = '#'
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Shared Mailbox Name'
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Primary E-Mail Address'
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 4
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Alternate E-Mail Addresses'
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 5
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Mailbox Size'
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$SharedMailboxes = Get-mailbox -RecipientTypeDetails sharedmailbox -Resultsize unlimited | Sort-Object Name
$SharedCount = $SharedMailboxes.count
$Sheet_SharedMailboxes_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Shared Mailboxes. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($SharedMailbox in $SharedMailboxes)
{
$RowNumber++
$Counter++
$Row = $Sheet_SharedMailboxes_Y++
$DisplayName = ($SharedMailbox).Name
$PrimarySmtpAddress = ($SharedMailbox).PrimarySmtpAddress
$TotalSize = (Get-MailboxStatistics $DisplayName -ErrorAction SilentlyContinue | Select-Object -Property TotalItemSize -ErrorAction SilentlyContinue | ft -hidetableheaders | Out-String).Trim()
$SecondarySmtpAddresses = @()
$SecondarySmtpAddresses = @(($SharedMailbox | Select-Object -ExpandProperty EmailAddresses | Where-Object { $_ -cnotmatch "^SMTP:$PrimarySmtpAddress" }) -replace "smtp:")


Write-Progress -Activity 'Processing Shared Mailboxes' -Status "Mailbox $counter of $SharedCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $SharedCount) * 100)

$Sheet_SharedMailboxes.Cells.Item($Row, 1) = $RowNumber
$Sheet_SharedMailboxes.Cells.Item($row, 1).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($row, 1).Interior.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, 1).HorizontalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, 1).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, 1).Font.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, 1).Font.ThemeFont = 1
$Even = $RowNumber % 2
If ($Even -eq 0)
{
$Sheet_SharedMailboxes.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($Row, 2) = $DisplayName
$Sheet_SharedMailboxes.Cells.Item($row, 2).Interior.ColorIndex = 15
$Sheet_SharedMailboxes.Cells.Item($Row, 3) = $PrimarySmtpAddress
$Sheet_SharedMailboxes.Cells.Item($row, 3).Interior.ColorIndex = 15
$Sheet_SharedMailboxes.Cells.Item($Row, 4) = $SecondarySmtpAddresses
$Sheet_SharedMailboxes.Cells.Item($row, 4).Interior.ColorIndex = 15
$Sheet_SharedMailboxes.Cells.Item($Row, 5) = $TotalSize
$Sheet_SharedMailboxes.Cells.Item($row, 5).Interior.ColorIndex = 15
$Sheet_SharedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 7).VerticalAlignment = -4160

}
Else
{
$Sheet_SharedMailboxes.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($Row, 2) = $DisplayName
$Sheet_SharedMailboxes.Cells.Item($row, 2).Interior.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($Row, 3) = $PrimarySmtpAddress
$Sheet_SharedMailboxes.Cells.Item($row, 3).Interior.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($Row, 4) = $SecondarySmtpAddresses
$Sheet_SharedMailboxes.Cells.Item($row, 4).Interior.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($Row, 5) = $TotalSize
$Sheet_SharedMailboxes.Cells.Item($row, 5).Interior.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160

}
$Sheet_SharedMailboxes.Columns.AutoFit() | Out-Null
$Sheet_SharedMailboxes.Rows.AutoFit() | Out-Null
}
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateContacts -eq "y"){
#Contact Sheet
$Sheet_Contacts.Activate()
$Sheet_Contacts.ActiveSheet
#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$Sheet_Contacts.Cells.Item($row, $column) = 'Contacts'
$Sheet_Contacts.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Contacts.Cells.Item($row,$column).Font.Bold=$True
$Sheet_Contacts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Contacts.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Contacts.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_Contacts.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_Contacts.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
$row = 2
$Column = 1
$Sheet_Contacts.Cells.Item($row, $column) = '#'
$Sheet_Contacts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Contacts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Contacts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Contacts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Contacts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_Contacts.Cells.Item($row, $column) = 'Contact Name'
$Sheet_Contacts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Contacts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Contacts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Contacts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Contacts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_Contacts.Cells.Item($row, $column) = 'Primary E-Mail Address'
$Sheet_Contacts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Contacts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Contacts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Contacts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Contacts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeFont = 1
$Contacts = Get-Contact | Sort-Object DisplayName
$ContactsCount = $Contacts.count
$Sheet_Contacts_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Contacts. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($Contact in $Contacts)
{
$RowNumber++
$Counter++
$Row = $Sheet_Contacts_Y++
$DisplayName = ($Contact).DisplayName
$ContactEmail = ($Contact).WindowsEmailAddress
Write-Progress -Activity 'Processing Contacts' -Status "Contact $counter of $ContactsCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $ContactsCount) * 100)

$Sheet_Contacts.Cells.Item($Row, 1) = $RowNumber
$Sheet_Contacts.Cells.Item($row, 1).Font.Bold = $True
$Sheet_Contacts.Cells.Item($row, 1).Interior.ColorIndex = 55
$Sheet_Contacts.Cells.Item($row, 1).HorizontalAlignment = -4108
$Sheet_Contacts.Cells.Item($row, 1).VerticalAlignment = -4108
$Sheet_Contacts.Cells.Item($row, 1).Font.Name = "Cambria"
$Sheet_Contacts.Cells.Item($row, 1).Font.ColorIndex = 2
$Sheet_Contacts.Cells.Item($row, 1).Font.ThemeFont = 1
$Even = $RowNumber % 2
If ($Even -eq 0)
{
$Sheet_Contacts.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_Contacts.Cells.Item($Row, 2) = $DisplayName
$Sheet_Contacts.Cells.Item($row, 2).Interior.ColorIndex = 15
$Sheet_Contacts.Cells.Item($Row, 3) = $ContactEmail
$Sheet_Contacts.Cells.Item($row, 3).Interior.ColorIndex = 15
}
Else
{
$Sheet_Contacts.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_Contacts.Cells.Item($Row, 2) = $DisplayName
$Sheet_Contacts.Cells.Item($row, 2).Interior.ColorIndex = 2
$Sheet_COntacts.Cells.Item($Row, 3) = $ContactEmail
$Sheet_Contacts.Cells.Item($row, 3).Interior.ColorIndex = 2
}
$Sheet_Contacts.Columns.AutoFit() | Out-Null
$Sheet_Contacts.Rows.AutoFit() | Out-Null
}
Start-Sleep -Seconds 2
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateResources -eq "y"){
#Resources Sheet
$Sheet_Resources.Activate()
$Sheet_Resources.ActiveSheet
#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$Sheet_Resources.Cells.Item($row, $column) = 'Resource Mailboxes'
$Sheet_Resources.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Resources.Cells.Item($row,$column).Font.Bold=$True
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_Resources.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_Resources.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
$row = 2
$Column = 1
$Sheet_Resources.Cells.Item($row, $column) = '#'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_Resources.Cells.Item($row, $column) = 'Resource Name'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_Resources.Cells.Item($row, $column) = 'Primary E-Mail Address'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 4
$Sheet_Resources.Cells.Item($row, $column) = 'Alternate Addresses'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 5
$Sheet_Resources.Cells.Item($row, $column) = 'Details'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$Resources = Get-Mailbox -ResultSize Unlimited -Filter '(RecipientTypeDetails -eq "RoomMailBox")' | Sort-Object Name
$ResourcesCount = $Resources.count
$Sheet_Resources_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "=================================================="
Write-Host "Processing Resource & Room Mailboxes. Please Wait."
Write-Host "=================================================="
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($Room in $Resources)
{
$RowNumber++
$Counter++
$Row = $Sheet_Resources_Y++
$DisplayName = ($Room).Name
$PrimarySmtpAddress = ($Room).PrimarySmtpAddress
$TotalSize = (Get-MailboxStatistics $DisplayName -ErrorAction SilentlyContinue | Select-Object -Property TotalItemSize -ErrorAction SilentlyContinue | ft -hidetableheaders | Out-String).Trim()
$SecondarySmtpAddresses = @()
$SecondarySmtpAddresses = @(($Room | Select-Object -ExpandProperty EmailAddresses | Where-Object { $_ -cnotmatch "^SMTP:$PrimarySmtpAddress" }) -replace "smtp:")
Write-Progress -Activity 'Processing Resource & Room Mailboxes' -Status "Resource $counter of $ResourcesCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $ResourcesCount) * 100)

$Details = ""
$Details = (Get-CalendarProcessing $PrimarySmtpAddress | fl | Out-String).Trim()

$Sheet_Resources.Cells.Item($Row, 1) = $RowNumber
$Sheet_Resources.Cells.Item($row, 1).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, 1).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, 1).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, 1).VerticalAlignment = -4108
$Sheet_Resources.Cells.Item($row, 1).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, 1).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, 1).Font.ThemeFont = 1
$Even = $RowNumber % 2
If ($Even -eq 0)
{
$Sheet_Resources.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_Resources.Cells.Item($Row, 2) = $DisplayName
$Sheet_Resources.Cells.Item($row, 2).Interior.ColorIndex = 15
$Sheet_Resources.Cells.Item($Row, 3) = $PrimarySmtpAddress
$Sheet_Resources.Cells.Item($row, 3).Interior.ColorIndex = 15
$Sheet_Resources.Cells.Item($Row, 4) = $SecondarySmtpAddresses
$Sheet_Resources.Cells.Item($row, 4).Interior.ColorIndex = 15
$Sheet_Resources.Cells.Item($Row, 5) = $Details
$Sheet_Resources.Cells.Item($row, 5).Interior.ColorIndex = 15
$Sheet_Resources.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_Resources.Rows.Item($row).RowHeight = 25
}
Else
{
$Sheet_Resources.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_Resources.Cells.Item($Row, 2) = $DisplayName
$Sheet_Resources.Cells.Item($row, 2).Interior.ColorIndex = 2
$Sheet_Resources.Cells.Item($Row, 3) = $PrimarySmtpAddress
$Sheet_Resources.Cells.Item($row, 3).Interior.ColorIndex = 2
$Sheet_Resources.Cells.Item($Row, 4) = $SecondarySmtpAddresses
$Sheet_Resources.Cells.Item($row, 4).Interior.ColorIndex = 2
$Sheet_Resources.Cells.Item($Row, 5) = $Details
$Sheet_Resources.Cells.Item($row, 5).Interior.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_Resources.Rows.Item($row).RowHeight = 25
}
$Sheet_Resources.Columns.AutoFit() | Out-Null
$Sheet_Resources.Columns.Item(5).columnWidth = 57
$Sheet_Resources.Columns.Item(4).columnWidth = 57
}
Start-Sleep -Seconds 2
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateDomains -eq "y"){
#Domain Sheet
$Sheet_Domains.Activate()
$Sheet_Domains.ActiveSheet
#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$Sheet_Domains.Cells.Item($row, $column) = 'Domains'
$Sheet_Domains.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Group.Cells.Item($row,$column).Font.Bold=$True
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_Domains.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_Domains.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
$row = 2
$Column = 1
$Sheet_Domains.Cells.Item($row, $column) = '#'
$Sheet_Domains.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_Domains.Cells.Item($row, $column) = 'Domain Name'
$Sheet_Domains.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_Domains.Cells.Item($row, $column) = 'Default'
$Sheet_Domains.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 4
$Sheet_Domains.Cells.Item($row, $column) = 'Verified'
$Sheet_Domains.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1
$Domains = Get-MsolDomain | Sort-Object Name
$Domainscount = $Domains.count
$Sheet_Domains_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Domains. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($Domain in $Domains)
{
$Row = $Sheet_Domains_Y++
$RowNumber++
$Counter++
$DisplayName = ($Domain).Name
$DefaultStatus = ($Domain).IsDefault
$Verified = (($Domain).Status | Out-String).TrimEnd()
Write-Progress -Activity 'Processing Domains' -Status "Domain $counter of $SharedCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $Domainscount) * 100)

If ($DefaultStatus -eq $True)
{
$Sheet_Domains.Cells.Item($Row, 1) = $RowNumber
$Sheet_Domains.Cells.Item($row, 1).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, 1).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, 1).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, 1).VerticalAlignment = -4108
$Sheet_Domains.Cells.Item($row, 1).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, 1).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, 1).Font.ThemeFont = 1
$Sheet_Domains.Cells.Item($Row, 2) = $DisplayName
$Sheet_Domains.Cells.Item($Row, 3) = $DefaultStatus
$Sheet_Domains.Cells.Item($Row, 4) = $Verified
$Sheet_Domains.Cells.Item($Row, 2).Font.Bold = $True
$Sheet_Domains.Cells.Item($Row, 3).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, 2).Interior.ColorIndex = 6
$Sheet_Domains.Cells.Item($row, 3).Interior.ColorIndex = 6
$Sheet_Domains.Cells.Item($row, 4).Interior.ColorIndex = 6
}
Else
{
$Sheet_Domains.Cells.Item($Row, 1) = $RowNumber
$Sheet_Domains.Cells.Item($row, 1).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, 1).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, 1).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, 1).VerticalAlignment = -4108
$Sheet_Domains.Cells.Item($row, 1).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, 1).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, 1).Font.ThemeFont = 1
$Even = $RowNumber % 2
If ($Even -eq 0)
{
$Sheet_Domains.Cells.Item($Row, 2) = $DisplayName
$Sheet_Domains.Cells.Item($row, 2).Interior.ColorIndex = 15
$Sheet_Domains.Cells.Item($Row, 3) = $DefaultStatus
$Sheet_Domains.Cells.Item($row, 3).Interior.ColorIndex = 15
$Sheet_Domains.Cells.Item($Row, 4) = $Verified
$Sheet_Domains.Cells.Item($row, 4).Interior.ColorIndex = 15
}
Else
{
$Sheet_Domains.Cells.Item($Row, 2) = $DisplayName
$Sheet_Domains.Cells.Item($row, 2).Interior.ColorIndex = 2
$Sheet_Domains.Cells.Item($Row, 3) = $DefaultStatus
$Sheet_Domains.Cells.Item($row, 3).Interior.ColorIndex = 2
$Sheet_Domains.Cells.Item($Row, 4) = $Verified
$Sheet_Domains.Cells.Item($row, 4).Interior.ColorIndex = 2
}
$Sheet_Domains.Cells.Item($Row, 2).Font.Bold = $False
$Sheet_Domains.Cells.Item($Row, 3).Font.Bold = $False
}
$Sheet_Domains.Columns.AutoFit() | Out-Null
$Sheet_Domains.Rows.AutoFit() | Out-Null
}
Start-Sleep -Seconds 1
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 1
}


if($CreateScripts -eq "y"){
#Scripts Worksheet
$Sheet_Scripts.Activate()
$Sheet_Scripts.ActiveSheet
$row = 1
$Column = 1
$Sheet_Scripts.Cells.Item($row, $column) = 'Scripts'
$Sheet_Scripts.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Scripts.Cells.Item($row, $column).Font.Bold=$True
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_Scripts.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_Scripts.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
#Create a Title for the first worksheet and adjust the font

$row = 2
$Column = 1
$Sheet_Scripts.Cells.Item($row, $column) = '#'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_Scripts.Cells.Item($row, $column) = 'User Display Name'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_Scripts.Cells.Item($row, $column) = 'Mailbox Creation Script'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 4
$Sheet_Scripts.Cells.Item($row, $column) = 'UPN And Settings Script'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 5
$Sheet_Scripts.Cells.Item($row, $column) = 'Mailbox Permissions Script'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 6
$Sheet_Scripts.Cells.Item($row, $column) = 'Additional Script'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Scripts.Columns.AutoFit() | Out-Null
$Sheet_Scripts.Rows.AutoFit() | Out-Null
$Mailboxes = Get-Mailbox -ResultSize Unlimited -ErrorAction SilentlyContinue | Sort-Object Name
$MailboxesCount = $Mailboxes.Count
$Sheet_Scripts_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Generating Scripts. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Write-Progress -Activity 'Generating Scripts' -Status "Please Wait"
Start-Sleep -Seconds 2
Foreach ($Mailbox in $Mailboxes)
{
  $RowNumber++
  $Counter++
  $Row = $Sheet_Scripts_Y++
  $DisplayName = ($Mailbox).DisplayName
  Write-Progress -Activity 'Generating Scripts' -Status "User Script $counter of $MailboxesCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $MailboxesCount) * 100)

#Mailbox Properties
  $MailboxProp = Get-Mailbox $Displayname -ErrorAction SilentlyContinue
  $MailboxPropString = ""
    $curUPN = ""
    $MailPropPrimary  = $null
    $DeliverForward  = $null
    $LitHold  = $null
    $ForwardingAddress  = $null
    $ForwardingSmtpAddress  = $null
    $MailPropUPN  = $null
    $WinLiveID  = $null
    $MSOSID  = $null
    $ArchiveStatus  = $null
    $MailPropAlias  = $null
    $MailPropSecondaryAddresses  = $null
    $MailPropShared  = $null
    $MailPropRecipientType  = $null
    $MailPropRecipientTypeDetails  = $null
    $ResourceType  = $null
    $RoomEnabled  = $null
    $MailPropPrimary = ($MailboxProp).PrimarySmtpAddress
    $DeliverForward = ($MailboxProp).DeliverToMailboxAndForward
    $LitHold = ($MailboxProp).LitigationHoldEnabled
    $ForwardingAddress = ($MailboxProp).ForwardingAddress
    $ForwardingSmtpAddress = ($MailboxProp).ForwardingSmtpAddress
    $MailPropUPN = ($MailboxProp).UserPrincipalName
    $MailPropUPNAlias = ($MailboxProp).UserPrincipalName.split('@')[0]
    $WinLiveID = ($MailboxProp).WindowsLiveID
    $MSOSID = ($MailboxProp).MicrosoftOnlineServicesID
    $ArchiveStatus = ($MailboxProp).ArchiveStatus
    $ArchiveName = ($MailboxProp).ArchiveName
    $MailPropAlias = ($MailboxProp).Alias
    $MailPropSecondaryAddresses = '"' + ((Get-Mailbox $DisplayName | Select-Object -Expand EmailAddresses) -join '","') + '"'
    $MailPropShared = ($MailboxProp).IsShared
    $MailPropRecipientType = ($MailboxProp).RecipientType
    $MailPropRecipientTypeDetails = ($MailboxProp).RecipientTypeDetails
    $GrantSendOnBehalfTo = ($MailboxProp).GrantSendOnBehalfTo
    $ResourceType = ($MailboxProp).ResourceType
    $RoomEnabled = ($MailboxProp).RoomMailboxAccountEnabled

$MailboxPropString = "Set-Variable -Name ""curUPN"" -Value ((Get-MsolUser | Where-Object {"+"$"+"_"+".DisplayName -eq ""$DisplayName""} | Select UserPrincipalName | ft -hide | Out-String).Trim()); Set-Mailbox -Identity ""$DisplayName"""
  If (!$DeliverForward) { } else { $MailboxPropString = -join ($MailboxPropString," ","-DeliverToMailboxAndForward ",$DeliverForward) }
  If (!$LitHold) { } else { $MailboxPropString = -join ($MailboxPropString," ","-LitigationHoldEnabled ",$LitHold) }
  If (!$ForwardingAddress) { } else { $MailboxPropString = -join ($MailboxPropString," ","-ForwardingAddress ",$ForwardingAddress) }
  If (!$ForwardingSmtpAddress) { } else { $MailboxPropString = -join ($MailboxPropString," ","-ForwardingSmtpAddress ",$ForwardingSmtpAddress) }
  If (!$MailPropAlias) { } else { $MailboxPropString = -join ($MailboxPropString," ","-Alias ",$MailPropAlias) }
  If (!$MailPropSecondaryAddresses) { } else { $MailboxPropString = -join ($MailboxPropString, " ","-EmailAddresses @{Add=",$MailPropSecondaryAddresses,"}") }
  If (!$MailPropPrimary) { } else { $MailboxPropString = -join ($MailboxPropString, " ","-WindowsEmailAddress ",$MailPropPrimary) }
  If (!$MailPropShared) { } else { $MailboxPropString = -join ($MailboxPropString," ","-IsShared ",$MailPropShared) }
  If (!$ResourceType) { } else { $MailboxPropString = -join ($MailboxPropString," ","-ResourceType ",$ResourceType) }
$MailboxPropString = -join ($MailboxPropString,"; ","Set-MsolUserPrincipalName -UserPrincipalName $"+"curUPN -NewUserPrincipalName ""$MailPropPrimary""")

  #Room Mailbox Settings
  If($MailPropRecipientTypeDetails -eq "RoomMailbox")
  {
    $CalProc = Get-CalendarProcessing $Displayname -ErrorAction SilentlyContinue
    $CalAutomateProcessing = ($CalProc).AutomateProcessing
    $CalAllowConflicts = ($CalProc).AllowConflicts
    $CalBookingWindowInDays = ($CalProc).BookingWindowInDays
    $CalMaximumDurationInMinutes = ($CalProc).MaximumDurationInMinutes
    $CalAllowRecurringMeetings = ($CalProc).AllowRecurringMeetings
    $CalScheduleOnlyDuringWorkHours = ($CalProc).ScheduleOnlyDuringWorkHours
    $CalConflictPercentageAllowed = ($CalProc).ConflictPercentageAllowed
    $CalMaximumConflictInstances = ($CalProc).MaximumConflictInstances
    $CalForwardRequestsToDelegates = ($CalProc).ForwardRequestsToDelegates
    $CalDeleteAttachments = ($CalProc).DeleteAttachments
    $CalDeleteComments = ($CalProc).DeleteComments
    $CalRemovePrivateProperty = ($CalProc).RemovePrivateProperty
    $CalDeleteSubject = ($CalProc).DeleteSubject
    $CalAddOrganizerToSubject = ($CalProc).AddOrganizerToSubject
    $RoomCalProcSet = "Set-CalendarProcessing ""$DisplayName"" -AutomateProcessing $CalAutomateProcessing -AllowConflicts $CalAllowConflicts -BookingWindowInDays $CalBookingWindowInDays -MaximumDurationInMinutes $CalMaximumDurationInMinutes -AllowRecurringMeetings $CalAllowRecurringMeetings -ScheduleOnlyDuringWorkHours $CalScheduleOnlyDuringWorkHours -ConflictPercentageAllowed $CalConflictPercentageAllowed -MaximumConflictInstances $CalMaximumConflictInstances -ForwardRequestsToDelegates $CalForwardRequestsToDelegates -DeleteAttachments $CalDeleteAttachments -DeleteComments $CalDeleteComments -RemovePrivateProperty $CalRemovePrivateProperty -DeleteSubject $CalDeleteSubject -AddOrganizerToSubject $CalAddOrganizerToSubject"
    $MailboxPropString = -join ($MailboxPropString,"; ",$RoomCalProcSet)
  }

  #Mailbox Permissions
  $Permissions = Get-MailboxPermission $DisplayName -ErrorAction SilentlyContinue | Where {$_.user.tostring() -ne "NT AUTHORITY\SELF" -and $_.IsInherited -eq $False}
  $PermArray = ""
  Foreach ($Permission in $Permissions)
  {
  $AccessRightsArray = @()
  $AccessRightsArray = @(($Permission | Select-Object -ExpandProperty AccessRights))
  $PermissionUser = ($Permission | Select-Object -ExpandProperty User)
  $PermArray = -join ($PermArray," Add-MailboxPermission -Identity ""$DisplayName"" -User $PermissionUser -AccessRights $AccessRightsArray -InheritanceType All -Confirm:"+"$"+"False);")
  }
  $RecipientPermissions = Get-RecipientPermission $DisplayName -ErrorAction SilentlyContinue | Where {$_.trustee.tostring() -ne "NT AUTHORITY\SELF" -and $_.IsInherited -eq $False}
  $RecipPermArray = ""
  Foreach ($RecipPermission in $RecipientPermissions)
  {
  $RecipAccessRightsArray = @()
  $RecipAccessRightsArray = @(($RecipPermission | Select-Object -ExpandProperty AccessRights))
  $Trustee = ($RecipPermission | Select-Object -ExpandProperty Trustee)
  $PermArray = -join ($PermArray," Add-RecipientPermission ""$DisplayName"" -Trustee $PermissionUser -AccessRights $RecipAccessRightsArray -Confirm:"+"$"+"False);")
  }

#Create Mailboxes
  $CreateUser = Get-User $DisplayName
  $CreateFirstName = ($CreateUser).FirstName
  $CreateLastName = ($CreateUser).LastName
  If($MailPropRecipientTypeDetails -eq "SharedMailbox")
  {
  $MailboxCreationScript = ""
  $MailboxCreationScript =    "New-Mailbox -Alias $MailPropAlias -Name ""$displayname"" -Shared -PrimarySmtpAddress $MailPropAlias@$MigrationOMSFT.onmicrosoft.com"
  }else
  {
    if($MailPropRecipientTypeDetails -eq "RoomMailbox")
    {
      $MailboxCreationScript = ""
      $MailboxCreationScript =    "New-Mailbox -Alias $MailPropAlias -Name ""$displayname"" -Room -PrimarySmtpAddress $MailPropAlias@$MigrationOMSFT.onmicrosoft.com"
    }Else
    {
      $MailboxCreationScript = ""
      $MailboxCreationScript = "New-Mailbox -Alias $MailPropAlias -Name ""$DisplayName"" -FirstName ""$CreateFirstName"" -LastName ""$CreateLastName"" -DisplayName ""$DisplayName"" -MicrosoftOnlineServicesID $MailPropUPNAlias@$MigrationOMSFT.onmicrosoft.com -Password $($SecurePassword) -ResetPasswordOnNextLogon $($false)"
    }
  }
  $Sheet_Scripts.Cells.Item($Row, 1) = $RowNumber
  $Sheet_Scripts.Cells.Item($row, 1).Font.Bold = $True
  $Sheet_Scripts.Cells.Item($row, 1).Interior.ColorIndex = 55
  $Sheet_Scripts.Cells.Item($row, 1).HorizontalAlignment = -4108
  $Sheet_Scripts.Cells.Item($row, 1).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($row, 1).Font.Name = "Cambria"
  $Sheet_Scripts.Cells.Item($row, 1).Font.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($row, 1).Font.ThemeFont = 1
  $Even = $RowNumber % 2
  If ($Even -eq 0)
  {
  $Sheet_Scripts.Cells.Item($Row, 2) = $DisplayName
  $Sheet_Scripts.Cells.Item($row, 2).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 2).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 3) = $MailboxCreationScript
  $Sheet_Scripts.Cells.Item($row, 3).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 3).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 4) = $MailboxPropString
  $Sheet_Scripts.Cells.Item($row, 4).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 4).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 5) = $PermArray
  $Sheet_Scripts.Cells.Item($row, 5).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 5).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 6) = $AdditionalScripts
  $Sheet_Scripts.Cells.Item($row, 6).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 6).VerticalAlignment = -4108
}
Else{
  $Sheet_Scripts.Cells.Item($Row, 2) = $DisplayName
  $Sheet_Scripts.Cells.Item($row, 2).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 2).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 3) = $MailboxCreationScript
  $Sheet_Scripts.Cells.Item($row, 3).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 3).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 4) = $MailboxPropString
  $Sheet_Scripts.Cells.Item($row, 4).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 4).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 5) = $PermArray
  $Sheet_Scripts.Cells.Item($row, 5).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 5).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 6) = $AdditionalScripts
  $Sheet_Scripts.Cells.Item($row, 6).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 6).VerticalAlignment = -4108
}
}
}
Clear-Host
Write-Progress -Activity 'Finalizing Report' -Status "Please Wait"
Start-Sleep -Seconds 2


$Workbook.Sheets(1).Select()
$excel.DisplayAlerts = $False
$workbook.SaveAs($OutputPath)
$excel.DisplayAlerts = $True
Start-Sleep -Seconds 2
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "==================================================================="
Write-Host "==================================================================="
Write-Host "Report Completed!"
Write-Host ""
Write-Host "Saved to Desktop as:"
Write-Host "''$FileName'"
Write-Host "==================================================================="
Write-Host "==================================================================="
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
}
function Get-MFAStatus
{
#Description: Generate report of users and their MFA status
Clear-Host
Write-Host "" -ForegroundColor Yellow
Write-Host "===============================MFA Status Report===============================" -ForegroundColor Yellow
Write-Host ""
Write-Host " Starting MFA Report generation..." -ForegroundColor Yellow
Write-Host ""
Write-Host "===============================================================================" -ForegroundColor Yellow
Start-Sleep -seconds 2

try
{ $var = Get-AzureADTenantDetail }

catch [Microsoft.Open.Azure.AD.CommonLibrary.AadNeedAuthenticationException]
{
Clear-Host
Write-Host "" -ForegroundColor Yellow
Write-Host "===============================MFA Status Report===============================" -ForegroundColor Yellow
Write-Host " You must be connected to Office 365 MSOnline and Exchange for this report!" -ForegroundColor Yellow
Write-Host "" -ForegroundColor Yellow
Write-Host " Run Connect-Office365 and selection options 2 and 3" -ForegroundColor Yellow
Write-Host "===============================================================================" -ForegroundColor Yellow
Read-Host " Press any key to continue"
break
}
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================" -ForegroundColor Yellow
Write-Host "Generating Report. Please Wait" -ForegroundColor Yellow
Write-Host "============================================" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Progress -Activity 'Creating Report File!' -Status "Please wait."
Start-Sleep -Seconds 2
$TenantDisplay = Get-AzureAdTenantDetail | Select -ExpandProperty DisplayName
$Date1 = Get-Date -Format "MM-dd-yyyy HH-mm"
$FileName = "$TenantDisplay - MFA Status - $Date1.xlsx"
$OutputPath = "$home\Desktop\$FileName"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
#Create the workbook
$workbook = $excel.Workbooks.Add()
$workbook.SaveAs($OutputPath)
$InitalWorksheet = $workbook.Worksheets.Item(1)
$InitalWorksheet.Name = "MFAStatus"
#$Worksheet = $Workbook.Worksheets.Add()
#$Worksheet.Name = "Public Folders"
#$Worksheet = $Workbook.Worksheets.Add()
#$Worksheet.Name = "MFAStatus"
$Sheet_MFAStatus = $Workbook.Worksheets.Item("MFAStatus")



#MFA Status Sheet
$Sheet_MFAStatus.Activate()
$Sheet_MFAStatus.ActiveSheet
#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$Sheet_MFAStatus.Cells.Item($row, $column) = 'MFA Status'
$Sheet_MFAStatus.Cells.Item($row, $column).Font.Size = 26
#$Sheet_MFAStatus.Cells.Item($row,$column).Font.Bold=$True
$Sheet_MFAStatus.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_MFAStatus.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_MFAStatus.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_MFAStatus.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
$row = 2
$Column = 1
$Sheet_MFAStatus.Cells.Item($row, $column) = '#'
$Sheet_MFAStatus.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_MFAStatus.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_MFAStatus.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_MFAStatus.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_MFAStatus.Cells.Item($row, $column) = 'Display Name'
$Sheet_MFAStatus.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_MFAStatus.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_MFAStatus.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_MFAStatus.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_MFAStatus.Cells.Item($row, $column) = 'User Principal Name'
$Sheet_MFAStatus.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_MFAStatus.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_MFAStatus.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_MFAStatus.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 4
$Sheet_MFAStatus.Cells.Item($row, $column) = 'MFA Enforced'
$Sheet_MFAStatus.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_MFAStatus.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_MFAStatus.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_MFAStatus.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 5
$Sheet_MFAStatus.Cells.Item($row, $column) = 'Auth Phone Number'
$Sheet_MFAStatus.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_MFAStatus.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_MFAStatus.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_MFAStatus.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 6
$Sheet_MFAStatus.Cells.Item($row, $column) = 'Auth Methods (Not Always Accurate)'
$Sheet_MFAStatus.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_MFAStatus.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_MFAStatus.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_MFAStatus.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($row, $column).Font.ThemeFont = 1

$UserMailboxes = Get-Mailbox | Where {$_.RecipientTypeDetails -eq "UserMailbox"} | Sort-Object -Property DisplayName
$UserArrayEnabled = @()
$UserArrayDisabled = @()
$UsersCount = $UserMailboxes.count
$counter = 0
ForEach ($user in $UserMailboxes){
    $UDN = $user.DisplayName
    $Counter++
    Write-Progress -Activity 'Processing Users' -Status "User $counter of $UsersCount" -CurrentOperation "$UDN" -PercentComplete (($Counter / $UsersCount) * 100)
    $OID = $user.ExternalDirectoryObjectID
    $Msol = Get-MsolUser -ObjectID $OID -ErrorAction SilentlyContinue | Where {$_.IsLicensed -eq $true} | Where {$_.UserType -ne 'Guest'}
    $Methods = @()
    ForEach ($method in $Msol.StrongAuthenticationmethods){
        $Type = $method.MethodType
        if($method.IsDefault -eq $true){$Default = " (Default)"}else{$Default = ""}
        $Methods += "$Type$Default"
        }
        
    $item = New-Object PSObject -Property @{
        DisplayName = $Msol.DisplayName;
        UPN = $Msol.UserPrincipalName;
        AuthPhone = $Msol.StrongAuthenticationUserDetails.PhoneNumber;
        OTPDevice = $Methods -join ", ";
        Status = if($Msol.StrongAuthenticationRequirements.State -ne $null){$Msol.StrongAuthenticationRequirements.State} else {"Disabled"}
        }
        
    if($Msol.StrongAuthenticationRequirements.State -ne $null){
        $UserArrayEnabled += $item
    }else{
        $UserArrayDisabled += $item
    }
}

$UserArray = $UserArrayEnabled + $UserArrayDisabled
$Sheet_MFAStatus_Y = 3
$RowNumber = 0
$Counter = 0

ForEach ($item in $UserArray){
        $RowNumber++
        $Counter++
        $Row = $Sheet_MFAStatus_Y++
        $DisplayName = $item.DisplayName
        $UPN = $item.UPN
        $AuthPhone = $item.AuthPhone
        $OTPDevice = $item.OTPDevice
        $Status = $item.Status
 Write-Progress -Activity 'Writing to Report' -Status "User $counter of $UsersCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $UsersCount) * 100)

        

$Sheet_MFAStatus.Cells.Item($Row, 1) = $RowNumber
$Sheet_MFAStatus.Cells.Item($row, 1).Font.Bold = $True
$Sheet_MFAStatus.Cells.Item($row, 1).Interior.ColorIndex = 55
$Sheet_MFAStatus.Cells.Item($row, 1).HorizontalAlignment = -4108
$Sheet_MFAStatus.Cells.Item($row, 1).VerticalAlignment = -4108
$Sheet_MFAStatus.Cells.Item($row, 1).Font.Name = "Cambria"
$Sheet_MFAStatus.Cells.Item($row, 1).Font.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($row, 1).Font.ThemeFont = 1
$Even = $RowNumber % 2
If ($Even -eq 0)
{
$Sheet_MFAStatus.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_MFAStatus.Cells.Item($Row, 2) = $DisplayName
$Sheet_MFAStatus.Cells.Item($row, 2).Interior.ColorIndex = 15
$Sheet_MFAStatus.Cells.Item($Row, 3) = $UPN
$Sheet_MFAStatus.Cells.Item($row, 3).Interior.ColorIndex = 15
$Sheet_MFAStatus.Cells.Item($Row, 4) = $Status
$Sheet_MFAStatus.Cells.Item($row, 4).Interior.ColorIndex = 15
$Sheet_MFAStatus.Cells.Item($Row, 5) = $AuthPhone
$Sheet_MFAStatus.Cells.Item($row, 5).Interior.ColorIndex = 15
$Sheet_MFAStatus.Cells.Item($Row, 6) = $OTPDevice
$Sheet_MFAStatus.Cells.Item($row, 6).Interior.ColorIndex = 15
}
Else
{
$Sheet_MFAStatus.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_MFAStatus.Cells.Item($Row, 2) = $DisplayName
$Sheet_MFAStatus.Cells.Item($row, 2).Interior.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($Row, 3) = $UPN
$Sheet_MFAStatus.Cells.Item($row, 3).Interior.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($Row, 4) = $Status
$Sheet_MFAStatus.Cells.Item($row, 4).Interior.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($Row, 5) = $AuthPhone
$Sheet_MFAStatus.Cells.Item($row, 5).Interior.ColorIndex = 2
$Sheet_MFAStatus.Cells.Item($Row, 6) = $OTPDevice
$Sheet_MFAStatus.Cells.Item($row, 6).Interior.ColorIndex = 2
}

$Sheet_MFAStatus.Columns.AutoFit() | Out-Null
$Sheet_MFAStatus.Rows.AutoFit() | Out-Null
}

$Workbook.Sheets(1).Select()
$excel.DisplayAlerts = $False
$workbook.SaveAs($OutputPath)
$excel.DisplayAlerts = $True
Start-Sleep -Seconds 2
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "===================================================================" -ForegroundColor Yellow
Write-Host "===================================================================" -ForegroundColor Yellow
Write-Host "Report Completed!" -ForegroundColor Yellow
Write-Host ""
Write-Host "Saved to Desktop as:" -ForegroundColor Yellow
Write-Host "'$FileName'" -ForegroundColor Yellow
Write-Host "===================================================================" -ForegroundColor Yellow
Write-Host "===================================================================" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
}
function New-PartnerUserReport
{
#Description: Report on user across Partner Tenants
    $Sku = @{
        "O365_BUSINESS_ESSENTIALS"      = "Office 365 Business Essentials"
        "O365_BUSINESS_PREMIUM"      = "Office 365 Business Premium"
        "DESKLESSPACK"      = "Office 365 (Plan K1)"
        "DESKLESSWOFFPACK"      = "Office 365 (Plan K2)"
        "LITEPACK"      = "Office 365 (Plan P1)"
        "EXCHANGESTANDARD"      = "Office 365 Exchange Online Only"
        "STANDARDPACK"      = "Enterprise Plan E1"
        "STANDARDWOFFPACK"      = "Office 365 (Plan E2)"
        "ENTERPRISEPACK" = "Enterprise Plan E3"
        "ENTERPRISEPACKLRG"      = "Enterprise Plan E3"
        "ENTERPRISEWITHSCAL" = "Enterprise Plan E4"
        "STANDARDPACK_STUDENT"      = "Office 365 (Plan A1) for Students"
        "STANDARDWOFFPACKPACK_STUDENT"      = "Office 365 (Plan A2) for Students"
        "ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students"
        "ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students"
        "STANDARDPACK_FACULTY"      = "Office 365 (Plan A1) for Faculty"
        "STANDARDWOFFPACKPACK_FACULTY"      = "Office 365 (Plan A2) for Faculty"
        "ENTERPRISEPACK_FACULTY" = "Office 365 (Plan A3) for Faculty"
        "ENTERPRISEWITHSCAL_FACULTY" = "Office 365 (Plan A4) for Faculty"
        "ENTERPRISEPACK_B_PILOT" = "Office 365 (Enterprise Preview)"
        "STANDARD_B_PILOT"      = "Office 365 (Small Business Preview)"
        "VISIOCLIENT"      = "Visio Pro Online"
        "POWER_BI_ADDON" = "Office 365 Power BI Addon"
        "POWER_BI_INDIVIDUAL_USE"      = "Power BI Individual User"
        "POWER_BI_STANDALONE"      = "Power BI Stand Alone"
        "POWER_BI_STANDARD"      = "Power-BI Standard"
        "PROJECTESSENTIALS"      = "Project Lite"
        "PROJECTCLIENT"      = "Project Professional"
        "PROJECTONLINE_PLAN_1"      = "Project Online"
        "PROJECTONLINE_PLAN_2"      = "Project Online and PRO"
        "ProjectPremium" = "Project Online Premium"
        "ECAL_SERVICES"      = "ECAL"
        "EMS"      = "Enterprise Mobility Suite"
        "RIGHTSMANAGEMENT_ADHOC" = "Windows Azure Rights Management"
        "MCOMEETADV" = "PSTN conferencing"
        "SHAREPOINTSTORAGE"      = "SharePoint storage"
        "PLANNERSTANDALONE"      = "Planner Standalone"
        "CRMIUR" = "CMRIUR"
        "BI_AZURE_P1"      = "Power BI Reporting and Analytics"
        "INTUNE_A"      = "Windows Intune Plan A"
        "PROJECTWORKMANAGEMENT"      = "Office 365 Planner Preview"
        "ATP_ENTERPRISE" = "Exchange Online Advanced Threat Protection"
        "EQUIVIO_ANALYTICS"      = "Office 365 Advanced eDiscovery"
        "AAD_BASIC"      = "Azure Active Directory Basic"
        "RMS_S_ENTERPRISE"      = "Azure Active Directory Rights Management"
        "AAD_PREMIUM"      = "Azure Active Directory Premium"
        "MFA_PREMIUM"      = "Azure Multi-Factor Authentication"
        "STANDARDPACK_GOV"      = "Microsoft Office 365 (Plan G1) for Government"
        "STANDARDWOFFPACK_GOV"      = "Microsoft Office 365 (Plan G2) for Government"
        "ENTERPRISEPACK_GOV" = "Microsoft Office 365 (Plan G3) for Government"
        "ENTERPRISEWITHSCAL_GOV" = "Microsoft Office 365 (Plan G4) for Government"
        "DESKLESSPACK_GOV"      = "Microsoft Office 365 (Plan K1) for Government"
        "ESKLESSWOFFPACK_GOV"      = "Microsoft Office 365 (Plan K2) for Government"
        "EXCHANGESTANDARD_GOV"      = "Microsoft Office 365 Exchange Online (Plan 1) only for Government"
        "EXCHANGEENTERPRISE_GOV" = "Microsoft Office 365 Exchange Online (Plan 2) only for Government"
        "SHAREPOINTDESKLESS_GOV" = "SharePoint Online Kiosk"
        "EXCHANGE_S_DESKLESS_GOV"      = "Exchange Kiosk"
        "RMS_S_ENTERPRISE_GOV"      = "Windows Azure Active Directory Rights Management"
        "OFFICESUBSCRIPTION_GOV" = "Office ProPlus"
        "MCOSTANDARD_GOV"      = "Lync Plan 2G"
        "SHAREPOINTWAC_GOV"      = "Office Online for Government"
        "SHAREPOINTENTERPRISE_GOV"      = "SharePoint Plan 2G"
        "EXCHANGE_S_ENTERPRISE_GOV"      = "Exchange Plan 2G"
        "EXCHANGE_S_ARCHIVE_ADDON_GOV"      = "Exchange Online Archiving"
        "EXCHANGE_S_DESKLESS"      = "Exchange Online Kiosk"
        "SHAREPOINTDESKLESS" = "SharePoint Online Kiosk"
        "SHAREPOINTWAC"      = "Office Online"
        "YAMMER_ENTERPRISE"      = "Yammer Enterprise"
        "EXCHANGE_L_STANDARD"      = "Exchange Online (Plan 1)"
        "MCOLITE"      = "Lync Online (Plan 1)"
        "SHAREPOINTLITE" = "SharePoint Online (Plan 1)"
        "OFFICE_PRO_PLUS_SUBSCRIPTION_SMBIZ" = "Office ProPlus"
        "EXCHANGE_S_STANDARD_MIDMARKET"      = "Exchange Online (Plan 1)"
        "MCOSTANDARD_MIDMARKET"      = "Lync Online (Plan 1)"
        "SHAREPOINTENTERPRISE_MIDMARKET" = "SharePoint Online (Plan 1)"
        "OFFICESUBSCRIPTION" = "Office ProPlus"
        "YAMMER_MIDSIZE" = "Yammer"
        "DYN365_ENTERPRISE_PLAN1"      = "Dynamics 365 Customer Engagement Plan Enterprise Edition"
        "ENTERPRISEPREMIUM_NOPSTNCONF"      = "Enterprise E5 (without Audio Conferencing)"
        "ENTERPRISEPREMIUM"      = "Enterprise E5 (with Audio Conferencing)"
        "MCOSTANDARD"      = "Skype for Business Online Standalone Plan 2"
        "PROJECT_MADEIRA_PREVIEW_IW_SKU" = "Dynamics 365 for Financials for IWs"
        "STANDARDWOFFPACK_IW_STUDENT"      = "Office 365 Education for Students"
        "STANDARDWOFFPACK_IW_FACULTY"      = "Office 365 Education for Faculty"
        "EOP_ENTERPRISE_FACULTY" = "Exchange Online Protection for Faculty"
        "EXCHANGESTANDARD_STUDENT"      = "Exchange Online (Plan 1) for Students"
        "OFFICESUBSCRIPTION_STUDENT" = "Office ProPlus Student Benefit"
        "STANDARDWOFFPACK_FACULTY"      = "Office 365 Education E1 for Faculty"
        "STANDARDWOFFPACK_STUDENT"      = "Microsoft Office 365 (Plan A2) for Students"
        "DYN365_FINANCIALS_BUSINESS_SKU" = "Dynamics 365 for Financials Business Edition"
        "DYN365_FINANCIALS_TEAM_MEMBERS_SKU" = "Dynamics 365 for Team Members Business Edition"
        "FLOW_FREE"      = "Microsoft Flow Free"
        "POWER_BI_PRO"      = "Power BI Pro"
        "O365_BUSINESS"      = "Office 365 Business"
        "DYN365_ENTERPRISE_SALES"      = "Dynamics Office 365 Enterprise Sales"
        "RIGHTSMANAGEMENT"      = "Rights Management"
        "PROJECTPROFESSIONAL"      = "Project Professional"
        "VISIOONLINE_PLAN1"      = "Visio Online Plan 1"
        "EXCHANGEENTERPRISE" = "Exchange Online Plan 2"
        "DYN365_ENTERPRISE_P1_IW"      = "Dynamics 365 P1 Trial for Information Workers"
        "DYN365_ENTERPRISE_TEAM_MEMBERS" = "Dynamics 365 For Team Members Enterprise Edition"
        "CRMSTANDARD"      = "Microsoft Dynamics CRM Online Professional"
        "EXCHANGEARCHIVE_ADDON"      = "Exchange Online Archiving For Exchange Online"
        "EXCHANGEDESKLESS"      = "Exchange Online Kiosk"
        "SPZA_IW"      = "App Connect"
        "WINDOWS_STORE"      = "Windows Store for Business"
        "MCOEV"      = "Microsoft Phone System"
        "VIDEO_INTEROP"      = "Polycom Skype Meeting Video Interop for Skype for Business"
        "SPE_E5" = "Microsoft 365 E5"
        "SPE_E3" = "Microsoft 365 E3"
        "ATA"      = "Advanced Threat Analytics"
        "MCOPSTN2"      = "Domestic and International Calling Plan"
        "FLOW_P1"      = "Microsoft Flow Plan 1"
        "FLOW_P2"      = "Microsoft Flow Plan 2"
        "CRMSTORAGE" = "Microsoft Dynamics CRM Online Additional Storage"
        "SMB_APPS"      = "Microsoft Business Apps"
        "MICROSOFT_BUSINESS_CENTER"      = "Microsoft Business Center"
        "DYN365_TEAM_MEMBERS"      = "Dynamics 365 Team Members"
        "STREAM" = "Microsoft Stream Trial"
        "EMSPREMIUM"                         = "ENTERPRISE MOBILITY + SECURITY E5"
        "SPB" = "Microsoft 365 Business"
        "MCOPSTN1" = "Domestic Calling Plan"
        "MEETING_ROOM" = "Teams Meeting Room"
        "POWERAPPS_PER_APP_IW" = "PowerApps Per App"
        "TEAMS_COMMERCIAL_TRIAL" = "Microsoft Teams"
        "POWERAPPS_PER_USER" = "PowerApps Per User"
        "FLOW_PER_USER" = "PowerAutomate Per User"
        "POWERFLOW_P1" = "Flow P1"
        
    }
    #Establish a PowerShell session with Office 365. You'll be prompted for your Delegated Admin credentials

    try {
        $var = Get-MsolPartnerContract -ErrorAction Stop
    } 
    catch [Microsoft.Online.Administration.Automation.MicrosoftOnlineException] {
        Write-Host "Not connected to Partner account!" -ForegroundColor Yellow
        Connect-MsolService
    }

    Write-Host
    Write-Host
    Write-Host "==============================================================" -ForegroundColor Yellow
    Write-Host " Identified Customer Tenants" -ForegroundColor Yellow
    Get-MsolPartnerContract | Select Name, DefaultDomainName | FT
    Write-Host "==============================================================" -ForegroundColor Yellow
    Write-Host
    Write-Host "Get User Report On Specific Customer?" -ForegroundColor Yellow
    $GetSpecificYN = Read-Host "Yes or No"
    Write-Host
    $customers = ""
    if($GetSpecificYN -like "*yes*"){
        Write-Host "Enter Default Domain Name of customer from above list" -ForegroundColor Yellow
        $TenantDomain = Read-Host "Default Domain"
        Write-Host
        $customers = Get-MsolPartnerContract -DomainName $TenantDomain
        $CustomerName = $customers.name
        Write-Host "Okay! Reporting on Users from $CustomerName." -ForegroundColor Yellow
        Write-Host
        Start-Sleep -seconds 1
        $Date1 = Get-Date -Format "MM-dd-yyyy HH-mm"
        $FileName = "$CustomerName User Report - $Date1.csv"
        $CSVpath = "$home\Downloads\$FileName"
        $XFileName = "$CustomerName User Report - $Date1.xlsx"
        $XLSpath = "$home\Downloads\$XFileName"
    }else{
        Write-Host 
        Write-Host "Okay! Reporting on Users from all Customers." -ForegroundColor Yellow
        Write-Host
        $customers = Get-MsolPartnerContract -All
        Start-Sleep -Milliseconds 500
        $Date1 = Get-Date -Format "MM-dd-yyyy HH-mm"
        $FileName = "All Customer User Report - $Date1.csv"
        $CSVpath = "$home\Downloads\$FileName"
        $XFileName = "All Customer User Report - $Date1.xlsx"
        $XLSpath = "$home\Downloads\$XFileName"
    }
    $OutputArray = @()

    #Get Users from Primary Tenant
    if($GetSpecificYN -like "*no*"){
        $customer = Get-MsolCompanyInformation
        Write-Host ""
        Write-Host "Retrieving license info for $($customer.DisplayName)" -ForegroundColor Green
        $licensedUsers = Get-MsolUser -All | Where-Object {$_.islicensed} | Sort-Object DisplayName
        Start-Sleep -Milliseconds 500
        foreach ($user in $licensedUsers) {
            Write-Host "$($user.displayname)" -ForegroundColor Yellow  
            $LicenseName = @()
            $licenseString = ""
            $Licenses = (($User).Licenses).AccountSkuID
            If (($Licenses).Count -gt 0)
            {
            Foreach ($License in $Licenses)
            {
            $LicenseItem = $License -split ":" | Select-Object -Last 1
            $TextLic = $Sku.Item("$LicenseItem")
            If (!($TextLic))
            {
            $fallback_Licenses = $LicenseItem
            $LicenseName += $fallback_Licenses
            }
            Else
            {
            $LicenseName += $TextLic
            }
            }
            }
            $licenseString = $LicenseName -join ", "

            Write-Host "$($user.displayname) has $licenseString" -ForegroundColor Blue

            $licensedSharedMailboxProperties = [pscustomobject][ordered]@{
                'Customer Name'     = $customer.DisplayName
                'Display Name'       = $user.DisplayName
                'First Name'        = $user.FirstName
                'Last Name'        = $user.LastName
                Office            = $user.Office
                Title             = $user.Title
                UserPrincipalName = $user.UserPrincipalName
                Licenses          = $licenseString
                TenantId          = $customer.ObjectID              
            }
            $OutputArray += $licensedSharedMailboxProperties
            $licensedSharedMailboxProperties | Export-CSV -Path $CSVpath -Append -NoTypeInformation   
        }  
    }

    #Get Users from Customer Tenant
    foreach ($customer in $customers) {
        Write-Host ""
        Write-Host "Retrieving license info for $($customer.name)" -ForegroundColor Green
        $licensedUsers = Get-MsolUser -TenantId $customer.TenantId -All | Where-Object {$_.islicensed} | Sort-Object DisplayName
        Start-Sleep -Milliseconds 500
        foreach ($user in $licensedUsers) {
            Write-Host "$($user.displayname)" -ForegroundColor Yellow  
            $LicenseName = @()
            $licenseString = ""
            $Licenses = (($User).Licenses).AccountSkuID
            If (($Licenses).Count -gt 0)
            {
            Foreach ($License in $Licenses)
            {
            $LicenseItem = $License -split ":" | Select-Object -Last 1
            $TextLic = $Sku.Item("$LicenseItem")
            If (!($TextLic))
            {
            $fallback_Licenses = $LicenseItem
            $LicenseName += $fallback_Licenses
            }
            Else
            {
            $LicenseName += $TextLic
            }
            }
            }
            $licenseString = $LicenseName -join ", "

            Write-Host "$($user.displayname) has $licenseString" -ForegroundColor Blue

            $licensedSharedMailboxProperties = [pscustomobject][ordered]@{
                'Customer Name'     = $customer.Name
                'Display Name'       = $user.DisplayName
                'First Name'        = $user.FirstName
                'Last Name'        = $user.LastName
                Office            = $user.Office
                Title             = $user.Title
                UserPrincipalName = $user.UserPrincipalName
                Licenses          = $licenseString
                TenantId          = $customer.TenantId              
            }
            $OutputArray += $licensedSharedMailboxProperties
            $licensedSharedMailboxProperties | Export-CSV -Path $CSVpath -Append -NoTypeInformation   
        }
    }
    #$OutputArray | Out-GridView
    ### Set input and output path
    $inputCSV = "$CSVPath"
    $outputXLSX = "$XLSPath"

    ### Create a new Excel Workbook with one empty sheet
    $excel = New-Object -ComObject excel.application 
    $workbook = $excel.Workbooks.Add(1)
    $worksheet = $workbook.worksheets.Item(1)

    ### Build the QueryTables.Add command
    ### QueryTables does the same as when clicking "Data » From Text" in Excel
    $TxtConnector = ("TEXT;" + $inputCSV)
    $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
    $query = $worksheet.QueryTables.item($Connector.name)

    ### Set the delimiter (, or ;) according to your regional settings
    $query.TextFileOtherDelimiter = $Excel.Application.International(5)

    ### Set the format to delimited and text for every column
    ### A trick to create an array of 2s is used with the preceding comma
    $query.TextFileParseType  = 1
    $query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
    $query.AdjustColumnWidth = 1
    ### Execute & delete the import query
    $query.Refresh()
    $query.Delete()
    $list = $excel.ActiveSheet.ListObjects.Add(
        [Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, # Add a range
        $excel.ActiveCell.CurrentRegion, # Get the current region, by default A1 is selected so it'll select all contiguous rows
        $null,
        [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes # Yes, we have a header row
    )
    ### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
    $Workbook.SaveAs($outputXLSX,51)
    $excel.Quit()
    Remove-Item -path "$inputCSV"


    Write-Host
    Write-Host 
    Write-Host "==============================================================" -ForegroundColor Yellow
    Write-Host " Done! See report in $XLSPath" -ForegroundColor Yellow
    Write-Host "==============================================================" -ForegroundColor Yellow
}
function Start-RoomScripts
{
#Description: Various scripts for managing Room Mailboxes
function Sub-New-RoomMailbox
{

Write-Host ""
$RoomName = Read-Host "Room Mailbox Display Name"
$RoomAlias = Read-Host "Room Alias to be used in email address(ex: conf.chiefs)"
$RoomDomain = Read-Host "Room email domain (ex: 321moonshot.com)"
$RoomAddress = "$RoomAlias@$RoomDomain"
$RoomPass = Read-Host "Room Mailbox Password"
New-Mailbox -Name "$RoomName" -Alias $RoomAlias -Room -EnableRoomMailboxAccount $true -MicrosoftOnlineServicesID $RoomAddress -RoomMailboxPassword (ConvertTo-SecureString -String '$RoomPass' -AsPlainText -Force)
Write-Host ""
}
function Sub-Get-RoomLists
{

Get-DistributionGroup -ResultSize Unlimited | Where-Object {$_.RecipientTypeDetails -eq "RoomList"} | Format-Table DisplayName,PrimarySmtpAddress -AutoSize
}
function Sub-New-RoomList
{

$NewListName = Read-Host "Display Name for New Room List"
New-DistributionGroup -Name "$NewListName" -RoomList
}
function Sub-Add-RoomListMember
{

$RoomAddress = Read-Host "Room Mailbox email address or alias"
$ListName = Read-Host "Display Name of Room List"
Add-DistributionGroupMember -Identity "$ListName" -Member "$RoomAddress"
}
function Sub-Set-RoomSettings
{

Write-Host "Allow recurring meetings to schedule with some conflicts?"
$ConflictAllow = Read-Host "Yes or No"
$ConflictAllowed = ""
if($ConflictAllow -like "*yes*"){
$ConflictAllowed = $True
}else{
$ConflictAllowed = $False}
If ($ConflictAllowed -eq $True)
{
    Write-Host ""
    #Allow some Recurring conflicts
    Write-Host ""
    $rm = Read-Host "Room Mailbox Alias"
    $ConflictPercent = Read-Host "Max percent (as number) of occurances that can conflict over series"
    $ConflictMax = Read-Host "Max number of conflicting occurances before series is declined"
    Set-CalendarProcessing "$rm" -AutomateProcessing AutoAccept -AllowConflicts $false -BookingWindowInDays 365 -MaximumDurationInMinutes 1440 -AllowRecurringMeetings $true -ConflictPercentageAllowed $ConflictPercent -MaximumConflictInstances $ConflictMax -ForwardRequestsToDelegates $true -DeleteAttachments $false -DeleteComments $false -RemovePrivateProperty $false -DeleteSubject $false -AddOrganizerToSubject $false -DeleteNonCalendarItems $true -TentativePendingApproval $true -EnableResponseDetails $true -OrganizerInfo $true -AllRequestOutOfPolicy $false -AllBookInPolicy $true -AllRequestInPolicy $false -AddNewRequestsTentatively $true -ProcessExternalMeetingMessages $true -RemoveForwardedMeetingNotifications $false
}else{
    Write-Host ""
    #Decline Recurring if any conflicts
    Write-Host ""
    $rm = Read-Host "Room Mailbox Alias"
    Set-CalendarProcessing "$rm" -AutomateProcessing AutoAccept -AllowConflicts $false -BookingWindowInDays 365 -MaximumDurationInMinutes 1440 -AllowRecurringMeetings $true -ConflictPercentageAllowed 0 -MaximumConflictInstances 0 -ForwardRequestsToDelegates $true -DeleteAttachments $false -DeleteComments $false -RemovePrivateProperty $false -DeleteSubject $false -AddOrganizerToSubject $false -DeleteNonCalendarItems $true -TentativePendingApproval $true -EnableResponseDetails $true -OrganizerInfo $true -AllRequestOutOfPolicy $false -AllBookInPolicy $true -AllRequestInPolicy $false -AddNewRequestsTentatively $true -ProcessExternalMeetingMessages $true -RemoveForwardedMeetingNotifications $false
}
}
function Sub-Set-MTRConfig
{

    Clear-Host
    try 
    { 
    $var = Get-CsOnlineSipDomain -ErrorAction SilentlyContinue
    Write-Host ""
    Write-Host "Connected to S4B Online"
    Write-Host ""
    $rm = Read-Host "Meeting Room Alias"
    $RoomPass = Read-Host "Mailbox Password"
    Set-Mailbox -Identity "$rm" -EnableRoomMailboxAccount $true -RoomMailboxPassword (ConvertTo-SecureString -String '$RoomPass' -AsPlainText -Force)
    $SIPPool = Get-CsOnlineUser -Identity "$rm" | Select -Expand RegistrarPool
    Enable-CsMeetingRoom -Identity "$rm" -RegistrarPool "$SIPPool" -SipAddressType EmailAddress
    } 
    catch [System.Exception] 
    { 
    Write-Host ""
    Write-Host "--------------------------------------------------------------------------------------"
    Write-Host " Please make sure you are connected to the Skype for Business Online module."
    Write-Host " In a new window, connect via the Connect-Office365 command and re-run script"
    Write-Host " If that command can not be found, follow the Helpdesk Solutions > M365 PowerShell"
    Write-Host " article titled ""Create & Set PowerShell Profile"" on the Moonshot Helpdesk."
    Write-Host "--------------------------------------------------------------------------------------"
    Write-Host ""
    }
}
function Sub-Show-ActionMenu
{
    Write-Host 
    Write-Host "================================================================" -ForegroundColor Yellow
    Write-Host " Room Mailbox Action Options:" -ForegroundColor Yellow
    Write-Host ""
    Write-Host " 1. New-RoomMailbox" -ForegroundColor Green
    Write-Host " Create a new Room Mailbox" -ForegroundColor Yellow
    Write-Host " 2. Get-RoomLists" -ForegroundColor Green
    Write-Host " Show existing Room Lists" -ForegroundColor Yellow
    Write-Host " 3. New-RoomList" -ForegroundColor Green
    Write-Host " Create a new Room List" -ForegroundColor Yellow
    Write-Host " 4. Add-RoomListMember" -ForegroundColor Green
    Write-Host " Add Room to Room List" -ForegroundColor Yellow
    Write-Host " 5. Set-RoomSettings" -ForegroundColor Green
    Write-Host " Configure default Scheduling settings" -ForegroundColor Yellow
    Write-Host " 6. Set-MTRConfig" -ForegroundColor Green
    Write-Host " Enable default requirements for Teams Room" -ForegroundColor Yellow
    Write-Host " 7. " -ForegroundColor Green
    Write-Host " -" -ForegroundColor Yellow
    Write-Host " 8. " -ForegroundColor Green
    Write-Host " -" -ForegroundColor Yellow
    Write-Host " 9. " -ForegroundColor Green
    Write-Host " -" -ForegroundColor Yellow
    Write-Host " 0. Exit Room Script" -ForegroundColor Green
    Write-Host "================================================================" -ForegroundColor Yellow
    Write-Host ""
    Write-Host ""
}
Do{
    Sub-Show-ActionMenu
    $Selection = ""
    Write-Host " Enter Action Number"
    $Selection = Read-Host " Number"
    Switch ($Selection)
    {
        '1' {
            Sub-New-RoomMailbox
        }
        '2' {
            Sub-Get-RoomLists
        }
        '3' {
            Sub-New-RoomList
        }
        '4' {
            Sub-Add-RoomListMember
        }
        '5' {
            Sub-Set-RoomSettings
        }
        '6' {
            Sub-Set-MTRConfig
        }
        '7' {
        }
        '8' {
        }
        '9' {
        }
        '0' {
            Write-Host
            Write-Host "Exiting Script!" -ForegroundColor Green
            Write-Host
        }
    }
}Until ($Selection -eq '0')
}
function Update-Moonshot365
{
#Description: Update the Moonshot365 Module
Write-Host "Checking Moonshot365 Version...."
$Installed = Get-Module -Name Moonshot365
$online = Find-Module -Name Moonshot365
$online | Where-Object {$installed.name -contains $_.name} |
                Select-Object -property Name,
            @{Name = "OnlineVersion"; Expression = {$_.Version}},
            @{Name = "InstalledVersion"; Expression = {
                    #save the name from the incoming online object
                    $name = $_.Name
                    $installed.Where( {$_.name -eq $name}).Version -join ","}
            },
            PublishedDate,
            @{Name = "UpdateNeeded"; Expression = {
                    $name = $_.Name
                    #there could me multiple versions installed
                    $installedVersions = $installed.Where( {$_.name -eq $name}).Version | Sort-Object
                    foreach ($item in $installedVersions) {
                        If ($_.Version -gt [version]$item) {
                            $result = $True
                            $ModManifestPath = "$PSScriptRoot\Moonshot365.psd1"
                            $ModManifest = Import-PowerShellDataFile $ModManifestPath
                            $NewVer = $ModManifest.ModuleVersion
                            $UpdateVer = ""
                            $WebVersion = Find-Module -Name Moonshot365 | Select -expandproperty Version
                            if($WebVersion -eq $NewVer){$UpdateVer = "$WebVersion"}else{$UpdateVer = "$WebVersion - Please Update"}
                            Write-Host ""
                            Write-Host "---------------------------------------->" -ForegroundColor Yellow
                            Write-Host " Installed Version: $NewVer" -ForegroundColor Yellow
                            Write-Host " Latest Version: $UpdateVer" -ForegroundColor Yellow
                            Write-Host " Version Bad. Updating!" -ForegroundColor Yellow
                            Write-Host "---------------------------------------->" -ForegroundColor Yellow
                            Remove-Module Moonshot365
                            Update-Module Moonshot365
                            Write-Host "Updated!"
                            Write-Host ""
                            Write-Host ""
                            Import-Module Moonshot365
                            
                        }
                        else {
                            $result = $False
                            
                            Write-Host "Version good!"
                            Write-Host ""
                            Write-Host ""
                            Import-Module Moonshot365
                            
                        }
                    }
                    $result
                }
            } | Out-Null
}
function Get-Commands
{
#Description: List available commands in Moonshot 365 Module
Write-Host ""
Write-Host "============================================================================>" -ForegroundColor Yellow
Write-Host ""
Write-Host " Connect-Office365" -ForegroundColor Green
Write-Host " Connect to various Office 365 PowerShell services" -ForegroundColor Yellow
Write-Host " Get-Office365Modules" -ForegroundColor Green
Write-Host " Install AzureAD, MsOnline, and SharePoint modules" -ForegroundColor Yellow
Write-Host " New-365Report" -ForegroundColor Green
Write-Host " Report on O365 tenant objects & generate migration scripts" -ForegroundColor Yellow
Write-Host " Get-MFAStatus" -ForegroundColor Green
Write-Host " Generate report of users and their MFA status" -ForegroundColor Yellow
Write-Host " New-PartnerUserReport" -ForegroundColor Green
Write-Host " Report on user across Partner Tenants" -ForegroundColor Yellow
Write-Host " Start-RoomScripts" -ForegroundColor Green
Write-Host " Various scripts for managing Room Mailboxes" -ForegroundColor Yellow
Write-Host " Update-Moonshot365" -ForegroundColor Green
Write-Host " Update the Moonshot365 Module" -ForegroundColor Yellow
Write-Host " Get-Commands" -ForegroundColor Green
Write-Host " List available commands in Moonshot 365 Module" -ForegroundColor Yellow
Write-Host ""
Write-Host "============================================================================>" -ForegroundColor Yellow
Write-Host ""
}

Export-ModuleMember -Function Connect-Office365, Get-Office365Modules, New-365Report, Get-MFAStatus, New-PartnerUserReport, Start-RoomScripts, Sub-New-RoomMailbox, Sub-Get-RoomLists, Sub-New-RoomList, Sub-Add-RoomListMember, Sub-Set-RoomSettings, Sub-Set-MTRConfig, Sub-Show-ActionMenu, Update-Moonshot365, Get-Commands
$ModManifestPath = "$PSScriptRoot\Moonshot365.psd1"
$ModManifest = Import-PowerShellDataFile $ModManifestPath
$NewVer = $ModManifest.ModuleVersion
$UpdateVer = ""
$WebVersion = Find-Module -Name Moonshot365 | Select -expandproperty Version
if($WebVersion -eq $NewVer){$UpdateVer = $WebVersion}else{$UpdateVer = "$WebVersion - Please Update"}
Write-Host ""
Write-Host "============================================================================>" -ForegroundColor Yellow
Write-Host " Installed Version: $NewVer" -ForegroundColor Yellow
Write-Host " Latest Version: $UpdateVer" -ForegroundColor Yellow
Write-Host ""
Write-Host " Current Moonshot365 Commands: " -ForegroundColor Yellow
Write-Host ""
Write-Host " Connect-Office365" -ForegroundColor Green
Write-Host " Connect to various Office 365 PowerShell services" -ForegroundColor Yellow
Write-Host " Get-Office365Modules" -ForegroundColor Green
Write-Host " Install AzureAD, MsOnline, and SharePoint modules" -ForegroundColor Yellow
Write-Host " New-365Report" -ForegroundColor Green
Write-Host " Report on O365 tenant objects & generate migration scripts" -ForegroundColor Yellow
Write-Host " Get-MFAStatus" -ForegroundColor Green
Write-Host " Generate report of users and their MFA status" -ForegroundColor Yellow
Write-Host " New-PartnerUserReport" -ForegroundColor Green
Write-Host " Report on user across Partner Tenants" -ForegroundColor Yellow
Write-Host " Start-RoomScripts" -ForegroundColor Green
Write-Host " Various scripts for managing Room Mailboxes" -ForegroundColor Yellow
Write-Host " Update-Moonshot365" -ForegroundColor Green
Write-Host " Update the Moonshot365 Module" -ForegroundColor Yellow
Write-Host " Get-Commands" -ForegroundColor Green
Write-Host " List available commands in Moonshot 365 Module" -ForegroundColor Yellow
Write-Host "============================================================================>" -ForegroundColor Yellow
Write-Host ""