Use these queries to get an overview on a CF/PCF environment from Cloud Controller's perspective.

You'll need to connect to your ccdb PostgreSQL or MySQL instance. Quick hint: all the db: info you need is in /var/vcap/jobs/cloud_controller_ng/config/cloud_controller_ng.yml.

Show Service Broker Utilization

For each service broker, show the service instances and app/space/org information about the bound service. Note that unbound services are not included.

select
	organizations.name as org_name,
	spaces.name as space_name,
	apps.name as app_name,
	service_brokers.name as service_broker_name,
	service_instances.name as service_instance_name,
	service_plans.name as service_plan_name,
	processes.state as process_state
from
	service_bindings,
	service_instances,
	service_plans,
	services,
	service_brokers,
	apps, 
	spaces, 
	organizations, 
	processes
where
	service_bindings.service_instance_guid = service_instances.guid
	and service_instances.service_plan_id = service_plans.id
	and service_plans.service_id = services.id
	and services.service_broker_id = service_brokers.id
	and apps.space_guid = spaces.guid 
	and spaces.organization_id=organizations.id 
	and processes.app_guid = apps.guid
	and service_bindings.app_guid = apps.guid
order by
	1,2,3,4,5;

Output:

 org_name | space_name |  app_name   | service_broker_name | service_instance_name | service_plan_name | process_state
----------+------------+-------------+---------------------+-----------------------+-------------------+---------------
 system   | system     | cf-apigen   | vault               | secrets               | shared            | STARTED
 system   | system     | cf-vault-ui | vault               | secrets               | shared            | STARTED
 system   | system     | web-app     | blacksmith          | postgresql            | standalone        | STARTED
 system   | system     | web-app     | shield-broker       | shield                | shared            | STARTED
 system   | system     | web-app     | vault               | secrets               | shared            | STARTED

Cell Resources Consumed

Use this to determine the amount of memory and disk currently allocated to apps:

select 
	sum(instances) as app_instances,  
	sum(memory * instances) as allocated_memory, 
	sum( disk_quota * instances) as allocated_disk, 
	state 
from 
	processes 
group by 
	state;

Output:

 app_instances | allocated_memory | allocated_disk |  state
---------------+------------------+----------------+---------
             1 |             1024 |           1024 | STOPPED
             9 |             1984 |           3600 | STARTED

List all Apps, Orgs, Spaces

This is useful for knowing a point-in-time the list of apps and their state.

SELECT 
	organizations.name as org_name, 
	spaces.name as space_name, 
	apps.name as app_name, 
	processes.instances as instance_count, 
	processes.state as state 
from 
	apps, 
	spaces, 
	organizations, 
	processes 
where 
	apps.space_guid = spaces.guid 
	and spaces.organization_id=organizations.id 
	and processes.app_guid = apps.guid 
order by 1,2,3;

Output:

 org_name | space_name |    app_name    | instance_count |  state
----------+------------+----------------+----------------+---------
 system   | system     | bolo-cf-nozzle |              4 | STARTED
 system   | system     | cachefire      |              1 | STOPPED
 system   | system     | cf-apigen      |              1 | STARTED
 system   | system     | cfseeker       |              1 | STARTED
 system   | system     | cf-vault-ui    |              1 | STARTED
 system   | system     | vault-broker   |              1 | STARTED
 system   | system     | web-app        |              1 | STARTED

Summary Queries

Org Count

SELECT COUNT(*) FROM organizations;

Spaces Count

SELECT COUNT(*) FROM spaces;

App Count

SELECT COUNT(*) FROM apps;

Quota Definitions

SELECT COUNT(*) FROM quota_definitions;

If you combine this output with the cf inventory scraper you'll get a full overview of your CF deployment.

Enjoy!