How to Export a List of Managed, Onboarded, and Discovered VMs from VMware Aria Automation 8.x

Products

• VMware Aria Suite

Issue/Introduction

This article will help you generate and export a list of Virtual Machines (VMs) along with their status (managed, onboarded, or discovered) from VMware Aria Automation.

Currently, there is no option to directly export this data from the VMware Aria Automation UI. This guide provides a detailed workaround by querying the VMware Aria Automation database.

Environment

VMware Aria Automation 8.x

Resolution

The list of VMs can be generated by running a SQL query against the VMware Aria Automation database. Carefully follow the steps below to export the list in CSV format.

Prerequisites

• Take snapshots or backups of all VMware Aria Automation nodes as a precautionary measure before proceeding.
• You have SSH access enabled.
• You have root username and password for the appliances.
• You are familiar with SQL query syntax.
• You have access to file transfer utilities such as scp or WinSCP. 

Procedure

1. Prepare the Query File:
   1. Create a SQL query file to retrieve the VM list. Save the file with the .sql extension. For example, name it VMStatusQuery.sql.
   2. The content of the query file should be as follows:

      SELECT name, state, origin FROM dep_resource WHERE type='Cloud.vSphere.Machine';

      • Note: This process may be used to run other different SQL Queries against the target database.
   3. Find the writable PSQL container by directly running this script from one of the appliances you are connected to over SSH:
      

      for pod in $(kubectl get pods --namespace prelude -l app=postgres -o jsonpath='{.items[?(@.status.phase=="Running")].metadata.name}'); do
        if kubectl exec --namespace prelude -it $pod -- /bin/bash -c "chpst -u postgres -- psql -t -c 'SELECT pg_is_in_recovery();'" | grep -q "f"; then
          echo "Writable master found: $pod"
          break
        fi
      done

   4. Using the output from Step 1.3, SSH to this node then use a file transfer utility like WinSCP to copy this .sql file to the VMware Aria Automation appliance. Place it in the /data directory.
2. Change File Permissions
   1. SSH to one of the VMware Aria Automation appliances and log in as root.
   2. Change the file ownership and permissions to ensure the database can execute the file:

      chown postgres:postgres /data/VMStatusQuery.sql
      chmod 666 /data/VMStatusQuery.sql

3. Connect to the VMware Aria Automation Database:
   1. Run the following command to connect to the database using the vracli tool:

      vracli dev psql

   2. You will be prompted to confirm; type yes and press Enter.
4. Run the Query and Export Data to CSV:
   1. Once connect to the database, change to the catalog-db (or any other database like provisioning-db) and run the query to export the data to a CSV (or a text / log) file:

      \c catalog-db;
      \o /data/VMStatus.csv;
      \i /data/VMStatusQuery.sql;
      \o

   2. This will execute the query and save the output to /data/VMStatus.csv.
5. Retrieve the CSV file:
   1. Use WinSCP or another file transfer utility to copy the CSV file from the /data directory to your local machine for further analysis.

Additional Information:

• The resulting CSV file can be opened with spreadsheet software like Microsoft Excel / Google Sheets for sorting, filtering, and other auditing purposes.
  • You may import this data on a regular schedule into another database for more granular auditing purposes or use Aria Automation Orchestrator to automate this procedure in a workflow using the SQL plug-in.
    • Support Services cannot help with that particular process. Please contact Professional Services for additional information.