Build your own RVtools with Powershell, Powercli and SQLlite

All of us probably know RVtool (https://www.robware.net/rvtools/), a tool which pulls out a lot of useful information about your vCenter, ESXi, VMS and other stuff. RVTool export the data as an excel spreadsheet for further usage.

I was always wondering how to put all the data into a SQL database instead of. And furthermore, I would like to add additional data to the tables which I am interested in to know.

I will show an easy and flexible way to put any vSphere data into SQL.

All data of get-vm into SQLlite

Prerequisites:

• PowerShell
• PowerCLI (https://developer.vmware.com/powercli)
• SQLliste Browser (https://sqlitebrowser.org/)

I will not cover the installation of these tools in this article!

Create Database

As a first example, we will put all Data of

• Get-vm

Into a table which we will create called “vm”

Step 1 : Create an empty DB

Open the SQLite Browser and create a new empty database

Step 2 : Create table ‘vm’

The question is how do we get the fields needed.

Actually, this is quite easy by just looking what get-vm returns

Get-Vm | select -first 1 | Get-member | where{$_.MemberType q "Property"} | select name, definition

This will return all properties of VMs.

With this information, we build a create table script:

CREATE TABLE VM (
    CoresPerSocket         text,
    CreateDate             text,
    CustomFields           text,
    DatastoreIdList        text,
    DrsAutomationLevel     text,
    ExtensionData          text,
    Folder                 text,
    FolderId               text,
    Guest                  text,
    GuestId                text,
    HAIsolationResponse    text,
    HardwareVersion        text,
    HARestartPriority      text,
    Id                     text,
    MemoryGB               text,
    MemoryMB               text,
    Name                   text,
    Notes                  text,
    NumCpu                 text,
    PersistentId           text,
    PowerState             text,
    ProvisionedSpaceGB     text,
    ResourcePool           text,
    ResourcePoolId         text,
    Uid                    text,
    UsedSpaceGB            text,
    VApp                   text,
    Version                text,
    VMHost                 text,
    VMHostId               text,
    VMResourceConfiguration text,
    VMSwapfilePolicy       text
);

For simplification, I just use “text” instead of “integer” for number values. You are free to optimize the table structure.

• Use this to create a table called ‘vm’ with you SQLite Browser.

Step 3 : Close DB

With this out, the database is ready. Write the changes to disk and close the SQLite Browser because it locks the file for further usage.

Create a Powershell script which puts the data of get-vm into our SQLite DB

Prerequisites:

• Powershell Module: PSSQLite PowerShell Module (https://github.com/RamblingCookieMonster/PSSQLite)

Warren Frame has written a nice Powershell to SQLite integration that covers all aspects needed for this tutorial.

Clone this project from GitHub.

Step 1: Put all the stuff together

• Login into vCenter using ‘connect-VIServer’
• Import the module from Warren ‘import-moudle’
• Define you datasource ‘$datayourde’
• Get the data of all of your VMs ‘get-vm’
• Convert it to a Datadale ‘Out-Datatable’
• Write the data to the database ‘Invoke-SQLiteBulkCopy’

All together it is:

# Connect to vCenter
connect-VIserver vcsa.vc.local

# IMport PSQLite module
Import-Module ".PSSQLite-masterPSSQLitePSSQLite.psm1"

#Define Database
$DataSource  = "C:UserslandeOneDrivesqliteblog1.db"    

# Get all Data and convert it to a Datatable
$DataTable = get-vm | Out-DataTable

# Write to DB
Invoke-SQLiteBulkCopy -DataTable $DataTable -DataSource $DataSource -Table Vm -NotifyAfter 1000 -verbose

Now open your SQLlite Browser again and look at your data:

Possible next steps

You can do that same procedure for all of you objects of interest like:

• Get-cluster
• Get-VDportgroup
• Get-Datastore
• …

Summary

As you can see, it is very effective to build your own Configuration Database (CMDB) base on simple Powercli querys insert into a SQLite database.