Powershell How to query multiple classes and write in to SQL Table

Question

Not knowing Powershell very well and indeed sql it's taken me all week to piece together a script thatkind of works but I need help expanding on it. It works for retrieving more than 1 value from the same class and inserts the values. What I need to do is to be able to retrieve values from other classes and insert the result in to the appropriate column. Any help / advice would be so appreciated.

$servernames = Get-WmiObject -computername Anycompname -class win32_ComputerSystem |     Select Name, Manufacturer

# Open Connection
$conn = New-Object System.Data.SqlClient.SqlConnection
$connectionString = "Server=;Database=;user=;pwd=;"
$conn.ConnectionString = $connectionString
$conn.Open()

# Create the Command object to execute the queries
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]::Text

# Write Data
foreach ($servername in $servernames)
{
# Compose Query for this $servername - watch the single quotes for string values!!
$query = "INSERT INTO dbo.U_Server (ServerName, OSVersion) VALUES ('" +        $servername.Name + "', '" + $servername.Manufacturer + "')"

# Uncomment next line to display query for checking
$query 

# Setup Command
$cmd.CommandText = $query

# Execute Command to insert data for this $drive
$result = $cmd.ExecuteNonQuery() 
}

# Tidy Up 
$conn.Close()

Bruce · Accepted Answer · 2013-11-25 18:02:19Z

0

Just get the other WMI object(s) in the foreach loop and keep adding to your insert string as needed.


foreach ($servername in $servernames)
{
##  Get other WMI info as needed here.
$os = Get-WmiObject -class win32_operatingsystem –computername $servername.Name
$processors = Get-WmiObject Win32_Processor -computername $servername.Name

# Compose Query for this $servername 
$query = "Insert Into dbo.U_Server (ServerName, OSVersion, OSName, OSServicePack) Values 
('$($servername.Name)', '$($servername.Manufacurer)', ‘$($os.Caption)’, ‘$($os.ServicePackMajorVersion)’ )"

##  The rest of your foreach loop follows

}  ##  End of foreach

edited Nov 25, 2013 at 18:02

answered Nov 22, 2013 at 14:04

Bruce

1,6231 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2841861 Over a year ago

Thanks a million Bruce. I will try this out and report back

user2841861 Over a year ago

Hey Bruce - when expanding the foreach loop how should it look? # Write Data foreach ($servername in $servernames),($OS in $OS) or foreach ($servername in $servernames) | ($OS in $OS) Thanks in advance. Really, really new to this:-)

Bruce Over a year ago

I expanded my answer to show where/how to get added WMI information.

user2841861 Over a year ago

Thanks Bruce - Will try this out asap.

user2841861 Over a year ago

Cheers Bruce. Got my head round it and it works. Thanks again. Sorry for delay in trying this out.

Collectives™ on Stack Overflow

Powershell How to query multiple classes and write in to SQL Table

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related