Process Server List

I have been asked to provide a listing of our Processes and the servers or at least the server roles that they effect. I’m having a hard time finding the server roles in the JSON fields. I can write something to go through a json field and bring out server roles, but I have to find the field that contains them first.

Not sure if this is accurate but

/****** Script for SelectTopNRows command from SSMS ******/
SELECT distinct e.name
,d.[name]
,b.name
FROM [OctopusDeploy].[dbo].[DeploymentRelatedMachine] as a,
[OctopusDeploy].[dbo].[machine] as b,
[OctopusDeploy].[dbo].[deployment] as c,
[OctopusDeploy].[dbo].[Project] as d,
[OctopusDeploy].[dbo].[ProjectGroup] as e
where a.MachineId = b.id and
a.DeploymentId = c.id and
c.ProjectId = d.id
and c.ProjectGroupId = e.id
and c.EnvironmentId = ‘environments-3’
Order by e.name,d.name,b.name

Hi Lance,

Thanks for getting in touch! Are you looking to retrieve the machine roles in which your deployment steps are scoped to? This is located in the JSON column in the dbo.DeploymentProcess table which will include something like Properties":{"Octopus.Action.TargetRoles":"web"}.

You can also query the API (we generally recommend retrieving info via the API over querying the db). At [OctopusURL]/api/deploymentprocesses/deploymentprocess-Projects-ID it’ll list all steps and under the Properties it’ll show something like Octopus.Action.TargetRoles "role".

I hope this helps! Let me know what you think and if you have any further questions going forward.

Best regards,

Kenny

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.