SRM Reports

Summary: How to create a weekly report to check on the status of your VMware Site Recovery Manager 5.1.
Date: 19 April 2013
Refactor: 29 April 2025: Checked links and formatting.

vmware, srm, powershell

Unfortunately SRM has no options to create something like a weekly report on the status of your VMs. Also, there are no powerCLI commandlets or any other option to easily create something. So, there are basically two options what can be done to create something like a weekly report:

Contact your DBA and Business Objects administrator and let them sort out a nice report by trial and error
Use powerCLI to create a report without any SRM specific information

The first one requires a lot of additional effort by other people, which is something that is not planned for within the project here, so I had no other option to go for the second one.

Luckily there is one thing that is common between protected VMs that is kind of SRM specific and that can be queried through powerCLI. All VMs are represented by a placeholder VM, that is created on a specific datastore called the placeholder datastore. If you follow the best practices and guidelines from VMware you could say that all VMs on that datastore are protected. So that will be the core of our report.

What Will be Reported

First from the vCenter that is located at the recovery site a list of protected VMs will be created. Then, all VMs from this list will be queried against the vCenter from the protected site requesting their name, powerstate and datastore. This will all be put in a nice excel file and all the differences from last week will be reported. Then the final report will be sent by email.

Schedule

The script will be scheduled to run each week on Monday morning at 06:00 hours. This will be done from the managementserver and the service account SHIFT\SRV-vCenterJOB will be used.

See Schedule Tasks on Windows Server for more information on scheduling powershell scripts.

Note: You might need to add the service account to the local administrators group, allow logon as a batchjob and you need to set the powershell executionpolicy to remotesigned in as well the 32bits as the 64bits shell. See Schedule Tasks on Windows Server for the first requirements and Getting Started with PowerShell and vSphere PowerCLI for the others.

The Script

# Author: Sjoerd Hooft
# Date: 20130419
# Date: 20130924 - Added application description
 
# What does the script do
# 1. Create a list of all VMs on the placeholder datastore
# 2. Check for the status of the production VMs
# 3. List differences compared to last week
 
# Start-transcript
start-transcript -path D:\Scripts\SRM-Reports\logtranscript.txt
 
# Variables
$scriptname = [System.IO.Path]::GetFilenameWithoutExtension($MyInvocation.MyCommand.Path.ToString())
$scriptlocation = Split-Path $myinvocation.mycommand.path
$timestamp = Get-Date -format "yyyyMMdd-HH.mm"
$recoveryvcenter = "vCenter2.shift.local"
$protectedvcenter = "vCenter.shift.local"
$placeholderdatastore = "DS200_SATA_SRM_MGMT"
$MailServer = "10.10.10.25"
$toemail = "sjoerd@getshifting_com"
$ccmail = "sjoerd@getshifting_com"
$fromemail = "it@getshifting_com"
$ExcelFile = "$scriptlocation\$scriptname.xlsx"
 
# Functions
# Email functions
Function Send-Email ($subject, $info){
   Send-MailMessage -To $toemail -From $fromemail -cc $ccmail -SmtpServer $mailserver -Subject $subject -Body $info -Attachments "$ExcelFile"}
 
# Add VMware snapin
if(-not (Get-PSSnapin VMware.VimAutomation.Core -ErrorAction SilentlyContinue)){
   Add-PSSnapin VMware.VimAutomation.Core}
 
# Connect to vCenter on recovery site
Connect-VIServer $recoveryvcenter
 
# Create a list of all VMs on placeholderdatastore
#$vms = Get-Datastore $placeholderdatastore | Get-VM | Sort $_.Name
#$vms = Get-Datastore $placeholderdatastore | Get-VM | Sort $_.Name | Select -First 5
$vms = Get-Datastore $placeholderdatastore | Get-VM | Sort $_.Name
$countvms = $vms.Count
 
# Disconnect from vCenter
Disconnect-VIServer * -Confirm:$false
 
# Connect to vCenter on protected site
Connect-VIServer $protectedvcenter
 
# Set Excel properties
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
$workfile = $Excel.Workbooks.open($ExcelFile)
$worksheet = $workfile.Worksheets.Add()
$Sheet = $workfile.Worksheets.Item(1)
$Sheet.Name = "$timestamp"
 
