Useful SQL queries

Still need help?

The Atlassian Community is here for you.

Ask the community

Plan

Plans with the most branches
SELECT p.full_key, p.title, COUNT(b.build_id)
  FROM build p
  JOIN build b ON p.build_id = b.master_id
 WHERE p.master_id IS NULL
   AND p.build_type = 'CHAIN'
GROUP BY p.full_key, p.title
ORDER BY 3 DESC
Artifact sizes per deployment project
SELECT dp.name, sum(dvia.artifact_size)/1024 AS artifacts_size 
FROM deployment_project dp
JOIN deployment_version dv ON (dp.deployment_project_id = dv.project_id)
JOIN deployment_version_item dvi ON (dv.deployment_version_id = dvi.deployment_version_id)
JOIN deployment_version_item_ba dvia ON (dvi.deployment_version_item_id = dvia.version_bam_artifact_item_id)
GROUP BY dp.name
Biggest artifacts in given deplopyment project
SELECT dp.name, dvia.artifact_id, dvia.artifact_size/1024 AS artifacts_size 
FROM deployment_project dp
JOIN deployment_version dv ON (dp.deployment_project_id = dv.project_id)
JOIN deployment_version_item dvi ON (dv.deployment_version_id = dvi.deployment_version_id)
JOIN deployment_version_item_ba dvia ON (dvi.deployment_version_item_id = dvia.version_bam_artifact_item_id)
WHERE dp.name='DEP'
ORDER BY dvia.artifact_size
LIMIT 100
Last modified on Sep 10, 2015

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.