Send network info directly to SQL table

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!

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
}

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

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()
}

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: (:slight_smile: , 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

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!

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!