Database

0

LittleCMDB (An Orchestrator and WaveMaker project) – Part 2

By
Christian Strijbos
– July 19, 2012Posted in: Call Workflows from outside, Database, Examples, Get Started!, Integrate vCO, vCO Use-Cases, Wavemaker

Table of Content

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

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

In Part3 we  finish the  collection of the VM information.

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

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

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

In Part7 problems with vAPP located virtual machines are fixed

Part2

Here we go with Part2 of the “Little CMDB” Project. Enjoy the Post 😉

After the SQL Database is ready, it is time to start with the Workflow development. As first task, let’s start and create a new workflow. I will name my as GetVMConfig.

Insert a good description for the Workflow, change the Version Number and then lets go to the schema tab.

 The first thing we need, is a action element to get a list of all virtual machines. Just drag the action element into the window.

 

Just insert „getAll“ into the filter field. As second step choose the “getAllVMs” Workflow.

After we have insert the workflow we change to the „General“ Tab and drag a „Scriptable Task“ and a „Custom Decision“ into the Schema

Now we insert a name for the „Scriptable task“, I choose CalculateVMNumber and a Description.

After that, we are changing the name the of the “Decision” to “NumberofVMs” and insert a description.

 

After we have complete this naming and description for the workflows, we have to create some Variables.

There are two ways to create the variables. The first one is, to drag and drop from the workflows and actions over the Visual Binding tab. The second is to create the variables on the general tab. I will use the first choice.

For all other, here is a sheet with the required variables and there value to create them on the General tab:

Local Parameter Variable Name Module Direction Type
actionResult allVMs GetAllVMs out Array/VC:VirtualMachine
allVMs allVMs CalculateVMNumber in Array/VC:VirtualMachine
NumberVMs NumberVMs CalculateVMNumber out Number
NumberVMs NumberVMs NumberofVMs in Number

Lets start with the “getAllVMs” Action

I recommend to give a variables a “speaking” name and a description

When your are finished, your “Out” tab must look like this:

The next thing we have to configure, is the “CalculateVMNumber”. Here we have to insert the “allVMs” variable over the “IN” Tab

You can insert the Variable over the “Plus” symbol.

At the moment we have only one possible value so we choose the “allVMs”.

Next we go to the “Out” tab. Here we have to export the “NumberVMs” Variable. You can insert the variable over the “Plus” Sign. If you don’t have created the Variable jet, you can do so over the link above.

After we have created the variable (don’t forget we need a “number” as type 😉 ) we go to the script tab.

Here we do our first scripting. We wont to get the length of the array. We can to that with this small script:

NumberVMs = allVMs.length;

You don’t have to literally type the variables. The bound variables for the Workflow are shown on top of the “Scripting” tab. You can simple click on them and there are used in the scripting field.

At last (for the moment) we go to the “NumberofVMs” Decision. There we insert the “NumberVMs” Variable on the in field and insert this script in the scripting tab.

Now, we have to connect the different modules. We can use the connector by clicking on the “connector symbol” and drag-and-drop between the different modules.

After you are finished, just click on the “Validate” button and lets see if we have any error in our workflow.

At the moment we have two errors, which are logical (we don’t have finished our CMDB jet…).

Save the Workflow and then we will in Part3 we will go one with our LittleCMDB so stay tuned 😉

De Vcoportal Part2
De Vcoportal Part2
de.vcoportal_Part2.package
110.7 KiB
Details...
0

LittleCMDB (An Orchestrator and WaveMaker project) – Part 1

By
Christian Strijbos
– July 18, 2012Posted in: Call Workflows from outside, Database, Examples, Get Started!, Integrate vCO, Plugins, vCO Use-Cases, Wavemaker

Table of Content

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

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

In Part3 we  finish the  collection of the VM information.

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

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

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

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…..

SQL Script
SQL Script
LittleCMDB_SQL.zip
622.0 B
Details...
0

Introducing: The LittleCMDB (a vCenter Orchestrator & WaveMaker Demo-Project)

By
Joerg Lew
– July 17, 2012Posted in: Call Workflows from outside, Database, Examples, Get Started!, Integrate vCO, vCO Use-Cases, Wavemaker

It is a pleasure for me to write these lines as an introduction to a full series of articles, posted on vcoportal over the next couple of days:

Christian Strijbos (you remember his posts about WaveMaker and vs. LDAP a couple of weeks ago!?) put together a comprehensive project involving vCenter Orchestrator (and its SQL Plugin) and WaveMaker:

***…drumroll…***

The LittleCMDB

