forked from liushilongbuaa/sonctsc
-
Notifications
You must be signed in to change notification settings - Fork 0
/
kusto.kql
311 lines (289 loc) · 14 KB
/
kusto.kql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
.drop table author
.drop table issues ingestion csv mapping "mapping1"
.clear table author data
.create table prs(
additions: string,
author: string,
baseRef_name: string,
merged_at: datetime,
number: string,
title_: string,
repo: string,
test_case: string
)
.create table prs ingestion json mapping "mapping1"
```
[
{"Properties": { "Path": "$.additions" }, "column": "additions", "datatype": "string"},
{"Properties": { "Path": "$.author"}, "column": "author", "datatype": "string"},
{"Properties": { "Path": "$.baseRefName"}, "column": "baseRef_name", "datatype": "string"},
{"Properties": { "Path": "$.mergedAt"}, "column": "merged_at", "datatype": "datetime"},
{"Properties": { "Path": "$.number"}, "column": "number", "datatype": "string"},
{"Properties": { "Path": "$.title"}, "column": "title_", "datatype": "string" },
{"Properties": { "Path": "$.repo"}, "column": "repo", "datatype": "string" },
{"Properties": { "Path": "$.testCase"}, "column": "test_case", "datatype": "string" },
]
```
.create table highLevelDisign(
repo: string,
file: string,
author: string,
created_at: datetime
)
.create table highLevelDisign ingestion csv mapping "mapping1"
```
[
{"Column": "repo", "Properties": {"Ordinal": "0"}},
{"Column": "file", "Properties": {"Ordinal": "1"}},
{"Column": "author", "Properties": {"Ordinal": "2"}},
{"Column": "created_at", "Properties": {"Ordinal": "3"}}
]
```
.create table author(
id: string,
name: string,
organization: string
)
.create table author ingestion csv mapping "mapping1"
```
[
{"Column": "id", "Properties": {"Ordinal": "0"}},
{"Column": "name", "Properties": {"Ordinal": "1"}},
{"Column": "organization", "Properties": {"Ordinal": "2"}}
]
```
.create table issues(
author: string,
created_at: datetime,
labels: string,
number: string,
state: string
)
.create table issues ingestion json mapping "mapping1"
```
[
{"Properties": { "Path": "$.author" }, "column": "author", "datatype": "string"},
{"Properties": { "Path": "$.createdAt"}, "column": "created_at", "datatype": "datetime"},
{"Properties": { "Path": "$.labels"}, "column": "labels", "datatype": "string"},
{"Properties": { "Path": "$.number"}, "column": "number", "datatype": "string"},
{"Properties": { "Path": "$.state"}, "column": "state", "datatype": "string"}
]
```
.create table testPlanHLD(
repo: string,
file: string,
author: string,
created_at: datetime
)
.create table testPlanHLD ingestion csv mapping "mapping1"
```
[
{"Column": "repo", "Properties": {"Ordinal": "0"}},
{"Column": "file", "Properties": {"Ordinal": "1"}},
{"Column": "author", "Properties": {"Ordinal": "2"}},
{"Column": "created_at", "Properties": {"Ordinal": "3"}}
]
```
.create table reviews(
number: string,
repo: string,
comment_at: datetime,
comment_author: string,
comment_body: string,
review_at: datetime,
review_author: string,
latestReview_at: datetime,
latestReview_author: string,
latestReview_state: string
)
.create table reviews ingestion json mapping "mapping1"
```
[
{"Properties": { "Path": "$.number" }, "column": "number", "datatype": "string"},
{"Properties": { "Path": "$.repo"}, "column": "repo", "datatype": "string"},
{"Properties": { "Path": "$.comment_at"}, "column": "comment_at", "datatype": "datetime"},
{"Properties": { "Path": "$.comment_author"}, "column": "comment_author", "datatype": "string"},
{"Properties": { "Path": "$.comment_body"}, "column": "comment_body", "datatype": "string"},
{"Properties": { "Path": "$.review_at"}, "column": "review_at", "datatype": "datetime" },
{"Properties": { "Path": "$.review_author"}, "column": "review_author", "datatype": "string" },
{"Properties": { "Path": "$.latestReview_at"}, "column": "latestReview_at", "datatype": "datetime" },
{"Properties": { "Path": "$.latestReview_author"},"column": "latestReview_author","datatype": "string" },
{"Properties": { "Path": "$.latestReview_state"}, "column": "latestReview_state", "datatype": "string" }
]
```
// author input microsoft info
author
| join kind=leftouter cluster('1es.kusto.windows.net').database('GitHub').githubemployeelink on $left.id==$right.githubUserName
| extend organization=iff(organization contains "null",iff(githubUserName != '', 'Microsoft', "null"),organization)
| extend organization=replace_string(organization,'@','')
| extend organization=iff(organization contains "microsoft", 'Microsoft',organization)
| extend organization=iff(organization contains "nvidia", 'Nvidia',organization)
| extend organization=iff(organization contains "dell", 'Dell',organization)
| extend organization=iff(organization contains "azure", 'Microsoft',organization)
| extend organization=iff(organization contains "cisco", 'Cisco',organization)
| extend organization=iff(organization contains "broadcom", 'Broadcom',organization)
| extend organization=iff(organization contains "arista", 'Broadcom',organization)
| extend organization=iff(organization contains "intel", 'Intel',organization)
| extend organization=iff(organization contains "barefoot", 'Barefoot',organization)
| extend organization=iff(organization contains "centec", 'Centec',organization)
| extend organization=iff(organization contains "cavium", 'Cavium',organization)
| extend organization=iff(organization contains "celestica", 'Celestica',organization)
| extend organization=iff(organization contains "edgecore", 'Edgecore',organization)
| extend organization=iff(organization contains "marvell", 'Marvell',organization)
| project github_id=id,github_name=name,organization;
// author map
let authorMap=author;
// 1. Merged HLD [1] Count
let hld_=
highLevelDisign
| extend year=getyear(created_at)
| extend score=50
| extend metric='hld'
| project author,year,score,metric;
// 2. Merged PR [2] Count (S/M/L)
let prs_ =
prs
| where test_case !contains "yes"
| extend year=getyear(merged_at)
| extend metric='pr'
| extend score=iff( (toint(additions)>300) , 50 , iff( (toint(additions)>50) , 20 , 10))
| project author,year,score,metric;
// 3. PR Review Count (S/M/L)
let reviews_=
reviews
| extend year=getyear(comment_at)
| extend year=iff(isempty(year), getyear(review_at),year)
| extend year=iff(isempty(year), getyear(latestReview_at),year)
| extend author=comment_author
| extend author=iff(isempty(author), review_author, author)
| extend author=iff(isempty(author), latestReview_author, author)
| project number,repo,year,author
| join kind=leftouter prs on $left.repo==$right.repo, $left.number==$right.number
| where test_case !contains "yes"
| project number,repo,year,author,pr_author=author1
| where author != pr_author
| extend tag=strcat(repo,number)
| partition hint.strategy=native by tag(
summarize count() by repo,number,year,author
)
| extend metric='pr_review'
| extend score=iff(count_ <= 2, 1, iff(count_ <= 5, 2, 5))
| project author,year,score,metric;
// 4. PR cherry-picking [3] Count ---- TODO
// 5. Documentations (Release Notes/Meeting Minutes) ---- DONE in item 1.
// 6. New ASIC [4] Introduction Count ---- TODO
// 7. Issues Opened Count
let issues_=
issues
| extend score=5
| extend year=getyear(created_at)
| extend metric='issue_open'
| project author,year,score,metric;
// 8. Issues Triaged/Fixed Count
let issues_triaged_=
issues
| extend organization=iff(labels contains "msft", "Microsoft", author)
| extend organization=iff(labels contains "brcm", "Broadcom", author)
| extend organization=iff(labels contains "nvidia", "Nvidia", author)
| extend organization=iff(labels contains "dell", "Dell", author)
| extend organization=iff(labels contains "accton", "Accton", author)
| extend organization=iff(labels contains "nokia", "Nokia", author)
| extend organization=iff(labels contains "mellanox", "Mellanox", author)
| where organization != ''
| extend score=10
| extend year=getyear(created_at)
| extend metric='issue_triaged'
| project author,year,score,metric;
// 9. Merged SONiC MGMT TEST Plan HLD [1] Count
let testplanhld_=
testPlanHLD
| extend score=100
| extend year=getyear(created_at)
| extend metric='testplan_hld'
| project author,year,score,metric;
// 10. Merged Test cases [2] (S/M/L)
let testPRs_=
prs
| where test_case contains "yes"
| extend score=iff( (toint(additions)>300) , 100 , iff( (toint(additions)>50) , 40 , 20))
| extend year=getyear(merged_at)
| extend metric='test_pr'
| project author,year,score,metric;
// 11. TEST PR review count (S/M/L)
let reviews_test_=
reviews
| extend year=getyear(comment_at)
| extend year=iff(isempty(year), getyear(review_at),year)
| extend year=iff(isempty(year), getyear(latestReview_at),year)
| extend author=comment_author
| extend author=iff(isempty(author), review_author, author)
| extend author=iff(isempty(author), latestReview_author, author)
| project number,repo,year,author
| join kind=leftouter prs on $left.repo==$right.repo, $left.number==$right.number
| where test_case contains "yes"
| project number,repo,year,author,pr_author=author1
| where author != pr_author
| extend tag=strcat(repo,number)
| partition hint.strategy=native by tag(
summarize count() by repo,number,year,author
)
| extend metric='testpr_review'
| extend score=iff(count_ <= 2, 2, iff(count_ <= 5, 4, 10))
| project author,year,score,metric;
// summarize by author.
union hld_, prs_, reviews_, issues_, issues_triaged_, testplanhld_, testPRs_, reviews_test_
| join kind=leftouter authorMap on $left.author==$right.id
| where author != 'linux-foundation-easycla'
| where author != 'lgtm-com'
| where author != 'mssonicbld'
| where author != 'azure-pipelines'
| where author != 'svc-acs'
| extend organization=iff(isempty(organization) and author contains "Nvidia", 'Nvidia', organization)
| extend organization=iff(isempty(organization) and author contains "Dell", 'Dell', organization)
| extend organization=iff(isempty(organization) and author contains "Microsoft", 'Microsoft', organization)
| extend organization=iff(isempty(organization) and author contains "Cisco", 'Cisco', organization)
| extend organization=iff(isempty(organization) and author contains "Broadcom", 'Broadcom', organization)
| extend organization=iff(isempty(organization) and author contains "BRCM", 'Broadcom', organization)
| extend organization=iff(isempty(organization) and author contains "Arista", 'Arista', organization)
| extend organization=iff(isempty(organization) and author contains "Intel", 'Intel', organization)
| extend organization=iff(isempty(organization) and author contains "Barefoot", 'Barefoot', organization)
| extend organization=iff(isempty(organization) and author contains "Centec", 'Centec', organization)
| extend organization=iff(isempty(organization) and author contains "Cavium", 'Cavium', organization)
| extend organization=iff(isempty(organization) and author contains "Celestica", 'Celestica', organization)
| extend organization=iff(isempty(organization) and author contains "Edgecore", 'Edgecore', organization)
| extend organization=iff(isempty(organization) and author contains "Marvell", 'Marvell', organization)
| extend organization=iff(isempty(organization) and author contains "Mellanox", 'Mellanox', organization)
| extend organization=iff(isempty(organization) and author contains "Alibaba", 'Alibaba', organization)
| extend organization=iff(isempty(organization) and author contains "Uber", 'Uber', organization)
| extend organization=iff(isempty(organization) and author contains "Nokia", 'Nokia', organization)
| extend score_i=iff( year == 2022, 0.3*score, iff(year == 2021, 0.25*score, iff(year == 2020, 0.2*score,iff(year == 2019, 0.15*score,iff(year == 2018, 0.1*score,0.0)))))
| project author, organization, year, score, metric, score_i
// summarize by organization
| extend organization=iff(organization contains "Nvidia", 'Nvidia', organization)
| extend organization=iff(organization contains "Dell", 'Dell', organization)
| extend organization=iff(organization contains "Microsoft", 'Microsoft', organization)
| extend organization=iff(organization contains "Cisco", 'Cisco', organization)
| extend organization=iff(organization contains "Broadcom", 'Broadcom', organization)
| extend organization=iff(organization contains "BRCM", 'Broadcom', organization)
| extend organization=iff(organization contains "Arista", 'Arista', organization)
| extend organization=iff(organization contains "Intel", 'Intel', organization)
| extend organization=iff(organization contains "Barefoot", 'Barefoot', organization)
| extend organization=iff(organization contains "Centec", 'Centec', organization)
| extend organization=iff(organization contains "Cavium", 'Cavium', organization)
| extend organization=iff(organization contains "Celestica", 'Celestica', organization)
| extend organization=iff(organization contains "Edgecore", 'Edgecore', organization)
| extend organization=iff(organization contains "Marvell", 'Marvell', organization)
| extend organization=iff(organization contains "Mellanox", 'Mellanox', organization)
| extend organization=iff(organization contains "Alibaba", 'Alibaba', organization)
| extend organization=iff(organization contains "Uber", 'Uber', organization)
| extend organization=iff(organization contains "Nokia", 'Nokia', organization)
| summarize sum(score_i) by organization
| sort by sum_score_i desc
// query review comment potential way.
database('internal').GitHubEvents
| sort by Timestamp asc
| where Name contains "pull_re"
| extend payload_json = parse_json(Payload)
| project Name,Action, payload_json.pull_request.html_url, payload_json
| where payload_json_pull_request_html_url startswith "https://github.com/sonic-net/sonic-buildimage/pull/14632"
| where payload_json contains "why do we need this"