VMware to cmdb script part 1¶

This is a two blog post divided into two posts. The second will be published within a week from this one.

Problem¶

Today we lack information about virtual servers in our VMware environment which cause problems within each billing period with manual labour of verifying disk size, memory and cpu of each VM. With little to no money we needed a way to inventory and save the information for others to use and present it on a website.

Pre requirements¶

There were some pre requirements. I will try to give them here.

Mysqlserver¶

To put the data into something we selected a MySQL database. The following structure was used.

cmdb_database
Cmdb_ci_table
id, persistenceent_id, folder_id, name, type, status, customer, cost_center, created, missing, updated
cmdb_ci_table_harddisks
id, disk_id, datetime, ci_id, filename, diskpool, diskpool_tier, capacity_gb, persistence, deleted
cmdb_ci_table_nic
id, ci_id, ip_address
nic_id, network_name, dns, network, connected, mac_address, datetime, deleted
cmdb_ci_table_properties
id, ci_id, datetime, cpu, sockets, cores, memory, powerstate, guesttools, os_family, os_fullname, hostname, tools_version_status
cmdb_ci_table_type
id, name
customers
id, name, active, parent

VMware vCenter¶

The vCenter environment was setup with all VMs into a folder structure. The folder structure was injected with customer numbers to be able to automatically populate the customer_id attribute in the cmdb.

The vCenter also provided a vCloud environment and that shouldn’t have the same billing process. To automatically populate each CI with a correct ci_type the cloud folder was located and the ID was statically programmed in the script.

To add attributes to vCenter we used following code

New-CustomAttribute -Name CustID -targetType Folder "Foldername"
New-Customattribute

The script¶

The script broken down into pieces

Preface¶

First, we should load the VMware module into Powershell. Then we need to connect to the vCenter server. This piece of script loads the snap in if it not already loaded and then connects to the vCenter server.

$PSSnapinName = "VMware.VimAutomation.Core"
if (!(Get-PSSnapin | where { $_.Name -eq $PSSnapinName }))
{
    Add-PSSnapin -name $PSSnapinName
}
Connect-VIServer "vcenter" -User "domain\vcenter_readuser" -Password ""

The next part is to to connect to the database server

$ConnectionString = "server=" + $sqlserver + ";port=" + $sqlport + ";uid=" + $sqlusr + ";pwd=" + $sqlpwd + ";database=" + $sqldb

#create an arraylist to put all SQL queries in
$queries = New-Object System.Collections.ArrayList

