Hello, vCenter!
lookup servers list in vCenter seeking responsible username or decommission date assigned to the server. Enabling us to update our users excel sheet straight away with huge time saving, although the output.xlsx file still needs to be double-checked manually due to non-patterned names values in vCenter.
If the server doesn’t exist in the vCenter then a message will be written out to PowerCLI terminal informing that, those will be excluded from the output excel sheet for further investigation.
The Output updated list.
We start with creating the output file where we will see our servers — responsible username/decommission date new list.
#Creat the output Excel
Write-Output "Creating the output file Servers_owners.xlsx in c:\temp " $excel_file_path = 'c:\example\Servers_owners.xlsx'
$Excel = New-Object -ComObject Excel.Application
$ExcelWorkBook = $Excel.Workbooks.Open($excel_file_path)
$ExcelWorkSheet = $Excel.WorkSheets.item("sheet1")
$ExcelWorkSheet.activate()
$ExcelWorkSheet.Visible = $True
$row=1
$col=1
Time to append the first thing, Headers! we have only two variables/columns “server” and “responsible”
# Add the headers to the worksheet
$headers = "Server","Responsible"
$headers | foreach {
$ExcelWorkSheet.Cells.Item($row,$col)=$_
$col++
}Now the output excel sheet is ready to receive the new values
Connect to the servers.
Since we are working with PowerCLI to query data from vCenter, then connecting to the servers is a crucial step.
#connect to the servers
Write-Output "Connecting to the servers / vCenter...." connect-viserver VMware-server-1,VMware-server-2,example-3
What to lookup?
I added all the servers needed to be looked-up in one text file so the “.txt” servers list should be ready by now, time to look it up. The script will ask for the list path input, please enter it.
#the unknown servers list
$listpath = Read-Host "Enter the server list.txt Path"
$servers = Get-Content $listpathQuerying the usernames.
The script will iterate around the servers list and grab the property/label called "Name" of each from the vCenter. If a machine doesn’t exist in the vCenter then the script will printout that this machine doesn’t exist in the vCenter, so you can later investigate them.
As mentioned previously the variable “Name” is an unclean string with extra information, however, you can always find consist string patterns to follow and later scan the list or double-check it for weird values!
Examples;
- The first space always occurs after the server name, server name is the string before the first space.
- The responsible username comes after “resp:”, username is the string after “resp:”
- The decommission date appears after one of those substrings “decomm” , “DEL “
Hence, the script is going to string manipulate the Variable “Name” to only extract the server name, the responsible username, and the decommission date if there is, then append it to the output file Servers_owners.xlsx in c:\example.
Beside excluding the non-existing servers, error handling is also implemented to tell you which server/row in the output file had a problem so you can manually check it.
#querying the names
Write-Output "Querying the data...."
$i = 0
foreach ($s in $servers)
{
$line = get-vm | where {$_.name -match "$s*"} | select name
if($line -eq $null)
{
Write-Output $s "DOES NOT EXIST in the vCenter"
}else{
try
{
if($line -match "resp:")
{
#server name
$sn= $line -split " "
$srn= $sn[0] -replace "^@{Name="
#appending the server name to the excel sheet
$row++
$col=1
$ExcelWorkSheet.Cells.Item($Row,$col)=$srn
#responsible name
$re = $line -split "resp:"
$res= $re[1] -replace "}$"
#appending the server responsible to the excel sheet
$col++
$ExcelWorkSheet.Cells.Item($Row,$col)=$res
#count the server index on the list
$i++
}elseif($line -match "resp ") {
#server name
$sn= $line -split " "
$srn= $sn[0] -replace "^@{Name="
#appending the server name to the excel sheet
$row++
$col=1
$ExcelWorkSheet.Cells.Item($Row,$col)=$srn
#responsible name
$re = $line -split "resp "
$res= $re[1] -replace "}$"
#appending the server responsible to the excel sheet
$col++
$ExcelWorkSheet.Cells.Item($Row,$col)=$res
#count the server index on the list
$i++
}elseif($line -match "decomm"){
#server name
$sn= $line -split " "
$srn= $sn[0] -replace "^@{Name="
#appending the server name to the excel sheet
$row++
$col=1
$ExcelWorkSheet.Cells.Item($Row,$col)=$srn
#responsible name
$re = $line -split "decomm"
$res= $re[1] -replace "}$"
#appending the server responsible to the excel sheet
$col++
$ExcelWorkSheet.Cells.Item($Row,$col)=$res
#count the server index on the list
$i++
}elseif($line -match "DEL "){
#server name
$sn= $line -split " "
$srn= $sn[0] -replace "^@{Name="
#appending the server name to the excel sheet
$row++
$col=1
$ExcelWorkSheet.Cells.Item($Row,$col)=$srn
#responsible name
$re = $line -split "DEL "
$res= $re[1] -replace "}$"
#appending the server responsible to the excel sheet
$col++
$ExcelWorkSheet.Cells.Item($Row,$col)=$res
#count the server index on the list
$i++
}
}catch{
#error handeling
"Error occurred in row number: " +$i + " check final excel sheet"
}
}
}Closing the output excel file.
Time to close the created output file.
#Close the workbook and exit Excel
Write-Output "Closing and saving your Excel >> c:\temp\Servers_owners.xlsx"
$ExcelWorkBook.Close($true)
$Excel.quit()Disconnect the servers.
Don't forget to disconnect the servers!
#disconnect to the servers
Write-Output "Disconnecting the servers / vCenter...."
disconnect-viserver VMware-server-1,VMware-server-2,example-3How to execute the script.
Save the script as a PowerShell script ".ps1"
Make sure you create an excel sheet in your "c:\example" file named Servers_owners.xlsx
invoke the script from PowerCLI
Enter the server lits.txt path as an input parameter.
script = "C:\example\responsquering.ps1"
Invoke-Expression -Command $scriptDisclaimer!
When I created this script it was an effort to save time and energy on searching servers’ responsible.
It is just simple automation for manually copy/paste process, therefore if the server name value in vCenter doesn’t align with the patterns implemented the script won’t be able to give the correct output.
e.g.
@{Name= ServerName_theResponsibleUsername+decommesiondate 20.20.2020}
Then the script output will be something like;
server:
ServerName_theResponsibleUsername+decomm
responsible:
esiondate 20.20.2020
Always manually edit the string manipulation with your VMware value’s patterns and then double-check the output excel sheet.
