My First vCenter Orchestrator Workflow – Part 5 – A little bit of SQL

Thus far in this series of posts we have installed and configured vCenter Orchestrator as well as setup and utilized a couple of plugins; the vCenter plug-in and the PowerShell plug-in.  Before the series ends I wanted to go through one more plug-in.  The SQL plug-in.  The SQL plug-in is used for, well, you guessed it; running SQL queries against a database.  If you remember in Part 4 we setup a workflow that took two input parameters; a host and a location code.  The script then went on to create a new port group on a vSwitch named locationcode-VM_Network.  The logic in itself works fine and the workflow validates, the only problem I see with it is that the user needs to enter the ‘location code’ manually as an input.  Now I know you are all very careful and take your time when doing things, but I’m not, and more than likely after doing this 40 or 50 times you can count on me keying in that location code wrong 🙂 – Enter the SQL plugin.

So the setup is as follows; I have a database containing a table with the following columns; locationcode and deployed (boolean).  What I would like to do is have an input parameter that would allow me to select the location code from a list of non-deployed locations in a drop-down box (rather than manually enter this information in), and in turn pass the location code to my PowerShell script.  Once the script has ran I’d like to update the deployed column for that specific location, ensuring it isn’t displayed in the list again in turn ensuring I can’t deploy the same location twice.  Make sense?  I know it’s a lot to follow but the concepts of setting it up are pretty much the same no matter what you are looking to do.

Alright – enough background – Let’s get into it.  I’m not going to go through the installation of the SQL Plug-in – it’s exactly the same installation process as the Powershell plug-in which we covered in Part 4.  Similar to how we setup our Powershell host we need to setup our SQL server database connection inside of vCO.  To do this fire up your vCO client and navigate through the workflow tree to Library->SQL->Configuration and select the ‘Add a database’ workflow, right click and Start Workflow.  There are a few parameters as you can see that we need to pass to the workflow in order for it to successfully run.  First, give this connection a name and select your desired Database Type – in my case MS SQL.  Also here we need to pass a Connection URL.  Now if you don’t know anything about jdbc connection urls no fear, it’s not that difficult.  Simply enter it in the the following format…

So, for a SQL Server with named DC.lab.local running on the default port 1433 and  database named ServerDeploy you would use the following…

After clicking Next we are once again presented with our Shared/Per User session mode – again, I chose shared to use one simple set of credentials rather than a per user authentication.  When you are ready click ‘Submit’ to add your new database to vCO’s inventory.  One thing to note here is that this step is not necessary   If we wanted we could perform all of this at run time inside code, however for tutorial purposes and learning purposes it’s sometimes easier to do it this way.

Alright, nuff config!  It’s time now to get started on our task at hand; Querying the database for non-deployed servers and pushing the result as a drop-down box as an input parameter to our workflow that we created in Part 4.  First off there is a simple change we need to make to our existing workflow.  Here’s a tip – don’t feel like buffalo-ing your original workflow, simply right click on it and  select ‘Duplicate Workflow’ to copy it.  OK, first off we need a new attribute.  We originally have locationcode setup an input parameter of type string – and we still need this, however the result we get back from our database will be an array of strings.  So, on the General tab of your workflow create a new attribute called  databaseParameter of type SQL:Database and assign it the value of the Database we created earlier (this should allow you to browse the inventory to do so).  Once you are done that simply Save & Close and continue on with any validation errors.

So here comes the real magic!   We need to take that database attribute and pass it to a newly created action which will in turn spit out an array of string attributes (our locations in our database).   Again, you could do the following all in script embedded within your workflow, but you never know when you are going to need to reuse something so I’ve decided to create a new action to do so.    To create an new action be sure you are on ‘Design’ view from within your client and click on the actions tab in the left hand side menu.  Where you place your action doesn’t really matter, I chose to right click com.vmware.library.sql and create my action inside that module.  Just remember where you put it and what you named it:).

OK, you should now be in the Action editor.  This is where we are going to place the code that does all the querying of the database.  As I said earlier we need to pass this Action a database parameter and it will return an array of string.   The setup of input parameters and return types, along with all the other work we are going to do is all done on the scripting tab.  First off define your return type of an Array/string.  Secondly add an input parameter of type SQL:Database.  You can see all of this outlined in the capture below…

Hey!  That was easy!  Now it’s time for a little scripting.  vCO script is nothing but Javascript which calls the rich set of API’s that vSphere provides, along with the functions and properties of all the plug-ins provided in vCO.  The script I used is displayed below…

Simply paste this into the script pane of your action.  As you can see it’s a pretty simple script.  Creates and array, queries our database, pushes the locationcode column value of each record returned into that array and finally returns the array.  So – time to Save and Close and head back to our workflow.

So at this point we are left with 2 tasks.  The first being the creation of the drop-down box as an input.  To do this we will need to change the way our original input parameter, locationcode, is displayed.  This is done on the Presentation tab of our script editor.  Be sure you have selected locationcode in the Presentation tree that the top of the screen and select the icon to add a new property to it.  There are many several different properties listed but the one we are after is called Predefined list of elements.  Under the value field of our new property select the icon to assign an action.  In the popup find/filter for our action we created earlier, assign the actions parameter to our database parameter and click Apply.

There done right…well, kinda, you could run the workflow now and it would populate our input and it would go ahead and run the PowerCLI script and the VM Network would be created, however if you remember it was my intent to go back at the end of the workflow and update our database to ensure that the same locationcode could not be selected twice.  To do this we will need to drag a new Scriptable task element to run after we invoke our PowerCLI script.  Inside this scriptable task we will need to import a couple of local attributes in order to accomplish the sql we need to do, the locationcode, and the databaseParameter.

As for the script it’s going to look very similar to the syntax that we placed inside of our action with the exception of and executeCustomQuery function in place of the readCustomQuery and the actual query itself is different.  The following is what I used…

And now at last we are done!!  Go ahead and run your workflow, either from within the vCO client or the Web Client and you should now see a drop-down selection for the locationcode.  Once it’s selected once the script will run, create the VM Network, then update our database to ensure that the selected locationcode is not shown again in the drop-down.

So that my friends is the end of the line for me on creating my first vCenter Orchestrator Workflow but it is most definitely not the end of the line with vCO.  With access to a huge set of vSphere API’s along with all of the functionality and properties provided by its’ plugins, throw in some sweet integration with the vSphere Web Client I’m beginning to see my usage of vCO ramp up within my current role.  This series has hardly even grazed the surface in terms of vCO’s functionality so I urge you all to go out there and learn as much as you can about this product.  I’ll do one more post in the series and outline some of the resources that I’ve found along the creation of this workflow so expect to see that soon.

My first vCenter Orchestrator Workflow

• Part 1 – Introduction
• Part 2 – Installing vCenter Orchestrator
• Part 3 – Create a test workflow and see the awesomesauce
• Part 4 – A look at the PowerShell plug-in
• Part 5 – A little bit of SQL
• Part 6 – Resources