***…drumroll…***

Features

The LittleCMDB is a Workflow-driven Database Application which stores information of Virtual Machine configurations in a SQL database. The user can see the configuration via a web-based UI. It’s even possible to file a Request to change the configuration of a VM via the Web-Frontend, it will automatically processed and the settings of the VM will be adjusted.

In short, you can…:

  • Show the current Virtual Machine configuration in a Web-Frontend
  • Initialize the Database automatically
  • Refresh the Database when the configuration of a Virtual Machine changes
  • Let the users change the Virtual Machine configuration via a Web-Frontend

Architecture

LittleCMDB uses Orchestrator Workflows to implement the “business logic” of the application. For that it leverages the SQL-Plugin for vCO, so you don’t have to write a single line of SQL Statements.

There will be some backend Workflows which gather information from vCenter and store them into the Database.

The web-based frontend is built with VMware WaveMaker.

What you get:

We will publish a full guide how-to build the LittleCMDB from scratch, separated in different parts over the next couple of days (see the picture above also as a “Table of Content”).

We will also provide the Workflow Packages and the WaveMaker project to download.

Our main goal is to give you…

  • examples and “hands-on” guides to develop workflows in vCO
  • same for WaveMaker
  • an example how easy it is to create such a “CMDB application” using vCO Plugins
  • ideas to use vCO and WaveMaker for your own use-cases

So: Even if the downloadable workflows should  run quite out-of-the-box, do NOT use them for production!
The complete project is for educational purposes only.
(If you like the idea, and want something like that for your company, engage Christian for a project!  8-))

Now, get your vCO Lab ready and look forward to Part 1…

…tomorrow in this Theater :mrgreen:!

Here we go:

Part 1:
http://www.vcoportal.de/2012/07/little-cmdb-part1/

Part 2:
http://www.vcoportal.de/2012/07/littlecmdb-an-orchestrator-and-wavemaker-project-part-2/

Part 3:
http://www.vcoportal.de/2012/07/little-cmdb-part3/

Part 4:
http://www.vcoportal.de/2012/07/little-cmdb-part4/

Part 5:
http://www.vcoportal.de/2012/07/littlecmdb-part-5/

Part 6:
http://www.vcoportal.de/2012/08/littlecmdb-an-orchestrator-and-wavemaker-project-part-6/

Part 7:
http://www.vcoportal.de/2012/08/littlecmdb-an-orchestrator-and-wavemaker-project-part-7/

0

Integrating vCO: Access a Database (Overview)

By
Joerg Lew
– August 24, 2011Posted in: Best-Practices, Database, Integrate vCO, vCO Use-Cases, Workflow Design

VMware Orchestrator allows you to access SQL-Databases from within workflows. This post shows you common Use-Cases for that, explain technical background and introduces three-and-a-half ways how to implement the database-access.

Use-Cases

Accessing external databases within workflows is used to…

  • …integrate Your Workflows with external systems: You can retrieve or update information for an external system’s database (Ticket-System, Asset Management, CMDB, Documentation, Your VM-Deployment-System, ….
  • …build Your very own database-driven solution: If you create a larger “Suite” of workflows (like the VM-Deployment-System), expect that you have to deal with more and more metadata around the objects your workflows handle. A database is a good place to store this metadata, and it allows external systems to integrate easily with your Workflows in future.

Technical Background

The ability to integrate with external databases is presented by…. 😀


…the bundled  Database-Plugin. It provides a couple of Scripting-Objects for JDBC-Connections. So when you look for further reference information you can google for JDBC-Examples, they will fit (with some small limitations: Check the API Explorer of the vCO-Client to figure out the actual properties and methods of the JDBC-Objects).
In the bundled Workflow-Library you also find a Folder “JDBC Examples”, which provides a complete typical “lifecycle” of a database. Here you can also find a very useful Workflow called “JDBC Url Generator” which creates the correct complete JDBC-Connection-String you need to connect to the database.

Out-of-the-box, vCO supports Microsoft SQL-Server and Oracle-Databases, because for them the JDBC-Driver are pre-installed. However, it’s possible to add any other vendor’s JDBC-Driver; to get an idea, how to do this, see http://www.vmware.com/files/pdf/techpaper/vco-experimental-dbs-openldap-support.pdf.

Implementation Options

No matter, WHY you want to access external databases from your workflows, there are 3 (and a half) options to HOW do this:

1. Plain Database Access in Scripting Element

Just add a scripting element to your Workflow, and place the database operation in its JavaScript. This is useful, if you only call the database very rarely in defined workflows, and for single operations (For example: You want to update a documentation-database after deploying a new Virtual Machine).

2. Encapsulate Database Access to “DAO”-Actions

When you need database-calls more often in your workflows (Acutally, you should use this if you need it more than once :-)), you should encaplusate the database-calls into Actions. Typically you create pretty generic Actions for each CRUD-Operation, sometimes additional some “higher-level” Actions which do exactly the call you need often in the workflows.

