Get-ExchangeMonitoringReports.ps1

<#
Author: Neeraj Kumar
This script is used to create Exchange Online Monitoring and governance reports. Please create required variables under shared resources.
Also register an app within Microsoft Entra Id with API permissions for Exchange.ManageAsApp and give the role of Exchange Administrator.
CreatedDate : 24-Nov-2023 
#>

$global:accessToken=$null

$tenantId = Get-AutomationVariable -Name 'TenantId'
$clientId = Get-AutomationVariable -Name 'ClientId'
$clientSecret = Get-AutomationVariable -Name 'ClientSecret'
$certThumbprint = Get-AutomationVariable -Name 'CERT_THUMBPRINT'
$spSiteUrl = Get-AutomationVariable -Name 'SharePointSiteUrl'
$spSiteName = Get-AutomationVariable -Name 'SharePointSiteName'
$spLibraryName = Get-AutomationVariable -Name 'ExchangeDocumentLibraryName'

Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser


$global:DestinationURL = "$spSiteUrl/sites/$spSiteName/$spLibraryName"

#Write-Output $global:DestinationURL

<# Connect to Graph API and get access tokens  #>
Function ConnectToGraph()
{
    try
    {
    Connect-MgGraph -ClientId $clientId -TenantID $TenantId -CertificateThumbprint $certThumbprint  -Nowelcome
      
        $graphtokenBody = @{
            Grant_Type    = "client_credentials"
            Client_Id     = $clientId
            Client_Secret = $clientSecret
            Scope         = "https://graph.microsoft.com/.default"
        }

        $jsonBody = $graphtokenBody #| ConvertTo-Json
        $oauth = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" -Body $jsonBody 
        $global:accessToken = @{'Authorization'="$($oauth.token_type) $($oauth.access_token)"}
        $global:accessToken = $oauth.access_token
       
       
        }
    catch {
            Write-Error $Error[0]
            Write-Output "Error in connecting to Microsoft Graph"
    }
}

<# 2. Connect to SharePoint Online #>

