SQL Plug-in + DynamicTypes = Simple CMDB for vCAC - Part 1

This multi-part series will step you through the process of mapping a Microsoft SQL Server table into vRealize Orchestrator, building out a DynamicTypes plug-in inventory based on that table (using my workflow package), then exposing it to vRealize Automation's Advanced Service Designer (ASD).

Introduction

vRealize Automation (vRA) features an Advanced Service Designer (ASD) that allows for you to offer nearly anything as a service (XaaS). In order to take advantage of that feature, it requires a vRealize Orchestrator (vRO) Inventory object. This means you must have a plug-in that provides such an inventory. In the past, this meant Java skills to build out a plug-in. This, fortunately, is no longer the case with the Dynamic Types plug-in. We touched on this plug-in in the past with regards to using the HTTP-REST plug-in. This article will take a different approach in that we will use the SQL Plug-in to provide our back-end service - a mini CMDB consisting of Server names and IPs.

 NOTE/WARNING: This package was developed and tested with a Microsoft SQL Server. As such, the query in the action "com.vmware.coe.sql.getTableColumnNames" is likely to only work with a SQL Server. If you are using PostgreSQL, MySQL, or Oracle, you will likely need to modify the first line of this action to reflect the proper syntax of retrieving a record.

This first article will cover the following topics:

  • Microsoft SQL Server Database Table for use in tutorial
  • How to determine JDBC URL for use with the SQL Plug-in (or jdbc plug-in)
  • Adding the Database table to vRO

Future articles will build upon the work performed here. They will cover:

The Database

the_database.png

For this tutorial series, we will keep things very simple. Our CMDB will consist of a single "Assets" table. The table will reside in a database named "CMDB" on a SQL Server 2008 R2 server (vcac-iaas-01a.corp.local).

The table will have only 5 fields:

  1. ServerName - Hostname of the server
  2. ServerIP - IP Address of the server
  3. ServerDNS - Domain that server resides in
  4. ServerID - Unique ID of the server, for simplicity, this article will simply use the same value as the ServerName
  5. ServerOwner - e-mail address of the person who provisioned the system

Before proceeding, make sure you have a simple table created and available for you to connect to. Also be sure to note whether or not your database is listening on its standard port. In my case, SQL Server IS listening on port 1433.

Database Plug-ins and JDBC URL

database_plug-ins_and_jdbc_url.png

vRO features two plug-ins capable of communicating with external databases: SQL Plug-in and JDBC Plug-in. Regardless of which plug-in you decide to use for your solution, you will require a JDBC URL to connect to a database. The syntax for the url can be tricky, but vRO ships with a handy workflow to help you figure that out: JDBC URL generator

Run that workflow now.

JDBC URL generator - Screen 1

jdbc_url_generator_-_screen_1.png

In my lab environment, the vcac-iaas-01a server is running SQL Server 2008 R2 and is a member of the corp.local domain. Additionally, the default instance is being used and Windows authentication is enabled.

  1. Use the drop-down to select your database. Based on my note above, I will select the SQL Server/MSDE entry
  2. Specify either the FQDN or IP Address of the Database Server
  3. Provide the name of the database you wish to connect to
  4. (OPTIONAL) Provide the database port - if left blank, the default port will be used as specified by the description above the inputs based on the type of database chosen
  5. Provide the username of an account with permission to add/remove content to the database
  6. Provide the password for the account

JDBC URL generator - Screen 2

jdbc_url_generator_-_screen_2.png

If the Microsoft database (SQL Server/MSDE) was chosen in the first screen then an additonal screen will be presented so that you may specify an optional instance name and domain name.

  1. If using the default instance of SQL Server, it should be safe to leave this field blank. Otherwise, specify the appropriate Instance Name that holds your database.
  2. If your database server is a member of a domain and is using WIndows based authentication, enter the Windows Domain here as shown in the screenshot above. If you are using a standalone SQL Server with Windows authentication (local accounts) then specify the server's HostName in this field.

Click submit

Upon submission, vRO will construct the JDBC URL then attempt a connection to the database using that URL and the credentials provided.

JDBC URL generator - Results

jdbc_url_generator_-_results.png

Review the Logs tab or the Variables tab to obtain your JDBC URL (Connection string).

If the workflow fails, you may need to try different variations of your inputs. Most commonly, I have seen issues around not having the correct port specified / DB listening on dynamic port instead of default, specifying invalid credentials, specifying an invalid database instance, or missing/incorrect domain name.

