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:
- Get information from VMM about VM:s
- Get information from database
- 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:
- Test if the servers uids is in database and in VMM. Then the servers is available and might have new information.
- 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.