Build Definition Summaries - Tracking Artifactory and SonarQube Adoption

BuildDefinitionSummary.sql

select 
  x.* into #TaskDefs from (select * from [AzureDevops_CollectionDb].[Task].tbl_TaskDefinition UNION
select 
  * 
from 
  [AzureDevops_ConfigDb].[Task].tbl_TaskDefinition
) x;
select 
  * 
from 
  (
    select 
      buildDef.DefinitionId, 
      replace(
        substring(
          buildDef.DefinitionName, 
          0, 
          len(buildDef.DefinitionName)
        ), 
        '>', 
        '\') [DefinitionName]
  , p.ProjectName
  , case when JSON_VALUE(buildDef.Process, ' $.yamlFilename ') is not null then 1 else 0 end [UsesYAML]
  , case when jfrogTasksPresent.DefinitionId is not null then 1 else 0 end [UsesJFrogClassicTasks]
  , case when sonarTasksPresent.DefinitionId is not null then 1 else 0 end [UsesSonarQubeClassicTasks]
  , buildRuns.AverageRunTime
  , buildRuns.LastRunFinished
  , buildDef.CreatedOn [LastChanged]
  , isNull(buildHistory.FirstCreated, buildDef.CreatedOn) [FirstCreated]
  , buildHistory.RevisionsCount [Revisions]
  , isnull(buildRuns.RunsCount, 0) [TotalRunsCount]
  , isnull(buildRuns.FailedRunsCount, 0) [FailedRunsCount]
  , isnull(buildRuns.SuccessfulRunsCount, 0) [SuccessfulRunsCount]
  , round(buildRuns.SuccessfulRunsCount / cast(buildRuns.RunsCount as decimal(18,2)), 2) as SuccessRate
  , round(buildRuns.FailedRunsCount / cast(buildRuns.RunsCount as decimal(18,2)), 2) as FailureRate
from [Build].tbl_Definition buildDef
left join (select 
      DefinitionId
    , COUNT(1) RunsCount
    , MAX(FinishTime) LastRunFinished
    , AVG(DATEDIFF(SECOND, StartTime, FinishTime)) AverageRunTime
    , SUM(case when Result = 8 then 1 else 0 end) FailedRunsCount
    , SUM(case when Result = 2 then 1 else 0 end) SuccessfulRunsCount
  from [Build].tbl_Build group by DefinitionId
) buildRuns on buildRuns.DefinitionId = buildDef.DefinitionId
left join (select DefinitionId, Min(CreatedOn) FirstCreated, Max(CreatedOn) ChangeDate, Count(1) RevisionsCount from [build].tbl_DefinitionHistory group by DefinitionId) buildHistory on buildHistory.DefinitionId = buildDef.DefinitionId
left join dbo.tbl_Dataspace ds on ds.DataspaceId = buildDef.DataspaceId
left join [AnalyticsModel].tbl_Project p on p.ProjectSK = ds.DataspaceIdentifier
left join (
  select distinct
  DefinitionId
  from build.tbl_definition def
  CROSS APPLY OPENJSON (def.Process, N' $.phases ') with (
    PhaseName nvarchar(500) ' $.name ',
    Steps nvarchar(max) ' $.steps ' as json
  ) as DefPhases
  CROSS APPLY OPENJSON (DefPhases.Steps) with (
    TaskId varchar(500) ' $.task.id '
  ) as DefTasks
  inner join (
    select cast(TaskId as varchar(255)) as TaskId from #TaskDefs where [Name] like ' % artifactory % ' or [Name] like ' % jfrog % '
  ) matched on matched.TaskId = DefTasks.TaskId
) jfrogTasksPresent 
  on jfrogTasksPresent.DefinitionId = buildDef.DefinitionId
left join (
  select distinct
  DefinitionId
  from build.tbl_definition def
  CROSS APPLY OPENJSON (def.Process, N' $.phases ') with (
    PhaseName nvarchar(500) ' $.name ',
    Steps nvarchar(max) ' $.steps ' as json
  ) as DefPhases
  CROSS APPLY OPENJSON (DefPhases.Steps) with (
    TaskId varchar(500) ' $.task.id '
  ) as DefTasks
  inner join (
  select cast(TaskId as varchar(255)) as TaskId from #TaskDefs where [Name] like ' % sonarqube % '
  ) matched on matched.TaskId = DefTasks.TaskId
) sonarTasksPresent
  on sonarTasksPresent.DefinitionId = buildDef.DefinitionId
where Deleted = 0 and LastRunFinished is not null
) results 
order by DefinitionId desc

drop table #TaskDefs;