user146
Contributor
Contributor

PowerCLI/PowerShell - working with properties and formatting output

Hi,

I'm working on script to gather details about specific hosts. The details are then output to an  excel workbook. I've run into some property data selection/formatting issues and would appreciate any help or guidance.

Here's a couple of examples of where I'm running into problems with extracting the exact strings I want.

Get-VMHost -Name $esxi_host | Select-Object Name - This outputs what appears to be a string like "Name : host-name". If I only want the host name, not the "Name:", how can I achieve this?

Get-VMHost -Name edi-pr-esx-13.cr.local | Get-datastore - I only want the DS name and capacity, not the header.

With the exception of the storage data my output to the excel workbook is almost right, but I've had to settle for the data being displayed encapsulated in "@{...}" which isn't very pleasing on the eye. If someone could suggest how improve this it would be helpful.

I've attached a copy my full script. Any advise or guidance welcome.

My powershell skill aren't great and this script will used by people with less skills that I have. I would to like to keep things simple, even if it mean the script has more lines.

Thanks.

0 Kudos
16 Replies
LucD
Leadership
Leadership

With the ExpandProperty parameter you avoid the @{} output.

Get-VMHost -Name $esxi_host | Select-Object -ExpandProperty Name

Not sure what you mean with the following failing?

$storage = Get-VMHost -Name $esxi_host | Get-datastore | select-object @{N="Datastore name"; E={$_.name}} , capacityGB  # This fails



I'm really wondering why you are using the COM object to write to an Excel file.
With the ImportExcel module this is a lot easier.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

user146
Contributor
Contributor

Thank you, ExpandProperty property is what I was looking for and works for mosts of the steps. I will read into this further later.

At the moment, ExpandProperty doesn't seem work with the lines below.

Failing lines:

Get-VMHost -Name $esxi_host | Select-Object -ExpandProperty @{N="CPU Sockets";E={$_.Extensiondata.Hardware.CpuInfo.NumCpuPackages}}

Get-VMHost -Name edi-pr-esx-13.cr.local | Select-Object -ExpandProperty @{N='DNS Server(s)';E={$_.Extensiondata.Config.Network.DnsConfig.Address -join ' , '}}

