DB query to find applications using a share components.

Release : 6.4, 6.5, 6.6, 6.7 or higher

Component : CA RELEASE AUTOMATION RELEASE OPERATIONS CENTER

Each shared component is version with each commit, hence to extract the usage information of the specific shared component we need to use the specific id of shared component version for which we want the usage. 

The query are in MS-SQL syntax (it may require adjustment if any for Oracle, MySQL)

• Get details of all shared components

SELECT 
 comp.ID, 
 comp.COMPONENT_NAME,
 comp.VERSION, 
 comp.DESCRIPTION,
 comp.ROOT_PARAM_FOLDER,
 comp.COMMITTED, 
 comp.CREATION_DATE,
 comp.COMMIT_DATE,
 comp.COMMITTING_USER, 
 comp.uuid,
 comp.parent_component_id,
 comp.rootParameterFolder, 
 comp.components_container_id
FROM components comp 
WHERE comp.components_container_id IN
(
 SELECT id 
 FROM components_containers
)

• For specific shared component version get the usage details passing the specific id's. You need to replace <comma separated id's of shared_component> in below query with comma separated numeric id's you identified from above query. Example (12,13,22)

SELECT 
 app.ID AS appId, 
 app.version, 
 app.APP_NAME AS appName, 
 app.APP_DESC AS appDesc, 
 app.PARAMS_FOLDER_ID , 
 app.isSharedLibrary , 
 app.uuid, 
 app.is_express, 
 app.delete_status 
FROM applications app 
WHERE app.ID IN 
(
 SELECT DISTINCT 
  application_id 
 FROM revision_mapping 
 WHERE component_id IN (<comma separated id's of shared_component>)
)