vRA7 Database Query

[vmsiul]

Hello, 

We need to generate a report for every business group that shows the deployments that were successful and get the VM name and the owner that's assigned to the VM (ideally the username name of the owner). I can get some of this data from within the deployments TAB but I was wondering if someone knows a better way to do it with a query from the DB.

Something like this:

Business Group A 

VM1    user1 

VM2   user2  

I have access the DB using the following but I am unable to generate the results that I need. 

https://vra4u.com/2019/08/09/vra-quick-tip-how-to-access-embedded-postgresql-db/

[bdamian]

The problem is that vRA has two DataBases, the Postgres inside vRA Appliance and the Sql Server of the IaaS.

To get the information you need, I suggest using the API, The following API call returns all Virtual Machines of a Tenant. In the response you'll find anything you need.

Method: GET
https://vraHost/catalog-service/api/consumer/resources?limit=99999&$filter=resourceType+eq+%27Infrastructure.Virtual%27

[RebeccaW]

Your Deployment data is going to be in the Postgres DB. The SQL DB will have all the machines and owners and which Business Group. In the SQL DB look for VirtualMachines table, the owner will be a number so you need to join in Users table, The HostReservationID will be a GUID for the HostReservation table. In turn HostReservation table will have the GroupID GUID for the ProvisioningGroup table which is your Business Group. One of the stock views uv_VirtualMachineView may have the data you want. Once you go to vRA8 no more SQL DB so working on figuring out a new solution. 

On machines that have been destroyed or unregistered look in VIrtualMachinesHistory.