I tried removing the array (excuse me if that's the wrong term) so it only uses "{$_.Extensiondata.Hardware.CpuInfo.NumCpuPackages}" & "{$_.Extensiondata.Config.Network.DnsConfig.Address -join ' , '}" after ExpandProperty. This still leads to errors about the property not being found. I'll see if I can find the answers in info about ExpandProperty.

With regard to my failing comment, it was meant to say the command was failing to output to the Excel workbook, sorry.

My choice to use COM object was made due to information I read in a blog post. The blog post covered formatting, which is often where I struggle (more than normal).

I'll have a look into the module you suggested. However, at the moment, since I'm so close to achieving what I set out to do I would really like to at least complete it before recreating everything to work with something else.

Basically, my mistakes and choices are from inexperience and lack knowledge, but I'm slowly improving on both.

Thanks for replying.

0 Kudos
LucD
Leadership
Leadership

The ExpandProperty parameter accepts only 1 property, and no calculated property (which is what you are using).
You can construct your string with variable substitution.
Something like this for example

$numCpu = "$((Get-VMHost -Name $esxi_host).Extensiondata.Hardware.CpuInfo.NumCpuPackages)"
$dnsServers = "$((Get-VMHost -Name edi-pr-esx-13.cr.local).Extensiondata.Config.Network.DnsConfig.Address -join ' , ')"


Remember that Select-Object is intended to provide data to display, not to assign a value to a variable.

Using the COM object with all the cell manipulation is way too much overhead to my liking.
At least compared to the cmdlets in the ImportExcel module.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

user146
Contributor
Contributor

So your method is "getting" the value using the get-vmhost cmdlet and assigning it to a variable, where as my method is using select-object to select the value then trying to assign it to a variable, which shouldn't be done?

As you recommend using the ImportExcel module I will most likely change to using that for my report, if I can get it to work. At the moment I'd like to complete this script to show that we can move away from manual testing.

I'm still struggling to get my head round the gathering the datastore details. Using Get-Datastore provides a table of information. I'm aiming to use the DS name and CapacityGB values. What I can't work out is how to interegate the output? I tried using the method you provided for numcpu and dnsServers, but I can only get one set of values. I also tried Concat and Join methods, but it gets pretty messy. What approach should I take to breaking down the infomation output from Get-Datastore and using what I want?

0 Kudos
LucD
Leadership
Leadership

The Select-Object cmdlet, except when you use ExpandProperty, doesn't not return a scalar, but an object (hence the @{} you saw).

Not sure what you mean by "one set of values".
If you do that in a loop, you can get the values for each ESXi node, but you have export that then to a row in the XLSX file.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
user146
Contributor
Contributor

re: one set of values.

The output from get-datastores is a table with three columns that have the headers "Name", "FreeSpaceGB" and "CapacityGB". I would like to select the "Name" and "CapacityGB" information and output that to my Excel workbook.

I'm struggling to work with the data output from the cmdlet as I can't understand how it's put together and therefore can't try to manipulate it. For example, if I run "Get-VMHost $esxi_host | Get-Datastore | Select-Object name, CapacityGB" this provides the data I want. However, I can't get that into the Excel workbook. I thought prehaps I could break down the output and add parts of it to the Excel work book.

Below is an example of the output. I'm only really interested in the "ds-0* 10239.75/15359.75" part (not the headers). I would like to put each line into my Excel workbook. Is it very complex to do this? If it's well beyond my current skill level I'll have to except that, but I can't help feel that I'm over complicating it.

Name CapacityGB
----      ----------
ds-01 10239.75
ds-02 10239.75
ds-03 10239.75
ds-04 15359.75
ds-05 15359.75

0 Kudos
LucD
Leadership
Leadership

The Headers you see will also be the column headers in the XLSX file.
I'm not sure how you could create/use an XLSX file with column headers.
 
In fact, this is another reason why I suggested to use the ImportExcel module.
To get those values in the XLSX file, you just would need to do
Get-VMHost $esxi_host | Get-Datastore | Select-Object name, CapacityGB |
Export-Excel -Path .\report.xlsx -WorksheetName Get-Datastore


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
user146
Contributor
Contributor

As you advised I've changed to use the ExportExcel module. I've updated my script to use the cmdlets that are part of the module. Of course, my lack of knowledge has caused me some problems that I'm working through and I have some quesitons.

When I run my script the information gathered and output to the Excel worksheet is what I want. The problem I'm having is with the fortmatting. As expected, the data is output to column A of the worksheet, starting at A2 to be precise. I would like to move the data across one column to start at B2. I would like to create my own entries in the A column cells.

Should I do this during the inital worksheet createa and data capture (if possible), or after?

I found this article to be helpful - https://adamtheautomator.com/powershell-excel/. In it the author shows how to change the value of a cell. When testing the mentioned method I receive an error if I use a quoted string, and if I use a variable it doesn't fail, but it doesn't add the string assigned to the variable to the worksheet cell. I'm assuming I'm making a mistake, do you have any advice?

Exmaple:

```
$title = "title"

Export-Excel -Path "C:\temp\Qual_result_test.xlsx" -WorksheetName 'test1'
$excel = Open-ExcelPackage -Path "C:\temp\Qual_result_test.xlsx"
$worksheet = $excel.Workbook.Worksheets['test1']
$worksheet.Cells['A1'].$title    ## I tried "= 'title'" rather than ".$title" and received "Unable to index into an object of type OfficeOpenXml.ExcelRange."
Close-ExcelPackage $excel

```

Basically, I'm struggling to format the output and I'm unsure if it's better to create the worksheet with the data already formatted, or import the worksheet and then manipulate the data?

0 Kudos
LucD
Leadership
Leadership

There is quite a bit of redundancy in your code.
You can easily limit that, by getting the values only once.
You don't need to export to the XLSX for each value.
It is much better to create an object with all the values you want in the XLSX, that is in fact what the New-Object is doing.
This also makes it a lot easier to introduce an empty column.

ForEach ($esxi_host in $esxi_hosts) {
    # Get proprery values
    $esx = Get-VMHost -Name $esxi_host
    $vmk = Get-VMHostNetworkAdapter -VMHost $esx -Name 'vmk0'
    Get-Datastore -RelatedObject  $esx -PipelineVariable ds |
        ForEach-Object -Process {
            New-Object -TypeName PSObject -Property ([ordered]@{
                    EmptyColumn      = ''
                    Name             = $esx.Name
                    Version          = $esx.Version
                    Build            = $esx.Build
                    ProcessorType    = $esx.ProcessorType
                    CpuCount         = $esx.Extensiondata.Hardware.CpuInfo.NumCpuPackages
                    MemoryTotalGB    = $esx.MemoryTotalGB
                    'Datastore name' = $ds.Name
                    CapacityGB       = $ds.CapacityGB
                    IP               = $vmk.IP
                    SubnetMask       = $vmk.SubnetMask
                    DNS              = $esx.ExtensionData.Config.Network.DnsConfig.Address -join ','
                    PingResult       = if (Test-Connection -ComputerName $esxi_host -Quiet) {
                        "$esxi_host responds to ping"
                    } else {
                        "$esxi_host No ping response"
                    }
                })
        } | Export-Excel -Path "C:\temp\Qual_result_$esxi_host.xlsx" -WorksheetName "Baseline" -Append -AutoSize
} 

 


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
user146
Contributor
Contributor

The formatting of the worksheet is a little off when using the above script.

It duplicates the output for each datastore the host can see. For example, if the host can see 10 datastores the worksheet will have 10 rows of repeated info, with the only change being the datastore.

I don't mean to be rude with the above comments. Your skills are far superior to mine.

0 Kudos
LucD
Leadership
Leadership

So how do you want to see the layout of the worksheet?


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
user146
Contributor
Contributor

I'm trying to have more of a list layout than a table.

I've attached a screen shot of a mock up layout.

I had almost achieved this before, but the datastore layout is the main problem.

0 Kudos
LucD
Leadership
Leadership

I understand, but that is not really a spreadsheet.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
user146
Contributor
Contributor

The attached script that uses a COM object creates the spreadsheet. The problem with it was I couldn't format the datastore output and you advised using the export-excel module was a better approach.

I provided a mock up to show what I'm trying to achieve with the datastore details.

After following your advice I moved to the export-excel module, but I've not been able to achieve the same results.

0 Kudos
LucD
Leadership
Leadership

It would have helped had you included that mockup from the start 🙄

Sorry, I'm not really going to start counting cells and working with that COM object.



Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
user146
Contributor
Contributor

That's ok, I didn't come here to have someone do the work for me. I was only looking for guidance.

Although I didn't provide the mockup at the start I did provide the script and an explanation.

Thanks for your time, I apprecaite your help.

0 Kudos