This might take you 6 minutes to read.

Last post we created a database and updated ci:s into it. Now we will continue to add data that will be used for billing.

Continue the script

Now we need to populate data into the other tables to create an inventory of CPU/memory, disk and nic changes. All this to help out with billing and inventory of VM changes.

VM properties

From the last post we now know if the VM is active in VMware we should now verify the properties around the VM. To keep an history of all properties we do not do updates but only inserts into the database.

The first thing is to retrieve the latest entry for the VM in properties.

Try
{
	$Command = New-Object MySql.Data.MySqlClient.MySqlCommand("SELECT t1.* FROM cmdb_ci_table_properties t1 WHERE t1.datetime = (SELECT MAX(t2.datetime) FROM cmdb_ci_table_properties t2 WHERE t2.ci_id = t1.ci_id)", $Connection)
	$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
	$DataSet = New-Object System.Data.DataSet
	$RecordCount = $dataAdapter.Fill($dataSet, "data")
	$vmproperties_in_database = $DataSet.Tables[0]
}
Catch
{
	Write-Debug "ERROR : Unable to run query : $query `n$Error[0]"
}

Now we compare the the properties table with the vm in vmware. First if there is no match i.e. there is no properties for the VM we insert the data. Otherwise we verifies if the data does match. If it does not we create a query that inserts the VM:s information in the properties table.

$total_count = $reportedvms.Count
foreach ($vm_in_vwmare in $reportedvms)
{
	if ($vmproperties_in_database.ci_id -contains $vm_in_vwmare.ci_id)
	{
		$updated = $false
		$vmproperty_in_database = $vmproperties_in_database | where { $_.ci_id -eq $vm_in_vwmare.ci_id } | select -Last 1
		
		if ($vmproperty_in_database.os_family -ne $vm_in_vwmare.os_family)
		{
			$updated = $true
		}
		if ($vmproperty_in_database.os_fullname -ne $vm_in_vwmare.os_fullname)
		{
			$updated = $true
		}
		if ($vmproperty_in_database.hostname -ne $vm_in_vwmare.hostname)
		{
			$updated = $true
		}
		if ($vmproperty_in_database.sockets -ne $vm_in_vwmare.sockets)
		{
			$updated = $true
		}
		if ($vmproperty_in_database.cores -ne $vm_in_vwmare.cores)
		{
			$updated = $true
		}
		if ($vmproperty_in_database.cpu -ne $vm_in_vwmare.cpu)
		{
			$updated = $true
		}
		if ($vmproperty_in_database.memory -ne $vm_in_vwmare.memory)
		{
			$updated = $true
		}
		if ($vmproperty_in_database.guesttools -ne $vm_in_vwmare.guesttools)
		{
			$updated = $true
		}
		if ($vmproperty_in_database.tools_version_status -ne $vm_in_vwmare.tools_version_status)
		{
			$updated = $true
		}
		if ($vmproperty_in_database.powerstate -ne $vm_in_vwmare.powerstate)
		{
			$updated = $true
		}
		if ($updated)
		{
			try
			{
				$query = "INSERT INTO cmdb_ci_table_properties (ci_id, datetime, cpu, sockets, cores, memory, powerstate, guesttools, hostname, os_fullname, os_family, tools_version_status) VALUES ('$($vm_in_vwmare.ci_id)', NOW(), $($vm_in_vwmare.cpu),$($vm_in_vwmare.sockets),$($vm_in_vwmare.cores), $($vm_in_vwmare.memory), $($vm_in_vwmare.powerstate), '$($vm_in_vwmare.guesttools)', '$($vm_in_vwmare.hostname)', '$($vm_in_vwmare.os_fullname)', '$($vm_in_vwmare.os_family)', '$($vm_in_vwmare.tools_version_status)') "
				$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $Connection)
				[void]$command.ExecuteNonQuery()
				Write-Debug $query
			}
			catch
			{
				Write-Debug "FAILED: $query"
			}
		}
	}
	else
	{
		try
		{
			$query = "INSERT INTO cmdb_ci_table_properties (ci_id, datetime, cpu, sockets, cores, memory, powerstate, guesttools, hostname, os_fullname, os_family) VALUES ('$($vm_in_vwmare.ci_id)', NOW(), $($vm_in_vwmare.cpu), $($vm_in_vwmare.sockets), $($vm_in_vwmare.cores), $($vm_in_vwmare.memory), $($vm_in_vwmare.powerstate), '$($vm_in_vwmare.guesttools)', '$($vm_in_vwmare.hostname)', '$($vm_in_vwmare.os_fullname)', '$($vm_in_vwmare.os_family)') "
			$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $Connection)
			[void]$command.ExecuteNonQuery()
			Write-Debug "NEW Property cause CI_ID not found for VM: $($vm_in_vwmare.Name) with CI_ID: $($vm_in_vwmare.ci_id)"
		}
		catch
		{
			Write-Debug "FAILED: $query"
		}
	}
}