# try to connect to database
# if it does not work, fail since there is no reason to run script
Try
{
    [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()
}
Catch
{
    Write-Debug "ERROR: Cannot connect to MySQL-server `n$Error[0]"
}

Now when we are connected to the vCenter and also the database. We can now load data

# Collect existing ci:s from database
Try
{
    # Get all CI:s with type 1 or 2 (this is set below and the the values is either 1: vCenter or 2: vcloud)
    $Command = New-Object MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM cmdb_ci_table WHERE type = 1 OR type =  2", $Connection)
    $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
    $DataSet = New-Object System.Data.DataSet
    $RecordCount = $dataAdapter.Fill($dataSet, "data")
    $vms_in_database = $DataSet.Tables[0]
}
Catch
{
    Write-Debug "ERROR : Unable to run query : $query `n$Error[0]"
}
# End collect existing CI:s from database

Then is the VMs in vCenter that should be collected with the Get-VM-command.

Verify each VM in main table¶

Now we can iterate through our arrays of data to make some data crunching. We take the part first were we go through all VMs currently in vCenter.

To make sure that the new VM:s gets a new ci_id, all old VM:s gets their old ci_id and

$total_count = $vms_in_vmware.Count
foreach ($vm in $vms_in_vmware)
{

Now since I found out that there is a performance issue when loading data from $vm.guest. I do load the guest just once and retrieves data from that variable.

    $vmguest = $vm.guest

Now is the time to locate the VM in the folder structure and retrieve the customer_id we inserted on th folder in vCenter before. In this installation the folder with id “Folder-group-xxxxx” was created by the vCloud and should be inventoried as another type to enable another billing structure and management structure.

        # get current folder of the VM
    $f = $vm.folder

    $customerid = ""
    # Set default type of VM
    $type = 1
        # Iterate through folder parents until top folder is found.
    while ($f.parent -and $f.name -ne "vm")
    {
                # If folder "Folder-group" is found VM is connected to vCloud.
        if ($f.ParentId -eq "Folder-group")
        {
            $type = 2
        }
                # Get custom attribute with Customer id
        $customerid = $f.CustomFields["CustID"]
        $f = $f.parent
    }
        # If customer id is empty set the customerid to NULL for the database
    if (-not $customerid) { $customerid = "NULL" }

Now we should get the powerstate of the VM. We collect it as an enum and store in later in the database as an int.


    Add-Type -TypeDefinition @"
        public enum VMPowerstates
        {
            PoweredOff,
            PoweredOn,
            Suspended
        }
"@
    $powerstate = [int][VMPowerstates]::($vm.powerstate)

Create an hash table with all MAcaddresses and IP:s for all the NICs. Now we use the “$vmGuest” variable to gain some speed. Otherwise every newiteration needs to load the complete VM-object and lookup each value.

    $vmnics = @{ }
    foreach ($nic in $vmguest.Nics)
    {
        $vmnics[$nic.MacAddress] = $nic.ipaddress
    }

Collect the guest os and set variables with os type and os family. Same here, we use the “$vmGuest” variable to gain speed.

    if ($vmguest.GuestFamily -match "windows")
    {
        $osfamily = "Microsoft Windows"
    }
    elseif ($vmguest.GuestFamily -match "linux")
    {
        $osfamily = "Linux"
    }
    else
    {
        if ($vmguest.OSFullName -match "windows")
        {
            $osfamily = "Microsoft Windows"
        }
        elseif ($vmguest.OSFullName -match "Linux" -or $vmguest.OSFullName -match "Debian" -or $vmguest.OSFullName -match "Centos" -or $vmguest.OSFullName -match "Ubuntu" -or $vmguest.OSFullName -match "Red Hat")
        {
            $osfamily = "Linux"
        }
        else
        {
            $osfamily = "unknown"
        }
    }
    $osfullname = $vmguest.OSFullName
    if (-not $osfullname)
    {
        $osfullname = ""
    }

Get cores and sockets for the CPU

    $cores = $vm.ExtensionData.Config.Hardware.NumCoresPerSocket
    $sockets = $vm.NumCpu / $cores

To handle all the data above we put it in an PSObject.

    $ManagedVM = New-Object PSObject
    Add-Member -Inputobject $ManagedVM -MemberType noteProperty -name Name -value ([string]$vm.Name)
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name persistent_id -value $vm.PersistentId
    Add-Member -Inputobject $ManagedVM -MemberType noteProperty -name folder_id -value $vm.folderid
    Add-Member -Inputobject $ManagedVM -MemberType noteProperty -name powerstate -value $powerstate
    Add-Member -Inputobject $ManagedVM -MemberType noteProperty -name cpu -value $vm.NumCpu
    Add-Member -Inputobject $ManagedVM -MemberType noteProperty -name cores -value $cores
    Add-Member -Inputobject $ManagedVM -MemberType noteProperty -name sockets -value $sockets
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name memory -value ([Math]::Round(($vm.MemoryGB), 0))
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name guesttools -value $vmguest.ToolsVersion
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name customer_id -value $customerid
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name os_family -value $osfamily
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name os_fullname -value $osfullname
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name vm_id -value ([string]$vm.id)
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name hostname -value ([string]$vmguest.HostName)
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name nics -value $vmnics
    Add-Member -InputObject $ManagedVM -MemberType noteProperty -name tools_version_status -value $vmGuest.Extensiondata.ToolsVersionStatus

Now the data should be inserted into database. If the VM already exists the information that is not the same should be updated. If there is no information that has changed, the column “updated” will update anyway so we now that the VM has been examined and verified against database. The unique key is the VMware persistent_id. Then each variable is examined to verify if data in database and VMware is the same. If the value is different a query is created and executed. If the VM is not in database an insert query is created and executed. The last thing that is happens is that the VM-object is inserted into an array.

    if ($vms_in_database.persistent_id -contains $ManagedVM.persistent_id)
    {
        $vm_in_database = $vms_in_database | where { $_.persistent_id -eq $vm.PersistentId } | select -first 1
        $divider = ""
        $query = "UPDATE cmdb_ci_table SET "
        Add-Member -InputObject $ManagedVM -MemberType noteProperty -name ci_id -value $vm_in_database.id
        if ($vm.name -ne $vm_in_database.name)
        {
            $query += "$divider name = '$($ManagedVM.name)'"
            $divider = ", "
        }
        if ($ManagedVM.folder_id -ne $vm_in_database.folder_id)
        {
            $query += "$divider folder_id = '$($ManagedVM.folder_id)'"
            $divider = ", "
        }
        if (-not $ManagedVM.customer_id -or $ManagedVM.customer_id -eq "NULL") { $ManagedVM.customer_id = "NULL" }
        if ($ManagedVM.customer_id -ne $vm_in_database.customer)
        {
            $query += "$divider customer = $($ManagedVM.customer_id)"
            $divider = ", "
        }
        if ($type -ne $vm_in_database.type)
        {
            $query += "$divider type = $($type)"
            $divider = ", "
        }
        $query += "$divider missing = NULL, updated = NOW() WHERE id = $($vm_in_database.id)"
        $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $Connection)
        Write-Debug $query
        [void]$command.ExecuteNonQuery()
    }
    else
    {
        $query = "INSERT INTO cmdb_ci_table (persistent_id, folder_id, name, type, customer, created, missing, updated) VALUES ('$($vm.persistentid)', '$($ManagedVM.folder_id)', '$($vm.name)', $type, $($ManagedVM.customer_id), NOW(), NULL, NOW())"
        #$query
        $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $Connection)
        [void]$command.ExecuteNonQuery()
        Add-Member -InputObject $ManagedVM -MemberType noteProperty -name ci_id -value $command.LastInsertedId
        Write-Debug $query
    }
    [void]$reportedvms.Add($ManagedVM)
}

After the VM to database comparison is done it’s time to do the opposite. A VM can be deleted in VMware and still should be in the database. For each VM in database not found in VMware do an update in database that the VM is missing in VMware.

$total_count = $vms_in_database.Rows.Count
foreach ($vm in $vms_in_database)
{
    if ($vms_in_vmware.persistentid -notcontains $vm.persistent_id)
    {
        $query = "Update cmdb_ci_table SET missing = NOW() WHERE id = $($vm.id)"
        $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $Connection)
        [void]$command.ExecuteNonQuery()
    }
}

Whats next?¶

With the above code you will update a database with the ci-part of the equation. Next part will be about inserting the CPU, memory and disk usage and also ip-addresses and some more information.