VMware Cloud Community
LordISP
Enthusiast
Enthusiast

vRA BluePrint drop down property value from MSSQL database via vRO

Hey Folks

I this is probably a easy thing but I think too complicated Smiley Happy

So what I actually want to do is to use a Database to provide a Blueprint a dynamic drop down menu where the requester can select a value.

What I have is a MSSQL database where I maintain the content via an ASD blueprint.

Now I have the database with the values an a vRA Blueprint and I want to add a drop down menu with the content of the DB. I thought in a direction to create a workflow that call a "select from" and return the outputs but I have no idea how I can tell vRA that this output shall be added to a specific Blueprint property if that actually works.

I know it easy by building the whole Service blueprint via ASD but I'm looking for a solution to use vRA blueprints.

Many thanks for your ideas!

Cheers

Lord

http://rafaelcamison.wordpress.com http://communities.vmware.com/people/LordISP/blog
4 Replies
Dan_Linsley
VMware Employee
VMware Employee

There is a new feature in vRA 7 that can help you here.

First, you'll need to create an action in vRO that returns an Array/string  type.  This action would end up performing your SQL select query.

Then you create a Property Definition for your blueprint input:

  • Set Display Advice to "Drop Down"
  • Check the "External Values" box, and you can choose the action that you developed.

This is also covered here:  Use a vRealize Orchestrator Script Action to Populate a Property Value

LordISP
Enthusiast
Enthusiast

Hey Dan

thanks this shall help after the upgrade which wil take a bit longer due too our high network security. So I assume if I run vRA version 6.2 there's no way to archive the same?

Cheers

http://rafaelcamison.wordpress.com http://communities.vmware.com/people/LordISP/blog
Reply
0 Kudos
codyde
Contributor
Contributor

I actually do this a ton across many workflows in 6.2.2. Lets see if I can get you there....

Create your database + database table and all that mess, lets for example call the database/table testsql01\[app-db].dbo.apptable, and lets say you have an index column and a data column. We'll call the data column appnames.

Start by creating a custom action in vRO - for grins lets call the action getAppDB

Give it a "Return type" of Array/String (or wahtever values youre going to place in your database). Create an input of dbo, as a string type.

Drop this code in -

------------------------------------------------------------------------------------------------

query = "select appnames from apptable"

var results = dbo.readCustomQuery(query)

var stuff = new Array();

var d, i;

for (i = 0; i < results.length; i++ ) {

  d = results[i];

  stuff.push(d.appnames);

  }

return stuff

------------------------------------------------------------------------------------------------


Save and close that bad boy. Were done with him for now.

A couple of things we'll want to do as prep for the next part -

  • Have you added your SQL database to vRO yet? (I.e. can you see it on the inventory screen, under the SQL plugin?) If not - add it via the Workflows > Library > SQL > Configuration > Add a Database, if you're having a hard time with the syntax, you can use the JDBC URL generator, but this syntax works for me - jdbc:jtds:sqlserver://testsql01.humblelab.com:1433, i used the SA account
  • Have you added an input parameter for the field you want to populate off the database? Add that now, as a string type (or whatever you want)

With those things done, pop into your workflow that you'll be presenting via ASD and enter edit mode....next steps...

  1. Create an attribute called DBO, thats a type of SQL, and maps to your SQL database I had you add previously
  2. Move to the Presentation tab, select your input i had you create previously
  3. Select Properties tab below, hit the add property button and select "predefined answers", ensure OGNL binding (green symbol) is selected, and press the button on the far right. Type in the action name we gave earlier, called getAppDB,
  4. For the parameters to map, map it to the DBO attribute we created earlier, you should just be able to type #dbo and have it pick up. Click ok. In this example, the field should look VERY similar to.... GetAction("com.org.demo","getAppDB").call( #dbo )

Hit save, and run your workflow. If you did everything right it should populate as a drop down now.

Let me know if you have any other questions.

Dan_Linsley
VMware Employee
VMware Employee

If you are using vRA 6.2 and the data is not too dynamic there is another option using vCAC / vRA Custom Property Toolkit for vCO / vRO

You can use workflow: Property Dictionary\Attributes\Create / Update Property Definition Attribute

Feed that workflow an comma delimited list of entries from your SQL database.  Schedule the generated workflow to update the dropdown every day, hour, your choice.

Reply
0 Kudos