VM Harddisks

For hard disks we replicate the data pattern. First we retrieves the latest entries for the hard disks in the database. then we retrieves all hard disks from VMware.

Try
{
	$Command = New-Object MySql.Data.MySqlClient.MySqlCommand("SELECT t1.* FROM cmdb_ci_table_harddisks t1 WHERE t1.datetime = (SELECT MAX(t2.datetime) FROM cmdb_ci_table_harddisks t2 WHERE t2.disk_id = t1.disk_id)", $Connection)
	$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
	$DataSet = New-Object System.Data.DataSet
	$RecordCount = $dataAdapter.Fill($dataSet, "data")
	$harddisks_in_database = $DataSet.Tables[0]
}
Catch
{
	Write-Debug "ERROR : Unable to run query : $query `n$Error[0]"
}

Then we iterate through all hard disks and generates an object with the following data: id, size, filename and ci_id retrieved from the reportedvm-array.  The diskpool and diskpool-tier is based on a the lun names in the vmware. The first letter represents the “speed” of the LUN “g” is gold, “s” is silver and “b” is bronze. The next char is an underscore “_”. This will represent the diskpooltier. If the latest entry in the database matches the value in VMware no update is done, otherwise an insert is created and executed in database.

$harddisks_in_vmware = Get-HardDisk *
$reportedvmdisks = New-Object System.Collections.ArrayList
$total_count = $harddisks_in_vmware.Count
foreach ($harddisk in $harddisks_in_vmware)
{
	$ManagedVMHarddisk = New-Object PSObject
	$diskpool = $harddisk.Filename -replace '^\[(.+?)\].*', '$1'
	if ($diskpool -match ".*\:(\w)_.*")
	{
		$diskpooltier = $diskpool -replace ".*[\:](.).*", '$1'
	}
	else
	{
		$diskpooltier = "l"
	}
	
	Add-Member -Inputobject $ManagedVMHarddisk -MemberType noteProperty -name disk_id -value $harddisk.id
	Add-Member -Inputobject $ManagedVMHarddisk -MemberType noteProperty -name capacity_gb -value ([Math]::Truncate($harddisk.CapacityGB))
	Add-Member -Inputobject $ManagedVMHarddisk -MemberType noteProperty -name persistence -value $harddisk.Persistence
	Add-Member -Inputobject $ManagedVMHarddisk -MemberType noteProperty -name diskpool -value $diskpool
	Add-Member -Inputobject $ManagedVMHarddisk -MemberType noteProperty -name diskpool_tier -value $diskpooltier
	Add-Member -Inputobject $ManagedVMHarddisk -MemberType noteProperty -name filename -value $harddisk.Filename
	Add-Member -Inputobject $ManagedVMHarddisk -MemberType noteProperty -name ci_id -value ($reportedvms | where { $_.vm_id -eq $harddisk.ParentId }).ci_id
	
	$reportedvmdisks.add($ManagedVMHarddisk) | Out-Null
	
	if ($harddisks_in_database.disk_id -contains $ManagedVMHarddisk.disk_id)
	{
		$harddisk_in_database = $harddisks_in_database | where { $_.disk_id -eq $ManagedVMHarddisk.disk_id } | select -first 1
		
		$updated = $false
		if ($ManagedVMHarddisk.ci_id -and $ManagedVMHarddisk.ci_id -ne $harddisk_in_database.ci_id)
		{
			$updated = $true
		}
		if ($ManagedVMHarddisk.filename -ne $harddisk_in_database.filename)
		{
			$updated = $true
		}
		if ($ManagedVMHarddisk.diskpool -ne $harddisk_in_database.diskpool)
		{
			$updated = $true
		}
		if ($ManagedVMHarddisk.diskpool_tier -ne $harddisk_in_database.diskpool_tier)
		{
			$updated = $true
		}
		if ($ManagedVMHarddisk.capacity_gb -ne $harddisk_in_database.capacity_gb)
		{
			$updated = $true
		}
		if ($ManagedVMHarddisk.persistence -ne $harddisk_in_database.persistence)
		{
			$updated = $true
		}
		if ($updated)
		{
			$query = "INSERT INTO cmdb_ci_table_harddisks (disk_id, datetime, ci_id, filename, diskpool, diskpool_tier, capacity_gb, persistence) VALUES ('$($ManagedVMHarddisk.disk_id)', NOW(), $($ManagedVMHarddisk.ci_id), '$($ManagedVMHarddisk.filename)', '$($ManagedVMHarddisk.diskpool)', '$($ManagedVMHarddisk.diskpool_tier)', $($ManagedVMHarddisk.capacity_gb), '$($ManagedVMHarddisk.persistence)') "
			$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $Connection)
			[void]$command.ExecuteNonQuery()
		}
	}
	else
	{
		if ($ManagedVMHarddisk.ci_id)
		{
			try
			{
				$query = "INSERT INTO cmdb_ci_table_harddisks (disk_id, datetime, ci_id, filename, diskpool, diskpool_tier, capacity_gb, persistence) VALUES ('$($ManagedVMHarddisk.disk_id)', NOW(), $($ManagedVMHarddisk.ci_id), '$($ManagedVMHarddisk.filename)', '$($ManagedVMHarddisk.diskpool)', '$($ManagedVMHarddisk.diskpool_tier)', $($ManagedVMHarddisk.capacity_gb), '$($ManagedVMHarddisk.persistence)') "
				$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $Connection)
				[void]$command.ExecuteNonQuery()
			}
			catch
			{
				Write-Debug "FAILED: $query"
			}
			
		}
		else
		{
			Write-Debug  "Cannot find any ci_id for: $($ManagedVMHarddisk.filename)"
		}
	}
}

