Home » IT - Microsoft » SRM Reports

SRM Reports

Escribe tu dirección de correo electrónico para suscribirte a este blog, y recibir notificaciones de nuevos mensajes por correo.

Join 5 other followers

May 2016
M T W T F S S
« Mar   Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

NO! A LOS TOROS

Disclaimer

All messages posted to this blog are provided "AS IS" with no warranties, and confer no rights. The content of this site are personal opinions and might not represent the Microsoft Corporation view. Regarding any sample code that we provide: This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. This blog serves 2 purposes. Firstly, I want to share information with other IT pros about the technologies we work with and how to solve problems we often face. Secondly, I use my blog as a notebook. There's so much to learn and remember in our jobs that it's impossible to keep up. By blogging, I have a notebook that I can access from anywhere. Anything you do to your IT infrastructure, applications, services, computer or anything else is 100% down to your own responsibility and liability. Mcselles bears no responsibility or liability for anything you do. Please independently confirm anything you read on this blog before doing whatever you decide to do.
Advertisements

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:

  1. Contact your DBA and Business Objects administrator and let them sort out a nice report by trial and error
  2. 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 2008 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 2008 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

Source: http://www.getshifting.com/wiki/srmreport


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Microsoft on the Issues

News and perspectives on legal, public policy and citizenship topics

Mike Crowley's Whiteboard

“There are no limits to what you can accomplish when you are supposed to be doing something else."

T.B.D.

There Be Dragons

Ken Cenerelli

My life in software development

VMware, Windows, Virtualization (Servers & Desktops)

VMware, Windows, Virtualization (Servers & Desktops)

Just a random "Microsoft Server / Client Tech" info..

"Feeding Your Training and Technology Obsessions"

WordPress.com

WordPress.com is the best place for your personal blog or business site.

DocSharing

Documentación técnica, notas y apuntes sobre Administración de Sistemas, Servidores, Redes y más

Microsoft Taste

Mary's Blog

%d bloggers like this: