Connecting vCenter Orchestrator to the WFA database

The last few posts have been describing how to use REST to execute NetApp Workflow Automation (WFA) workflows remotely. The most recent post showed how to use the NetApp Workflow Automation Package for vCenter Orchestrator to execute those workflows by simply calling one vCenter Orchestrator (vCO) workflow.

However, if you followed along in that post you noticed that the data which is dynamically populated in drop downs and lists when executed from WFA is static when executed from vCO. WFA uses it’s database, which is periodically updated from OCUM, to provide real-time information when executing workflows. This includes selecting, and filtering, things like available clusters, storage virtual machines, and other important data when executing WFA workflows from the WFA GUI.

How do we get that information into vCO so that we can provide dynamic, valid, choices to the user who is executing the vCO workflow? Well, it turns out there are a couple of ways, but for this post, and the next one, we are going to focus on connecting vCO to the WFA database. In the future we will also include another way, using REST to query WFA finders.

This post will focus on connecting vCO to the WFA database and executing basic queries. The follow-on post will show how to integrate those queries into vCO workflows.

Step 1: Ensuring the WFA database is available remotely

The WFA cache database is a MySQL instance that runs from your WFA server. By default it is only accessible locally from that server using a shared memory connection. In order for vCO to leverage the information contained in that database we need to enable remote access. The database has a pre-existing user, “wfa”, who has read-only access to the schemas needed.

The process has been loosely documented on the NetApp Communities here and here. The overall gist is the following steps are required:

  1. Stop the WFA Database service on your server.

    wfa_vco_db_1

    This will also stop the WFA Server serivce.

  2. Browse to C:\Program Files\NetApp\WFA\mysql and open the my.ini file using a text editor…notepad works fine here.
  3. The bottom line, which reads bind-address=localhost needs to be commented out. To comment the line, insert a # symbol at the beginning of the line. Save and close the file.

    Note that if you are using Windows 2008 or above you will need to open the file as an administrator. You can execute Notepad with elevated credentials by browsing to it on the start menu, right clicking, and selecing “Run as Administrator…”.

    Also note that if you have a firewall on your server you will need to open port 3306 for the database connection.

  4. Restart the WFA Database and WFA Server services.

Step 2: Adding the WFA database to vCO using the SQL plugin

The SQL plugin is distributed with vCenter Orchestrator. You should not have to do anything extra to install it. The full documentation for the plugin is here, if you need it.

We want to execute the vCO workflow located at Library->SQL->Configuration titled “Add a database”.

wfa_vco_db_2

In the prompt, input a short name for the database connection. I’ve used “WFA”. Change the database type to “MySQL”, and in the third input append the name or IP of your WFA server followed by the MySQL port, 3306.

wfa_vco_db_3

On the next screen we will need to provide credentials. Use the default “Shared Session” mode. The read-only username for the WFA database is “wfa“, the password is “Wfa123“.

wfa_vco_db_4

Click the submit button and wait for the workflow to complete, which should only take a few seconds. To validate that the database connection was successful, we want to browse to the Inventory tab (it looks like a sheet of paper with a puzzle piece in front of it), expand the SQL Plug-in tree item, then expand the newly created WFA database item. If successful, you should see list of all the tables that are available in the WFA database.

wfa_vco_db_5

Step 3: Querying the database

To further validate our database connection, and to demonstrate executing a query against the database, we are going to use the vCO workflow “Read a custom query from a database”. This workflow does exactly what it says it does…you supply a SELECT query, it executes the query and returns the record set.

To keep things simple we are going to execute a simple query: SELECT * FROM cluster. This will return the clusters that are cached in WFA’s database.

wfa_vco_db_6

After hitting submit the query should only take a few seconds to execute. This is meant to be a utility workflow, not a pretty display or test workflow, so the output is minimal. To view the returned records, ensure you are looking at the “Variables” tab for the workflow execution. One of the variables is “resultRecords” which is an array or SQL:ActiveRecord types. Clicking the circle with a lower case I in it will display the contents of that variable. In my lab I have a single cluster (“cluster1”), which is the only element in the array.

wfa_vco_db_7

Conclusion

This is the just the basics of connecting vCenter Orchestrator to any database, and only begins to show the potential. If your ticketing system uses a database, or you have a custom developed web application, or even want to develop vCO workflows that need to persistently store relational data, this can act as the starting point for that integration where you can query and insert data as needed.

In the next post we will show how to use actions to query the database and return data to a workflow to populate a drop down selection.

Questions and comments are always welcome below. If you have any suggestions for, or need help with, NetApp/VMware integration, automation, and solutions, please let me know using the comments!

Leave a Reply