vRealize XaaS - Building Dynamic Lists From SQL Tables

vRealize XaaS - Building Dynamic Lists From SQL Tables

· by CodyDe · Read in about 7 min · (1318 words) ·


I've fully bought into the value of leveraging XaaS (Anything as a Service) for a high number of catalog items in your vRealize Automation environment. So much so in fact, that I'm doing a a session on it at VMWorld 2016! This presentation is primarily about using XaaS to deploy systems, instead of the native IAAS “modules” already boxed in vRealize Automation.

One thing I tend to do a lot of is build large lists in vRA for various reasons. vRealize Automation has many different ways to present lists natively within its walls, however I've had more than a few occasions where I wanted the list to be somewhat extensible. What if you want external systems to be able to “push” additional menu choices out for your end users? Or remove options? What if you have an in house application list that you want to be able to associate servers with, and you want that list to be dynamically maintained by another process?

This post is going to show you how I create lists housed within a table on a SQL Database. Lets get started! We'll be leveraging vRealize Orchestrator and vRealize Automation. I've tested this in 6.2.x and 7.x.

This guide assumes you already have a workflow that has a list you would like to present via an Input field. We're not going to cover building one of those workflows from scratch, but we might cover that in a future blog post. The workflow I'm using is an XaaS (Anything as a service) based System Request.

Getting Started

First, we're going to need to add our database to vRealize Automation. We'll leverage the JDBC URL Generator to grab our connection string formatted appropriately for the database. Make sure the credentials you use have appropriate access to your database/tables.

<img src=”/images/JDBC-URL-Generator.png#center” alt="JDBC URL Generator” style="width 700px”;> Fill in all the required fields - Don't forget the Microsoft tab!

If you've filled everything in correctly, you should be presented with the correctly formatted URL String for your JDBC connection.

<img src=”/images/JDBC-Successful.png#center” alt="JDBC Successful” style="width 700px”;>

In this case - you can see the following:

[2016-08-11 22:07:56.813] [I] Connection String: jdbc:jtds:sqlserver://humblesql01.humblelab.com:1433/otc;domain=humblelab.com
[2016-08-11 22:07:56.951] [I] Connection to database successful

Fantastic! Now we can use this string to continue on the process, and add our database to vRealize Orchestrator using the native SQL Plugin.

<img src=”/images/Add-Database-1.png#center” alt="Add Database” style="width 700px”;>

<img src=”/images/Add-database-2.png#center” alt="Add Database” style="width 700px”;>

Once you successfully hit submit, we'll tab over to the Plugin's screen and expand the SQL Plugin to verify the Database is listed

<img src=”/images/SQL-Validate.png#center” alt="SQL Validate” style="width 700px”;>

Excellent! We've added the OneTimeCharge database (poorly named for a different testing reason…) which has our apps table. That's what we care about!

Lets take a quick minute to check out that table in SQL and ensure it has the data that we want listed in our vRO workflow…

<img src=”/images/app-table.png#center” alt="Application Table” style="width 700px”;>

As you can see, its a current project list, which we've titled out to be various applications. In our lab, we're going to use this to present a drop down that users will pick when they build a server. All of these clearly has a high WAF (Wife Approval Factor).

Now that we've verified our Database Table is setup how we want it to be, we need to build the code in the vRealize Orchestrator workflow that will let us call these items in a list format.

First, we need to create an attribute on our existing workflow for our SQL database, later, we'll pipe this into our custom action that will return our list. We'll add a new attribute called “dbs” to our workflow, with a type of SQL:Database. That's all we need to do here for now, save and quit the workflow.

<img src=”/images/Attribute-Bindnig.png#center” alt="Attribute Binding” style="width 700px”;>

In vRealize Orchestrator, switch over to the “Design”¬†mode.¬†

<img src=”/images/vRO-Design.png#center” alt="vRO Design” style="width 700px”;>

And Select the “Actions” tab (Little Gear with a blue triangle).

I'd recommend creating a “New Module” to store all of your Custom Actions in - it just keeps things cleaner. I've named mine “com.thl.customDev” just as a reference. Right click on your module. and select New Action.

<img src=”/images/vRO-New-Module.png#center” alt="Add New Module” style="width 700px”;>

This is where things get interesting. We're going to throw some basic JavaScript together to grab that table via a query, turn it into an array of strings, and spit it back out into a workflow via an OGNL action binding.

<img src=”/images/Scripting-Action.png#center” alt="Scripting Action” style="width 700px”;>

Theres a few things going on in this picture that we should talk about -

  • Note that we've created an input named “dbs”, and set its type to SQL: Database. We'll use this to pipe in our SQL Attribute from our main workflow into this action.
  • We've set the return type to be an Array/String. This means we're going to build an array within the action, and send that array back out to the workflow
  • The SQL query is quite simple, and since we're already going to be bringing in the SQL database from our Plugin, we don't need to do anything crazy. Just a simple select statement for the column we want.
  • We iterate through the results, pushing teach item into a cleverly named array called “stuff”
  • We return the array “stuff” back out

With this workflow created, save and close, we're now going to bind it to our actual input field in our vRO workflow.

Return to your workflow and select edit (or do Ctrl+E if you're a straight up pro). Tab over to the presentation view, and select the input field that you want to bind your drop down to.

<img src=”/images/Data-Binding-NoProps.png#center” alt="Data Binding” style="width 700px”;>

Select the Properties tab on the bottom, and press the + button for “Add Property…". For this action we're going to use “Predefined Answers”. Go ahead and select that, and ensure that on the “Value” section the OGNL drop down is select, as pictured above. Select the “Help Create an Action Call” on the far right, and we can get started!

We'll type in our action name in the search above, and bind the values. You can either do a hashtag with your property name, or select the pencil icon and choose your property.

<img src=”/images/Create-an-Action-Call.png#center” alt="Create an Action Call” style="width 700px”;>

Select apply, and close. We're done!

PRO TIP: Ensure that your input field that the action is “returning” to matches the same type that you are trying to return. In this case, we are using a Array/String. Make sure that is returning an array string, or it just wont work.

<img src=”/images/Workflow-Validation-1.png#center” alt="Workflow Validation” style="width 700px”;>

Once you run this workflow, you'll be presented with an actual drop down instead of a blank “input” field. You can now take this workflow, and create an XaaS blueprint out of it to present out!


Leveraging actions (which are a lot like functions in the rest of the code world…) strategically in your workflows can do some pretty interesting stuff. In this case we've presented a menu, but here are a few examples of what binding a custom action to an input field can do:

  • Return cost for a specific resource (pipe in values, do math in the action, return the cost)
  • Return a dynamically changing default value (if building in 1 location, present these storage choices, if building in this location: allow SRM choices yes or no)
  • Store hidden fields to write to custom properties for cataloging later (resolving a UPN to the users actual first and last name, resolving an email address)

XaaS opens up the doors and makes the possibilities endless. Go forth and code.

If you want to hear more about how I'm leveraging custom actions, I'd love to have you stop by my session at VMWorld! NET7648: How PG&E is Automating Secure Environments Using NSX, vRealize Automation, and vRealize Orchestrator!