Hi I'm trying to use LucD's GetViEvent and Export-XLS functions.
For some reason when I export the data to a csv file the output is fine. But when I try to use the Export-XLS function the output ends up in the wrong columns.
I'm running the script from a bat file:
batch Code:
-----------------
PowerShell.exe -File D:\VM\Vmotion.ps1 -Server VcenterServer1 -DC Datacenter1
PS Code:
---------------
Param (
[string]$Server,
[string]$DC
)
#load Vmware Module
if ((Get-PSSnapin -Name VMware.VimAutomation.Core -ErrorAction SilentlyContinue) -eq $null )
{Add-PSsnapin VMware.VimAutomation.Core -ErrorAction SilentlyContinue}
#Change to multi-mode vcenter management
#Set-PowerCLIConfiguration -DefaultVIServerMode Multiple -Confirm:$false
#Get vCenter Server Names
$destVI = $Server
#connect to Destination Server
connect-viserver $destVI -WarningAction SilentlyContinue
$hours = 24 # Number of hours back
$start = (Get-Date).AddHours(-$hours)
$tasknumber = 999 # Windowsize for task collector
$eventnumber = 100 # Windowsize for event collector
$tgtTaskDescriptions = "VirtualMachine.migrate","Drm.ExecuteVMotionLRO"
$migrations = @()
$report = @()
# Get the guest for which we want the report
$vmHash = @{}
Get-Datacenter -Name $DC | Get-VM | %{
$vmHash[$_.Name] = $_.Host
}
#XLS File Name
$FileName = "D:\Reports\vmotion-history-.xls"
# Retrieve the vMotion tasks and the corresponding events
$taskMgr = Get-View TaskManager
$eventMgr = Get-View eventManager
$tFilter = New-Object VMware.Vim.TaskFilterSpec
$tFilter.Time = New-Object VMware.Vim.TaskFilterSpecByTime
$tFilter.Time.beginTime = $start
$tFilter.Time.timeType = "startedTime"
$tCollector = Get-View ($taskMgr.CreateCollectorForTasks($tFilter))
$dummy = $tCollector.RewindCollector
$tasks = $tCollector.ReadNextTasks($tasknumber)
while($tasks){
$tasks | where {$tgtTaskDescriptions -contains $_.DescriptionId} | % {
$task = $_
$eFilter = New-Object VMware.Vim.EventFilterSpec
$eFilter.eventChainId = $task.EventChainId
$eCollector = Get-View ($eventMgr.CreateCollectorForEvents($eFilter))
$events = $eCollector.ReadNextEvents($eventnumber)
while($events){
$events | % {
$event = $_
switch($event.GetType().Name){
"VmBeingHotMigratedEvent" {
$migrations += New-Object PSObject -Property @{
VMname = $task.EntityName
Source = $event.Host.Name
Destination = $event.DestHost.Name
Start = $task.StartTime
Finish = $task.CompleteTime
Result = $task.State
User = $task.Reason.UserName
DRS = &{if($task.DescriptionId -like "Drm.*"){$true}else{$false}}
}
}
Default {}
}
}
$events = $eCollector.ReadNextEvents($eventnumber)
}
$ecollection = $eCollector.ReadNextEvents($eventnumber)
# By default 32 event collectors are allowed. Destroy this event collector.
$eCollector.DestroyCollector()
}
$tasks = $tCollector.ReadNextTasks($tasknumber)
}
# By default 32 task collectors are allowed. Destroy this task collector.
$tCollector.DestroyCollector()
# Handle the guests that have been vMotioned
$grouped = $migrations | Group-Object -Property VMname
$grouped | Sort-Object -Property Count -Descending | where{$vmHash.ContainsKey($_.Name)} | %{
$i = 1
$row = New-Object PSObject
Add-Member -InputObject $row -Name VM -Value $_.Name -MemberType NoteProperty
$_.Group | Sort-Object -Property Finish | %{
# The original location of the guest
if($i -eq 1){
Add-Member -InputObject $row -Name ("Time" + $i) -Value $start -MemberType NoteProperty
Add-Member -InputObject $row -Name ("Host" + $i) -Value $_.Source -MemberType NoteProperty
$i++
}
# All the vMotion destinations
Add-Member -InputObject $row -Name ("Time" + $i) -Value $_.Finish -MemberType NoteProperty
Add-Member -InputObject $row -Name ("Host" + $i) -Value $_.Destination -MemberType NoteProperty
Add-Member -InputObject $row -Name ("DRS" + $i) -Value $_.DRS -MemberType NoteProperty
Add-Member -InputObject $row -Name ("User" + $i) -Value $_.User -MemberType NoteProperty
$i++
}
$report += $row
$vmHash.Remove($_.Name)
}
# Add remaining guests to report
$vmHash.GetEnumerator() | %{
$row = New-Object PSObject
Add-Member -InputObject $row -Name VM -Value $_.Name -MemberType NoteProperty
Add-Member -InputObject $row -Name Time1 -Value $start -MemberType NoteProperty
Add-Member -InputObject $row -Name Host1 -Value $_.Value -MemberType NoteProperty
Add-Member -InputObject $row -Name DRS1 -Value $false -MemberType NoteProperty
$report += $row
}
#
#-------------------------------------------------------------------
#requires -version 2
function Export-Xls{
<#
.SYNOPSIS
Saves Microsoft .NET Framework objects to a worksheet in an XLS file
.DESCRIPTION
The Export-Xls function allows you to save Microsoft .NET Framework objects
to a named worksheet in an Excel file (type XLS). The position of the
worksheet can be specified.
.NOTES
Author: Luc Dekens
.PARAMETER InputObject
Specifies the objects to be written to the worksheet. The parameter accepts
objects through the pipeline.
.PARAMETER Path
Specifies the path to the XLS file.
.PARAMETER WorksheetName
The name for the new worksheet. If not specified the name will
be "Sheet" followed by the "Ticks" value
.PARAMETER SheetPosition
Specifies where the new worksheet will be inserted in the series of
existing worksheets. You can specify "begin" or "end". The default
is "begin".
.PARAMETER ChartType
Specifies the type of chart you want add to the worksheet.
All types in the [microsoft.Office.Interop.Excel.XlChartType]
enumeration are accepted.
.PARAMETER NoTypeInformation
Omits the type information from the worksheet. The default is to
include the "#TYPE" line.
.PARAMETER AppendWorksheet
Specifies if the worksheet should keep or remove the existing
worksheet in the spreadsheet. The default is to append.
.EXAMPLE
PS> $data = Get-Process | Select-Object Name, Id, WS
PS> Export-Xls $data C:\Reports\MyWkb.xls -WorksheetName "WS" -AppendWorksheet:$false
.EXAMPLE
PS> $data = Get-Process | Select-Object Name, Id, WS
PS> Export-Xls $data C:\Reports\MyWkb.xls -SheetPosition "end"
.EXAMPLE
PS> $data = Get-Process | Select-Object Name, Id, WS
PS> Export-Xls $data C:\Reports\MyWkb.xls -WorksheetName "WS" -ChartType "xlColumnClustered"
#>
param(
[parameter(ValueFromPipeline = $true,Position=1)]
[ValidateNotNullOrEmpty()]
$InputObject,
[parameter(Position=2)]
[ValidateNotNullOrEmpty()]
[string]$Path,
[string]$WorksheetName = ("Sheet " + (Get-Date).Ticks),
[string]$SheetPosition = "begin",
[PSObject]$ChartType,
[switch]$NoTypeInformation = $true,
[switch]$AppendWorksheet = $true
)
begin{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
if($ChartType){
[microsoft.Office.Interop.Excel.XlChartType]$ChartType = $ChartType
}
function Set-ClipBoard{
param(
[string]$text
)
process{
Add-Type -AssemblyName System.Windows.Forms
$tb = New-Object System.Windows.Forms.TextBox
$tb.Multiline = $true
$tb.Text = $text
$tb.SelectAll()
$tb.Copy()
}
}
function Add-Array2Clipboard {
param (
[PSObject[]]$ConvertObject,
[switch]$Header
)
process{
$array = @()
if ($Header) {
$line =""
$ConvertObject | Get-Member -MemberType Property,NoteProperty,CodeProperty | Select -Property Name | %{
$line += ($_.Name.tostring() + "`t")
}
$array += ($line.TrimEnd("`t") + "`r")
}
else {
foreach($row in $ConvertObject){
$line =""
$row | Get-Member -MemberType Property,NoteProperty | %{
$Name = $_.Name
if(!$Row.$Name){$Row.$Name = ""}
$line += ([string]$Row.$Name + "`t")
}
$array += ($line.TrimEnd("`t") + "`r")
}
}
Set-ClipBoard $array
}
}
$excelApp = New-Object -ComObject "Excel.Application"
$originalAlerts = $excelApp.DisplayAlerts
$excelApp.DisplayAlerts = $false
if(Test-Path -Path $Path -PathType "Leaf"){
$workBook = $excelApp.Workbooks.Open($Path)
}
else{
$workBook = $excelApp.Workbooks.Add()
}
$sheet = $excelApp.Worksheets.Add($workBook.Worksheets.Item(1))
if(!$AppendWorksheet){
$workBook.Sheets | where {$_ -ne $sheet} | %{$_.Delete()}
}
$sheet.Name = $WorksheetName
if($SheetPosition -eq "end"){
$nrSheets = $workBook.Sheets.Count
2..($nrSheets) |%{
$workbook.Sheets.Item($_).Move($workbook.Sheets.Item($_ - 1))
}
}
$sheet.Activate()
$array = @()
}
process{
$array += $InputObject
}
end{
Add-Array2Clipboard $array -Header:$True
$selection = $sheet.Range("A1")
$selection.Select() | Out-Null
$sheet.Paste()
$Sheet.UsedRange.HorizontalAlignment = [microsoft.Office.Interop.Excel.XlHAlign]::xlHAlignCenter
Add-Array2Clipboard $array
$selection = $sheet.Range("A2")
$selection.Select() | Out-Null
$sheet.Paste() | Out-Null
$selection = $sheet.Range("A1")
$selection.Select() | Out-Null
$sheet.UsedRange.EntireColumn.AutoFit() | Out-Null
$workbook.Sheets.Item(1).Select()
if($ChartType){
$sheet.Shapes.AddChart($ChartType) | Out-Null
}
$workbook.SaveAs($Path)
$excelApp.DisplayAlerts = $originalAlerts
$excelApp.Quit()
Stop-Process -Name "Excel"
}
}
#-------------------------------------------------------------------
$report |
Export-Xls -Path $FileName -WorksheetName $DC
Disconnect-VIServer -Server $destVI -Force -confirm:$false
#End of script
Thanks
