Helpful CCDB Queries to Show Active Organizations

November 16, 2017

I will likely get dirty looks for querying the CCDB directly but I’m a recovering DBA. API endpoints are nice, I like database queries. If you don’t know how to connect to the CCDB database you probably shouldn’t be using this blog post 🙂

To get the list of Organizations and the number of application instances they’ve started in the last 7 days:

SELECT
  sum(processes.instances) as app_instance_count,
  organizations.name
FROM
  processes,
  apps,
  spaces,
  organizations
WHERE
  processes.app_guid=apps.guid and
  apps.space_guid=spaces.guid and
  spaces.organization_id=organizations.id and
  processes.state='STARTED' and
  processes.created_at > DATE(NOW()) - INTERVAL 7 DAY
GROUP BY
  organizations.id,
  organizations.name
ORDER BY 1 desc;

To get the list of Organizations and how much RAM their running application instances have reserved:

SELECT
  sum(processes.memory * processes.instances) AS memory_reserved,
  organizations.name
FROM
  processes,
  apps,
  spaces,
  organizations
WHERE
  processes.app_guid=apps.guid and
  apps.space_guid=spaces.guid and
  spaces.organization_id=organizations.id and
  processes.state='STARTED'
GROUP BY
  organizations.id,
  organizations.name
ORDER BY 1 desc;

Written by:
Chris Weibel

Senior Cloud Engineer