When successful, the Logs tab should contain text like this:
[2014-12-02 10:13:57.831] [I] Connection String: jdbc:jtds:sqlserver://vcac-iaas-01a.corp.local:1433/CMDB;domain=corp
[2014-12-02 10:13:58.170] [I] Connection to database successful

You should now have the necessary connection string for use in the SQL and JDBC workflows to connect to a database!

Add SQL Database to vCO

add_sql_database_to_vco.png

Now that we have the JDBC URL, we can run the Add a database workflow (found under Workflows\Library\SQL\Configuration)

For the Name input, this is the display name shown in the vRO Inventory so set it to something friendly and that makes sense. In my case, I will simply call it CMDB - I could just as well name it "Server Inventory).

Choose the Database Type, then paste in your connection url from the workflow you ran in the last step.

On the next screen, I suggest using Shared Session, then providing the same credentials as you used in the JDBC URL generator workflow.

Upon submission, your Logs tab should display something similar to this:
[2014-12-02 10:57:19.071] [I] Database object added: DynamicWrapper (Instance) : [SQLDatabase]-[class com.vmware.o11n.plugin.database.Database] -- VALUE : Database[name: CMDB, type: MS SQL, connectionURL: jdbc:jtds:sqlserver://vcac-iaas-01a.corp.local:1433/CMDB;domain=corp, sessionMode: Shared Session, username: administrator]

Verify SQL Inventory

verify_sql_inventory.png

View the Inventory of your vRO Server and expand the SQL plug-in to confirm that the "CMDB" entry is there and you can see the tables in the database.

Create Folder for CMDB Workflows

create_folder_for_cmdb_workflows.png

Return to the workflows tab in the vRO client and create a new root folder named "CMDB Workflows"
We will use this folder in the next step...

Generate Workflows to manage the table

generate_workflows_to_manage_the_table.png

We are now ready to generate some simple CRUD (Create Read Update Delete) workflows for our Assets table.

  • Run the Generate CRUD workflows for a table workflow
  • Select the Assets table and then specify the CMDB Workflows folder (Workflow Category) as the destination
  • Click Submit

Verify Workflows are Generated

verify_workflows_are_generated.png

Confirm that the four workflows have been created in the folder specified...

NOTE/WARNING: It seems that in version 5.5.2.1 of vRO, there is a small bug (I have just reported this to Engineering) that does not bind the "databaseResource" attribute to the ResourceElement object properly. This "Not found" value will prevent the workflows from validating or running properly. If this happens for you, it is pretty simple to fix. Just EDIT each of the four generated workflows, and DELETE the "databaseResource" attribute from the General tab AND remove it from the "IN" binding of the Scriptable task.

Be sure to Test / Adjust each of these CRUD workflow to make sure they function with the Database you are using. With the slight mod above, they work fine for me with SQL Server, but I needed to modify the query syntax when working with PostgreSQL.

Wrapping up...

This concludes the first article in the series. Stay tuned for additional articles that will build upon the database table we have mapped here in this article.
Part 2: http://www.vcoteam.info/articles/learn-vco/292-sql-plug-in-dynamictypes-simple-cmdb-for-vcac-part-2.html

Comments  

0 # Michael Teofrio 2016-08-31 13:08
Great Article! I would like to add that in some environments, if using windows authentication you may need to add the ";useNTLMv2=Tru e" to the end of the JDBC string. This may not be required depending on the security settings of the environment. Thank you!
0 # burke 2016-07-26 14:25
Anu - That error you pasted is telling me that you have a backslash in your hostname ... without a screenshot or a paste of what JDBC url you are actually trying to use, it is difficult to determine anything at this point.
0 # anu 2016-07-26 09:37
I am trying to generate the cmdb url from the workflow but getting the below error:
Connection to database failed (Reason: InternalError: Unknown server host name ' ST-W4494\PM601' . (Workflow:JDBC connection example / Test JDBC connection (item0)#3)) (Workflow:JDBC connection example / Test JDBC connection (item0)#7)

Please let me know if any1 face the similar kind of issue and got it fixed.

NUC Lab Kit

Below are my thoughts for a vSAN nuc lab. Since I already have cables, not including them here. I ordered (and received by Nov 30, 2016)
3 x nuc, 3 x 32GB Crucial mem, 3 x Toshiba NVMe drive, 3 x Startech USB to GB NIC, and 3 x Crucial 1TB SSD. I've also been very happy with my Cisco SG300-10 so I bought one more since my existing one only has one port available. Each of the items listed here are linked below - all were purchased using the provided links below.
single NIC (See this post for details on using the USB -> GB NIC item listed below

I stayed with the i5 for the power consumption and form factor vs. the i7 Skull Canyon ;)

 

Search

Experts Exchange