locked
Inserting servername from a get-content foreach loop RRS feed

  • Question

  • I have a list on servers on a file called Servers.txt

    I am running the following powershell:

    $insertSql = @'
    
    Insert Into dbo.serverpatch ([Source],[Description],[HotFixID],[InstalledBy],[InstalledOn])
    Values('{0}','{1}','{2}','{3}','{4}')'@
    
     Get-Content C:\Servers.txt | foreach{
        Get-wmiobject -class win32_quickfixengineering |
            ForEach-Object{           
             $query = $insertSql -f $_.Source,$_.Description,$_.HotFixId,$_.InstalledBy,$_.InstalledOn
                Invoke-Sqlcmd  -ServerInstance Server\Instance -Query $query -Database MyDB
             }
         }

    This cycles through the servers in Servers.txt and inserts the update info into a SQL table. I need to include the server name as a column in the insert, but I cant seem to get it to work..

    Thanks,

    Mark

    Thursday, March 12, 2020 11:27 AM

Answers

  • you need Get-WmiObject -ComputerName $_ 

    otherwise you are always querying , at every loop, the win32_quichfixengineering class of your source pc.

    you could assing

    $thisservername =$_ #the servername at each loop

    you can add a column in your table "servername" and then add $thisservername as value 

    -----------------------------------

     Get-Content C:\Servers.txt | foreach{

    $thisServerName=$_

        Get-wmiobject -class win32_quickfixengineering  -computername $thisServerName |

            ForEach-Object{           
             $query = $insertSql -f $_.Source,$_.Description,$_.HotFixId,$_.InstalledBy,$_.InstalledOn,$thisServerName
                Invoke-Sqlcmd  -ServerInstance Server\Instance -Query $query -Database MyDB
             }
         }


    • Edited by baleng Thursday, March 12, 2020 11:52 AM
    • Marked as answer by mcai9mh3 Thursday, March 12, 2020 1:26 PM
    Thursday, March 12, 2020 11:50 AM

All replies

  • you need Get-WmiObject -ComputerName $_ 

    otherwise you are always querying , at every loop, the win32_quichfixengineering class of your source pc.

    you could assing

    $thisservername =$_ #the servername at each loop

    you can add a column in your table "servername" and then add $thisservername as value 

    -----------------------------------

     Get-Content C:\Servers.txt | foreach{

    $thisServerName=$_

        Get-wmiobject -class win32_quickfixengineering  -computername $thisServerName |

            ForEach-Object{           
             $query = $insertSql -f $_.Source,$_.Description,$_.HotFixId,$_.InstalledBy,$_.InstalledOn,$thisServerName
                Invoke-Sqlcmd  -ServerInstance Server\Instance -Query $query -Database MyDB
             }
         }


    • Edited by baleng Thursday, March 12, 2020 11:52 AM
    • Marked as answer by mcai9mh3 Thursday, March 12, 2020 1:26 PM
    Thursday, March 12, 2020 11:50 AM
  • That worked perfectly!

    Cheers!

    Thursday, March 12, 2020 1:27 PM