function ConnectToSharePoint
{
    $SiteUrl = "$spSiteUrl/sites/$spSiteName"
    try 
    {
        $SiteMemberCredential = Get-AutomationPSCredential -Name "SharePoint"
        Connect-PnPOnline -Url $SiteUrl -Credentials $SiteMemberCredential
        Write-Output "Connected to SharePoint Online "
    }   
    catch{
         Write-Error $Error[0]
         Write-Host "Unable to connect to SharePoint Online.."
    }
}
<# 3. Connect to Exchange Powershell #>
function ConnectToExchange
{
    $certThumbprint = Get-AutomationVariable -Name 'CERT_THUMBPRINT'
    #$AppId = Get-AutomationVariable -Name 'AppID'
    $Organization = Get-AutomationVariable -Name 'organization'
    try {
         Connect-ExchangeOnline -AppId $clientId  -CertificateThumbprint  $certThumbprint -Organization $Organization
         Write-Output "Connected to Exchange Online "
    }
    
    catch {
            Write-Error $Error[0]
            Write-Output "Unable to connect to Exchange Online"
    }
}
<#5. Fetch Mailbox statistics #>
function GetMailBoxStatistics
{
   param ( $CSVFileName)    
    $report = [System.Collections.Generic.List[Object]]::new()
    $Error.Clear()

 try
  {
      Write-Output "Execute EXOMailbox"
   $Mailboxes = Get-EXOMailbox -PropertySets Minimum , Archive,Audit,Hold,Retention,Quota,Delivery,AddressList
   Foreach ($Mailbox in $Mailboxes)
  {
     $mailstats = $null
     Write-Output "Fetching Mailbox statistics for User"+ $Mailbox.UserPrincipalName 
     $mailstats =Get-EXOMailboxStatistics -PropertySets All -UserPrincipalName $Mailbox.UserPrincipalName 
     $TotalItemSize=$mailstats.TotalItemSize
     $Itemcount= $mailstats.ItemCount
     $LastLogonTime=$mailstats.LastLogonTime
     $MailboxType=$mailstats.MailboxType
     $MailboxTypeDetail=$mailstats.MailboxTypeDetail
     $StorageLimitStatus=$mailstats.StorageLimitStatus
     $IsArchiveMailbox =$mailstats.IsArchiveMailbox

     $obj = [pscustomObject][ordered] @{  
    Name =$Mailbox.Name
    UPN=$Mailbox.UserPrincipalName
    Alias=$Mailbox.Alias
    PrimarySMTPAddress=$Mailbox.PrimarySMTPAddress
    ExchangeVersion=$Mailbox.ExchangeVersion
    RecipientType=$Mailbox.RecipientType
    RecipientTypeDetails=$Mailbox.RecipientTypeDetails
    TotalItemSize=$TotalItemSize
    Itemcount= $ItemCount
    LastLogonTime=$LastLogonTime
    MailboxType=$MailboxType
    MailboxTypeDetail=$MailboxTypeDetail
    StorageLimitStatus=$StorageLimitStatus
    IsArchiveMailbox =$IsArchiveMailbox
    ArchiveStatus=$Mailbox.ArchiveStatus
    ArchiveQuota=$Mailbox.ArchiveQuota
    IssueWarningQuota=$Mailbox.IssueWarningQuota
    ArchiveWarningQuota=$Mailbox.ArchiveWarningQuota
    ArchiveName=$Mailbox.ArchiveName
    AuditEnabled=$Mailbox.AuditEnabled
    LitigationHold=$Mailbox.LitigationHoldEnabled
    InPlaceHolds=$Mailbox.InPlaceHolds
    RetentionHold=$Mailbox.RetentionHoldEnabled
    HideFromAddressList=$Mailbox.HiddenFromAddressListsEnabled
    ForwardingAddress=$Mailbox.ForwardingAddress
    ForwardingSMTPAddress=$Mailbox.ForwardingSMTPAddress
     }
     $report.Add($obj) 
   }#for
 
   $filePath = $env:Temp
   $report | Export-Csv -Path $filePath\$CSVFileName -NoTypeInformation
   $Values = @{"Title" = 'Mailbox detail report (CSV)'}
    # Add the file to the Reports folder
 
     WritetoSharePoint($CSVFileName)
  } #try

  catch { Write-Error $Error[0]
       Write-Output "Error executing  GetMailBoxStatistics"}
      $report = $null

}
<# 4. Execute Graph API Reports for Exchange #>
function ExecuteReportAPI
{ 
    param([String[]] $Param )
    $CSVFileName = $Param[0]
    $graphApiUri = $Param[1]

    $headers = @{
        "Authorization" = "Bearer $global:accessToken"
        "Content-Type"  = "application/json"
    }
   
    Write-Output $global:accessToken
    $filePath = $env:Temp
try{
                
        Write-Output "Fetching report output by executing Graph API"
      
        #$graphApiUri = "https://graph.microsoft.com/v1.0/reports/getMailboxUsageMailboxCounts(period='D30')"
        $Reports = Invoke-RestMethod -Method Get -Uri $graphApiUri -Headers $headers | ConvertFrom-Csv
     
        $Reports | Export-Csv $filePath\$CSVFileName -NoTypeInformation
        $Values = @{"Title" = 'Mailbox Reports'}
        # Add the file to the Reports folder
        Write-Output " Upload file to sharePoint"
         WritetoSharePoint($CSVFileName)
 
    }
    catch 
    { 
        Write-Error $Error[0]
        Write-Output "Exception while fetching Mailbox usage report"}

}
function getServiceHealth
{
    param ( $CSVFileName)
    $filePath=$null
    Write-Output "Get Service Health Overview"
     try {
     
        $serviceHealthId = Get-MgServiceAnnouncementHealthOverview  -All -ExpandProperty "issues"
        $filePath =$env:Temp
    
    
   		$serviceHealth | Export-Csv -Path  $filePath\$CSVFileName -NoTypeInformation 
     		 $Values = @{"Title" = 'Microsoft 365 Service Health Report (CSV)'}
    		# Add the file to the Reports folder
      		
   		  WritetoSharePoint($CSVFileName)
    	}#try	
	catch { 
        Write-Error $Error[0]
        Write-Output "Error creating report"} 
}

function getServiceHealthIssues
{
    param ( $CSVFileName)
    $filePath=$null
    Write-Output "Get Service Health Issues"
    try {
    $serviceIssues= Get-MgServiceAnnouncementIssue |Select-Object StartDateTime,LastModifiedDateTime, Id, ImpactDescription,service, Feature, Classification, Status | Sort-Object StartDateTime
      $filePath =$env:Temp
    
     
   		$serviceIssues | Export-Csv -Path  $filePath\$CSVFileName -NoTypeInformation 
     		 $Values = @{"Title" = 'Microsoft 365 Service Issues Report (CSV)'}
    		# Add the file to the Reports folder
      		
   		 WritetoSharePoint($CSVFileName)
    	}#try	
	catch { 
        Write-Error $Error[0]
        Write-Output "Error creating report"} 
}
function getO365Groups
{
    param ( $CSVFileName)
    $filePath=$null
    Write-Output "Get O365 Groups"
    
     try {
     $groups = Get-MgGroup  | Select-Object id,createdDateTime,deletedDateTime,classification,displayName,description,mailEnabled,mail,securityEnabled,Visibility
       
        $filePath =$env:Temp
    
    
   		$groups | Export-Csv -Path  $filePath\$CSVFileName -NoTypeInformation 
     		 $Values = @{"Title" = 'M365 Groups (CSV)'}
    		# Add the file to the Reports folder
      		
   		 WritetoSharePoint($CSVFileName)
   		Write-Output "O365 Groups  report created"
    	}#try	
	catch { 
            Write-Error $Error[0]
            Write-Output "Error creating report"
        } 
}

