Write Win32_Product result to database thr ODBC

PowerShell Help
1

by qjanda at 2012-12-09 17:49:05

HI There,
I have a requirement to write some infomation obtained by WMI to DB by thru SQL INSERT INTO clause ,but failure.I can’t understand how to write a right insert clause.I really don’t understand how a scalar to receive a object .It maybe many pitfall.

please reference as below code .


$process = gwmi Win32_process|select name
$Names = $process
foreach ($p in $Names) {
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=2012IT001;DataBase=TEST;Uid=sa;PWD=abcd@1234")
$cn.Open() $strCommand = "INSERT INTO [TEST].[DBO].software VALUES (‘2012IT001’,‘$p.Name’)"
$cmd = New-Object system.data.Sqlclient.Sqlcommand($strCommand,$cn)
$cmd.ExecuteNonQuery()
$cmd.Dispose()
$cn.close
}

But i get SoftWare columes form "@{name = oneNOTE.exe}" ,actually i only want to "oneNOTE.exe".
I know maybe call string function to filter ,But i want to know any good idea?

thank you very much.
by Infradeploy at 2012-12-10 02:00:00
and ‘get-process’ in stead of WMI?
by cmille19 at 2012-12-10 04:35:04
Enclose $p.Name in $():
"INSERT INTO [TEST].[DBO].[software](StrComputer,SoftWare) VALUES ('2012IT001','$($p.Name)')"
by qjanda at 2012-12-10 19:19:38

#Enumerate specific OU computers.
Function List-Hosts
{
$ou = [ADSI]"LDAP://OU=WorkStations,DC=ASLHk,DC=com"
foreach ($child in $ou.psbase.Children)
{
if ($child.ObjectCategory -like ‘computer’)
{
$child.Name
}
}
}

#Detect whethere online by ping CMD.
Function IsAlive($strTarget)
{
if (((ping -n 2 -w 1000 $strTarget|Out-String).ToLower()).contains("reply from"))
{
return $true
}
else
{
return $false
}

}

Function Write-Records($strComputer)
{
begin{
$connection = New-Object -ComObject ADODB.Connection
$strConn = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test;User=sa;Password=abcd@1234;Option=3;"
$connection.Open($strConn)
}
process{
$input|foreach {$}{
$name = $.Name
$vendor = $.Vendor
$version = $.Version
$command = "INSERT INTO software2(DATE,TIME,HostName,Name,Vendor,Version) VALUES (CURDATE(),CURTIME(),‘$strComputer’,‘$name’,‘$vendor’,‘$version’)"
Write-Host $command
$connection.Execute($command)
}
}
end {
$connection.Close()
}
}

#obtain installed software list by thru WMI win32_Product class.
Function Get-InstalledSoftware($ComputerName)
{
Write-Output "Get software info....>" $ComputerName
$lists = Get-WmiObject win32_Product -ComputerName $ComputerName |where {$_.Name -NOTmatch "^Microsoft*"}|Write-Records($ComputerName)
}





$strTarget = List-Hosts

foreach($pc in $strTarget)
{

if(IsAlive($pc))
{
Get-InstalledSoftware($pc)
}
else
{
}
}





thanks,the problem has been solved.
this script for inventory software in domain and write to Database.
Powershell Function it’s really a pitfall.Just suggest use pipe to instead of.