LandingPage Pipeline

Last updated by Biao Guo on 3/18/2022 7:38:32 AM This content is over 784 days old.

Backgroup

So far, the data sources of landing pages come from many different places. In order to unify data, multiple different data sources are automatically integrated into the msaidataplatformmetrics SQL Server database through pipelines, including SLA, P95, counting Issues, privacy/security completion, EE, COGS, etc

Configure details of pipeline

The pipeline contains five copies of data. Each replicated data is the process of integrating the previous data source into the landing page metrics table of the msaidataplatformmetrics SQL Server database. Set the source and receiver in copy data, and then match the columns in the map

Copy SQL_landing_SLA to AzureSQL

Source

auriga/Sentry

Sink

MSAIDataPlatformMetrics

Query

SELECT 
	MetricName,
	convert(DATETIME,CONCAT(YM,'-01')) AS 'Date',
	CAST((Number*0.1/SLA*1000) as DECIMAL(6,2)) AS 'VALUE'
FROM (SELECT A.MetricName,A.YM,B.Number,A.SLA
FROM (
	SELECT 
		'(%) Availability of the Daily Session Data within SLA 48h' AS MetricName,
		CONVERT(varchar(7),JobDate,120) AS YM,
		count([Generation SLA]) AS SLA
	FROM Landing
	GROUP BY CONVERT(varchar(7),JobDate,120)) AS A
JOIN (
	SELECT 
		CONVERT(varchar(7),JobDate,120) YM,
		COUNT(1) Number
	FROM Landing WHERE GenerationDuration <= 48 
	GROUP BY CONVERT(varchar(7),JobDate,120)) AS B
ON A.YM=B.YM) AS C

Pre-copy script

DELETE FROM LandingPageMetrics 
WHERE MetricName = '(%) Availability of the Daily Session Data within SLA 48h'

Copy SQL_IncidentsData to AzureSQL

Source

auriga/Sentry

Sink

MSAIDataPlatformMetrics

Query

SELECT 
	'(#) Count of the Customer Impacted Issues' AS MetricName,
	CONVERT(DATETIME,CONCAT(CONVERT(varchar(7),CreateTime,120),'-01')) AS 'Date',
	CAST(COUNT(IncidentIds) AS decimal(6,2)) AS 'VALUE'
FROM [dbo].[IncidentsData]
GROUP BY CONVERT(DATETIME,CONCAT(CONVERT(varchar(7),CreateTime,120),'-01'))

Pre-copy script

DELETE FROM LandingPageMetrics 
WHERE MetricName = '(#) Count of the Customer Impacted Issues' 
AND Date != '2021-11-01'

Copy SQL_landing_P95 to AzureSQL

Source

auriga/Sentry

Sink

MSAIDataPlatformMetrics

Query

SELECT DISTINCT
	'(Hour) P95 of the Daily Session Data delay' AS MetricName,
	YM AS 'Date',
	CAST(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY GenerationDuration) OVER (PARTITION BY YM) AS decimal(6,2)) AS 'Value'
FROM(
	SELECT
		CONVERT(DATETIME,CONCAT(FORMAT(JobDate,'yyyy-MM'),'-01')) AS YM,
		GenerationDuration
	FROM [dbo].[Landing]) AS A

Pre-copy script

DELETE FROM LandingPageMetrics 
WHERE MetricName = '(Hour) P95 of the Daily Session Data delay'

Copy Azure_Kusto Compliance to AzureSQL

Source

AzureDevOps1ESKusto

Sink

MSAIDataPlatformMetrics

Query

