Write Win32_Product result to database thr ODBC

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 10 months ago.

  • Author
    Posts
  • #6373

    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](StrComputer,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.

You must be logged in to reply to this topic.