ImportExcel: Douglas Finke has created an amazing module for creating Excel workbooks, even when Excel is not installed on the system you run the script from. This is a must have for your pragmatic PowerShell tool box. I use it all the time. I’ve linked to it’s page on the PowerShell gallery.
Rubrik: The official Rubrik module provided by the Rubrik team. Can be installed from the PowerShell Gallery
Rubrik PowerShell Module
# Start Rubrik Daily Report
# Start setup
$fdate = Get-Date -Format "yyyy-MM-dd"
$rootpath = "D:\PowerShell_Scripts"
$credpath = #full path including filename
# setup dir structure and filename
$BackUpfilename = [String]$fdate + "_svr_backup_health_check.xlsx"
$BackUpoutpath = "$rootpath\backup\out"
$BackUpscriptDir = "$rootpath\backup\script"
$BackUpfulloutpath = $BackUpoutpath + '\' + $fdate.Split('-')[0] + '\' + $fdate.Split('-')[1] +'\'
$BackUppath = $BackUpfulloutpath + $BackUpfilename
$BackUpstartstring = "BacKUpReport - Started"
$BackUpexitString = "BackupReport - Ending"
$BackUpSubject = 'Daily Server Backup Compliance Report'
$SMTPServer = #smtpsrv
$MailSender = #noreply
$mailDL = # List of email addresses
$Named_Report = #The base report name you want to use from the Rubrik appliance report gallery
# set the event source if doesn't exist
New-EventLog -LogName "Application" -Source "CustomScripts" -ErrorAction SilentlyContinue
$eventid = 1020
$RubrikSVR = #DNS name or IPV4 address
#
Write-EventLog -LogName Application -Source "CustomScripts" -EventId $eventid -EntryType Information -Message $BackUpstartstring
if(Test-Path $credpath) {
$RubrikCredential = Import-CliXml -Path $credpath
Connect-Rubrik -Server $RubrikSVR -Credential $RubrikCredential
} else {
Write-EventLog -LogName Application -Source "CustomScripts" -EventId $eventid -EntryType Error -Message "No Rubrik credential exiting"
Exit
}
# Global variable set by Connect-Rubrikm if it succeeds
if($global:RubrikConnection.Token) {
$rresult = Get-RubrikReport -Name $Named_Report | Get-RubrikReportData # Must select a report that reports backup events
# Convert result to PSCustomeObject - Code from Rubrik module pull request
# "Added structured output to Get-RubrikReportData - #549 #635"
$rresult = $rresult | Select-Object -Property *,@{
name = 'DataObject'
expression = {
$rresult.datagrid | ForEach-Object {
$_ | ForEach-Object -Begin {
$Count = 0
$HashProps = [ordered]@{}
} -Process {
$HashProps.$($rresult.columns[$Count]) = $_
$Count++
} -End {
[pscustomobject]$HashProps
}
}
}
}
$rdata = $rresult.DataObject
# do some clean up of the results to aide readability
$rdata | Add-Member -NotePropertyName 'Name' -NotePropertyValue $null
$output = @()
Foreach($r in $rdata) {
# improve the date columns when we use them in excel
$r.StartTime = [datetime]::parseexact($r.startTime, 'yyyy-MM-dd HH:mm:ss', $null)
$r.EndTime = [datetime]::parseexact($r.endTime, 'yyyy-MM-dd HH:mm:ss', $null)
if($null -eq $Name) {
$N = "$($r.Location)\$($r.ObjectName)"
switch ($r.ObjectType)
{
'LinuxFileset' {
$r.Name = $r.Location
}
'Mssql' {
$r.Name = $N
}
'SQL Server DB' {
$r.Name = $N
}
'NutanixVirtualMachine' {
$r.Name = $r.ObjectName
}
'ShareFileset' {
$r.Name = $r.Location
}
'VmwareVirtualMachine' {
$r.Name = $r.ObjectName
}
'WindowsFileset' {
$r.Name = $r.Location
}
'Windows Volumes' {
$r.Name = $r.location
}
'WindowsVolumeGroup' {
$r.Name = $r.location
}
}
}
}
# Create Excel file
$xl = $output | Select Name,ObjectType,TaskStatus,TaskType,StartTime,EndTime,FailureReason | Export-Excel -Path $BackUppath -WorksheetName RubrikData -TableName RubrikData -BoldTopRow -FreezeTopRow -AutoNameRange -PassThru
$ws = $xl.Workbook.Worksheets["RubrikData"]
$ws.Column(1) | Set-Format -AutoFit
$ws.Column(2) | Set-Format -AutoFit
$ws.Column(3) | Set-Format -AutoFit
$ws.Column(4) | Set-Format -AutoFit
$ws.Column(5) | Set-Format -NumberFormat "dd MMMM HH:mm" -AutoFit
$ws.Column(6) | Set-Format -NumberFormat "dd MMMM HH:mm" -AutoFit
$ws.Column(7) | Set-Format -Width 29 -WrapText
Export-Excel -ExcelPackage $xl -WorkSheetname "RubrikData"
if(Test-Path $BackUppath){
Send-MailMessage -To $mailDL -From $MailSender -SmtpServer $SMTPServer -Subject $BackUpSubject -Body "See attached Excel file. This is an automated scheduled daily report." -Attachments $BackUppath
Write-EventLog -LogName Application -Source "CustomScripts" -EventId 2000 -EntryType Information -Message $BackUpexitString
}
# End Get Rubrik data
Exit
} else {
Write-EventLog -LogName Application -Source "CustomScripts" -EventId $eventid -EntryType Error -Message "No Rubrik Connection"
Exit
}
