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.

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

Download the zipped solution from above and extract it.

 

First step is to update EOLUMExtensionReport.ps1

 

####################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###

Update the variables marked in yellow

 

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
Edit|Remove
<#     
    .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 
 
#######################################################################################
 

 

Thanks for downloading

Sukhija Vikas

http://SysCloudPro.com