Bonjour a tous,
Je recherche actuellement du monde qui pourrais m'aider à créer un script me permettant d'exporter toutes les informations vmware de mes VM ( via rvtools) selon une liste de VM que j'ai prédéfinis sous un fichier excel.
N'étant vraiment pas bon en scripting j'aurais vraiment besoin d'aide pour résoudre ma problématique .
Par avance merci de vos intervention
cdt
Traduction :
Hello everyone,
I am currently looking for a world that could help me to create a script allowing me to export all the vmware information of my VM (via rvtools) according to a list of VM that I have predefined in an excel file.
Not really good in scripting I really need help to solve my problem.
In advance thank you for your intervention
Ok, I think I have a working solution.
It does require Doug's ImportExcel function, and it needs to run on a station where Excel is installed (due to the XLS to XSLX converson).
The resulting spreadsheet will be named <name-of-the vCenter>-new.xlsx
$ExportPath = 'C:\Temp'
$Server = 'MyVC'
$worksheets = 'tabvInfo','tabvCPU','tabvMemory','tabvDisk','tabvPartition','tabvNetwork','tabvTools'
$vmNames - 'C:\Temp\vmnames.txt'
# Run RVTools
. "C:\Program Files\RobWare\RVTools\RVTools.exe" -passthroughAuth -s "$Server" -c ExportAll2xls -d "$ExportPath" -f "$Server.xls"
while(Get-Process -Name 'RVTools' -ErrorAction SilentlyContinue){
sleep 5
}
# Convert XLS to XLSX
$xlsFileName = "$($ExportPath)\$($Server).xls"
$xlsxFileName = $xlsFileName.Replace('.xls','.xlsx')
$xlsxNewFileName = $xlsxFileName.Replace('.xlsx','-new.xlsx')
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("$($ExportPath)\$($Server).xls")
$excel.DisplayAlerts = $false
$workbook.SaveAs($xlsxFileName,
[int][Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing,
[int][Microsoft.Office.Interop.Excel.XlSaveConflictResolution]::xlLocalSessionChanges,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing)
$excel.Quit()
$excel.DisplayAlerts = $true
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel
# Contains the names of the VMs that need to be included
$vms = Get-Content -Path $vmNames
# Filter on worksheets and in each sheet on VM names
foreach($ws in (Get-ExcelSheetInfo -Path $xlsxFileName | where{$worksheets -contains $_.Name})){
Import-Excel -Path $xlsxFileName -WorkSheetname $ws.Name | where{$vms -contains $_.VM} |
Export-Excel -Path $xlsxNewFileName -WorkSheetname $ws.Name
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
You can use RvTools in batch mode
There is no need to duplicate the functionality in a script imho.
See for example RVTools: Use it and Automate it
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
thank you,
The only thing I still can not find is the call to an external file (excel) to give me information about my vm list.
Not sure what you mean.
The call to rvtools.exe will create XLS files in the designated folder.
Do you want a command to open the XLS files in your script?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
When I launch my future script, I would like it to call an excel file (with a list of my pre-inserted VMs) and that the result of my script be the list of my VMs of my excel file with all the information That rvtools can give. You see what I'm trying to do? If you want I can give you an explanatory diagram?
Thank you
No, I think I see what you are trying to do.
You want a report, like RvTools, on a selected set of VMs.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
The script I'm trying to create call my predefined VM list in an excel file.
Then rvtools make me export to another excel file depending on the list of vm
An additional question, which tabs do you want to get?
RVTools works based on the vCenter connection, and returns all kinds of information in different tabs.
Not all of those tabs are VM related.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
I would like :
tabvInfo
tabvCPU
tabvMemory
tabvDisk
tabvPartition
tabvNetwork
tabvTools
Ok, I think I have a working solution.
It does require Doug's ImportExcel function, and it needs to run on a station where Excel is installed (due to the XLS to XSLX converson).
The resulting spreadsheet will be named <name-of-the vCenter>-new.xlsx
$ExportPath = 'C:\Temp'
$Server = 'MyVC'
$worksheets = 'tabvInfo','tabvCPU','tabvMemory','tabvDisk','tabvPartition','tabvNetwork','tabvTools'
$vmNames - 'C:\Temp\vmnames.txt'
# Run RVTools
. "C:\Program Files\RobWare\RVTools\RVTools.exe" -passthroughAuth -s "$Server" -c ExportAll2xls -d "$ExportPath" -f "$Server.xls"
while(Get-Process -Name 'RVTools' -ErrorAction SilentlyContinue){
sleep 5
}
# Convert XLS to XLSX
$xlsFileName = "$($ExportPath)\$($Server).xls"
$xlsxFileName = $xlsFileName.Replace('.xls','.xlsx')
$xlsxNewFileName = $xlsxFileName.Replace('.xlsx','-new.xlsx')
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("$($ExportPath)\$($Server).xls")
$excel.DisplayAlerts = $false
$workbook.SaveAs($xlsxFileName,
[int][Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing,
[int][Microsoft.Office.Interop.Excel.XlSaveConflictResolution]::xlLocalSessionChanges,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing,
[Type]::Missing)
$excel.Quit()
$excel.DisplayAlerts = $true
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel
# Contains the names of the VMs that need to be included
$vms = Get-Content -Path $vmNames
# Filter on worksheets and in each sheet on VM names
foreach($ws in (Get-ExcelSheetInfo -Path $xlsxFileName | where{$worksheets -contains $_.Name})){
Import-Excel -Path $xlsxFileName -WorkSheetname $ws.Name | where{$vms -contains $_.VM} |
Export-Excel -Path $xlsxNewFileName -WorkSheetname $ws.Name
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Ok I'll test your script right away. In the vmnames file you should note the vm like this:
Xxxxx1; xxx2; ?
Thank you I make you a quick return
I have an export of all my infrastructure and not the specified vm
error =>>>>> $vmNames - 'C:\Temp\vmnames.txt'
$vmNames = 'C:\Temp\vmnames.txt'
but not wokk for the moment
In vmnames.txt, the names of the VMs should be one name per line, no headers.
Like this
vm1
vm2
vm3
From the error message it looks as if you haven't installed the ImportExcel module.
If you are on PowerShell 5.x, you can do
Find-module -Name ImportExcel | Install-Module
You can check your PowerSHell version by displaying the content of the variable $PSVersionTable.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Ok, I'm in powershell 2.0, so I have to install powershell 5.0 and now the modul Install is ok.
Everything works fine your script is fabulous.
Thank you thank you thank you
You have done a great super job I am really grateful.
LUCD is the best