SQL query for artifacts

[Paul Entwistle]

Hi All

I'm looking to use this sql from the below sql query to get the artifact sizes per deployment project   Useful SQL queries | Bamboo | Atlassian Documentation

 im not an SQL expert, i just steal with abandon from the internet!

The sql all works fine up until the two lines pasted below

The sql was from 2015, so have any tables changed?

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)

from this query

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

[Shashank Kumar]

Hi Paul,

Welcome to Atlassian community.

From Bamboo 9.5 below tables there have been some changes to these tables structures.

1) DEPLOYMENT_VERSION_ITEM and DEPLOYMENT_VERSION_ITEM_BA have been removed and merged to DEPLOYMENT_VERSION_ARTIFACT

2) DEPLOYMENT_PROJECT_ITEM and DEPLOYMENT_PROJECT_ITEM_BA have been removed and merged to DEPLOYMENT_PROJECT_ARTIFACT

Are you using Bamboo version >=9.5 and need the modified query?

Regards,

Shashank Kumar

**please don't forget to Accept the answer if your query was answered**

[Paul Entwistle]

Hi Shashank, yes we are on 9.6.5. If you can provide the modified query that would be great!

I just need to get onto my manager to get some training...