# Remove old worksheets after half year if run each week
$workfile.Worksheets.Item(26).Delete()
 
# Fill in headers and additional information
# Header
$Sheet.Cells.Item(1,1) = "SHIFT DR Report: Protected Virtual Machines"
$range = $Sheet.Range("a1","e2")
$range.Style = 'Title'
$range.Merge() | Out-Null
$range.VerticalAlignment = -4108
$range.HorizontalAlignment = -4108
 
# Date Information
$readdate = Get-Date -format "d MMM yyyy"
$weekdate = Get-Date -uformat %V
$Sheet.Cells.Item(3,1) = "Date: $readdate"
$Sheet.Cells.Item(5,1) = "Week: $weekdate"
$range = $Sheet.Range("a3","b5")
$range.Style = 'Title'
$range.VerticalAlignment = -4108
$range.HorizontalAlignment = -4108
$range = $Sheet.Range("a3","b4")
$range.Merge() | Out-Null
$range = $Sheet.Range("a5","b5")
$range.Merge() | Out-Null
 
# Fill in VM Columns
$headerrow = 6
$Sheet.Cells.Item($headerrow,1) = "VMName"
$Sheet.Cells.Item($headerrow,2) = "VMHostName"
$Sheet.Cells.Item($headerrow,3) = "PowerState"
$Sheet.Cells.Item($headerrow,4) = "DataStore"
$Sheet.Cells.Item($headerrow,5) = "Application"
$range = $Sheet.Range("a$headerrow","e$headerrow")
$range.Font.Bold = $True
$range.Font.ColorIndex = 2
$range.Interior.ColorIndex = 1
 
# Start with data
$dataRow = $headerrow + 1
 
ForEach ($vm in $vms){
 
  $prdvm = Get-VM $vm
  if ($prdvm.PowerState -eq "PoweredOn"){
     $power = 1}
  else{
     $Power = 0}
  $vmview = Get-VM $vm | Get-View
  $ds = Get-VM $vm | Get-Datastore | ForEach {$_.Name}
  $appvmname = [string]$vmview.Name
  $app = [string]$appvmname.Substring(0,3)
  $file = [string]$appvmname.Substring(6,1)
  if     ($app -eq "ctx"){$application = "Citrix Environment"}
  elseif ($app -eq "bes"){$application = "Blackberry Environment"}
  elseif ($app -eq "dcs"){$application = "Domain Controller"}
  elseif (($app -eq "gds") -and ($file -eq "f")){$application = "Fileserver"}
  elseif (($app -eq "gds") -and ($file -eq "c")){$application = "Archive Controller"}
  elseif ($app -eq "off"){$application = "Office Server"}
  elseif ($app -eq "dbs"){$application = "Database Server"}
  elseif ($app -eq "shp"){$application = "SharePoint"}
  elseif ($app -eq "bos"){$application = "Business Objects"}
  elseif ($app -eq "top"){$application = "TOPdesk"}
  elseif ($app -eq "sol"){$application = "Solaris"}
  elseif ($app -eq "lnx"){$application = "Linux"}
  else {$application = "Not Defined"}
 
  $Sheet.Cells.Item($dataRow, 1) = [string]$vmview.Name
  $Sheet.Cells.Item($dataRow, 2) = [string]$vmview.Guest.HostName
  $Sheet.Cells.Item($dataRow, 3) = [string]$power
  $Sheet.Cells.Item($dataRow, 4) = [string]$ds
  $Sheet.Cells.Item($dataRow, 5) = [string]$application
 
  $datarow++
}
 
# Use a symbol to indicate wheter a VM is turned off
$startrow = $headerrow + 1
$endrow = $startrow + $countvms
$powerstatecol = "c"
$r1 = $powerstatecol + $startrow
$r2 = $powerstatecol + $endrow
$range = $Sheet.Range("$r1","$r2")
$range.VerticalAlignment = -4108
$range.HorizontalAlignment = -4108
 
