Get Inventory of virtual platform in XLS format
============================================================================
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Addsheet = $Excel.sheets.Add()
$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Name = "VMDetails"
$Sheet.Cells.Item(1,1) = "Name"
$Sheet.Cells.Item(1,2) = "Power State"
$Sheet.Cells.Item(1,3) = "Memory (MB)"
$Sheet.Cells.Item(1,4) = "Number of CPUs"
$Sheet.Cells.Item(1,5) = "Guest Operating system"
$Sheet.Cells.Item(1,6) = "Total Storage"
$Sheet.Cells.Item(1,7) = "DataStore Path"
$Sheet.Cells.Item(1,8) = " Description"
$WorkBook = $Sheet.UsedRange
$WorkBook.Font.Bold = $True
$intRow = 2
$colItems = Get-VM | Select-Object -property "Name","PowerState" ,"MemoryMB","NumCPU" ,"OSName", "ProvisionedSpaceGB","VmxDatastoreFullPath","Description"
foreach ($objItem in $colItems)
{
$Sheet.Cells.Item($intRow,1) = $objItem.Name
$powerstate = $objItem.PowerState
If ($PowerState -eq 1) {$power = "Powerd On"}
Else {$power = "Powerd Off"}
$Sheet.Cells.Item($intRow,2) = $power
$Sheet.Cells.Item($intRow,3) = $objItem.MemoryMB
$Sheet.Cells.Item($intRow,4) = $objItem.NumCPU
$Sheet.Cells.Item($intRow,5) = $objItem.OSName
$Sheet.Cells.Item($intRow,6) = [math]::Round(($objItem.ProvisionedSpaceGB),2)
$Sheet.Cells.Item($intRow,7) = $objItem.VmxDatastoreFullPath
$Sheet.Cells.Item($intRow,8) = $objItem.Description
$intRow = $intRow + 1
}
$WorkBook.EntireColumn.AutoFit()
$Sheet = $Excel.WorkSheets.Item(2)
$Sheet.Name = "DataStoreDetails"
$Sheet.Cells.Item(1,1) = "Name"
$Sheet.Cells.Item(1,2) = "Free Space"
$Sheet.Cells.Item(1,3) = "Capacity"
$WorkBook = $Sheet.UsedRange
$WorkBook.Font.Bold = $True
$intRow = 2
$colItems = Get-Datastore "hit*" | Select-Object -property "Name","FreeSpaceMB","CapacityMB"
foreach ($objItem in $colItems)
{
$Sheet.Cells.Item($intRow,1) = $objItem.Name
$Sheet.Cells.Item($intRow,2) = [math]::Round(($objItem.FreeSpaceMB/1024), 2 )
$Sheet.Cells.Item($intRow,3) = $objItem.CapacityMB /1024
$intRow = $intRow + 1
}
$WorkBook.EntireColumn.AutoFit()
$Sheet = $Excel.WorkSheets.Item(3)
$Sheet.Name = "HostDetails"
$Sheet.Cells.Item(1,1) = "Name"
$Sheet.Cells.Item(1,2) = "State"
$Sheet.Cells.Item(1,3) = "Physical Memory"
$Sheet.Cells.Item(1,4) = "# of VM"
$Sheet.Cells.Item(1,5) = "Used vCPU"
$Sheet.Cells.Item(1,6)= "ProcessorType"
$Sheet.Cells.Item(1,7) = "version"
$Sheet.Cells.Item(1,8) = "Build"
$Sheet.Cells.Item(1,9) = "Manufacturer "
$Sheet.Cells.Item(1,10) = "Model"
$Sheet.Cells.Item(1,11) = "CpuTotalMhz"
$Sheet.Cells.Item(1,12) = "CpuUsageMhz"
$WorkBook = $Sheet.UsedRange
$WorkBook.Font.Bold = $True
$intRow = 2
$colItems = Get-VMhost | Select-Object -property "Name","State" ,"MemoryTotalGB" , "NumberOfPoweredOnVMs" ,"NumPoweredOnvCPUs","ProcessorType" ,"version","build" ,"Manufacturer" ,"Model" , "CpuTotalMhz", "CpuUsageMhz"
foreach ($objItem in $colItems)
{
$Sheet.Cells.Item($intRow,1) = $objItem.Name
$state = $objItem.State
If ($state -eq 0) {$status = "Connected"}
Else {$status = "Disconnected"}
$Sheet.Cells.Item($intRow,2) = $status
$Sheet.Cells.Item($intRow,3) = $objItem.MemoryTotalGB
$Sheet.Cells.Item($intRow,4) = $objItem.NumberOfPoweredOnVMs
$Sheet.Cells.Item($intRow,5) = $objItem.NumPoweredOnvCPUs
$Sheet.Cells.Item($intRow,6) = $objItem.ProcessorType
$Sheet.Cells.Item($intRow,7) = $objItem.Version
$Sheet.Cells.Item($intRow,8) = $objItem.Build
$Sheet.Cells.Item($intRow,9) = $objItem.Manufacturer
$Sheet.Cells.Item($intRow,10) = $objItem.Model
$Sheet.Cells.Item($intRow,11) = $objItem.CpuTotalMhz
$Sheet.Cells.Item($intRow,12) = $objItem.CpuUsageMhz
$intRow = $intRow + 1
}
$WorkBook.EntireColumn.AutoFit()
##Netowork Card details
##Save the Report
$date=Get-Date
$Folder = "c:\shashi\repo"
$FileName ="$Folder\Inventory-{0}-{1:d2}-{2:d2}.xlsx" -f $date.Year,$date.Month,$date.Day
$Excel.SaveAs($Filename)
$Excel.Close()
============================================================================
Script to disconnect the sessions with the Idle time - 30 min ============================================================================
Function Get-ViSession {
<#
.SYNOPSIS
Lists vCenter Sessions.
.DESCRIPTION
Lists all connected vCenter Sessions.
.EXAMPLE
PS C:\> Get-VISession
.EXAMPLE
PS C:\> Get-VISession | Where { $_.IdleMinutes -gt 5 }
#>
$SessionMgr = Get-View $DefaultViserver.ExtensionData.Client.ServiceContent.SessionManager
$AllSessions = @()
$SessionMgr.SessionList | Foreach {
$Session = New-Object -TypeName PSObject -Property @{
Key = $_.Key
UserName = $_.UserName
FullName = $_.FullName
LoginTime = ($_.LoginTime).ToLocalTime()
LastActiveTime = ($_.LastActiveTime).ToLocalTime()
}
If ($_.Key -eq $SessionMgr.CurrentSession.Key) {
$Session | Add-Member -MemberType NoteProperty -Name Status -Value "Current Session"
} Else {
$Session | Add-Member -MemberType NoteProperty -Name Status -Value "Idle"
}
$Session | Add-Member -MemberType NoteProperty -Name IdleMinutes -Value ([Math]::Round(((Get-Date) – ($_.LastActiveTime).ToLocalTime()).TotalMinutes))
$AllSessions += $Session
}
$AllSessions
}
Function Disconnect-ViSession {
<#
.SYNOPSIS
Disconnects a connected vCenter Session.
.DESCRIPTION
Disconnects a open connected vCenter Session.
.PARAMETER SessionList
A session or a list of sessions to disconnect.
.EXAMPLE
PS C:\> Get-VISession | Where { $_.IdleMinutes -gt 30 } | Disconnect-ViSession
.EXAMPLE
PS C:\> Get-VISession | Where { $_.Username -eq "User19" } | Disconnect-ViSession
#>
[CmdletBinding()]
Param (
[Parameter(ValueFromPipeline=$true)]
$SessionList
)
Process {
$SessionMgr = Get-View $DefaultViserver.ExtensionData.Client.ServiceContent.SessionManager
$SessionList | Foreach {
Write "Disconnecting Session for $($_.Username) which has been active since $($_.LoginTime)"
$SessionMgr.TerminateSession($_.Key)
}
}
}
Get-VISession | Where { $_.IdleMinutes -gt 30 } | Disconnect-ViSession
