List all jobs and steps #adtempus #version4 #sql

readme.md

This SQL query demonstrates how to list all jobs and steps in the adTempus 4.x database, including the full group name for each job.

The group name is assembled by following the group hierarchy up to a limit of 10 levels. The top level group of the hierarchy is always the "Root" group and its name is not included in the group name; jobs in the root group show an empty string for the group name.

The JobStep.StepName is populated only if a name has been set in the Console. The Console displays a description for each step that is generated from the step's settings, but this description is not stored in the database.

sample.sql

SELECT
	   case 
		when jg2.name is null then
			''
		when jg3.name is null then
			isnull(jg1.name,'')
		when jg4.name is null then
			isnull(jg2.name,'') + '\' + isnull(jg1.name,'')
		when jg5.name is null then
			isnull(jg3.name,'') + '\' + isnull(jg2.name,'') + '\' + isnull(jg1.name,'')
		when jg6.name is null then
			isnull(jg4.name,'')  + '\' +  isnull(jg3.name,'') + '\' + isnull(jg2.name,'') + '\' + isnull(jg1.name,'')
		when jg7.name is null then
			isnull(jg5.name,'')  + '\' +  isnull(jg4.name,'')  + '\' +  isnull(jg3.name,'') + '\' + isnull(jg2.name,'') + '\' + isnull(jg1.name,'')
		when jg8.name is null then
			isnull(jg6.name,'')  + '\' +  isnull(jg5.name,'')  + '\' +  isnull(jg4.name,'')  + '\' +  isnull(jg3.name,'') + '\' + isnull(jg2.name,'') + '\' + isnull(jg1.name,'')
		when jg9.name is null then
			isnull(jg7.name,'')  + '\' +  isnull(jg6.name,'')  + '\' +  isnull(jg5.name,'')  + '\' +  isnull(jg4.name,'')  + '\' +  isnull(jg3.name,'') + '\' + isnull(jg2.name,'') + '\' + isnull(jg1.name,'')
		else
			isnull(jg10.name,'') +  '\' + isnull(jg9.name,'') +  '\' + isnull(jg8.name,'') +  '\' + isnull(jg7.name,'') +  '\' + isnull(jg6.name,'') +  '\' + isnull(jg5.name,'')  + '\' +  isnull(jg4.name,'')  + '\' +  isnull(jg3.name,'') + '\' + isnull(jg2.name,'') + '\' + isnull(jg1.name,'')
		end

	   AS [GroupName],
       j.name AS JobName, 
       cp.userID AS CredentialProfile,
       js.stepNumber AS StepNumber,
       ISNULL(js.name,'') AS StepName

  FROM [dbo].[job] AS j

  LEFT OUTER JOIN [dbo].[jobGroup] AS jg1 ON j.jobGroupOID = jg1.oid
  LEFT OUTER JOIN [dbo].[jobGroup] AS jg2 ON jg1.parentGroupOID = jg2.oid
  LEFT OUTER JOIN [dbo].[jobGroup] AS jg3 ON jg2.parentGroupOID = jg3.oid
  LEFT OUTER JOIN [dbo].[jobGroup] AS jg4 ON jg3.parentGroupOID = jg4.oid
  LEFT OUTER JOIN [dbo].[jobGroup] AS jg5 ON jg4.parentGroupOID = jg5.oid
  LEFT OUTER JOIN [dbo].[jobGroup] AS jg6 ON jg5.parentGroupOID = jg6.oid
  LEFT OUTER JOIN [dbo].[jobGroup] AS jg7 ON jg6.parentGroupOID = jg7.oid
  LEFT OUTER JOIN [dbo].[jobGroup] AS jg8 ON jg7.parentGroupOID = jg8.oid
  LEFT OUTER JOIN [dbo].[jobGroup] AS jg9 ON jg8.parentGroupOID = jg9.oid
  LEFT OUTER JOIN [dbo].[jobGroup] AS jg10 ON jg9.parentGroupOID = jg10.oid
  LEFT OUTER JOIN dbo.CredentialProfile AS cp ON j.credentialsOID = cp.oid
  LEFT OUTER JOIN dbo.[jobStep] AS js ON j.oid = js.JobOID

  order by GroupName, JobName,StepNumber