Send network info directly to SQL table

Tagged: ,

This topic contains 6 replies, has 2 voices, and was last updated by Profile photo of Creed Cordonier Creed Cordonier 5 months ago.

  • Author
    Posts
  • #60330
    Profile photo of Creed Cordonier
    Creed Cordonier
    Participant

    Hello!

    I have a small script that returns a clients computer name and network info...

    $computer = $ENV:COMPUTERNAME
    
    function netInfo() {
    
    get-wmiobject Win32_NetworkAdapterConfiguration -filter "IPEnabled=TRUE" -computername $computer | foreach-object {
      new-object PSObject -property @{
        "Computer" = $computer
        "MACAddress" = $_.MACAddress
        "IPAddress" = $_.IPAddress[0]
        "DefaultIPGateway" = $_.DefaultIPGateway[0]
      } | select-object Computer,MACAddress,IPAddress,DefaultIPGateway }
      }

    I am looking to pipe this to a script that I found online, which looks like it will send an INSERT to a table in SQL for me. I am already have the table working and taking INSERTS.

    Here is the link to that script.

    My question for this forum is about this section:

    $sqlCommand.CommandText = "SET NOCOUNT ON; " +
    "INSERT INTO dbo.NetworkInfo (Computer,MacAddress,IpAddress,DefaultGateWay) " +
    "VALUES (@Computer,@MacAddress,@IpAddress,@DefaulyGateWay); " +

    The line in bold is presumably where I need to set a variable containing the results from my 'netInfo' function. I am not sure how to get these two working together. I know I could have Powershell spit out a csv, then get-content, then take out the quotes, then set-content to another file, then import to SQL. I would MUCH rather just get these results and perform the INSERT directly.

    Any advice much appreciated. Thank you!

  • #60340
    Profile photo of Jonathan Warnken
    Jonathan Warnken
    Participant

    Below is an example of how to do what you want. The script stores the output from your function in a variable and then build the sql insert statement.

    $computer = $ENV:COMPUTERNAME
    
    function netInfo() {
    
    get-wmiobject Win32_NetworkAdapterConfiguration -filter "IPEnabled=TRUE" -computername $computer | foreach-object {
      new-object PSObject -property @{
        "Computer" = $computer
        "MACAddress" = $_.MACAddress
        "IPAddress" = $_.IPAddress[0]
        "DefaultIPGateway" = $_.DefaultIPGateway[0]
      } | select-object Computer,MACAddress,IPAddress,DefaultIPGateway }
      }
    
    $info = netInfo
    foreach($i in $info){
    $sqlCmdtxt = "SET NOCOUNT ON; INSERT INTO dbo.NetworkInfo (Computer,MacAddress,IpAddress,DefaultGateWay) VALUES ($($i.Computer),$($i.MacAddress),$($i.IpAddress),$($i.DefaultIPGateway)) " 
    Write-Output $sqlCmdtxt
    }
    • #60343
      Profile photo of Creed Cordonier
      Creed Cordonier
      Participant

      Very nice! This make a ton of sense. Right now, this is the whole script.

      #Import-Module Sqlps -DisableNameChecking;
      
      $computer = $ENV:COMPUTERNAME
      
      function netInfo() {
      
      get-wmiobject Win32_NetworkAdapterConfiguration -filter "IPEnabled=TRUE" -computername $computer | foreach-object {
        new-object PSObject -property @{
          "Computer" = $computer
          "MACAddress" = $_.MACAddress
          "IPAddress" = $_.IPAddress[0]
          "DefaultIPGateway" = $_.DefaultIPGateway[0]
        } | select-object Computer,MACAddress,IPAddress,DefaultIPGateway }
        }
      
      $info = netInfo
      foreach($i in $info){
      $sqlCmdtxt = "SET NOCOUNT ON; INSERT INTO dbo.NetworkInfo (Computer,MacAddress,IpAddress,DefaultGateWay) VALUES ($($i.Computer),$($i.MacAddress),$($i.IpAddress),$($i.DefaultIPGateway))" 
      Write-Output $sqlCmdtxt
      }
      
      # Open SQL connection
      $DBServer = "RQSASSETMGMT\RQSASTMGMT"
      $DBName = "RQSASTMGMT_NewCopy"
      
      $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server=$DBServer;Database=$DBName;Integrated Security=True;")
      #$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"
      $sqlConnection.Open()
      
      # Create SQL command
      $insert = $sqlConnection.CreateCommand()
      $insert.CommandText = $sqlCmdtxt
      $insert.ExecuteNonQuery()
      $sqlConnection.Close()

      It finishes with an error:

      Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near ':'."
      At line:33 char:1
      + $insert.ExecuteNonQuery();
      + ~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : SqlException

      I am still working on it.

      Thank you again!!

      **BONUS POINTS** I am also working on lumping the resultant object of this command into the INSERT as well. Advice here appreciated...

      $strDump = netsh wlan show interfaces
      $objInterface = "" | Select-Object BSSID
      
      foreach ($strLine in $strDump) {
      	if ($strLine -match "^\s+BSSID") {
      		$objInterface.BSSID = $strLine -Replace "^\s+BSSID\s+:\s+",""
      	}
      }
      
      $objInterface | Write-Output

      Thank you

  • #60358
    Profile photo of Jonathan Warnken
    Jonathan Warnken
    Participant

    There are a couple of things going on but the biggest on was you commented out the execution of the sql command.

    The other thing is how and when you are calling the functions. To simply the flow and help you understand the flow a bit better I took out the sql functions and just did the commands in the main script.

    The Overview of the script flow below is – The script loads the sqlps module and the netinfo function. Next come the variables to prep for the real work. Then the script opens the connection to the sql server and checks to ensure that it is open. If not the script will exit. If it is, you continue on to collecting the IPinfo and then inserting it into the database. Once the inserts are done the connection to the database is closed and the script ends.

    Import-Module Sqlps -DisableNameChecking;
    
    function netInfo() {
    
    get-wmiobject Win32_NetworkAdapterConfiguration -filter "IPEnabled=TRUE" -computername $computer | foreach-object {
      new-object PSObject -property @{
        "Computer" = $computer
        "MACAddress" = $_.MACAddress
        "IPAddress" = $_.IPAddress[0]
        "DefaultIPGateway" = $_.DefaultIPGateway[0]
      } | select-object Computer,MACAddress,IPAddress,DefaultIPGateway }
      }
    
    $computer = $ENV:COMPUTERNAME
    # Open SQL connection
    $DBServer = "RQSASSETMGMT\RQSASTMGMT"
    $DBName = "RQSASTMGMT_NewCopy"
    
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"
    $sqlConnection.Open()
    
    if ($sqlConnection.State -ne [Data.ConnectionState]::Open) {
        "Connection to DB is not open."
        Exit
    }
    
    $info = netInfo
    foreach($i in $info){
        $sqlCmdtxt = "SET NOCOUNT ON; INSERT INTO dbo.NetworkInfo (Computer,MacAddress,IpAddress,DefaultGateWay) VALUES ($($i.Computer),$($i.MacAddress),$($i.IpAddress),$($i.DefaultIPGateway)) " 
        $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
        $sqlCommand.Connection = $sqlConnection
        $sqlCommand.CommandText = $sqlCmdtxt
        $sqlCommand.ExecuteScalar()
    }
    
    if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {
        $sqlConnection.Close()
    }
    
    • #60360
      Profile photo of Creed Cordonier
      Creed Cordonier
      Participant

      Explaining the flow really helped. It all makes good sense. This will stick with me going forward. Thank you for that.

      It still doesn't seem to like something. Syntax error near ':' ...

      Exception calling "ExecuteScalar" with "0" argument(s): "Incorrect syntax near ':'."
      At line:38 char:5
      + $sqlCommand.ExecuteScalar()
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : SqlException

      This is probably to do with the MAC address? Thing is, I have MAC addresses formatted the same way already in this table. It is just a string data type.

      *UPDATE* I needed single quotes around all the sql VALUES variables. It worked after considering them strings.

        Thank you!

        Would you have any advice on somehow including my other object in the same sql insert?

        $strDump = netsh wlan show interfaces
        $objInterface = "" | Select-Object BSSID
        
        foreach ($strLine in $strDump) {
        	if ($strLine -match "^\s+BSSID") {
        		$objInterface.BSSID = $strLine -Replace "^\s+BSSID\s+:\s+",""
        	}
        }
        
        $objInterface | Write-Output
    • #60495
      Profile photo of Creed Cordonier
      Creed Cordonier
      Participant

      I have experimented with this a bit more, and am still looking for the best way (or any way, at this point), to integrate these objects into the same INSERT statement. The INSERT we worked on initially is working. Here is what I am working to integrate...

      # dump the netsh output into $wlanraw 
          $wlanraw = netsh wlan show interface
      
      # create the object as "empty"
          $objWlan = "" | Select-Object BSSID
      
      # populate the object from the output, processing 1 line at a time
          ForEach ($Line in $wlanraw) {if ([regex]::IsMatch($Line,"    BSSID")) {
          	 	$objWlan.BSSID = $Line -Replace"    BSSID                  : ",""
      		}
      }
      
      #$objWlan | Get-Member
      
      #$objWlan.BSSID | Out-File -FilePath #anywhere you like

      The commented out lines at the bottom are there just to show the output I need.

      Thank you!

    • #60549
      Profile photo of Creed Cordonier
      Creed Cordonier
      Participant

      Thank you so much for your direction on this. My final answer was much simpler than I expected. After a little break from it, here is the final working script...

      Import-Module Sqlps -DisableNameChecking;
      
      # dump the netsh output into $wlanraw 
          $wlanraw = netsh wlan show interface
      
      # create the object as "empty"
          $objWlan = "" | Select-Object BSSID
      
      # populate the object from the output, processing 1 line at a time
          ForEach ($Line in $wlanraw) {if ([regex]::IsMatch($Line,"    BSSID")) {
          	 	$objWlan.BSSID = $Line -Replace"    BSSID                  : ",""
      		}
      }
      
      $apMAC = $objWlan.BSSID
      
      $computer = $env:COMPUTERNAME
      
      function netInfo() {
      
      get-wmiobject Win32_NetworkAdapterConfiguration -filter "IPEnabled=TRUE" -computername $computer | foreach-object {
        new-object PSObject -property @{
          Computer = $computer
          MACAddress = $_.MACAddress
          IPAddress = $_.IPAddress[0]
          DefaultIPGateway = $_.DefaultIPGateway[0]  
        } | select-object Computer,MACAddress,IPAddress,DefaultIPGateway}
        }
      
      # Open SQL Connections
      $DBServer = "RQSASSETMGMT\RQSASTMGMT"
      $DBName = "RQSASTMGMT_NewCopy"
      $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
      $sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"
      $sqlConnection.Open()
      
      # Check if the connection is open, exit if not
      if ($sqlConnection.State -ne [Data.ConnectionState]::Open) {
          "Connection to DB is not open."
          Exit
      }
      
      # Load netInfo function into variable and execute SQL command
      $info = netInfo
      foreach($i in $info){
      $sqlCmdtxt = "SET NOCOUNT ON; INSERT INTO dbo.NetworkInfo (Computer,MacAddress,IpAddress,DefaultGateWay,ApMAC) VALUES ('$($i.Computer)','$($i.MacAddress)','$($i.IpAddress)','$($i.DefaultIPGateway)','$apMAC')" 
          $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
          $sqlCommand.Connection = $sqlConnection
          $sqlCommand.CommandText = $sqlCmdtxt
          $sqlCommand.ExecuteNonQuery()
      }
      
      # Close connection when finished
      if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {
          $sqlConnection.Close()
      }

      It will populate SQL with the host name, local MAC, IP address, Default Gateway, and more elusively, the MAC of the most recently connected wireless AP.

      Thank you!

You must be logged in to reply to this topic.