$xlConditionValueNumber = 0
$xlIconSet = 6
$xl3Symbols = 7
$range.FormatConditions.Add($xlIconSet)
$range.FormatConditions.AddIconSetCondition
$range.FormatConditions.Item(1).ShowIconOnly = $true
$range.FormatConditions.Item(1).IconSet = $workfile.IconSets.Item($xl3Symbols)
$range.FormatConditions.Item(1).IconCriteria.Item(2).Value = 0
$range.FormatConditions.Item(1).IconCriteria.Item(2).Operator = 5
$range.FormatConditions.Item(1).IconCriteria.Item(3).Value = 1
$range.FormatConditions.Item(1).IconCriteria.Item(3).Operator = 7
 
# Save and close Excel sheet
$Sheet.SaveAs($ExcelFile)
$workfile.Close()
 
#Quit Excel
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
 
# Search for missing or added VMs
# Create csv files for easy comparison
# Set Excel properties
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
$workfile = $Excel.Workbooks.open($ExcelFile)
$Sheet = $workfile.Worksheets.Item(1)
$csvfile1 = "$scriptlocation\$scriptname-01.csv"
$csvfile2 = "$scriptlocation\$scriptname-02.csv"
$csvfile3 = "$scriptlocation\$scriptname-03.csv"
$csvfile4 = "$scriptlocation\$scriptname-04.csv"
$Sheet.SaveAs("$csvfile1", 6)
sleep 3
$LastWeekSheet = $workfile.Worksheets.Item(2)
$LastWeekSheet.SaveAs("$csvfile2", 6)
sleep 3
# Create csv files without headers
$skip = $headerrow - 1
Get-Content $csvfile1 | Select -skip $skip | Set-Content $csvfile3
sleep 3
Get-Content $csvfile2 | Select -skip $skip | Set-Content $csvfile4
sleep 3
$thisweek = Import-CSV $csvfile3
$lastweek = Import-CSV $csvfile4
#$lostvms = Compare $thisweek $lastweek -Property VMName | where {$_.SideIndicator -eq '=>'} | Select VMName | Format-Table -hidetableheader
#$addedvms = Compare $thisweek $lastweek -Property VMName | where {$_.SideIndicator -eq '<='} | Select VMName | Format-Table -hidetableheader
$lostvms = Compare $thisweek $lastweek -Property VMName | where {$_.SideIndicator -eq '=>'} | Select VMName | ForEach {$_.VMName}
$addedvms = Compare $thisweek $lastweek -Property VMName | where {$_.SideIndicator -eq '<='} | Select VMName | ForEach {$_.VMName}
 
#Quit Excel without saving
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
 
# Final Excel modifications
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
$workfile = $Excel.Workbooks.open($ExcelFile)
$Sheet = $workfile.Worksheets.Item(1)
 
# VM Comparison
#note: this section might have to move to the back when the lostvms and addedvms are known
$Sheet.Cells.Item(3,3) = "Protected VMs: $countvms"
$Sheet.Cells.Item(4,3) = "Missing VMs: $lostvms"
$Sheet.Cells.Item(5,3) = "Added VMs: $addedvms"
$range = $Sheet.Range("c3","e5")
$range.Style = 'Title'
$range.VerticalAlignment = -4108
$range.HorizontalAlignment = -4108
$range = $Sheet.Range("c3","e3")
$range.Merge() | Out-Null
$range = $Sheet.Range("c4","e4")
$range.Merge() | Out-Null
$range = $Sheet.Range("c5","e5")
$range.Merge() | Out-Null
 
# Set sheet formatting
$formatting = $Sheet.UsedRange
$formatting.EntireColumn.AutoFit()
 
# Save and close Excel sheet
$Sheet.SaveAs($ExcelFile)
$workfile.Close()
 
#Quit Excel
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
 
#Cleanup files
Remove-Item $csvfile1
Remove-Item $csvfile2
Remove-Item $csvfile3
Remove-Item $csvfile4
 
# Send Report
$subject = "BCP SRM Report week $weekdate"
$info = "Find the report attached to this email. Kind regards, Sjoerd Hooft."
Send-Email $subject $info
 
# Disconnect from vCenter
Disconnect-VIServer * -Confirm:$false
 
stop-transcript

Table of Contents

SRM Reports

What Will be Reported

Schedule

The Script