up to CF v245: Query CCDB in Cloud Foundry for the app(s) that correspond to a domain

find_apps_for_example_com.sql

# CF <= v245
USE ccdb;
SELECT
  apps.id AS AppID, 
  apps.name AS AppName, 
  apps.guid AS AppGUID, 
  apps.state AS AppState, 
  routes.id AS RouteID, 
  routes.host as RouteHost, 
  routes.path AS RoutePath,
  routes.space_id AS SpaceID, 
  spaces.name AS SpaceName, 
  organizations.id AS OrganizationID, 
  organizations.name AS OrganizationName, 
  domains.id AS DomainID,
  domains.name AS DomainName
FROM routes 
  INNER JOIN domains ON routes.domain_id=domains.id 
  INNER JOIN spaces ON routes.space_id=spaces.id 
  INNER JOIN organizations ON spaces.organization_id=organizations.id 
  INNER JOIN apps_routes ON routes.id=apps_routes.route_id 
  INNER JOIN apps ON apps.id=apps_routes.app_id 
WHERE domains.name='example.com' 
LIMIT 5;

CF >= v246 use

SELECT
  apps.id AS AppID, 
  apps.name AS AppName,
  apps.guid AS AppGUID, 
  processes.type AS ProcessType,
  processes.guid AS ProcessGuid,
  apps.desired_state AS AppState, 
  routes.id AS RouteID, 
  routes.host as RouteHost, 
  routes.path AS RoutePath,
  routes.space_id AS SpaceID, 
  spaces.name AS SpaceName, 
  organizations.id AS OrganizationID, 
  organizations.name AS OrganizationName, 
  domains.id AS DomainID,
  domains.name AS DomainName
FROM routes 
  INNER JOIN domains ON routes.domain_id=domains.id 
  INNER JOIN spaces ON routes.space_id=spaces.id 
  INNER JOIN organizations ON spaces.organization_id=organizations.id 
  INNER JOIN route_mappings ON routes.guid=route_mappings.route_guid 
  INNER JOIN apps ON apps.guid=route_mappings.app_guid 
  INNER JOIN processes ON apps.guid=processes.app_guid AND processes.type=route_mappings.process_type
WHERE domains.name='example.com'
LIMIT 5;