Rob Simmers and Don Jones,
First, thanks for your time once again!
I’m not running this on a server =p. I’m trying to run it on my workstation with “Windows 7 Professional” and “Office 2013 Home and business (Click-to-run)”.
The idea of having an Excel file is not to use it as an important database. We only need some fast and updated information about our servers. That’s why the scheduled task only runs once in the day. Nothing really big deal, but we need to fill this file automatically.
About the profile, that’s the reason I’ve created those desktop folders on Windows\System32\config\systemprofile and Windows\SysWOW64\config\systemprofile.
Read about it on this link (last post):
http://stackoverflow.com/questions/22670344/powershell-script-cannot-access-a-file-when-run-as-a-scheduled-task
And yes, I’ve tested and it works on a “Windows 7 Professional” with “Office 2010 (not click-to-run version)”. We only have to create those two folders and it works.
Let me try to show you what I’m doing:
1. Have PowerShell 4.0 and VMware PowerCLI 6 installed
2. Created the script below [portuguese-brazilian]
`
$pwd = Get-Content C:\VMware\powerclicred | ConvertTo-SecureString
$cred = New-Object System.Management.Automation.PSCredential “user$”, $pwd
Connect-VIServer -Server server1, server2, server3, server4, server5 -Credential $cred
function HostInfo {
#$WorkBook01.Sheets.Item($ESXiServer.Name).Activate()
$TotalGB = $Null
$Datastores = Get-VMHost $ESXiServer.Name | Get-Datastore | Where {($.Name -notlike “VMD*”) -and ($.Name -notlike “BKP*”)}
foreach ($Datastore in $Datastores) {
$TotalGB += $Datastore.CapacityGB
}
($WorkBook01.Sheets.Item($ESXiServer.Name)).Cells.Item(7,2) = $ESXiServer.NumCpu
($WorkBook01.Sheets.Item($ESXiServer.Name)).Cells.Item(7,3) = ($ESXiServer | select @{N=“RAM”;E={[math]::Round($_.MemoryTotalGB,0)}}).RAM
($WorkBook01.Sheets.Item($ESXiServer.Name)).Cells.Item(7,4) = [math]::Round($TotalGB,0)
($WorkBook01.Sheets.Item($ESXiServer.Name)).Cells.Item(7,6) = "VMware “+($ESXiServer).Version+” build "+($ESXiServer).Build
($WorkBook01.Sheets.Item($ESXiServer.Name)).Cells.Item(7,7) = if ($ESXiServer.PowerState -eq 1){“Ligada”}else{“Desligada”}
}
function Recursos {
$ServidoresAtivos = Get-VM -Server $ESXiServer.Name | Where {$.PowerState -eq “PoweredOn”}
$CPUUtilizados = $Null
$RAMUtilizada = $Null
$TotalHD = $Null
foreach ($CPU in $ServidoresAtivos.NumCpu){
$CPUUtilizados += $CPU
}
foreach ($RAM in $ServidoresAtivos.MemoryGB){
$RAMUtilizada += $RAM
}
$ServidorRAM = ($ESXiServer | select @{N=“RAM”;E={[math]::Round($.MemoryTotalGB,0)}}).RAM
$TotalCPU = $ESXiServer.NumCpu - $CPUUtilizados
$Datastores = Get-VMHost $ESXiServer.Name | Get-Datastore | Where {($.Name -notlike “VMD*”) -and ($.Name -notlike “BKP*”)}
foreach ($Datastore in $Datastores) {
$TotalFreeGB += $Datastore.FreeSpaceGB
}
($WorkBook01.Sheets.Item($ESXiServer.Name)).Cells.Item(8,2) = $TotalCPU
($WorkBook01.Sheets.Item($ESXiServer.Name)).Cells.Item(8,3) = [math]::Round(($ServidorRAM - $RAMUtilizada),0)
($WorkBook01.Sheets.Item($ESXiServer.Name)).Cells.Item(8,4) = [math]::Round($TotalFreeGB,0)
}
function ServerInfo {
foreach ($Servidor in $Servidores) {
#$WorkBook01.Sheets.Item($ESXiServer).Activate()
$TotalHD = $Null
$HardDisks = (Get-HardDisk $Servidor).CapacityGB
foreach ($HardDisk in $HardDisks) {
$TotalHD += $HardDisk
}
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,1) = $Servidor.Name
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,2) = $Servidor.NumCpu
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,3) = [math]::Round($Servidor.MemoryGB)
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,4) = [math]::Round($TotalHD,0)
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,5) = (Get-Datastore -VM $Servidor.name).name
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,6) = $Servidor.Guest.OSFullName
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,7) = if ($Servidor.PowerState -eq 1){“Ligada”}else{“Desligada”}
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,8) = $Servidor.Version.ToString()
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,9) = ($Servidor.Guest.ExtensionData).ToolsStatus.ToString()
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Linha,10) = if (Get-VM $Servidor | Get-Snapshot){“Sim”}else{“Não”}
#($WorkBook01.Sheets.Item($ESXiServer)).Columns.Item(“A:I”).EntireColumn.AutoFit() | out-null
($WorkBook01.Sheets.Item($ESXiServer)).Columns.Item(“A:I”).HorizontalAlignment = -4108
$Linha = $Linha + 1
}
}
$ESXiServers = Get-VMHost | Sort Name
$Excel01 = New-Object -ComObject Excel.Application
$Excel01.Visible = $False
$Workbook01 = $Excel01.Workbooks.Open(“C:\VMware\Arquivos\ConfigVirtual.xlsx”)
for ($i = 1; $i -le ($ESXiServers.Count); $i++)
{
$WorkSheet = $WorkBook01.Sheets.Item($i)
#$WorkSheet.Activate()
While ($Worksheet.Cells.Item(9,1).Value() -NotLike $Null){
[void]$Worksheet.Cells.Item(9,1).EntireRow.Delete()
}
}
foreach ($ESXiServer in $ESXiServers) {
HostInfo
Recursos
}
foreach ($ESXiServer in $ESXiServers.Name) {
$Linha = 9
$Servidores = (Get-VM -Server $ESXiServer | Where {$_.Name -notlike “Unknown*”})
ServerInfo
}
$WorkBook01.Save()
#$WorkBook01.Close($True)
$Excel01.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel01) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook01) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Worksheet) | Out-Null
$Excel01 = $Null
`
3. This scheduled task
Run everyday at 5:00 a.m. this command: C:\VMware\AgConfigVirtual.bat
4. bat content
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -PSConsoleFile “C:\Program Files (x86)\VMware\Infrastructure\vSphere PowerCLI\vimaxx.psc1” -command “ampersand{C:\VMware\AgConfigVirtual.ps1}” > C:\VMware\Arquivos\ErrCmd.txt
PS: The PSconsole file is another one that the default because I had to remove the PSModules info from it to work.
PS2: Wrote ampersand because the special character was being erased of the final text.
5. Content of ErrCmd.txt file
`
Name Port User
server1 443 DOMAIN\user$
server2 443 DOMAIN\user$
server3 443 DOMAIN\user$
server4 443 DOMAIN\user$
server5 443 DOMAIN\user$
New-Object : Falha na recupera‡Æo de f brica de classes COM do componente com
CLSID {00024500-0000-0000-C000-000000000046} devido ao seguinte erro: 80080005
Falha na execu‡Æo do servidor (Exce‡Æo de HRESULT: 0x80080005
(CO_E_SERVER_EXEC_FAILURE)).
No C:\VMware\AgConfigVirtual.ps1:67 caractere:12
A propriedade ‘Visible’ nÆo foi encontrada neste objeto. Verifique se a
propriedade existe e pode ser definida.
No C:\VMware\AgConfigVirtual.ps1:68 caractere:1
… etc etc …
`
I’m thinking on something about the click-to-run version of Office 2013 than is different that MSI installed version… The tricky thing is why it worked yesterday…
Every research I make it shows DCOM security settings (already done) and ASPNET on IIS (which I don’t have on my machine).
Sorry about the english. Trying to learn (english and powershell) =p
[New-Object], COMExcept