How to view server capabilities for deployment projects.

[Jason Monsorno]

What I did:
I'm running v5.2 and I go into the admin page -> Server capabilities -> then click on an executable to bring up the view mode. From here it lists all the jobs that use that executable, what I am expecting is to also see the deployment projects that uses that capability.

What I'm doing that created this need:
I'm trying to migrate new functionality/logic to all my plans and deployment projects, I have a command that I'd like to switch over to new command but I'm passing in different arguments as well so I can't just change the executable path. The information I can see now gives me a nice audit of what is switched over to the new commands and which ones haven't for the Stages,Jobs,Tasks side but I can't find anything for deployment tasks without opening them to edit individually.

What I think I need, minimally:
I've already printed out a checklist and been making by way through it but I'd like an audit/sanity check for the deployment projects from Bamboo. I don't care if it's deployment project, environment, or task granularity; I'm looking for yes/no results. If necessary I can do an API call or database query since this is ideally a one off audit but would still like this capability for later uses as well.

[Jason Monsorno · Accepted solution]

I found what I was looking for and more in the database. DEPLOYMENT_ENVIRONMENT has the entire environment in xml which I was able to parse to make sure I had only one task, the executable was what I wanted and the arguments were correct. Including SQL:

USE Bamboo

SELECT DP.[NAME] [Project], DE.[NAME] [Environment], DE.[XML_DEFINITION_DATA]
FROM [DEPLOYMENT_ENVIRONMENT] DE
INNER JOIN [DEPLOYMENT_PROJECT] DP ON DP.[DEPLOYMENT_PROJECT_ID] = DE.[PACKAGE_DEFINITION_ID]
INNER JOIN [BUILD] B ON B.[FULL_KEY] = DP.[PLAN_KEY]
WHERE NOT 
(
	CONVERT(VARCHAR(max), DE.[XML_DEFINITION_DATA]) LIKE '%<taskDefinition>%<value>"SvnUrl=${bamboo.planRepository.repositoryUrl}" "SvnRevision=${bamboo.planRepository.revision}" "ReleaseName=${bamboo.ReleaseName}" "DeploymentType=${bamboo.deploy.environment}"</value>%<value>CallerDeploy</value>%'
	AND 
	CONVERT(VARCHAR(MAX), DE.[XML_DEFINITION_DATA]) NOT LIKE '%<taskDefinition>%<taskDefinition>%'
)
--Not a DB
AND DP.[NAME] NOT LIKE 'DB.%'
--Not in list of disabled plans
AND B.[SUSPENDED_FROM_BUILDING] = 0
ORDER BY [Project], [Environment]

I also did the same thing for the build since I could check arguments:

USE Bamboo

SELECT PROJ.TITLE [Project], P.FULL_KEY [Plan], BD.[XML_DEFINITION_DATA]
FROM [BUILD] B
INNER JOIN [BUILD] P ON P.[FULL_KEY] + '-' + B.[BUILDKEY] = B.[FULL_KEY]
INNER JOIN [BUILD_DEFINITION] BD ON BD.[BUILD_ID] = B.[BUILD_ID]
INNER JOIN [PROJECT] PROJ ON PROJ.[PROJECT_ID] = B.[PROJECT_ID]
WHERE 
NOT (
	CONVERT(VARCHAR(MAX), BD.[XML_DEFINITION_DATA]) LIKE '%<taskDefinition>%<value>"SvnUrl=${bamboo.repository.svn.repositoryUrl}" "SvnRevision=${bamboo.repository.revision.number}" "ReleaseName=${bamboo.ReleaseName}" "BuildKey=${bamboo.buildKey}" "BuildNumber=${bamboo.buildNumber}"</value>%<value>CallerBuild</value>%'
	AND 
	CONVERT(VARCHAR(MAX), BD.[XML_DEFINITION_DATA]) NOT LIKE '%<taskDefinition>%<taskDefinition>%'
)
AND B.[MASTER_ID] IS NULL
--Not a DB
AND PROJ.[TITLE] NOT LIKE 'DB.%'
--Not in list of disabled plans
AND P.[SUSPENDED_FROM_BUILDING] = 0
--Remove for final checks to ensure package job is gone
AND B.[BUILDKEY] = 'BUILD'
ORDER BY [Project], [Plan]

They include some extra stuff like ignoring disabled plans and focusing on a specific job which I wanted but just beware if you can reuse it.