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

Show Orgs Exceeding Quota

Use this query to show the Orgs which are exceeding their current memory quotas which will prevent restarting the existing apps in that org. This is usually the result of a change to a quota associated to an org.

The memory_buffer column will show by how many mb of RAM is left. Any orgs with negative numbers in this field have a total memory which exceeds the quota.

SELECT a.org_name,
       a.actual_app_instances,
       (CASE WHEN a.max_app_instances > 0 THEN a.max_app_instances
             ELSE NULL END) AS max_app_instances,
       (CASE WHEN a.max_app_instances > 0 THEN a.max_app_instances - a.actual_app_instances
             ELSE 99999999 END) AS instances_buffer,
       a.allocated_memory, a.max_memory,
       a.max_memory - a.allocated_memory AS memory_buffer
FROM (
    SELECT
      o.name AS org_name,
      sum(p.instances) as actual_app_instances,
      max(q.app_instance_limit) AS max_app_instances,
      sum(p.memory * p.instances) as allocated_memory,
      max(q.memory_limit) AS max_memory
    FROM 
        processes p, apps a, spaces s, organizations o, quota_definitions q
    WHERE
          p.app_guid = a.guid
      AND s.guid = a.space_guid
      AND o.id = s.organization_id
      AND q.id = o.quota_definition_id
      AND p.state = 'STARTED'
    GROUP BY 
        o.id) a
ORDER BY 7,1;
+------------+----------------------+-------------------+------------------+------------------+------------+---------------+
| org_name   | actual_app_instances | max_app_instances | instances_buffer | allocated_memory | max_memory | memory_buffer |
+------------+----------------------+-------------------+------------------+------------------+------------+---------------+
| org1       |                    2 |              1000 |              998 |             2048 |       2048 |             0 |
| org2       |                    3 |              1000 |              997 |             2024 |       2048 |            24 |
| org3       |                    2 |              1000 |              998 |             2000 |       2048 |            48 |
+------------+----------------------+-------------------+------------------+------------------+------------+---------------+

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!