function getDistributionLists
{
     param ( $CSVFileName)
    $filePath=$null
    Write-Output "Get Distribution Lists"
    
     try {
           $AllDistributionGroups = Get-DistributionGroup -Filter "RecipientTypeDetails -eq 'MailUniversalDistributionGroup'" -resultsize unlimited| Select-object Name,Guid,HiddenFromAddressListsEnabled,PrimarySmtpAddress,DisplayName,RequireSenderAuthenticationEnabled,AcceptMessagesOnlyFromDLMembers,CustomAttribute1,@{label="ManagedBy";expression={[string]($_.managedby  | foreach {(Get-Mailbox $_).PrimarySMTPAddress})}} 
     
         $filePath =$env:Temp
      	$AllDistributionGroups | Export-Csv -Path  $filePath\$CSVFileName -NoTypeInformation 
     		 $Values = @{"Title" = 'M365 Distribution Lists (CSV)'}
    		# Add the file to the Reports folder
      		
   		 WritetoSharePoint($CSVFileName)
    	}#try	
	catch { 
            Write-Error $Error[0]
            Write-Output "Error creating report"} 
}

function importmodules{
    Import-Module Microsoft.Graph.Authentication
    Import-Module Microsoft.Graph.Devices.ServiceAnnouncement
    Import-Module Microsoft.Graph.Groups
    Import-Module Microsoft.Graph.Identity.DirectoryManagement
    
}
function getAdmins{
    param ($CSVFileName)
    try
    {
        $filePath =$env:Temp
      $report = [System.Collections.Generic.List[Object]]::new()
       $roles= Get-MgDirectoryRole -All 
       Foreach ($role in $roles)
       {
           Write-Output "Roleid ", $role.Id
        $members = Get-MgDirectoryRoleMember -DirectoryRoleId $role.Id -All
        if ($members -ne $null)
        {
            Write-Output $members
            $obj=  [pscustomobject]@{
            RoleName = $role.DisplayName
            RoleId = $role.Id
            RoleDescription =$role.description 
            Id                = $members.id
            displayName       = $members.additionalproperties['displayName']
            mail              = $members.additionalproperties['mail']
            userPrincipalName = $members.additionalproperties['userPrincipalName']
            }
            $report.add($obj)
        }
        }
       $report | Export-Csv -NoTypeInformation -Path $filePath\$CSVFileName
       WritetoSharePoint($CSVFileName)
    	}#try	
	catch { 
            Write-Error $Error[0]
            Write-Output "Error creating report"
            }
}
# Output results to SharePoint folder
function WritetoSharePoint{
   param( $CSVFileName )
     # Export to CSV locally
    $filePath = $env:Temp
      
    # Upload to SharePoint
    $FolderObject = Get-PnPFolder -Url $global:DestinationURL
    $Upload= Add-PnPFile -Path $filePath\$CSVFileName  -Folder $FolderObject
    If ($Upload -ne $null)
    {
         Write-Output $CSVFileName " Report sucessfully uploaded"
    }
    # Clean up local file
    Remove-Item -Path $filePath\$CSVFileName -Force
}
function main
{
   
    ConnectToGraph
    ConnectToSharePoint
    ConnectToExchange
    GetMailBoxStatistics("1. Mailbox Report.csv")
    ExecuteReportAPI("2.Mailbox Usage Counts_30.csv","https://graph.microsoft.com/v1.0/reports/getMailboxUsageMailboxCounts(period='D30')")
    ExecuteReportAPI("2.Mailbox Usage Quota_30.csv","https://graph.microsoft.com/v1.0/reports/getMailboxUsageQuotaStatusMailboxCounts(period='D30')")
    ExecuteReportAPI("2.Mailbox Usage Detail_30.csv","https://graph.microsoft.com/v1.0/reports/getMailboxUsageDetail(period='D30')")
    ExecuteReportAPI("2.Mailbox Usage Storage_30.csv","https://graph.microsoft.com/v1.0/reports/getMailboxUsageStorage(period='D30')")
    ExecuteReportAPI("3.Email Activity User Detail_30.csv","https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D30')")
    ExecuteReportAPI("3.Email Activity Counts_30.csv","https://graph.microsoft.com/v1.0/reports/getEmailActivityCounts(period='D30')")
    ExecuteReportAPI("4.Outlook App Detail_30.csv","https://graph.microsoft.com/v1.0/reports/getEmailAppUsageUserDetail(period='D30')")
    ExecuteReportAPI("4.Outlook App Usage_30.csv","https://graph.microsoft.com/v1.0/reports/getEmailAppUsageUserCounts(period='D30')")
    ExecuteReportAPI("4.Outlook App Versions30.csv","https://graph.microsoft.com/v1.0/reports/getEmailAppUsageVersionsUserCounts(period='D30')")
    getO365Groups("6.O365 Groups.csv")
    getDistributionLists("6.Distribution Lists.csv")
    getAdmins("7.Admins.csv")
}
   main