Repository navigation
Expand file tree
/
Copy pathSecurityOverviewReport.workbook
More file actions
584 lines (584 loc) · 33.4 KB
/
Copy pathSecurityOverviewReport.workbook
File metadata and controls
584 lines (584 loc) · 33.4 KB
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
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
{
"version": "Notebook/1.0",
"items": [
{
"type": 1,
"content": {
"json": "# AAD-Domain Services: Security Overview Report #"
},
"name": "text - 0"
},
{
"type": 11,
"content": {
"version": "LinkItem/1.0",
"style": "nav",
"links": [
{
"cellValue": "https://aka.ms/aadds-secoverviewwb",
"linkTarget": "Url",
"linkLabel": "how to",
"preText": "Learn",
"postText": "use this workbook or customize your own",
"style": "link"
}
]
},
"name": "links - 0"
},
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"query": "",
"crossComponentResources": [
"value::selected"
],
"parameters": [
{
"id": "90303e6c-f16c-4afb-bfc2-642234cbaac8",
"version": "KqlParameterItem/1.0",
"name": "DefaultSubscription_Internal",
"type": 1,
"isRequired": true,
"query": "where type =~ 'microsoft.operationalinsights/workspaces'\r\n| take 1\r\n| project subscriptionId",
"crossComponentResources": [
"value::selected"
],
"isHiddenWhenLocked": true,
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources"
},
{
"id": "735f8f15-3031-4f1c-9e10-7dc7c7e32bc4",
"version": "KqlParameterItem/1.0",
"name": "Subscription",
"type": 6,
"isRequired": true,
"query": "summarize by subscriptionId\r\n| project value = strcat(\"/subscriptions/\", subscriptionId), label = subscriptionId, selected = iff(subscriptionId =~ '{DefaultSubscription_Internal}', true, false)",
"crossComponentResources": [
"value::selected"
],
"typeSettings": {
"additionalResourceOptions": []
},
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources"
},
{
"id": "ee1d4330-b4ef-46b7-b555-d15e2ccd0ced",
"version": "KqlParameterItem/1.0",
"name": "Workspace",
"type": 5,
"isRequired": true,
"query": "where type =~ 'microsoft.operationalinsights/workspaces'\n| project id",
"crossComponentResources": [
"{Subscription}"
],
"typeSettings": {
"resourceTypeFilter": {
"microsoft.operationalinsights/workspaces": true
},
"additionalResourceOptions": []
},
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources"
},
{
"id": "3dbe3381-e5e3-4e17-bbbf-2d715a70e95b",
"version": "KqlParameterItem/1.0",
"name": "TimeRange",
"label": "Time range",
"type": 4,
"isRequired": true,
"typeSettings": {
"selectableValues": [
{
"durationMs": 3600000
},
{
"durationMs": 43200000
},
{
"durationMs": 86400000
},
{
"durationMs": 259200000
},
{
"durationMs": 604800000
},
{
"durationMs": 1209600000
},
{
"durationMs": 2419200000
},
{
"durationMs": 2592000000
},
{
"durationMs": 5184000000
},
{
"durationMs": 7776000000
}
],
"allowCustom": true
},
"timeContext": {
"durationMs": 0
}
},
{
"id": "c2315535-f2b3-475c-ba7f-dcb883005e86",
"version": "KqlParameterItem/1.0",
"name": "TileView",
"label": "Tile view",
"type": 2,
"description": "What do you want the tiles to show?",
"isRequired": true,
"value": "A",
"typeSettings": {
"additionalResourceOptions": []
},
"jsonData": "[\r\n { \"label\":\"Percentages\", \"value\":\"A\" },\r\n { \"label\":\"Counts\", \"value\":\"B\" }\r\n]"
},
{
"id": "3cfa8969-9796-4026-9786-4c6bb94d25c0",
"version": "KqlParameterItem/1.0",
"name": "TimeView",
"label": "Chart view",
"type": 2,
"description": "Do you want the chart to highlight anomalies?",
"isRequired": true,
"value": "1",
"typeSettings": {
"additionalResourceOptions": []
},
"jsonData": "[\r\n { \"label\":\"Highlight anomalies\", \"value\":\"1\" },\r\n { \"label\":\"Don't highlight anomalies\", \"value\":\"2\" }\r\n]"
}
],
"style": "pills",
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources"
},
"name": "parameters - 0"
},
{
"type": 1,
"content": {
"json": "___"
},
"name": "text - 1"
},
{
"type": 1,
"content": {
"json": "## Sign-in summary ##"
},
"name": "text - 2"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// Q0A: Generate tiles for sign-in attempts with result percentages\r\n// Define lookup table for auth attempt result types\r\nlet ErrorCodes = datatable(ErrorCode:string, ErrorType:string)\r\n [ \"0xC0000064\", \"Bad Username\"\r\n , \"0xC000006A\", \"Bad Password\"\r\n , \"0xC000006D\", \"Generic Logon Failure\"\r\n , \"0xC000006F\", \"Outside Authorized Hours\"\r\n , \"0xC0000070\", \"Unauthorized Workstation\"\r\n , \"0xC0000071\", \"Expired Password\"\r\n , \"0xC0000072\", \"Account Disabled\"\r\n , \"0xC0000193\", \"Account Expired\"\r\n , \"0xC0000224\", \"Successful\"\r\n , \"0xC0000234\", \"Account Locked Out\"\r\n , \"0xC0000371\", \"The local account store does not contain secret material for the specified account.\"\r\n , \"0x0\", \"Successful\"\r\n ];\r\n// Get counts for each result type\r\nlet SourceTable = materialize(\r\n AADDomainServicesAccountLogon\r\n | where TimeGenerated {TimeRange:value}\r\n | project Event_ID = extract(\".+/(.+[0-9])\",1,tostring(OperationName))\r\n , ErrorCode = extract(\"Error Code:\\t(.+[0-9A-Za-z])\",1,tostring(ResultDescription))\r\n | where Event_ID == \"4776\"\r\n | lookup kind=leftouter ErrorCodes on ErrorCode\r\n | summarize count() by ErrorType\r\n | extend flag = 1\r\n);\r\n// Get total count for all auth attempts\r\nlet TotalCount = materialize(\r\n SourceTable\r\n | summarize sum(count_)\r\n | extend flag = 1\r\n);\r\n// Combine tables and calculate percentages\r\nlet percentTable = materialize(\r\n SourceTable\r\n | lookup kind=leftouter TotalCount on flag\r\n | project ErrorType\r\n , value = todouble(count_)\r\n , total = todouble(sum_count_)\r\n | project ErrorType\r\n , percentage = (value / total) * 100\r\n);\r\n// Make and merge a table for all attempts at 100%\r\nlet totalPercent = datatable(ErrorType:string, percentage:real) [ 'All',100 ];\r\nunion withsource=TableName percentTable, totalPercent\r\n| project ErrorType\r\n , percentage\r\n| sort by percentage",
"size": 3,
"exportFieldName": "ErrorType",
"exportParameterName": "AuthResultA",
"exportDefaultValue": "All",
"title": "Sign-in results",
"noDataMessage": "No sign-in attempts found",
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{Workspace}"
],
"visualization": "tiles",
"tileSettings": {
"titleContent": {
"columnMatch": "ErrorType",
"formatter": 1,
"formatOptions": {
"showIcon": true
}
},
"leftContent": {
"columnMatch": "percentage",
"formatter": 12,
"formatOptions": {
"palette": "auto",
"showIcon": true
},
"numberFormat": {
"unit": 1,
"options": {
"style": "decimal",
"maximumFractionDigits": 2,
"maximumSignificantDigits": 3
}
}
},
"showBorder": true,
"sortCriteriaField": "percentage",
"sortOrderField": 2
}
},
"conditionalVisibility": {
"parameterName": "TileView",
"comparison": "isEqualTo",
"value": "A"
},
"name": "query - 0A"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// Q0B: Generate tiles for sign-in attempts with result counts\r\n// Define lookup table for auth attempt result types\r\nlet ErrorCodes = datatable(ErrorCode:string, ErrorType:string)\r\n [ \"0xC0000064\", \"Bad Username\"\r\n , \"0xC000006A\", \"Bad Password\"\r\n , \"0xC000006D\", \"Generic Logon Failure\"\r\n , \"0xC000006F\", \"Outside Authorized Hours\"\r\n , \"0xC0000070\", \"Unauthorized Workstation\"\r\n , \"0xC0000071\", \"Expired Password\"\r\n , \"0xC0000072\", \"Account Disabled\"\r\n , \"0xC0000193\", \"Account Expired\"\r\n , \"0xC0000224\", \"Successful\"\r\n , \"0xC0000234\", \"Account Locked Out\"\r\n , \"0xC0000371\", \"The local account store does not contain secret material for the specified account.\"\r\n , \"0x0\", \"Successful\"\r\n ];\r\n// Get counts for each result type\r\nlet SourceTable = materialize(\r\n AADDomainServicesAccountLogon\r\n | where TimeGenerated {TimeRange:value}\r\n | project Event_ID = extract(\".+/(.+[0-9])\",1,tostring(OperationName))\r\n , ErrorCode = extract(\"Error Code:\\t(.+[0-9A-Za-z])\",1,tostring(ResultDescription))\r\n | where Event_ID == \"4776\"\r\n | lookup kind=leftouter ErrorCodes on ErrorCode\r\n | summarize count() by ErrorType\r\n);\r\n// Get total count for all auth attempts\r\nlet TotalCount = materialize(\r\n SourceTable\r\n | summarize count_ = sum(count_)\r\n | extend ErrorType = \"All\"\r\n);\r\n// Combine the tables\r\nunion withsource=TableName SourceTable, TotalCount\r\n| project ErrorType\r\n , count_\r\n| sort by count_",
"size": 3,
"exportFieldName": "ErrorType",
"exportParameterName": "AuthResultB",
"exportDefaultValue": "All",
"title": "Sign-in results",
"noDataMessage": "No sign-in attempts found",
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{Workspace}"
],
"visualization": "tiles",
"tileSettings": {
"titleContent": {
"columnMatch": "ErrorType",
"formatter": 1,
"formatOptions": {
"showIcon": true
}
},
"leftContent": {
"columnMatch": "count_",
"formatter": 12,
"formatOptions": {
"palette": "auto",
"showIcon": true
}
},
"showBorder": true,
"sortCriteriaField": "percentage",
"sortOrderField": 2
}
},
"conditionalVisibility": {
"parameterName": "TileView",
"comparison": "isEqualTo",
"value": "B"
},
"name": "query - 0B"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// THIS QUERY HAS TWO VERSIONS - THEY ARE IDENTICAL EXCEPT THEY USE DIFFERENT PARAMETERS FOR AUTH RESULT. THIS IS ...\r\n// ... SO THAT BOTH VERSIONS OF THE TILE OBJECT CAN CONTROL THE TIME CHART\r\n// Q1A: Generate time-chart for sign-in attempts with a selected auth result type (with or without anomaly detection) - IF SHOWING PERCENTAGES\r\n// Define lookup table for auth attempt result types\r\nlet ErrorCodes = datatable(ErrorCode:string, ErrorType:string)\r\n [ \"0xC0000064\", \"Bad Username\"\r\n , \"0xC000006A\", \"Bad Password\"\r\n , \"0xC000006D\", \"Generic Logon Failure\"\r\n , \"0xC000006F\", \"Outside Authorized Hours\"\r\n , \"0xC0000070\", \"Unauthorized Workstation\"\r\n , \"0xC0000071\", \"Expired Password\"\r\n , \"0xC0000072\", \"Account Disabled\"\r\n , \"0xC0000193\", \"Account Expired\"\r\n , \"0xC0000224\", \"Successful\"\r\n , \"0xC0000234\", \"Account Locked Out\"\r\n , \"0xC0000371\", \"The local account store does not contain secret material for the specified account.\"\r\n , \"0x0\", \"Successful\"\r\n ];\r\nAADDomainServicesAccountLogon\r\n// Filter down to the selected username and auth result type\r\n| where TimeGenerated {TimeRange:value}\r\n| project TimeGenerated\r\n , Event_ID = extract(\".+/(.+[0-9])\",1,tostring(OperationName))\r\n , ErrorCode = extract(\"Error Code:\\t(.+[0-9A-Za-z])\",1,tostring(ResultDescription))\r\n| where Event_ID == \"4776\"\r\n| lookup kind=leftouter ErrorCodes on ErrorCode\r\n| extend flag = \"All\" // Defined because boolean checks must begin with a column variable\r\n| where ErrorType == \"{AuthResultA:value}\"\r\n or flag == \"{AuthResultA:value}\"\r\n// Keyword \"step\" refers to the aggregation bin width, or the period length that each data point in the ...\r\n// ... series will represent. For example, if we make a series ~ \"from 10am to 10pm step 1h\", the data ...\r\n// ... gets split into 12 hour-long segments (10-11, 11-12, 12-1, etc), so our series has 12 data points ...\r\n// ... where each one is the number of events that occurred in that hour-long stretch of time. You can ...\r\n// ... make this \"step\" variable any length of time. We recommend using the default {TimeRange:grain} ...\r\n// ... which changes based on how long the time range is, or just 1h for anything less than 30 days \r\n| make-series y = count() on TimeGenerated from {TimeRange:start} to {TimeRange:end} step 1h\r\n// This function calculates the average value for the series and gives each data point in the series a ...\r\n// ... K-Score, which is based on standard deviation. Then, it \"flags\" every point with a K-score which ...\r\n// ... is greater than a positive threshold (or less than the negative of the threshold). The function ...\r\n// ... in the next line returns the average, the K-scores, and the flags as 3 additioanl series\r\n| extend series_decompose_anomalies(y)\r\n| extend series_stats(y)\r\n// This variable lets us turn off anomaly highlighting without making a separate query object\r\n| extend ShowAnomalies = \"1\"\r\n// Since we have a series of flags, and each flag is either +1, -1, or 0 (for anomaly greater than the ...\r\n// ... average, anomaly less than the average, or no anomaly, respectively), and that series lines up to ...\r\n// ... the original series on the time scale, we can make a new series that would visually draw attention ...\r\n// ... to the anomalies in the series. We do this by multiplying the flag series by the original series ...\r\n// ... which produces a new series that sits at zero for normal data but jumps up/down to the extreme ...\r\n// ... values where the series has an anomaly\r\n| project TimeGenerated\r\n , Attempt = y\r\n , Anomaly = iif(ShowAnomalies == {TimeView:value}, series_multiply(series_decompose_anomalies_y_ad_flag, y), series_multiply(y, 0))\r\n// If workbooks adds support for anomaly charts, we can just call \"render anomalychart\" on our original ...\r\n// ... data set without having to deal with flags and multiplication.\r\n| render timechart",
"size": 0,
"title": "Sign-in distribution*",
"noDataMessage": "No sign-in attempts found",
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{Workspace}"
],
"visualization": "timechart"
},
"conditionalVisibility": {
"parameterName": "TileView",
"comparison": "isEqualTo",
"value": "A"
},
"name": "query - 1A"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// THIS QUERY HAS TWO VERSIONS - THEY ARE IDENTICAL EXCEPT THEY USE DIFFERENT PARAMETERS FOR AUTH RESULT. THIS IS ...\r\n// ... SO THAT BOTH VERSIONS OF THE TILE OBJECT CAN CONTROL THE TIME CHART\r\n// Q1A: Generate time-chart for sign-in attempts with a selected auth result type (with or without anomaly detection) - IF SHOWING COUNTS\r\n// Define lookup table for auth attempt result types\r\nlet ErrorCodes = datatable(ErrorCode:string, ErrorType:string)\r\n [ \"0xC0000064\", \"Bad Username\"\r\n , \"0xC000006A\", \"Bad Password\"\r\n , \"0xC000006D\", \"Generic Logon Failure\"\r\n , \"0xC000006F\", \"Outside Authorized Hours\"\r\n , \"0xC0000070\", \"Unauthorized Workstation\"\r\n , \"0xC0000071\", \"Expired Password\"\r\n , \"0xC0000072\", \"Account Disabled\"\r\n , \"0xC0000193\", \"Account Expired\"\r\n , \"0xC0000224\", \"Successful\"\r\n , \"0xC0000234\", \"Account Locked Out\"\r\n , \"0xC0000371\", \"The local account store does not contain secret material for the specified account.\"\r\n , \"0x0\", \"Successful\"\r\n ];\r\nAADDomainServicesAccountLogon\r\n// Filter down to the selected username and auth result type\r\n| where TimeGenerated {TimeRange:value}\r\n| project TimeGenerated\r\n , Event_ID = extract(\".+/(.+[0-9])\",1,tostring(OperationName))\r\n , ErrorCode = extract(\"Error Code:\\t(.+[0-9A-Za-z])\",1,tostring(ResultDescription))\r\n| where Event_ID == \"4776\"\r\n| lookup kind=leftouter ErrorCodes on ErrorCode\r\n| extend flag = \"All\" // Defined because boolean checks must begin with a column variable\r\n| where ErrorType == \"{AuthResultB:value}\"\r\n or flag == \"{AuthResultB:value}\"\r\n// Keyword \"step\" refers to the aggregation bin width, or the period length that each data point in the ...\r\n// ... series will represent. For example, if we make a series ~ \"from 10am to 10pm step 1h\", the data ...\r\n// ... gets split into 12 hour-long segments (10-11, 11-12, 12-1, etc), so our series has 12 data points ...\r\n// ... where each one is the number of events that occurred in that hour-long stretch of time. You can ...\r\n// ... make this \"step\" variable any length of time. We recommend using the default {TimeRange:grain} ...\r\n// ... which changes based on how long the time range is, or just 1h for anything less than 30 days \r\n| make-series y = count() on TimeGenerated from {TimeRange:start} to {TimeRange:end} step 1h\r\n// This function calculates the average value for the series and gives each data point in the series a ...\r\n// ... K-Score, which is based on standard deviation. Then, it \"flags\" every point with a K-score which ...\r\n// ... is greater than a positive threshold (or less than the negative of the threshold). The function ...\r\n// ... in the next line returns the average, the K-scores, and the flags as 3 additioanl series\r\n| extend series_decompose_anomalies(y)\r\n| extend series_stats(y)\r\n// This variable lets us turn off anomaly highlighting without making a separate query object\r\n| extend ShowAnomalies = \"1\"\r\n// Since we have a series of flags, and each flag is either +1, -1, or 0 (for anomaly greater than the ...\r\n// ... average, anomaly less than the average, or no anomaly, respectively), and that series lines up to ...\r\n// ... the original series on the time scale, we can make a new series that would visually draw attention ...\r\n// ... to the anomalies in the series. We do this by multiplying the flag series by the original series ...\r\n// ... which produces a new series that sits at zero for normal data but jumps up/down to the extreme ...\r\n// ... values where the series has an anomaly\r\n| project TimeGenerated\r\n , Attempt = y\r\n , Anomaly = iif(ShowAnomalies == {TimeView:value}, series_multiply(series_decompose_anomalies_y_ad_flag, y), series_multiply(y, 0))\r\n// If workbooks adds support for anomaly charts, we can just call \"render anomalychart\" on our original ...\r\n// ... data set without having to deal with flags and multiplication.\r\n| render timechart",
"size": 0,
"title": "Sign-in distribution*",
"noDataMessage": "No sign-in attempts found",
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{Workspace}"
],
"visualization": "timechart"
},
"conditionalVisibility": {
"parameterName": "TileView",
"comparison": "isEqualTo",
"value": "B"
},
"name": "query - 1B"
},
{
"type": 1,
"content": {
"json": "_\\* Showing **{AuthResultA}** sign-in attempts_"
},
"conditionalVisibility": {
"parameterName": "TileView",
"comparison": "isEqualTo",
"value": "A"
},
"name": "text - 3A"
},
{
"type": 1,
"content": {
"json": "_\\* Showing **{AuthResultB}** sign-in attempts_"
},
"conditionalVisibility": {
"parameterName": "TileView",
"comparison": "isEqualTo",
"value": "B"
},
"name": "text - 3B"
},
{
"type": 1,
"content": {
"json": "___"
},
"name": "text - 4"
},
{
"type": 1,
"content": {
"json": "## Accounts to check ##"
},
"name": "text - 12"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// Q2: Generate a parent-child stacked grid of account lockout instances\r\n// Find all account lockout events\r\nlet LockoutEvents = materialize(\r\n AADDomainServicesAccountManagement\r\n | where TimeGenerated {TimeRange:value}\r\n | project TimeGenerated\r\n , Event_ID = extract(\".+/(.+[0-9])\",1,tostring(OperationName))\r\n , ResultDescription\r\n | where Event_ID == \"4740\"\r\n | parse ResultDescription with * \"Account That Was Locked Out:\" accountInfo \"Additional Information:\" *\r\n | project TimeGenerated\r\n , AccountName = tolower(extract(\"Account Name:\\t\\t(.+[0-9A-Za-z])\",1,tostring(accountInfo)))\r\n);\r\n// De-duplicate the list of accounts which have been locked out\r\nlet LockedAccounts = materialize(\r\n LockoutEvents\r\n | distinct AccountName\r\n);\r\n// Filter and serialize the list of sign-in attempts to just accounts that have been locked out\r\nlet LockedAccountEvents = materialize(\r\n AADDomainServicesAccountLogon\r\n | where TimeGenerated {TimeRange:value}\r\n | project TimeGenerated\r\n , Event_ID = extract(\".+/(.+[0-9])\",1,tostring(OperationName))\r\n , AccountName = tolower(extract(\"Logon Account:\\t(.+[0-9A-Za-z])\",1,tostring(ResultDescription)))\r\n , ErrorCode = extract(\"Error Code:\\t(.+[0-9A-Za-z])\",1,tostring(ResultDescription))\r\n , ResultDescription\r\n , RecordId\r\n | where Event_ID == \"4776\"\r\n and AccountName in ( LockedAccounts )\r\n and ErrorCode in (\"0xC000006A\" , \"0xC0000234\")\r\n | sort by AccountName, TimeGenerated asc\r\n | serialize id = row_number()\r\n);\r\n// Lookup sign-in attempts that failed with \"Bad Password\" with acount lockout that have matching timestamps ...\r\n// ... and account names. This will return the list of sign-in attempts filtered to just the ones that caused ...\r\n// ... that account to get locked out. Then, return the serial ID for these \"lockout strike events\" \r\nlet LockoutStrikeIDs = materialize(\r\n LockoutEvents\r\n | lookup kind=leftouter LockedAccountEvents on AccountName, TimeGenerated\r\n | project id\r\n);\r\n// Since an account gets locked out when it has 5 sign-in attempts fail with \"Bad Password\" within a period ...\r\n// ... of 2 minutes, we can find all the \"lockout sequences\" by looking up the 4 serialized sign-in attempts ...\r\n// ... preceding each of the \"lockout strike events\". So, return those serial IDs\r\nlet LockoutSequenceIDs = materialize(\r\n LockoutStrikeIDs\r\n | project sequence = range(id - 4, id + 1, 1)\r\n);\r\n// Count how many times each account was locked out\r\nlet LockoutStrikeSummary = materialize(\r\n LockoutEvents\r\n | summarize count() by AccountName\r\n);\r\n// Lookup the serialized sign-in events matching the returned serial IDs\r\nlet LockoutSequenceEvents = materialize(\r\n LockedAccountEvents\r\n | where id in (LockoutSequenceIDs)\r\n | extend SequenceID = case( ErrorCode == \"0xC0000234\", -1\r\n , (id + 0) in (LockoutStrikeIDs), 0\r\n , (id + 1) in (LockoutStrikeIDs), 1\r\n , (id + 2) in (LockoutStrikeIDs), 2\r\n , (id + 3) in (LockoutStrikeIDs), 3\r\n , 4\r\n )\r\n | serialize rn = row_number()\r\n | extend LockoutSequenceTimeStamp = case( ErrorCode == \"0xC0000234\", prev(TimeGenerated)\r\n , SequenceID == 0, TimeGenerated\r\n , SequenceID == 1, next(TimeGenerated, 1)\r\n , SequenceID == 2, next(TimeGenerated, 2)\r\n , SequenceID == 3, next(TimeGenerated, 3)\r\n , next(TimeGenerated, 4)\r\n )\r\n | lookup kind=leftouter LockoutStrikeSummary on AccountName\r\n);\r\n// Format and show the workstation if available\r\nLockoutSequenceEvents\r\n| extend Source_Workstation = extract(\"Source Workstation:\\t(.+[0-9A-Za-z])\",1,tostring(ResultDescription))\r\n| sort by count_ desc, AccountName\r\n| project TimeGenerated\r\n , AccountName = strcat(AccountName, \" [\", count_, \" lockouts]\")\r\n , ErrorType = strcat(\"Sign in Attempt:\\t\", iif(ErrorCode == \"0xC000006A\", \"Bad Password\", \"Account Locked Out\"))\r\n , SequenceName = strcat(\"Account Lockout: \", tostring(format_datetime(LockoutSequenceTimeStamp, 'yy-MM-dd, HH:mm:ss')), \" [UTC]\")\r\n , Event_ID\r\n , Workstation = iff(Source_Workstation != \"\", Source_Workstation, \"<empty>\")\r\n , DC_ID = extract(\"([0-9A-Za-z]+)\",1,tostring(RecordId))",
"size": 2,
"title": "Account lockouts",
"noDataMessage": "No account lockouts found",
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{Workspace}"
],
"visualization": "table",
"gridSettings": {
"formatters": [
{
"columnMatch": "TimeGenerated",
"formatter": 6,
"formatOptions": {
"showIcon": true
}
},
{
"columnMatch": "AccountName",
"formatter": 5,
"formatOptions": {
"showIcon": true
}
},
{
"columnMatch": "ErrorType",
"formatter": 5,
"formatOptions": {
"showIcon": true
}
},
{
"columnMatch": "SequenceName",
"formatter": 5,
"formatOptions": {
"showIcon": true
}
},
{
"columnMatch": "Event_ID",
"formatter": 5,
"formatOptions": {
"showIcon": true
}
},
{
"columnMatch": "Workstation",
"formatter": 1,
"formatOptions": {
"showIcon": true
}
},
{
"columnMatch": "DC_ID",
"formatter": 0,
"formatOptions": {
"showIcon": true
}
},
{
"columnMatch": "$gen_group",
"formatter": 0,
"formatOptions": {
"showIcon": true
}
}
],
"rowLimit": 10000,
"filter": true,
"hierarchySettings": {
"treeType": 1,
"groupBy": [
"AccountName",
"SequenceName"
],
"expandTopLevel": false,
"finalBy": "ErrorType"
},
"labelSettings": [
{
"columnId": "TimeGenerated",
"label": "Time"
},
{
"columnId": "AccountName",
"label": "Username"
},
{
"columnId": "ErrorType",
"label": ""
},
{
"columnId": "SequenceName"
},
{
"columnId": "Event_ID",
"label": "Event",
"comment": "Event ID in Windows Server 2016 protocol"
},
{
"columnId": "Workstation",
"label": "",
"comment": "Shows as <empty> if not recorded"
},
{
"columnId": "DC_ID",
"label": "Domain Controller",
"comment": "Which Domain Controller the event was logged on"
}
]
},
"tileSettings": {
"showBorder": false
}
},
"customWidth": "70",
"name": "query - 2",
"styleSettings": {
"showBorder": true
}
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "// Q3: Generate a grid of bad usernames and how many sign-in attempts have hit each one\r\nAADDomainServicesAccountLogon\r\n| where TimeGenerated {TimeRange:value}\r\n| project Event_ID = extract(\".+/(.+[0-9])\",1,tostring(OperationName))\r\n , AccountName = tolower(extract(\"Logon Account:\\t(.+[0-9A-Za-z])\",1,tostring(ResultDescription)))\r\n , ErrorCode = extract(\"Error Code:\\t(.+[0-9A-Za-z])\",1,tostring(ResultDescription))\r\n| where Event_ID == \"4776\"\r\n and ErrorCode == \"0xC0000064\"\r\n| summarize count() by AccountName\r\n| sort by count_ desc\r\n| project AccountName = iif(AccountName != \"\", AccountName, \"<empty>\")\r\n , count_",
"size": 2,
"title": "Bad usernames",
"noDataMessage": "No bad usernames found",
"timeContext": {
"durationMs": 0
},
"timeContextFromParameter": "TimeRange",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{Workspace}"
],
"visualization": "table",
"gridSettings": {
"formatters": [
{
"columnMatch": "AccountName",
"formatter": 1,
"formatOptions": {
"showIcon": true,
"workbookContext": {
"componentIdSource": "workbook",
"resourceIdsSource": "workbook",
"templateIdSource": "static",
"templateId": "adcb0d2c-72ce-4b2a-9f0f-c417208ea01a",
"typeSource": "default",
"gallerySource": "default"
}
}
},
{
"columnMatch": "count_",
"formatter": 1,
"formatOptions": {
"showIcon": true
}
},
{
"columnMatch": "Attempted_Sign_ins",
"formatter": 1,
"formatOptions": {
"showIcon": true
}
}
],
"rowLimit": 10000,
"filter": true,
"labelSettings": [
{
"columnId": "AccountName",
"label": "Username",
"comment": "Fake usernames with recorded sign-in attempts"
},
{
"columnId": "count_",
"label": "Sign-in attempts",
"comment": "How many sign-in attempts each bad username has"
}
]
}
},
"customWidth": "30",
"name": "query - 3",
"styleSettings": {
"showBorder": true
}
}
],
"styleSettings": {},
"$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}