4 Replies Latest reply on Jun 8, 2016 9:58 PM by Dan_Linsley

    vRA BluePrint drop down property value from MSSQL database via vRO

    LordISP Novice

      Hey Folks

       

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

       

      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

        • 1. Re: vRA BluePrint drop down property value from MSSQL database via vRO
          Dan_Linsley Hot Shot
          VMware Employees

          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

          • 2. Re: vRA BluePrint drop down property value from MSSQL database via vRO
            LordISP Novice

            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

            • 3. Re: vRA BluePrint drop down property value from MSSQL database via vRO
              codyde Novice
              vExpert

              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.

              • 4. Re: vRA BluePrint drop down property value from MSSQL database via vRO
                Dan_Linsley Hot Shot
                VMware Employees

                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.