let o365OrgName = "o365trustcompliance";
let trustProjectId = "5f3e2371-d84c-4941-bc5f-862dc76529d0";
let msaiudpUserStories = dynamic([320427, 320438]);
let areaPaths = dynamic(["Trust\\Security", "Trust\\Privacy"]);
let parentUserStories = materialize(cluster("1es.kusto.windows.net").database("AzureDevOps").WorkItem
| where OrganizationName =~ o365OrgName and WorkItemType =~ "User Story"
| where WorkItemId in (msaiudpUserStories)
| project Title, WorkItemId, WorkItemType);
// Fetch the 1CS tasks detail base on the Parent User Story
let workItems = materialize(cluster("1es.kusto.windows.net").database("AzureDevOps").WorkItemLink
| where SourceId in (msaiudpUserStories) and SourceProjectId =~ trustProjectId
| project SourceId, SourceProjectId, TargetId, TargetProjectId
| join kind = inner
(cluster("1es.kusto.windows.net").database("AzureDevOps").WorkItem | where OrganizationName =~ o365OrgName)
on $left.TargetId == $right.WorkItemId
| extend ParentItemId = SourceId
| project TaskId=WorkItemId, WorkItemType, Title, CreatedByDisplayName, AssignedToUniqueName, State, ParentItemId, IterationPath, OrganizationName, TeamProject, Tags, AreaPath
| join kind = leftouter parentUserStories on $left.ParentItemId == $right.WorkItemId
| extend ParentTitle = Title1, ParentItemType = WorkItemType1
| project WorkItemType, TaskId, Title, CreatedByDisplayName, AssignedToUniqueName, State, ParentItemId, ParentItemType, ParentTitle, IterationPath, OrganizationName, TeamProject, Tags, AreaPath);
// Count the task total for each Area
let 1csTaskCount = materialize(workItems
| summarize TaskTotal=count() by AreaPath);
// Filter out the work item id set which its latest state is closed
let closedWorkItemIds = materialize(workItems
| where State =~ "Closed"
| summarize make_set(TaskId));
// Check the work item history revision to get the accurate completion time
let workItemsWithCompletionTime = materialize(cluster("1es.kusto.windows.net").database("AzureDevOps").WorkItemRevision
| where OrganizationName =~ o365OrgName and tostring(WorkItemId) in~ (closedWorkItemIds) // Only fetch the completion time for the current closed work items
| order by WorkItemId, ChangedDate asc // Sort the work item state with Change Date asc
| extend Rank = row_number(1, prev(WorkItemId) != WorkItemId or prev(State) != State) // Find out the first record for the same state
| where State == "Closed" and Rank == 1 // Filter out the close action record
| order by WorkItemId, ChangedDate desc // Select the latest close operation record
| extend CloseRank = row_number(1, prev(WorkItemId) != WorkItemId) // Filter the close action item for each work item
| where CloseRank == 1
| extend TaskCompletionMonth=startofmonth(ChangedDate) // Compute the Close Month
| project WorkItemId, TaskCompletionMonth, TaskCompletionTime=ChangedDate
);
let months = materialize(
range index from -1 to 6 step 1
| extend Month = datetime_add("month", -index, startofmonth(datetime(now))), Area=(areaPaths)
| mv-expand Area
| project Area=tostring(Area), Month
| order by Area, Month asc);
let 1csProcess = materialize(workItems
| join kind=leftouter workItemsWithCompletionTime on $left.TaskId == $right.WorkItemId
| project-away WorkItemId
| where isnotempty(TaskCompletionTime)
| order by AreaPath, TaskCompletionTime asc
| extend CompletedTaskCount = row_number(1, prev(AreaPath)!=AreaPath)
| order by TaskCompletionTime desc
| extend CompletionRankInMonth = row_number(1, prev(TaskCompletionMonth) != TaskCompletionMonth)
| where CompletionRankInMonth == 1
| project TaskCompletionMonth, CompletedTaskCount, AreaPath
| order by TaskCompletionMonth asc
| join kind=inner 1csTaskCount on AreaPath
| project-away *1
| extend CompletionRateByMonth = CompletedTaskCount * 100.0 / TaskTotal);
let 1csReviewLastState = materialize(1csProcess
| order by AreaPath, TaskCompletionMonth desc
| extend Rank = row_number(1, prev(AreaPath)!=AreaPath)
| where Rank == 1
| project LastMonth=TaskCompletionMonth, AreaPath, CompletedTaskCount, CompletionRateByMonth);
let 1csReviewStartState = materialize(1csProcess
| order by AreaPath, TaskCompletionMonth asc
| extend Rank = row_number(1, prev(AreaPath)!=AreaPath)
| where Rank == 1
| project StartMonth=TaskCompletionMonth, AreaPath);
let ComputeTaskCount = (startMonth:datetime, endMonth:datetime, currentMonth:datetime, currentCount:int, lastCount:int){
 let ret = iff(currentMonth < startMonth, 0,
           iff(currentMonth > endMonth, lastCount, currentCount));
 ret;
};
let ComputeTaskCompletionRate = (startMonth:datetime, endMonth:datetime, currentMonth:datetime, currentRate:double, lastRate:double){
 let ret = iff(currentMonth < startMonth, double(0),
           iff(currentMonth > endMonth, lastRate, currentRate));
 ret;
};
1csProcess
| join kind=rightouter months on $left.AreaPath == $right.Area and $left.TaskCompletionMonth == $right.Month
| join kind=leftouter 1csReviewLastState on $left.Area == $right.AreaPath
| join kind=leftouter 1csReviewStartState on $left.Area == $right.AreaPath
| project-rename LastCompletedTaskCount = CompletedTaskCount1, LastCompletionRate = CompletionRateByMonth1
| order by Area, Month asc
| extend MetricsName = iff(Area contains "Privacy","(%) Compliance 1CS Privacy Review Completion Rate","(%) Compliance 1CS Security Review Completion Rate")
| project MetricsName,Month,CompletionRateByMonth=ComputeTaskCompletionRate(StartMonth, LastMonth, Month,CompletionRateByMonth,LastCompletionRate)

