This might take you 6 minutes to read.

I have created a SMA Runbook that is triggered once everyday to update a MySQL database with information about VM:s. The information is stored for administrative usage.

The short story of the script:

  1. Get information from VMM about VM:s
  2. Get information from database
  3. Compare data and update differences

Complications

What was complicated was that I could not execute the VMM and MySQL on the same server.

The VMM-code and MySQL-code could not be executed outside “Innerscript”.

Database

A database created as following:

CREATE TABLE `TABLENAME` (
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `VMid` varchar(45) NOT NULL,
  `CustomerID` varchar(45) DEFAULT NULL,
  `VMName` varchar(45) DEFAULT NULL,
  `CiID` varchar(45) DEFAULT NULL,
  `Subscription` varchar(45) DEFAULT NULL,
  `FQDN` varchar(45) DEFAULT NULL,
  `ClusterName` varchar(45) DEFAULT NULL,
  `CreationTime` varchar(45) DEFAULT NULL,
  `ModifiedTime` varchar(45) DEFAULT NULL,
  `Deleted` varchar(45) DEFAULT NULL,
  `Cloud` varchar(45) DEFAULT NULL,
  `OperatingSystem` varchar(45) DEFAULT NULL,
  `CPUCount` int(11) DEFAULT NULL,
  `CPUType` varchar(45) DEFAULT NULL,
  `MaximumMemory` int(11) DEFAULT NULL,
  `BootDisk` int(11) DEFAULT NULL,
  `ExtraDisk` int(11) DEFAULT NULL,
  `VlanName` varchar(45) DEFAULT NULL,
  `Vlan` varchar(45) DEFAULT NULL,
  `IP` varchar(45) DEFAULT NULL,
  `HasVMAdditions` varchar(45) DEFAULT NULL,
  `VMAddition` varchar(45) DEFAULT NULL,
  `VirtualMachineState` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`VMid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

The first part, extract data from VMM

Get VMM-conection data from SMA Variable.

$VmmConnection = Get-AutomationConnection -Name 'VmmConnection'
$VmmServerName = $VmmConnection.ComputerName
$SecurePassword = ConvertTo-SecureString -AsPlainText -String $VmmConnection.Password -Force
$VmmCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $VmmConnection.Username, $SecurePassword

Run the Inlinescript on the VMM-server and return the array of hash tables to a variable.

$VMServers += inlinescript {
# Code will be triggered here on the VMM-server.
} -PSComputerName $VmmServerName -PSCredential $VmmCredential

This part is inside the inline script.

I have statically written the hostgroup I want to collect data from: hostgrouptocollect. 

Gets all Hosts inside the host group and then gets all VM:s on the host group.

$VMServers = @{}
$hosts = $null
$vms = $null
Import-Module virtualmachinemanager
$hostgroupservers = (Get-SCVMHostGroup hostgrouptocollect).AllChildGroups | select name
foreach( $hostgroupserver in $hostgroupservers ){
$hosts += Get-SCVMHost -VMHostGroup $hostgroupserver.name | select FQDN 
}

foreach($h in $hosts){
$vms += Get-SCVirtualMachine -VMHost $h.fqdn
}

After the VMs is collected I iterate through the array and gets all interesting data from them. (all data I want to populate mys database with. If the VM is using dynamic memory I choose to get the max amount of RAM it can use.

I collect all NICs and get the IP-adresses, VLAN-IDs and VLAN-names and concatenates them.

Then I collect all Disk sizes and if the Disk is marked as “BootAndSystem” it is registered as boot disk otherwise it is registered as other disk.

At last I collect some other VM-data and stores it as a hash table inside an array.

foreach ($VM in $VMS)
{
if ($vm.dynamicmemoryenabled -eq "true")
{$mem = $vm.DynamicMemoryMaximumMB}
else
{$mem = $vm.memory}
$VirtualNetworkAdapters = $vm.VirtualNetworkAdapters
$ip =""
$vlan =""
$vlanName =""
foreach ($VirtualNetworkAdapter in $VirtualNetworkAdapters)
{
if ($ip) {$ip+=","}
if ($vlan) {$vlan+=","}
if ($vlanName) {$vlanName+=","}
$ip += $VirtualNetworkAdapter.IPv4Addresses
$vlan += $VirtualNetworkAdapter.vlanid
$vlanName += $VirtualNetworkAdapter.VMNetwork
}
$othersize = 0
$bootsize = 0
$disks = Get-SCVirtualDiskDrive -VM $vm
foreach ($disk in $disks) {
if ($disk.VolumeType -eq "BootAndSystem") {
$bootsize += $disk.VirtualHardDisk.MaximumSize
}else{ 
$othersize += $disk.VirtualHardDisk.MaximumSize
}
}
$othersize = ($othersize/1GB)
$bootsize = ($bootsize/1GB)
$VMServers.Add( $vm.VMid ,  @{"CustomerID" = $vm.CustomProperty.CustomerID;"CiID" = $vm.CustomProperty.CiID;"Subscription" = $vm.CustomProperty.Subscription;"VMName" = $vm.Name;"FQDN" = $vm.ComputerNameString;"VMid" = $vm.VMid;"ClusterName" = $vm.VMHost.HostCluster.ClusterName;"CreationTime" = $vm.CreationTime;"ModifiedTime" = $vm.ModifiedTime;"Cloud" = $vm.Cloud;"OperatingSystem" = $vm.OperatingSystem;"CPUCount" = $VM.CPUCount;"CPUType" = $vm.CPUType;"Maximummemory" = $Mem;"Bootdisk" = $bootsize;"Extradisk" = $othersize;"VlanName" = $VlanName;"vlan" = $vlan;"ip" = $ip;"HasVMAdditions" = $vm.HasVMAdditions;"VMAddition" = $vm.VMAddition;"VirtualMachineState" = $vm.VirtualMachineState})
}

The second part MySQL

This is also an inline script, but this one is triggered on the SMA-server, which has MySQL-drivers installed locally.

First I get the result from the variable VMServers that was returned to the workflow from the last script.

Then I load the “MySql.Data” and “MySql.Data.MySqlClient” assemblies.

inlinescript{
$vmsNotFoundIRL = @()
$vmsFoundIRL = @()
$vmsFoundIRLandDB = @()
$queries = @()
$VMServers = $using:VMServers
[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data.MySqlClient")
}

The connection string (should have put that i SMA variable)

$connstr = “server=mysql.SERVERFQDN;user=USERNAME;database=DATABASENAME;port=3306;password=PASSWORD;"
$conn = New-Object "MySql.Data.MySqlClient.MySqlConnection" $connstr
Generate a SQL query and run it against the SQL.
$ds = New-Object System.Data.DataSet
$sql = "SELECT * FROM TABLE";
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $conn)
$da  = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)
$ds = New-Object System.Data.DataSet
$da.Fill($ds) > $null
$databaseservers = $ds.Tables[0]

Have the dataset stored in a variable

$databaseuids = @()
$vmmuids = @()
$manageduids = @()

 

Generate array with uids with servers in database.

 $databaseuids =  $databaseservers | Select-Object -ExpandProperty vmid

Generate array with uids with servers in VMM.

foreach ($serverinvm in $($VMServers.keys)) {
$vmmuids += ($VMServers[$serverinvm]).vmid
}

 

Iterate through the servers in the database and do the following:

  1. Test if the servers uids is in database and in VMM. Then the servers is available and might have new information.
  2. If the server is only in Database and not in VMM the VMM should be marked as deleted.
foreach ($serverindb in $databaseservers) {

$vmid = $serverindb.vmid
if ($vmmuids.Contains($serverindb.vmid)) {
#server is allocated in both Database and VMM
} else {
# server is allocated in Database and NOT in VMM
}
$manageduids += $serverindb.vmid
}

Iterate through all VMs from VMM and verify if already “managed”

foreach ($VMServerkey in $($VMServers.keys)) {
$manageduids += $serverinvm.vmid
}

If the Server is located in both Database and VMM The data is compared and a SQL-query is generated and the query is pushed to an array.

if ($vmmuids.Contains($serverindb.vmid)) {
$vmserver = $vmservers[$serverindb.vmid]
$query = "UPDATE TABLE SET timestamp = now() "
foreach ($value in $vmserver.Keys) {
if ($value -ne "CreationTime") {
if ( $($serverindb.$value) -ne  $vmserver[$value] -and $vmserver[$value]) {
("New Value: " + $value + " = " + $vmserver[$value], "Old Value: " + $value + " = " + $($serverindb.$value)) | Out-File -FilePath ("d:logChangelog.log") -append
$query += " , $value = '"+ $vmserver[$value] +"' "
}
}
}
if ($serverindb.deleted){
$serverindb.item($value) = " , deleted = NULL "
}
$query += " WHERE vmid = '$vmid' LIMIT 1"
$queries += $query

 

If the server is located only in the database a query is created as follows.

$query = "UPDATE QHCloud SET timestamp = now() " 
if ($serverindb.deleted){ 
$query += " , deleted = now() " 
} 
$query += " WHERE vmid = '$vmid' LIMIT 1" 
$queries += $query

Iterate through all VMs in VMM, test if the VM already has been managed via Database. If not the VM is new ans should be inserted into databse.

$serverinvm = $VMServers[$VMServerkey]
$key = ""
$valu = ""
if ($vmmuids.Contains($serverinvm.vmid) -and (-not $manageduids.Contains($serverinvm.vmid) ) ) {
# server finns i VMM och inte DB
try {
foreach ($value in $serverinvm.Keys) {
$key += ", $value"
$valu += " , '"+ $serverinvm.item($value) +"' " 
}
$query = "INSERT INTO hyperv.QHCloud (timestamp $key) VALUES (now() $valu)"
$queries += $query
} catch {
$_.exception.message | Out-File -FilePath ("d:logQstatsExecution.log") -append
}
}

The last of the second part

What is left? Take all queries in the array and run them against the MySQL.

$conn.Open()
try {
foreach ($query in $queries) {
##Exekvera Mysql
$cmd = New-Object "MySql.Data.MySqlClient.MySqlCommand" -ArgumentList $query,$conn
$cmd.ExecuteNonQuery()
}
} Catch {
$_.exception.message | Out-File -FilePath ("d:logExecution.log") -append
} Finally {
$conn.Close()
}
}

 

The whole script

The complete script can be downloaded here.