MySQL inserts with Powercli

Everyone who knows RVTools may wish to add the data into his own database for further investigation of the configuration data. A simple way is to use Powercli and create the appropriate inserts as an sql statement.

The following script uses a generic way to create SQL INSERTS of a arbitrary Powercli command. It is very simple and puts every value in a text field.

function insert-command($command, $table)
{
  $array = @()

  $myexpression = "$command | gm -membertype properties"
  $members = invoke-expression $myexpression
  $items = invoke-expression $command

  foreach($item in $items)
  {
    foreach ($member in $members)
    {
      $insert+= $member.name+", "
      $values+= "'"+$item.($member.name)
      $values+="', "
    }

    $insert = $insert.trimend(", ")
    $values = $values.trimend(", ")

    $insertstring = "INSERT INTO "+$table+" ("+ $insert+") values ("+$values+")"

    $values="
    $insert ="
    $array += $insertstring  
  }
  return $array
}

##### Call
$myinserts1 = insert-command "get-vmhost" "vmhost-table"
$myinserts2 = insert-command "get-vm" "vm-table"

Creating a database connection to mysql :

$MySQLDatenbankName = "db_test"

$UserName = "username"
$Password = "password"

## MySQL Provider
[void][system.reflection.Assembly]::LoadFrom("C:Program Files (x86)MySQLMySQL Connector Net 6.9.4Assembliesv2.0MySql.Data.dll")

$MySqlConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$MySqlConnection.ConnectionString = "server=$MySQLServerName;user id=$Username;password=$Password;database=$MySQLDatenbankName;pooling=false"

$MySqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand

# INSERTS

$MySqlCommand.CommandText = $myinserts1 # Here is the line result of the created inserts
$MySqlConnection.Open()
$numberOfRows = $mySqlCommand.ExecuteNonQuery();

write-host $numberOfRows " Zeile eingetragen" #$insert
$MySqlConnection.Close()