Exchange Online Unified Messaging Extensions Report

Here is another task we got to generate a report in excel and publish it on Sharepoint or Onedrive to share it with our Voice team.

We will be using three scripts here but one solution that can be downloaded from above.

One script is used for encryption of password
One for report generation
last one for exporting to Excel.

These all three are binded together so you don’t have to think of them as seprate scripts.

Download the zipped solution from below and extract it.

https://github.com/VikasSukhija/Downloads/blob/master/EOLUMExtensionReport.zip

First step is to update EOLUMExtensionReport.ps1

Update the variables marked in yellow

Email Alert related information
Path to save the excel report (you can choose onedrive path so that you can share it with others as well as you can use excel online)
UserprincipalName for the account to be used to connect to Exchange online.

Second step is to launch encrypt.bat

It will generate the secure password inside the script folder so that it can be used to authenticate to Exchange Online.

Thats it, now run the batch file or schedule it via task scheduler to generate the report in excel format.

Task Scheduler Settings:

Program/Script : E:\scripts\EOLUMExtensionReport\EOLUMExtensionReport.bat

Start in (optional): E:\scripts\EOLUMExtensionReport\

Last part of the script sets the retention of log files/csv report saved under logs/report folder to 60 days.

PowerShell

<#     
    .NOTES 
    =========================================================================== 
     Created on:       7/04/2018 1:11 PM 
     Created by:       Vikas Sukhija (http://SysCloudPro.com) 
     Organization:      
     Filename:         EOLUMReport.ps1 
    =========================================================================== 
    .DESCRIPTION 
        Unified messaging Extension report for Voice team 
#> 
######################ADD Functions############### 
function Write-Log 
{ 
    [CmdletBinding()] 
    param 
    ( 
        [Parameter(Mandatory = $true)] 
        [array]$Name, 
        [Parameter(Mandatory = $true)] 
        [string]$Ext, 
        [Parameter(Mandatory = $true)] 
        [string]$folder 
    ) 
     
    $log = @() 
    $date1 = get-date -format d 
    $date1 = $date1.ToString().Replace("/", "-") 
    $time = get-date -format t 
     
    $time = $time.ToString().Replace(":", "-") 
    $time = $time.ToString().Replace(" ", "") 
     
    foreach ($n in $name) 
    { 
         
        $log += (Get-Location).Path + "\" + $folder + "\" + $n + "_" + $date1 + "_" + $time + "_.$Ext" 
    } 
    return $log 
} 
 
function LaunchEOL 
{ 
    param 
    ( 
        [Parameter(Mandatory = $true)] 
        $Credentials 
    ) 
     
    Write-Host "Enter Exchange Online Credentials" -ForegroundColor Green 
    $UserCredential = $Credentials 
     
     
    $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection 
     
     
    Import-pssession $Session -Prefix "EOL" 
} 
 
Function RemoveEOL 
{ 
     
    $Session = Get-PSSession | where { $_.ComputerName -like "outlook.office365.com" } 
    Remove-PSSession $Session 
     
} 
####################Variables/Logs########################### 
$log = Write-Log -Name "EOLUM-Report" -folder "logs" -Ext "log" 
$Report = Write-Log -Name "EolUM-Report" -folder "Report" -Ext "csv" 
 
$smtpserver = "SMTPServer" 
$erroremail = "ReportsLogs@labtest.com" 
$from = "DoNotReply@labtest.com" 
 
$collection = @() 
 
$reportexlpath = "E:\scripts\EOLUMReport.xlsx" 
Start-transcript -path $log 
 
##################Userid & password################# 
$userId = "MGMT-SVC@labtest.com" 
$encrypted1 = Get-Content .\password1.txt 
$pwd = ConvertTo-SecureString -string $encrypted1 
$Credential = New-Object System.Management.Automation.PSCredential -ArgumentList $userId, $pwd 
 
###########Start main script and fetch data from EOL### 
 
try 
{ 
    LaunchEOL -Credentials $Credential 
} 
catch 
{ 
    $($_.Exception.Message) 
    Write-Host "exception has occured loading EOL Shell" -ForegroundColor Yellow 
    Send-MailMessage -SmtpServer $smtpserver -From $from -To $erroremail -Subject "EOL Shell Error UM Report" -Body $($_.Exception.Message) 
    break; 
} 
 
$EOlUMMBX = Get-EOLUMMailbox -resultsize:unlimited 
 
 
$EOlUMMBX | foreach-object{ 
    Write-host "Processing................. "$_.DisplayName"" -foregroundcolor green 
     
    $UMrep = "" | select DisplayName,Identity, PrimarySmtpAddress,Extensions,PhoneNumber,UMEnabled,UMDialPlan,UMMailboxPolicy 
 
    $UMrep.DisplayName = $_.DisplayName 
    $UMrep.Identity = $_.Identity 
    $UMrep.PrimarySmtpAddress = $_.PrimarySmtpAddress 
    $UMrep.Extensions = $_.Extensions 
    $UMrep.PhoneNumber = $_.PhoneNumber 
    $UMrep.UMEnabled = $_.UMEnabled 
    $UMrep.UMDialPlan = $_.UMDialPlan 
    $UMrep.UMMailboxPolicy = $_.UMMailboxPolicy 
    $Collection += $UMrep 
} 
 
#export the collection to csv , change the path accordingly 
 
$Collection | export-csv $Report -notypeinformation 
 
#################Recycle Logs/reports################### 
 
    $path1 = ".\report\" 
    $path2 = ".\Logs\" 
 
    $limit = (Get-Date).AddDays(-60) #for report recycling 
    Get-ChildItem -Path $path1 | Where-Object { 
        $_.CreationTime -lt $limit 
    } | Remove-Item -recurse -Force 
     
    Get-ChildItem -Path $path2 | Where-Object { 
        $_.CreationTime -lt $limit 
    } | Remove-Item -recurse -Force 
     
    get-date 
 
 
##################Save to Excel#################### 
if(test-path -path $reportexlpath){ 
 remove-item -Path $reportexlpath -Force 
} 
.\ConvertCSV2XL.ps1 -csvpath $Report -Exceloutputpath $reportexlpath 
 
 
Stop-Transcript 
 
Send-MailMessage -SmtpServer $smtpserver -From $from -To $erroremail -Subject "Transcript Log - Exchange Online UM Report" -Attachments $log 
 
#######################################################################################