LittleCMDB (An Orchestrator and WaveMaker project) – Part 1

Table of Content

• LittleCMDB (An Orchestrator and WaveMaker project) – Part 1

In Part1 we start with the SQL DB Plugin and create the required database for our need.

• LittleCMDB (An Orchestrator and WaveMaker project) – Part 2

In Part2 we start with the development of our Workflow. We will start with a few elements.

• LittleCMDB (An Orchestrator and WaveMaker project) – Part 3

In Part3 we  finish the  collection of the VM information.

• LittleCMDB (An Orchestrator and WaveMaker project) – Part 4

In Part4 we insert our data into the database and test our created workflow

• LittleCMDB (An Orchestrator and WaveMaker project) – Part 5

In Part5 we create our webview to get a look on our Data in the SQL Database

• LittleCMDB (An Orchestrator and WaveMaker project) – Part 6

In Part6 we will make our Workflow smarter to update the DB with actual VM information

• LittleCMDB (An Orchestrator and WaveMaker project) – Part 7

In Part7 problems with vAPP located virtual machines are fixed

Part1

Today I want to start with a series of posts for an example how to to create an Orchestrator and Wavemaker project. This project came from a “real-world” situation. The customer wants his VM configuration saved outside the virtual environment into a SQL Database. During the talks with the customer, the idea for a little CMDB was born. The actual customer project is much bigger than the example in this article series, but you will become a good insight view on all relevant topics and themes.

When we think about a CMDB we have to choose which data we want to save in our database. Here are some things I included in my database:

• Unified Identifier (VMID) (The primary key for the VM)

• VM UUID (the second key for the VM)

• VM name

• CPU Configuration

• Memory Configuration

• Cluster

• Host

• Folder

• Resource Pools

• Network

• IP Address

• Datastore(s)

• Disk Size

Surely there are more things which could be integrated but in this post I will focus these values. These identified values bring us to a database schema. Also there are not so many values; I prefer to save the data in different tables. On every table I choose the Unified Identifier with the name VMID as primary key. As second key I select the VMUUID with should be unique for every virtual machine within a vCenter Server. I also split the different information for the VMs in different tables for maybe future grow.

I create the following tables with these values:

VM_Info

Name	Data Type	IsNull	Comments
VMID	varchar(100)	False	PrimaryKey
VMUUID	varchar(100)	False
VMName	varchar(100)	False
CPUConfig	Numeric(18)	False
MemConfig	Numeric(18)	False

VM_Host

Name	Data Type	Is Null Allowed	Comments
VMID	varchar(100)	False	PrimaryKey
VMUUID	varchar(100)	False
Cluster	varchar(100)	True
Host	varchar(100)	False
ResourcePool	varchar(100)	True
Folder	varchar(100)	True

VM_Network

Name	Data Type	Is Null Allowed	Information
VMID	varchar(100)	False	PrimaryKey
VMUUID	varchar(100)	False
Network	varchar(100)	True	Multiple Values Possible
IPAddress	varchar(500)	true	Multiple Values Possible

VM_Datastore

Name	Data Type	Is Null Allowed	Information
VMID	varchar(100)	False	PrimaryKey
VMUUID	varchar(100)	False
DiskSize	varchar(100)	True	Multiple Values Possible
Datastore	varchar(500)	true	Multiple Values Possible

I am not a database Admin and I cannot say if this is a good DB schema, but it fits my needs and I can grow with additional tables.

As Database a use a MS-SQL Server. My DB is named LittleCMDB. Here is the Script to create the DB on an MS-SQL or MS-SQL Express DB.