(Side note: I call them DAO-Actions, compared to “Data-Access-Objects” in Software Engineering, when it comes to persistence and object-relational mapping in object-oriented software design.
Of course Actions are not “Objects”, but the idea is the same:)

So you create a single point/module, where all the database-related stuff is implemented. This makes it easier to do DRY-style developing, and rises the readability, re-useability, and maintain-ability of your works.
Consider the table-schema of the database you access is changing, so with DAO-Actions you only have to change the code in a single place.

Another big advantage using Actions to encasulate the database-access: You can use it in the Input Presentation of a Workflow! So you can get database-information even before the workflow starts. That can be used for example to make it easier for the end-user to start workflow which needs a VM as input, by providing a list of the current user’s VMs (and this information of course comes from the database):

.5 Call Stored Procedures

This is the “and-a-half” option, because it can be used in addition to the options above: JDBC allows you to call Stored Procedures of the database. This can be used e.g. if the vendor of an external system provide a bunch of Stored Procedures as an “API” for its database (or if you just want to pass all the SQL-work to the database-team :mrgreen: ).

See an example how to call a Stored Procedure in vCO.

3. Build Your own vCO-Plugin

The final option is the holy grail: Build Your own Plugin for the Orchestrator, which handles the integration to the external database. This of course is some effort, but it’s worth it if you integrate your workflows very frequently with this external database (or of course, if you build your own database-driven workflow-suite, see the second use-case above).

Building Your own custom Plugin allows you

  • to make database-entities accessible as JavaScript-Objects, and move all the database-specific stuff (table-layout, database connection details) to the Plugin. So the usage of the entities inside the Workflows and Actions is object-oriented, and all the SQL-stuff is hidden from the Workflow-Developer (see picture below).
  • to present database-entities as Inventory-Objects to the Orchestrator, which allows you to use these entities as Attributes and Input/Output-Parameters in Workflows. (Example: When integrating an Ticket-System, you can let the workflow-user select the actual Ticket for a certain workflow)

There are to flavors building such a Plugin:

1. Create the Plugin just with a vso.xml and some Hibernate-Mapping files (one for each entity you need in vCO). This allows you to present entites to the inventory, but you still have to wirte some generic JavaScript-Code in your Workflows & Actions.
On the other hand: You don’t need to develop a single line of Java-Code for the plugin 8-), and let the automatic Hibernate-Mapping-Feature of the Database-plugin do the work for you.

2. Create real Java-Objects in the Plugin and use the full vCO-Plugin API. This allows you to present your database-entities as real objects to the Workflows and Actions, and adds the full functionality of the API (like Triggers, …).
On the other hand: You have to develop Java-Code (a bunch of it), and you have to learn how the vCO-Plugin-API works. See the Solar-System-Plugin in the vco-examples-package an this presentation (Plugin-Development starts at ~28:45min) to get an idea of it. Good luck! 😈

Summary

The JDBC-Database-Plugin  provides very powerful integration-abilities for Your Workflows. You can start with a couple of JavaScript-lines for a simple integration, refactor them to DAO-Actions to have a clean Design when the need for integration grows, and unleash the full power of vCO by creating your own custom Plugin.

0

Call Database Stored Procedures via JDBC

By
Joerg Lew
– March 1, 2011Posted in: Database, Integrate vCO
With the Database Plugin (which provides JDBC-Classes for your scripting elements) you can execute Stored Procedures, using following Syntax:
var main = new JDBCConnection();
 var con;
 try  {
 con = main.getConnection( url, user, password );
 System.log( "Connection to database successful" );
 var cstmt = con.prepareCall("{call nameOfStoredProcedure(?,?)}");
 cstmt.setString(1,firstParameter);
 cstmt.setString(2, anotherParameter);
 var rs =  cstmt.executeQuery();
 //removed line, see comment
 //removed line, see above, and then the comment :)
 while ( rs.next() )  {
 //... Process results of the Stored Procedure, if necessary.
 }
 rs.close();
 cstmt.close();
 } catch( ex )  {
 throw "Exception during database action " + ex + ")";
 } finally {
 if (con) {
 con.close();
 }