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" } #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 { $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" } #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 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 = @() 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} 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 New-RoomMailbox { #Description: Create a new Room Mailbox Resource 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 Get-RoomLists { #Description: Show existing Room Lists Get-DistributionGroup -ResultSize Unlimited | Where-Object {$_.RecipientTypeDetails -eq "RoomList"} | Format-Table DisplayName,PrimarySmtpAddress -AutoSize } function New-RoomList { #Description: Create a new Room List $NewListName = Read-Host "Display Name for New Room List" New-DistributionGroup -Name "$NewListName" -RoomList } function Add-RoomListMember { #Description: Add a resource to a Room List $RoomAddress = Read-Host "Room Mailbox email address or alias" $ListName = Read-Host "Display Name of Room List" Add-DistributionGroupMember -Identity "$ListName" -Member "$RoomAddress" } function Set-RoomSettings { #Description: Set default Room Resource settings 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 Set-MTRConfig { #Description: Apply default config for a Microsoft Teams Room resource 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 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 " Create a new Room Mailbox Resource" -ForegroundColor Yellow Write-Host " New-RoomMailbox" -ForegroundColor Green Write-Host " Show existing Room Lists" -ForegroundColor Yellow Write-Host " Get-RoomLists" -ForegroundColor Green Write-Host " Create a new Room List" -ForegroundColor Yellow Write-Host " New-RoomList" -ForegroundColor Green Write-Host " Add a resource to a Room List" -ForegroundColor Yellow Write-Host " Add-RoomListMember" -ForegroundColor Green Write-Host " Set default Room Resource settings" -ForegroundColor Yellow Write-Host " Set-RoomSettings" -ForegroundColor Green Write-Host " Apply default config for a Microsoft Teams Room resource" -ForegroundColor Yellow Write-Host " Set-MTRConfig" -ForegroundColor Green Write-Host " Update the Moonshot365 Module" -ForegroundColor Yellow Write-Host " Update-Moonshot365" -ForegroundColor Green Write-Host " List available commands in Moonshot 365 Module" -ForegroundColor Yellow Write-Host " Get-Commands" -ForegroundColor Green Write-Host "" Write-Host "============================================================================>" -ForegroundColor Yellow Write-Host "" } Export-ModuleMember -Function Connect-Office365, Get-Office365Modules, New-365Report, Get-MFAStatus, New-PartnerUserReport, New-RoomMailbox, Get-RoomLists, New-RoomList, Add-RoomListMember, Set-RoomSettings, Set-MTRConfig, 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 " Create a new Room Mailbox Resource" -ForegroundColor Yellow Write-Host " New-RoomMailbox" -ForegroundColor Green Write-Host " Show existing Room Lists" -ForegroundColor Yellow Write-Host " Get-RoomLists" -ForegroundColor Green Write-Host " Create a new Room List" -ForegroundColor Yellow Write-Host " New-RoomList" -ForegroundColor Green Write-Host " Add a resource to a Room List" -ForegroundColor Yellow Write-Host " Add-RoomListMember" -ForegroundColor Green Write-Host " Set default Room Resource settings" -ForegroundColor Yellow Write-Host " Set-RoomSettings" -ForegroundColor Green Write-Host " Apply default config for a Microsoft Teams Room resource" -ForegroundColor Yellow Write-Host " Set-MTRConfig" -ForegroundColor Green Write-Host " Update the Moonshot365 Module" -ForegroundColor Yellow Write-Host " Update-Moonshot365" -ForegroundColor Green Write-Host " List available commands in Moonshot 365 Module" -ForegroundColor Yellow Write-Host " Get-Commands" -ForegroundColor Green Write-Host "============================================================================>" -ForegroundColor Yellow Write-Host "" |