CA Release Automation - Query to list Deployment Details and Agents involved in the Deployment

Document ID : KB000115637
Last Modified Date : 24/09/2018
Show Technical Document Details
Introduction:
CA Release Automation - SQL Query to list Deployment Details and Agents involved in the Deployment
Environment:
All versions of CA Release Automation
Instructions:
The following SQL Query can be used to list successful Deployments, Deployment Plans and Agents assigned to the Deployment:

select 
rc.id as deployment_plan_id, 
max(rc.name) as deployment_plan_name, 
max(r.id) as release_id, 
max(r.name) as release_name, 
dateadd(S, min(m.startTime)/1000, '1970-01-01 08:00:00') as StartTime, 
dateadd(S, max(m.endTime)/1000, '1970-01-01 08:00:00') as End_time, 
max(a.APP_NAME) as application, 
max(e.name)as environment, 
rs.server_id as agent_id, 
max(se.server_name) as agent_name 
from 
rc_release_candidate rc 
inner join rc_releases r on rc.id = r.release_candidate 
inner join applications a on r.application = a.id 
inner join rc_stages s on r.id = s.release_id 
inner join environments e on s.environment = e.id 
inner join rc_modules m on s.id = m.stage_id 
inner join rc_server_types st on m.id = st.module_id 
inner join rc_servers rs on st.id = rs.rc_server_type_id 
left join servers se on se.id = rs.server_id 
where r.release_result = 'SUCCESS' 
group by rc.id,rs.server_id 
order by rc.id;