USE [LittleCMDB]
GO
/****** Object:  Table [dbo].[VM_Network]    Script Date: 07/13/2012 15:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VM_Network](
[VMID] [varchar](100) NOT NULL,
[VMUUID] [varchar](100) NOT NULL,
[Network] [varchar](100) NULL,
[IPAddress] [varchar](500) NULL,
CONSTRAINT [PK_VM_Network] PRIMARY KEY CLUSTERED
(
[VMID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VM_Info]    Script Date: 07/13/2012 15:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VM_Info](
[VMID] [varchar](100) NOT NULL,
[VMUUID] [varchar](100) NOT NULL,
[VMName] [varchar](100) NOT NULL,
[CPUConfig] [numeric](18, 0) NOT NULL,
[MemConfig] [numeric](18, 0) NOT NULL,
CONSTRAINT [PK_VM_Info_1] PRIMARY KEY CLUSTERED
(
[VMID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VM_Host]    Script Date: 07/13/2012 15:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VM_Host](
[VMID] [varchar](100) NOT NULL,
[VMUUID] [varchar](100) NOT NULL,
[Cluster] [varchar](100) NULL,
[Host] [varchar](100) NULL,
[ResourcePool] [varchar](100) NULL,
[Folder] [varchar](100) NULL,
CONSTRAINT [PK_VM_Host] PRIMARY KEY CLUSTERED
(
[VMID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VM_Datastore]    Script Date: 07/13/2012 15:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VM_Datastore](
[VMID] [varchar](100) NOT NULL,
[VMUUID] [varchar](100) NOT NULL,
[DiskSize] [varchar](100) NULL,
[Datastore] [varchar](500) NULL,
CONSTRAINT [PK_VM_Datastore] PRIMARY KEY CLUSTERED
(
[VMID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

For the usage of a DB in Orchestrator, you can use the SQL-Plugin. The Plug in could be get from the VMware Website (https://my.vmware.com/web/vmware/details?downloadGroup=VCO_SQL_PLUGIN_10&productId=229)

The Installation is done over the Orchestrator Configuration site (http://ORCHESTRATOR_IP:8282). I will not describe the installation. If you are not familiar with the plug in installation then RTFM in the official documentation here http://pubs.vmware.com/orchestrator-plugins/index.jsp?topic=/com.vmware.using.sql.plugin.doc_10/GUID-66110FFD-EB0F-484A-937B-8C131C8DFFB2.html

After you finished the installation of the SQL Plug in, it is time to integrate the SQL Server and the required Database tables.

You can add your database with the vCO Client  Workflows  Library  SQL  “Add a database”

There you have to define your SQL Connection Details. In my case, that are the required parameter for the SQL connection. In my case that are:

• A name for the connection

• The SQL Server IP with Port and database name (for details look in the screen shot..)

• A username with permissions on the DB

• And the password for the user

After the database is successfully added, we have to integrate the preconfigured tables. This is done with the workflow “Add tables to a database”

 

After the start of the workflow we first have to choose the database

In the opening window we take our DB (LittleCMDB in my case…)

 

Then we have to integrate our tables

 

You can add tables by clicking in the “Tables” field

 

there you have to insert all fields beginning with VM_*. Add the end the your Array of String has to look like this:

 

After you have „Accept“ your choice you can „Submit“ the Workflow.

 

Before I start to generate the SQL Statements, I create a Folder in which I will place my SQL-Statements. For that I have created a folder “vcoportal.de” there a sub folder “LittleCMDB”. In the “LittleCMDB” Folder a also created a Subfolder with the name “SQL-Statements”.

Feel free to change your names and folders, i prefer this structure.

To generate your SQL-Statements we have to browse to the Workflow “Generate CRUD workflows for a table”

This Workflow must be executed for every table we want to use.

For that, you have to provide the following inputs:

• The table

• The destination directory

• If you want to overwrite existing workflows

• And the read-only columns

 

Here is a example for one of my tables:

 

When you are ready for your tables, take a look into your destination folder. For every table, there must be a record to “Insert, Update, Read and Delete” values in your tables.

The preparation of the SQL Server statements is done. Know we can start to build up our workflow to feed the SQL Database…..

So, that’s all for Part1. Stay tuned for Part2. Then we will start to create the Workflow…..