insert output of Get-WmiObject -Class "Win32_computersystem" to table

PowerShell Help
1

Hi,

I would like to write the result of

Get-WmiObject -Class "Win32_computersystem" | Format-List -Property *

to a SQL server table.

I have have created a table that matches the output and I had hoped that below code would do the trick.

Get-WmiObject -Class "Win32_computersystem" | Format-List -Property * | Write-SqlTableData -ServerInstance $TargetServerName -DatabaseName $TargetDatabaseName -SchemaName $TargetSchemaName -TableName $TargetTableName -Force

 

The above code does not perform the insert. I would be grateful for input to achieve what I aim for :slight_smile:

 

Thanks

2

Do you get an error? If so, what is the error?

Just looking at the syntax I would think you don’t want to pipe to Format-List if you are going to pass the objects to pipe the objects to Write-SqlTableData. Format-List will change the objects to format type objects and is usually used for display on the screen not to pipe to other commands.

3

Hi Mike.

If I do not use the | Format-List -Property * I will not get all the output that I am would like. Is there a better way ?

4

By “output you would like”, do you mean what is displayed on the screen? When you run a command like Get-WmiObject -Class “Win32_computersystem” objects are returned but only select properties are displayed to the screen, but that does not mean other properties are not present. If you pipe the command to Get-Member you will see all the possible properties. Format-List * is just a way to display all the properties * in a list format. In doing so though it is now creating different object types than what was originally returned. If you run Get-WmiObject -Class “Win32_computersystem” | Get-Member and then run Get-WmiObject -Class “Win32_computersystem” | format-List * | Get-Member you will see the difference.

 

5

Hi Mike.

Thank you for taking your time to explain that part. I have restructered the scriptline and got this now:

Invoke-command -ComputerName $sourceServerName -ScriptBlock {Get-WmiObject -Class "Win32_computersystem" } | Write-SqlTableData -ServerInstance $TargetServerName -DatabaseName $TargetDatabaseName -SchemaName $TargetSchemaName -TableName $TargetTableName -Force

The statement does not fail now but I do not get data into my table.

I was under the impression that a scriptBlock would resturn an object by default but is that not the case ?

 

6

WMI returns default properties, but there are hidden properties:

PS C:\WINDOWS\system32> Get-WmiObject -Class "Win32_computersystem"


Domain              : WORKGROUP
Manufacturer        : LENOVO
Model               : 81EQ
Name                : DESKTOP-123456
PrimaryOwnerName    : Rob
TotalPhysicalMemory : 17014935552




PS C:\WINDOWS\system32> Get-WmiObject -Class "Win32_computersystem"  | Select *


PSComputerName              : DESKTOP-123456
AdminPasswordStatus         : 0
BootupState                 : Normal boot
ChassisBootupState          : 3
KeyboardPasswordStatus      : 0
PowerOnPasswordStatus       : 0
PowerSupplyState            : 3
PowerState                  : 0
FrontPanelResetStatus       : 0
ThermalState                : 3
Status                      : OK
Name                        : DESKTOP-123456
PowerManagementCapabilities : 
PowerManagementSupported    : 
...

I’ve not used this cmdlet to write SQL data, but I’m not sure what it would do if you give it more data than the table schema. I would recommend removing the Invoke-Command and using Select-Object to send properties that match your table schema for testing:

Get-WmiObject -Class "Win32_computersystem" | 
Select-Object -Property Domain,Manufacturer,Model,Name,PrimaryOwnerName,TotalPhysicalMemory |
Write-SqlTableData -ServerInstance $TargetServerName -DatabaseName $TargetDatabaseName -SchemaName $TargetSchemaName -TableName $TargetTableName -Force
7

If you remove -Force do you get any errors?

8

I got it working using the following code

 

Invoke-command -ComputerName $sourceServerName -ScriptBlock {Get-WmiObject -Class “Win32_computersystem” | Select-Object * -ExcludeProperty __DERIVATION,OEMStringArray,Roles,SystemProperties,Scope,Path,Options,ClassPath,Properties,Qualifiers,BootStatus,ChassisSKUNumber,SystemFamily,SystemSKUNumber } -Credential $qaoneadrCredential | Write-SqlTableData -ServerInstance $TargetServerName -DatabaseName $TargetDatabaseName -SchemaName $TargetSchemaName -TableName $TargetTableName -Force

 

Thank you all for your support