foreach loop ot insert multiple rows into database

Question

I need to figure out who has logged into an endpoint. We have a SID history table that I am using as a conversion table. This will result in a username instead of SID. The connection works fine, but it only inserts one event from the event log.

I included the insert statement into the foreach loop. What am I missing?

The sample host I am running this on has over 15 events, I need to insert all 15 of those events into a MySQL database so I can query for what machines a specific SID has logged into.

Below is the simple and ugly code. The connection works, but is only inserting one row at a time.

# MySQL Connection string #
function Connect-MySQL([string]$user,[string]$pass, [string]$MySQLHost,[string]$database) {
  # Load MySQL .NET Connector Objects
  [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

  $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
  $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
  $conn.Open()
  return $conn
}

# Close Conn
function Disconnect-MySQL($conn) {
  $conn.Close()
}

# MySQL Connection Vars #
$user = ''
$pass = ''
$database = ''
$MySQLHost = ''

$conn = Connect-MySQL $user $pass $MySQLHost $database

function Execute-MySQLNonQuery($conn, [string]$query) {
  $command = $conn.CreateCommand()
  $command.CommandText = $query
  $RowsInserted = $command.ExecuteNonQuery()
  $command.Dispose()

  if ($RowsInserted) {
    return $RowInserted
  } else {
    return $false
  }
}

$ErrorActionPreference = 'silentlycontinue'

$events = Get-Eventlog -Log system -ComputerName '.' | Where-Object {
  (($_.EventID -like "*7001*") -or
  ($_.EventID -like "*7002*"))
} | Select-Object replacementstrings, machinename, timegenerated ;
foreach ($event in $events) {
  $outObj = new-object psobject -Property @{
    user = $event.ReplacementStrings[1]
    time = $event.timegenerated
    machine = $event.machinename
  }

  $uservar = $outObj.user
  $timevar = $outObj.time
  $machinevar = $outObj.machine

  $query = "INSERT INTO infosec.ewarp_testing (data1,data2,data3) VALUES ('$uservar', '$timevar','$machinevar')"
  echo "$uservar $timevar $machinevar"
  $Rows = Execute-MySQLNonQuery $conn $query
}

Remove the line $ErrorActionPreference = 'silentlycontinue' and see if something breaks. Also, verify that $events actually contains more than one item. — Ansgar Wiechers
nothing break when I take the EA off. $Events has over 15 results when I echo it outside or inside the loop. If I echo $uservar outside the loop I only get one return to the console- $events I get all of them.... :/ — Charles

Hatem Jaber Hatem Jaber · Accepted Answer · 2015-09-28T11:53:21

I'm not sure how dot net works, never used it, but looking at your code, you are looping and actually doing a query for each record. Maybe it's only committing the last record or just one of them, not sure since I don't know dot net. But one thing that you can do is to run a single query inserting all the values at one time, here is a solution based on your code.

$query = "INSERT INTO infosec.ewarp_testing (data1,data2,data3) VALUES ('$uservar', '$timevar','$machinevar')"

change that line to where it is concatenating a string of values that would like so:

$values = ""
$values = $values + "('$uservar', '$timevar','$machinevar'),"

This will build a string of "values" putting a comma between each set of (...), which you will have to remove the last one with some sort of string function I would imagine.

Once the loop is completed, you would go ahead and take all the values and run a single query outside of the loop:

$query = "INSERT INTO infosec.ewarp_testing (data1,data2,data3) VALUES $values"

I hope this helps.

foreach loop ot insert multiple rows into database

1 Answers