How To: Use DMC database to query for License and Deployment Counts

There is a PostgreSQL database that accompanies the Deployment Monitoring Console (DMC) application.

This database can be queried to find information regarding license counts, deployment counts, and other information. Liquibase recommends setting up a READ-ONLY user for this purpose.

Instructions

If you have Administrative privileges on the DMC application you should be able to lookup the DMC database information by using the Client Connect menu option.

Sample DMC Queries

License Counts

Reference Databases do not impact license counts. Update the %REF% syntax below for your specific Reference database names.

select count(*) as dmc_steps
from dprj_pipeline_step dps
inner join dprj_pipeline dp on (dp.id = dps.pipeline_id)
inner join dprj_project p on (p.id = dp.project_id)
where dps.name not like '%REF%';

List of steps by project and pipeline

select  p.name as project, dp.name pipeline, dps.name as step, dps.dbms
from dprj_pipeline_step dps
inner join dprj_pipeline dp on (dp.id = dps.pipeline_id)
inner join dprj_project p on (p.id = dp.project_id)
order by dps.dbms, p.name, dp.name, dps.step_order;

Deployments

select dp.id, dp.start_date, dp.end_date, de.name, proj.name, dp.successful from datical.dprj_deployment dp
join datical.dprj_environment de on (dp.environment_id = de.id)
join datical.dprj_project proj on (de.project_id = proj.id);

Deployments with Packager Deployments excluded

Your organization may wish to exclude the Reference Database from this count because it is considered a build operation versus a deployment to a target database. Update the %REF% syntax below for your specific Reference database names.

select dp.id, dp.start_date, dp.end_date, de.name, proj.name, dp.successful from datical.dprj_deployment dp
join datical.dprj_environment de on (dp.environment_id = de.id)
join datical.dprj_project proj on (de.project_id = proj.id)
where de.name not like '%REF%';

SQL Statement Query Example

The following query can be used to pull the SQL statements that were applied in the different environments. The failure_message column will show any resulting errors from these SQL statements:

select o.project_name, o.pipeline_name, o.deploy_result, o.action_type, o.step, o.total_time, cd.changeset_id, cd.author, cd.labels, cd.result, cis.sql_text, ci.failure_message, ci.command_output, ci.duration_ms from datical.operations o
join datical.changeset_details cd on (cd.fk_operations_id = o.id)
join datical.change_impacts ci on (ci.fk_operations_id = o.id and ci.fk_changeset_details_id = cd.id)
join datical.change_impact_sqls cis on (cis.fk_change_impacts_id = ci.id)
and o.action_type = 'DEPLOY'
and ci.change_description like 'Execute SQL%';

Liquibase Enterprise Knowledge Base