Pre-copy script

DELETE FROM LandingPageMetrics WHERE MetricName 
IN ('(%) Compliance 1CS Security Review Completion Rate',
'(%) Compliance 1CS Privacy Review Completion Rate')

Copy Azure_Kusto EE to AzureSQL

Source

AzureDevOps1ESKusto

Sink

MSAIDataPlatformMetrics

Query

let msasgOrgName = "msasg";
let aurigaProjectId = "6f29a3cb-79a6-41bd-819c-abd019b2243d";
let pullRequestStatus = dynamic(["completed", "active"]);
alias database["ado"] = cluster("1es.kusto.windows.net").database("AzureDevOps");
let msaiDataPlatformPullRequest = materialize(database("ado").PullRequest
| where OrganizationName =~ "msasg" and ProjectId =~ aurigaProjectId and RepositoryId =~ "4fbe610b-3666-46e9-beb2-8cb9c1fa6490"
| extend PullRequestCompletionDurationMinutes = iff(isnotempty(ClosedDate),datetime_diff('minute',ClosedDate,CreationDate), long(null))
| extend PullRequestCompletionDuration = PullRequestCompletionDurationMinutes/1440.00
| where Status in~ (pullRequestStatus)
);
let msaiDataPlatformPullRequestIds = materialize(msaiDataPlatformPullRequest
| summarize make_set(PullRequestId));
let pullRequestIterationNumber = materialize(database("ado").PullRequestIteration
| where OrganizationName =~ msasgOrgName and ProjectId =~ aurigaProjectId and RepositoryId =~ "4fbe610b-3666-46e9-beb2-8cb9c1fa6490"
| summarize IterationCount = count() by PullRequestId);
let earliestComments = materialize(database("ado").PullRequestThreadComment
| where OrganizationName =~ "msasg" and ProjectId =~ aurigaProjectId and RepositoryId =~ "4fbe610b-3666-46e9-beb2-8cb9c1fa6490"
| where PullRequestId in (msaiDataPlatformPullRequestIds)
| where isnotempty(AuthorUniqueName)
| join kind = inner (database("ado").PullRequest | project RepositoryId, PullRequestId, ProjectId, CreatedByUniqueName) on ProjectId, RepositoryId, PullRequestId
| where AuthorUniqueName != CreatedByUniqueName // We just fetch the comment from who is not the PullRequest author
| sort by PullRequestId asc, CommentType desc, LastUpdatedDate asc
| extend Rank = row_number(1, prev(PullRequestId) != PullRequestId)
| where Rank == 1);
msaiDataPlatformPullRequest
| join kind=inner pullRequestIterationNumber on PullRequestId // Join the Iteration number
| join kind=leftouter earliestComments on PullRequestId, RepositoryId
| extend ReviewStartDuration = LastUpdatedDate - CreationDate // Compute the Duration from Create to Start review
| extend ReviewDuration = iff(isnotempty(ClosedDate), iff(isnotempty(LastUpdatedDate) and ClosedDate > LastUpdatedDate, ClosedDate - LastUpdatedDate, timespan(null)), timespan(null))
| extend Month = startofmonth(CreationDate)
| where isnotempty(CommentType) and isnotempty(ClosedDate)
| summarize AvgPullRequestCompletionDuration =round(avg(PullRequestCompletionDuration),2) by Month
| project MetricsName = "(#) Days of PullRequest Completion Duration (EE)",Month,AvgPullRequestCompletionDuration

Pre-copy script

DELETE FROM LandingPageMetrics 
WHERE MetricName = '(#) Days of PullRequest Completion Duration (EE)'

Setup Trigger

Set the refresh frequency according to the time stamp displayed on the dashboard, and consider the load of ADF, so set the refresh interval as the end of the month