Change Failure Rate

javascript
deployFailuresLast6Months = await sql`SELECT date_trunc('month', datetime) as month, COUNT(*) FROM custom_data_points where datetime > date_trunc('month', current_date - interval '6' month) AND tag='deploy_failure' GROUP BY month;` deploysLast6Months = await sql`SELECT date_trunc('month', external_merged_at) as month, COUNT(*) FROM pull_requests where external_merged_at > date_trunc('month', current_date - interval '6' month) AND base_branch_name='deploy' GROUP BY month; ` changeFailureRaterArray = []; for(deploysForMonth of deploysLast6Months) { deployFailuresForMonth = deployFailuresLast6Months.find((failuresPerMonth) => { return JSON.stringify(deploysForMonth['month']) === JSON.stringify(failuresPerMonth['month']) }); deployFailuresForMonth = deployFailuresForMonth ? deployFailuresForMonth['count'] : 0; console.log('deploysForMonth', deploysForMonth['count']) console.log('deployFailuresForMonth', deployFailuresForMonth) changeFailureRate = deployFailuresForMonth / deploysForMonth['count'] // 0.02 is 2% changeFailureRaterArray.push({ month: deploysForMonth['month'], changeFailureRate: Math.round(changeFailureRate * 100) / 100 }) } return changeFailureRaterArray

Change Failure Rate Using PR Filters

javascript
begin_date = {begin_date} end_date = {end_date} time_period_group_by_id = {time_period_group_by_id} should_include_date_filter = begin_date && end_date date_filter = sql`AND external_merged_at > ${begin_date} AND external_merged_at <= ${end_date}` deployFailuresOverTime = await sql` SELECT DATE_TRUNC(${time_period_group_by_id}, external_merged_at) AS period, COUNT(*) AS number_of_failed_deploys FROM pull_requests WHERE external_merged_at IS NOT NULL AND ( body ILIKE '%revert%' OR title ILIKE '%revert%' OR body ILIKE '%rollback%' OR title ILIKE '%rollback%' OR body ILIKE '%roll back%' OR title ILIKE '%roll back%' OR body ILIKE '%fix deploy%' OR title ILIKE '%fix deploy%' OR body ILIKE '%hot fix%' OR title ILIKE '%hot fix%' OR body ILIKE '%fix prod%' OR title ILIKE '%fix prod%' OR body ILIKE '%fixes prod%' OR title ILIKE '%fixes prod%' ) AND base_branch_name='master' ${should_include_date_filter ? date_filter : sql``} GROUP BY period ORDER BY period ` deploysOverTime = await sql`SELECT date_trunc(${time_period_group_by_id}, external_merged_at) as period, COUNT(*) FROM pull_requests where base_branch_name='master' ${should_include_date_filter ? date_filter : sql``} GROUP BY period;` changeFailureRaterArray = []; for(deploysForPeriod of deploysOverTime) { deployFailuresForPeriod = deployFailuresOverTime.find((failuresPerPeriod) => { return JSON.stringify(deploysForPeriod['period']) === JSON.stringify(failuresPerPeriod['period']) }); deployFailuresForPeriod = deployFailuresForPeriod ? deployFailuresForPeriod['number_of_failed_deploys'] : 0; changeFailureRate = deployFailuresForPeriod / deploysForPeriod['count'] // console.log('Month', deploysForMonth['period']) // console.log('deployFailuresForMonth', deployFailuresForMonth) // console.log('deploysForMonth', deploysForMonth['count']) // 0.02 is 2% changeFailureRaterArray.push({ period: deploysForPeriod['period'], changeFailureRate: Math.round(changeFailureRate * 100) / 100 }) } return changeFailureRaterArray

Time to restore service

javascript
last6MonthsOutagesAndRestores = await sql` SELECT * from custom_data_points where (tag='system_outage' OR tag='system_restored') AND datetime > date_trunc('month', current_date - interval '6' month) ORDER BY datetime ASC ` last6MonthsOutagesAndRestoresNoRepeats = []; for(i=0; i < last6MonthsOutagesAndRestores.length; i++) { currentValue = last6MonthsOutagesAndRestores[i] previousValue = last6MonthsOutagesAndRestores[i - 1] if (!previousValue || currentValue['tag'] != previousValue['tag']) { last6MonthsOutagesAndRestoresNoRepeats.push(currentValue) } } systemRestoresWithTimes = []; for(i=0; i < last6MonthsOutagesAndRestoresNoRepeats.length; i++) { currentValue = last6MonthsOutagesAndRestoresNoRepeats[i] if (currentValue['tag'] === 'system_restored') { previousValue = last6MonthsOutagesAndRestoresNoRepeats[i - 1] if (previousValue) { restoredDate = new Date(currentValue['datetime']) outageDate = new Date(previousValue['datetime']) systemRestoresWithTimes.push({ datetime: currentValue['datetime'], timeToRestoreMs: restoredDate - outageDate }) } } } function getRestoresWithin(restores, startDate, endDate) { return restores.filter((restore) => { return new Date(restore['datetime']) >= startDate && new Date(restore['datetime']) < endDate; }) } begginingOfMonth = new Date(); begginingOfMonth.setDate(1); begginingOfMonth.setMonth(begginingOfMonth.getMonth() - 6); restoreTimesByMonth = [] for (i = 0; i <= 6; i++) { begginingOfNextMonth = new Date(begginingOfMonth); begginingOfNextMonth.setMonth(begginingOfMonth.getMonth() + 1); begginingOfNextMonth.setDate(1); restoresForCurrentMonth = getRestoresWithin( systemRestoresWithTimes, begginingOfMonth, begginingOfNextMonth ) restoreTimesByMonth.push({ month: begginingOfMonth.toISOString(), restoresForCurrentMonth }); begginingOfMonth.setMonth(begginingOfMonth.getMonth() + 1); } restoreTimeAverageByMonth = [] for(restoreTimesForSingleMonth of restoreTimesByMonth) { restoreTimes = restoreTimesForSingleMonth['restoresForCurrentMonth'].map((time) => time['timeToRestoreMs']); averageRestoreTimeInMs = restoreTimes.reduce((a, b) => a + b, 0) / restoreTimes.length; restoreTimeAverageByMonth.push({ month: restoreTimesForSingleMonth['month'], averageRestoreTimeInMinutes: Math.round(averageRestoreTimeInMs / 1000.0 / 60.0) }) } return restoreTimeAverageByMonth

CI Runs vs Restarts Per Week

javascript
allCiRunsByWeek = await sql`SELECT date_trunc('week', datetime) as Week, COUNT(*) from custom_data_points where tag='ci_run' GROUP BY Week`; restartedCiRunsByWeek = await sql`SELECT date_trunc('week', datetime) as Week, COUNT(*) from custom_data_points where tag='ci_run' AND metadata ILIKE '%is-rerun-true%'GROUP BY Week`; runData = []; for(ciRunsForSingleWeek of allCiRunsByWeek) { numberOfRestartedCiRunsForCurrentWeek = restartedCiRunsByWeek.find((restartedCiRunsForSingleWeek) => { return JSON.stringify(restartedCiRunsForSingleWeek['week']) === JSON.stringify(ciRunsForSingleWeek['week']) }); runData.push({ week: ciRunsForSingleWeek['week'], total_runs: ciRunsForSingleWeek['count'], restarted_runs: numberOfRestartedCiRunsForCurrentWeek['count'] }) } return runData

CI Runs vs Restarts Ratio Per Week

javascript
allCiRunsByWeek = await sql`SELECT date_trunc('week', datetime) as Week, COUNT(*) from custom_data_points where tag='ci_run' GROUP BY Week`; restartedCiRunsByWeek = await sql`SELECT date_trunc('week', datetime) as Week, COUNT(*) from custom_data_points where tag='ci_run' AND metadata ILIKE '%is-rerun-true%'GROUP BY Week`; runData = []; for(ciRunsForSingleWeek of allCiRunsByWeek) { numberOfRestartedCiRunsForCurrentWeek = restartedCiRunsByWeek.find((restartedCiRunsForSingleWeek) => { return JSON.stringify(restartedCiRunsForSingleWeek['week']) === JSON.stringify(ciRunsForSingleWeek['week']) }); restartedRunsCount = numberOfRestartedCiRunsForCurrentWeek ? numberOfRestartedCiRunsForCurrentWeek['count'] : 0 runData.push({ week: ciRunsForSingleWeek['week'], restart_ratio: restartedRunsCount / ciRunsForSingleWeek['count'], }) } return runData

Impact Over Time With Filters

javascript
begin_date = {begin_date} end_date = {end_date} team_id = {team_id} repository_id = {repository_id} contributor_parent_id = {contributor_parent_id} time_period_group_by_id = {time_period_group_by_id} should_include_date_filter = begin_date && end_date date_filter = sql`AND authored_at > ${begin_date} AND authored_at <= ${end_date}` should_include_team_filter = !!team_id team_filter = sql`AND contributor_parent_teams.team_id = ${team_id}` team_inner_join = sql`INNER JOIN contributor_parent_teams ON contributor_parents.id=contributor_parent_teams.contributor_parent_id` should_include_repository_filter= !!repository_id repository_filter = sql`AND repositories.id = ${repository_id}` should_include_contributor_parent_filter= !!contributor_parent_id contributor_parent_filter = sql`AND contributor_parents.id = ${contributor_parent_id}` return sql`SELECT date_trunc(${time_period_group_by_id}, subquery.authored_at) as Period, ROUND(SUM(subquery.impact)) as Impact FROM ( SELECT distinct(commits.id), impact, authored_at FROM commits INNER JOIN contributors ON commits.contributor_id=contributors.id INNER JOIN contributor_parents ON contributor_parents.id=contributors.contributor_parent_id ${should_include_team_filter ? team_inner_join : sql``} INNER JOIN repositories ON repositories.id=commits.repository_id where 1=1 ${should_include_date_filter ? date_filter : sql``} ${should_include_team_filter ? team_filter : sql``} ${should_include_repository_filter ? repository_filter : sql``} ${should_include_contributor_parent_filter ? contributor_parent_filter : sql``} ) subquery GROUP BY Period`;

Impact List With Filters

javascript
begin_date = {begin_date} end_date = {end_date} team_id = {team_id} repository_id = {repository_id} contributor_parent_id = {contributor_parent_id} time_period_group_by_id = {time_period_group_by_id} should_include_date_filter = begin_date && end_date date_filter = sql`AND authored_at > ${begin_date} AND authored_at <= ${end_date}` should_include_team_filter = !!team_id team_filter = sql`AND contributor_parent_teams.team_id = ${team_id}` team_inner_join = sql`INNER JOIN contributor_parent_teams ON contributor_parent_teams.contributor_parent_id=contributor_parents.id` should_include_repository_filter= !!repository_id repository_filter = sql`AND repositories.id = ${repository_id}` should_include_contributor_parent_filter= !!contributor_parent_id contributor_parent_filter = sql`AND contributor_parents.id = ${contributor_parent_id}` return sql` SELECT distinct(commits.id), impact, authored_at FROM commits INNER JOIN contributors ON commits.contributor_id=contributors.id INNER JOIN contributor_parents ON contributor_parents.id=contributors.contributor_parent_id INNER JOIN repositories ON repositories.id=commits.repository_id ${should_include_team_filter ? team_inner_join : sql``} where 1=1 AND impact IS NOT NULL ${should_include_date_filter ? date_filter : sql``} ${should_include_team_filter ? team_filter : sql``} ${should_include_repository_filter ? repository_filter : sql``} ${should_include_contributor_parent_filter ? contributor_parent_filter : sql``} ORDER BY impact DESC `;