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.