VM NICs

For Network cards we replicate the data pattern. First we retrieves the latest entries for the NICs in the database. then we retrieves all NICs from VMware.

Try
{
	$Command = New-Object MySql.Data.MySqlClient.MySqlCommand("SELECT t1.* FROM cmdb_ci_table_nic t1 WHERE t1.datetime = (SELECT MAX(t2.datetime) FROM cmdb_ci_table_nic t2 WHERE t2.nic_id = t1.nic_id)", $Connection)
	$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
	$DataSet = New-Object System.Data.DataSet
	$RecordCount = $dataAdapter.Fill($dataSet, "data")
	$networkcards_in_database = $DataSet.Tables[0]
}
Catch
{
	Write-Debug "ERROR : Unable to run query : $query `n$Error[0]"
}

Then we iterate through all NICs and generates an object. All IP:s is inserted into the same column. If the latest entry in the database matches the value in VMware no update is done, otherwise an insert is created and executed in database.

$nics_in_vmware = Get-NetworkAdapter *
$reportedvmnetworkCards = New-Object System.Collections.ArrayList
$total_count = $nics_in_vmware.Count
foreach ($nic in $nics_in_vmware)
{
	$ManagedVMNetworkCard = New-Object PSObject
	Add-Member -Inputobject $ManagedVMNetworkCard -MemberType noteProperty -name nic_id -value $nic.id
	Add-Member -Inputobject $ManagedVMNetworkCard -MemberType noteProperty -name ci_id -value (($reportedvms | where { $_.vm_id -eq $nic.ParentId }).ci_id)
	Add-Member -Inputobject $ManagedVMNetworkCard -MemberType noteProperty -name dns -value ""
	Add-Member -Inputobject $ManagedVMNetworkCard -MemberType noteProperty -name ip_address -value (($reportedvms | where { $_.vm_id -eq $nic.ParentId }).nics[$nic.MacAddress] -join ";")
	Add-Member -Inputobject $ManagedVMNetworkCard -MemberType noteProperty -name mac_address -value $nic.macaddress
	Add-Member -Inputobject $ManagedVMNetworkCard -MemberType noteProperty -name network -value $nic.networkname
	Add-Member -Inputobject $ManagedVMNetworkCard -MemberType noteProperty -name connected -value ([int]($nic.ConnectionState.Connected))
	Add-Member -Inputobject $ManagedVMNetworkCard -MemberType noteProperty -name network_name -value $nic.networkname
	$reportedvmnetworkCards.add($ManagedVMNetworkCard) | Out-Null
	
	if ($networkcards_in_database.nic_id -contains $ManagedVMNetworkCard.nic_id)
	{
		$networkcard_in_database = $networkcards_in_database | where { $_.nic_id -eq $ManagedVMNetworkCard.nic_id }	
		$updated = $false
		if ($ManagedVMNetworkCard.ci_id -and $ManagedVMNetworkCard.ci_id -ne $networkcard_in_database.ci_id)
		{
			$updated = $true
		}
		if ($ManagedVMNetworkCard.dns -ne $networkcard_in_database.dns)
		{
			$updated = $true
		}
		if ($ManagedVMNetworkCard.ip_address -ne $networkcard_in_database.ip_address)
		{
			$updated = $true
		}
		if ($ManagedVMNetworkCard.mac_address -ne $networkcard_in_database.mac_address)
		{
			$updated = $true
		}
		if ($ManagedVMNetworkCard.network -ne $networkcard_in_database.network)
		{
			$updated = $true
		}
		if ($ManagedVMNetworkCard.connected -ne $networkcard_in_database.connected)
		{
			$updated = $true
		}
		if ($ManagedVMNetworkCard.network_name -ne $networkcard_in_database.network_name)
		{
			$updated = $true
		}
		if ($updated)
		{
			$query = "INSERT INTO cmdb_ci_table_nic (nic_id, datetime, ci_id, dns, ip_address, network, connected, network_name, mac_address) VALUES ('$($ManagedVMNetworkCard.nic_id)', NOW(), $($ManagedVMNetworkCard.ci_id), '$($ManagedVMNetworkCard.dns)', '$($ManagedVMNetworkCard.ip_address)', '$($ManagedVMNetworkCard.network)', $($ManagedVMNetworkCard.connected), '$($ManagedVMNetworkCard.network_name)', '$($ManagedVMNetworkCard.mac_address)') "
			$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $Connection)
			[void]$command.ExecuteNonQuery()
		}
	}
	else
	{
		if ($ManagedVMNetworkCard.ci_id)
		{
			try
			{
				$query = "INSERT INTO cmdb_ci_table_nic (nic_id, datetime, ci_id, dns, ip_address, network, connected, network_name, mac_address) VALUES ('$($ManagedVMNetworkCard.nic_id)', NOW(), $($ManagedVMNetworkCard.ci_id), '$($ManagedVMNetworkCard.dns)', '$($ManagedVMNetworkCard.ip_address)', '$($ManagedVMNetworkCard.network)', $($ManagedVMNetworkCard.connected), '$($ManagedVMNetworkCard.network_name)', '$($ManagedVMNetworkCard.mac_address)') "
				$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $Connection)
				[void]$command.ExecuteNonQuery()
			}
			catch
			{
				Write-Debug "FAILED: $query"
			}
		}
		else
		{
			Write-Debug  "Cannot find any ci_id for: $($ManagedVMNetworkCard.nic_id) on VM: $($nic.Parent)"
		}
	}
}

This is it

Running this script as a scheduled task a couple times each day and you will have a good start to automate billing and have updated CMDB.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.