Repository navigation
Expand file tree
/
Copy pathERGateway.workbook
More file actions
399 lines (399 loc) · 58 KB
/
Copy pathERGateway.workbook
File metadata and controls
399 lines (399 loc) · 58 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
{
"version": "Notebook/1.0",
"items": [
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"crossComponentResources": [
"{workspace}"
],
"parameters": [
{
"id": "f79d5185-6941-488e-a728-3beaec49f9d4",
"version": "KqlParameterItem/1.0",
"name": "timeInterval",
"label": "Time interval",
"type": 4,
"description": "Select time interval to get data",
"isRequired": true,
"value": {
"durationMs": 86400000
},
"typeSettings": {
"selectableValues": [
{
"durationMs": 1800000
},
{
"durationMs": 3600000
},
{
"durationMs": 43200000
},
{
"durationMs": 86400000
},
{
"durationMs": 604800000
},
{
"durationMs": 1209600000
},
{
"durationMs": 2592000000
}
],
"allowCustom": true
}
},
{
"id": "5f59846f-1e06-4f6f-b52f-d06bb63b97fb",
"version": "KqlParameterItem/1.0",
"name": "workspace",
"label": "Log Analytics workspace",
"type": 5,
"description": "Select a workspace where Traffic Analytics is configured",
"isRequired": true,
"isGlobal": true,
"query": "Resources\r\n| where type=~ \"microsoft.network/networkwatchers/flowlogs\"\r\n| where properties.flowAnalyticsConfiguration.networkWatcherFlowAnalyticsConfiguration.enabled == true\r\n| project wsResourceId = tolower(properties.flowAnalyticsConfiguration.networkWatcherFlowAnalyticsConfiguration.workspaceResourceId)\r\n| distinct wsResourceId\r\n| project wsResourceId, wsName = tolower(split(wsResourceId, '/')[8])\r\n",
"crossComponentResources": [
"value::all"
],
"value": null,
"typeSettings": {
"additionalResourceOptions": [],
"showDefault": false
},
"timeContext": {
"durationMs": 86400000
},
"queryType": 1,
"resourceType": "microsoft.resourcegraph/resources"
},
{
"id": "8ba132ac-8838-476c-8eb2-ed42aa2aa983",
"version": "KqlParameterItem/1.0",
"name": "subscriptions",
"label": "Discovered Subscriptions",
"type": 2,
"description": "VNets in these subscriptions are configured to send their flow logs to the selected Log Analytics workspace. Select subscriptions, to analyze traffic from those subscriptions.",
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "NTATopologyDetails\r\n| where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n| where SubType == 'StatusMessage' and ComponentType == 'Topology'\r\n| distinct Subscription, SubscriptionName\r\n| project Subscription, SubscriptionName, selected = 1",
"crossComponentResources": [
"{workspace}"
],
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"selectAllValue": "*",
"showDefault": false
},
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
{
"id": "d2028f10-9c63-499c-947d-08eb9a41121d",
"version": "KqlParameterItem/1.0",
"name": "resourceGroups",
"label": "Resource groups",
"type": 2,
"description": "Traffic from the VNets in the selected resource groups, shall be analyzed. Select one or more resource groups.",
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "NTATopologyDetails\r\n| where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n| where SubType =='Topology' and AzureResourceType !in ('VirtualSubnetwork','VirtualNetworkGatewayConnection','RemoteGatewayConnection', 'Route','NetworkAnalytics','VirtualNetworkPeering','NetworkSecurityGroupRule','SubnetworkConnection')\r\n| where iff(\"{subscriptions}\" == \"'*'\", true, Subscription in~ ({subscriptions}))\r\n| project rgName = tostring(split(Name,'/')[0]), Subscription\r\n| distinct rgName, Subscription\r\n| project value = rgName, label = rgName, selected = 1",
"crossComponentResources": [
"{workspace}"
],
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"selectAllValue": "*",
"showDefault": false
},
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
{
"id": "282d8918-3c0c-43a0-aa36-6c14c3f5be79",
"version": "KqlParameterItem/1.0",
"name": "vnets",
"label": "VNet",
"type": 2,
"description": "Select one or more virtual networks",
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "NTATopologyDetails\r\n| where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n| where SubType =='Topology' and AzureResourceType == 'VirtualNetwork'\r\n| where iff(\"{subscriptions}\" == \"'*'\", true, Subscription in~ ({subscriptions}))\r\n| extend rgToCheck = tostring(split(Name, '/')[0])\r\n| where iff(\"{resourceGroups}\" == \"'*'\", true, rgToCheck in~ ({resourceGroups}))\r\n| project vnetId = strcat(Subscription, '/', Name )\r\n| distinct vnetId\r\n| project vnetName = tostring(split(vnetId, '/')[2])\r\n| project value = vnetName, label = vnetName, selected = 1",
"crossComponentResources": [
"{workspace}"
],
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"selectAllValue": "*",
"showDefault": false
},
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
{
"id": "99b8addb-cbb8-4763-8dbd-fe01c284cd5a",
"version": "KqlParameterItem/1.0",
"name": "erGateways",
"label": "ER Gateway",
"type": 2,
"description": "Select one or more VPN gateways",
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "NTATopologyDetails\r\n| where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n| where SubType == 'Topology' and AzureResourceType == 'VirtualNetworkGateway'\r\n| where isnotempty(Subscription) and iff(\"{subscriptions}\" == \"'*'\", true, Subscription in~ ({subscriptions}))\r\n| extend rgToCheck = split(Name, '/')[0]\r\n| where iff(\"{resourceGroups}\" == \"'*'\", true, rgToCheck in~ ({resourceGroups}))\r\n| where GatewayType == 'ExpressRoute'\r\n| extend SubnetSpilt = split(VirtualSubnetwork, '/')\r\n| extend vnetToCheck = strcat(Subscription, '/', SubnetSpilt[0], '/', SubnetSpilt[1])\r\n| where iff(\"{vnets}\" == \"'*'\", true, vnetToCheck in~ ({vnets}))\r\n| extend GatewayName = strcat(Subscription, '/', Name)\r\n| distinct GatewayName\r\n| project value = GatewayName, label = split(GatewayName, '/')[2], selected = 1",
"crossComponentResources": [
"{workspace}"
],
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"selectAllValue": "*",
"showDefault": false
},
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
{
"id": "4d509a3d-79d4-4734-9872-538d0d287a93",
"version": "KqlParameterItem/1.0",
"name": "erGatewayConnections",
"label": "ER Gateway connection",
"type": 2,
"description": "Select one or more ER Gateway connections",
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "NTATopologyDetails\r\n | where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. datetime_add('day', 7, todatetime('{timeInterval:endISO}'))), TimeGenerated between (todatetime('{timeInterval:startISO}') .. datetime_add('day', 7, todatetime('{timeInterval:endISO}'))))\r\n | where SubType == 'Topology' and AzureResourceType == 'VirtualNetworkGatewayConnection'\r\n | where isnotempty(Subscription) and iff(\"{subscriptions}\" == \"'*'\", true, Subscription in~ ({subscriptions}))\r\n | extend rgToCheck = split(Name, '/')[0]\r\n | where iff(\"{resourceGroups}\" == \"'*'\", true, rgToCheck in~ ({resourceGroups}))\r\n | where ConnectionType == 'ExpressRoute'\r\n | extend vnet1ToCheck = VirtualNetwork1, vnet2ToCheck = VirtualNetwork2\r\n | where iff(\"{vnets}\" == \"'*'\", true, vnet1ToCheck in ({vnets})) or iff(\"{vnets}\" == \"'*'\", true, vnet2ToCheck in ({vnets}))\r\n | extend Connection = strcat(Subscription, '/', Name)\r\n | where iff(\"{erGateways}\" == \"'*'\", true, VirtualNetworkGateway1 in~ ({erGateways}) or VirtualNetworkGateway2 in~ ({erGateways}))\r\n| project value = Connection, label = split(Connection, '/')[2], selected = 1",
"crossComponentResources": [
"{workspace}"
],
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"selectAllValue": "*",
"showDefault": false
},
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "parameters - 2"
},
{
"type": 1,
"content": {
"json": "##### Total traffic per ExpressRoute gateway"
},
"name": "text - 10"
},
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"parameters": [
{
"id": "8a4902b7-b0a6-4949-92b4-910cef05f453",
"version": "KqlParameterItem/1.0",
"name": "graphUnit",
"label": "Units in",
"type": 2,
"description": "This unit will be used to get the top 5 IPs.",
"typeSettings": {
"additionalResourceOptions": [],
"showDefault": false
},
"jsonData": "[\r\n { \"value\":\"flows\", \"label\":\"Flows\", \"selected\":true },\r\n { \"value\":\"bytes\", \"label\":\"Bytes\" },\r\n { \"value\":\"packets\", \"label\":\"Packets\"}\r\n]",
"timeContext": {
"durationMs": 86400000
}
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "parameters - 3"
},
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"crossComponentResources": [
"{workspace}"
],
"parameters": [
{
"id": "8340f941-9688-4c62-9074-543636f115cb",
"version": "KqlParameterItem/1.0",
"name": "top5Gateways",
"type": 2,
"isRequired": true,
"multiSelect": true,
"quote": "'",
"delimiter": ",",
"query": "(NTATopologyDetails\r\n| where SubType == 'Topology'\r\n and AzureResourceType == 'VirtualNetworkGatewayConnection'\r\n and ConnectionType == 'ExpressRoute' \r\n//project connname, gateway here onwards \r\n| extend ConnectionName = strcat(Subscription, '/', Name)\r\n//filters\r\n | where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n | where isnotempty(Subscription) and iff(\"{subscriptions}\" == \"'*'\", true, Subscription in~ ({subscriptions}))\r\n | extend rgToCheck = split(Name, '/')[0]\r\n | where iff(\"{resourceGroups}\" == \"'*'\", true, rgToCheck in~ ({resourceGroups}))\r\n | extend vnet1ToCheck = VirtualNetwork1, vnet2ToCheck = VirtualNetwork2\r\n | where iff(\"{vnets}\" == \"'*'\", true, vnet1ToCheck in ({vnets})) or iff(\"{vnets}\" == \"'*'\", true, vnet2ToCheck in ({vnets}))\r\n//\r\n| project ConnectionName, Gateway = VirtualNetworkGateway1\r\n| distinct Gateway, ConnectionName)\r\n| join kind = inner (NTANetAnalytics\r\n| where SubType == 'FlowLog' and TimeGenerated between (todatetime('{timeInterval:startISO}') .. datetime_add('day', 7, todatetime('{timeInterval:endISO}'))) and (FaSchemaVersion == '3') and FlowStartTime between (datetime('{timeInterval:startISO}') .. datetime('{timeInterval:endISO}'))\r\n | extend ConnectionNameSplit = split(ConnectionName, '/')\r\n | extend subscription1ToCheck = tostring(ConnectionNameSplit[0])\r\n | where iff(\"{subscriptions}\" == \"'*'\", true, SrcSubscription in~ ({subscriptions}))\r\n | extend rg1ToCheck = tostring(ConnectionNameSplit[1])\r\n | where iff(\"{resourceGroups}\" == \"'*'\", true, rg1ToCheck in~ ({resourceGroups})) \r\n | where iff(\"{erGatewayConnections}\" == \"'*'\", true, ConnectionName in~ ({erGatewayConnections}))\r\n| where FlowType == 'S2S' and ConnectionType == 'ExpressRoute')\r\non ConnectionName\r\n | extend \r\n AllowedInboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), \r\n BlockedInboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), \r\n AllowedOutboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), \r\n BlockedOutboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), \r\n AllowedInboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), \r\n BlockedInboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), \r\n AllowedOutboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0), \r\n BlockedOutboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0)\r\n | extend \r\n AllowedInboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), \r\n BlockedInboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), \r\n AllowedOutboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), \r\n BlockedOutboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), \r\n AllowedInboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), \r\n BlockedInboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), \r\n AllowedOutboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0), \r\n BlockedOutboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0)\r\n | extend AllowedOutbound_inferred = max_of(AllowedOutFlows, AllowedInFlows + DeniedInFlows)\r\n | extend Outbound = AllowedOutbound_inferred + DeniedOutFlows, Inbound = AllowedOutbound_inferred\r\n | extend FlowCount = max_of(Inbound, Outbound)\r\n | extend AllowedOutboundBytesAtSrc_inferred = max_of(AllowedOutboundBytesAtSrc, AllowedInboundBytesAtDest + BlockedInboundBytesAtDest)\r\n | extend AllowedOutboundBytesAtDest_inferred = max_of(AllowedOutboundBytesAtDest, AllowedInboundBytesAtSrc + BlockedInboundBytesAtSrc)\r\n | extend OutboundBytesAtSrc = AllowedOutboundBytesAtSrc_inferred + BlockedOutboundBytesAtSrc, InboundBytesAtSrc = AllowedOutboundBytesAtDest_inferred\r\n | extend OutboundBytesAtDest = AllowedOutboundBytesAtDest_inferred + BlockedOutboundBytesAtDest, InboundBytesAtDest = AllowedOutboundBytesAtSrc_inferred\r\n | extend BytesCount = max_of(OutboundBytesAtSrc, InboundBytesAtDest) + max_of(OutboundBytesAtDest, InboundBytesAtSrc)\r\n | extend AllowedOutboundPacketsAtSrc_inferred = max_of(AllowedOutboundPacketsAtSrc, AllowedInboundPacketsAtDest + BlockedInboundPacketsAtDest)\r\n | extend AllowedOutboundPacketsAtDest_inferred = max_of(AllowedOutboundPacketsAtDest, AllowedInboundPacketsAtSrc + BlockedInboundPacketsAtSrc)\r\n | extend OutboundPacketsAtSrc = AllowedOutboundPacketsAtSrc_inferred + BlockedOutboundPacketsAtSrc, InboundPacketsAtSrc = AllowedOutboundPacketsAtDest_inferred\r\n | extend OutboundPacketsAtDest = AllowedOutboundPacketsAtDest_inferred + BlockedOutboundPacketsAtDest, InboundPacketsAtDest = AllowedOutboundPacketsAtSrc_inferred\r\n | extend PacketsCount = max_of(OutboundPacketsAtSrc, InboundPacketsAtDest) + max_of(OutboundPacketsAtDest, InboundPacketsAtSrc)\r\n| summarize TotalTraffic = sum(iff('{graphUnit:value}' =~ 'bytes', BytesCount, iff('{graphUnit:value}' =~ 'packets', PacketsCount, tolong(FlowCount)))) by Gateway\r\n| where TotalTraffic > 0\r\n| top 5 by TotalTraffic",
"crossComponentResources": [
"{workspace}"
],
"typeSettings": {
"additionalResourceOptions": [
"value::all"
],
"showDefault": false
},
"defaultValue": "value::all",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
{
"id": "a050abd6-c366-4fc9-b1aa-f309acafa79c",
"version": "KqlParameterItem/1.0",
"name": "binningTime",
"type": 2,
"isRequired": true,
"query": "datatable (id:int) [1]\r\n| project value = strcat(max_of({timeInterval:seconds}/3000, 1), 'm'), label = strcat(max_of({timeInterval:seconds}/3000, 1), 'm'), selected = 1",
"crossComponentResources": [
"{workspace}"
],
"isHiddenWhenLocked": true,
"typeSettings": {
"additionalResourceOptions": [],
"showDefault": false
},
"timeContext": {
"durationMs": 1800000
},
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"conditionalVisibility": {
"parameterName": "tabSelected",
"comparison": "isEqualTo",
"value": "-1"
},
"name": "parameters - 18"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "NTATopologyDetails\r\n| where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n| where SubType == 'Topology' and AzureResourceType == 'VirtualNetworkGatewayConnection' \r\n and ConnectionType == 'ExpressRoute'\r\n| where VirtualNetworkGateway1 in~ ({top5Gateways})\r\n| extend ConnectionName = strcat(Subscription, '/', Name)\r\n| distinct ConnectionName\r\n| join kind = inner \r\n (NTANetAnalytics\r\n | where SubType == 'FlowLog' and TimeGenerated between (todatetime('{timeInterval:startISO}') .. datetime_add('day', 7, todatetime('{timeInterval:endISO}'))) and (FaSchemaVersion == '3') and FlowStartTime between (datetime('{timeInterval:startISO}') .. datetime('{timeInterval:endISO}'))\r\n | where FlowType == 'S2S' and ConnectionType == 'ExpressRoute') \r\n on ConnectionName\r\n | extend \r\n AllowedInboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), \r\n BlockedInboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), \r\n AllowedOutboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), \r\n BlockedOutboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), \r\n AllowedInboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), \r\n BlockedInboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), \r\n AllowedOutboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0), \r\n BlockedOutboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0)\r\n | extend \r\n AllowedInboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), \r\n BlockedInboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), \r\n AllowedOutboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), \r\n BlockedOutboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), \r\n AllowedInboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), \r\n BlockedInboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), \r\n AllowedOutboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0), \r\n BlockedOutboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0)\r\n | extend AllowedOutbound_inferred = max_of(AllowedOutFlows, AllowedInFlows + DeniedInFlows)\r\n | extend Outbound = AllowedOutbound_inferred + DeniedOutFlows, Inbound = AllowedOutbound_inferred\r\n | extend FlowCount = max_of(Inbound, Outbound)\r\n | extend AllowedOutboundBytesAtSrc_inferred = max_of(AllowedOutboundBytesAtSrc, AllowedInboundBytesAtDest + BlockedInboundBytesAtDest)\r\n | extend AllowedOutboundBytesAtDest_inferred = max_of(AllowedOutboundBytesAtDest, AllowedInboundBytesAtSrc + BlockedInboundBytesAtSrc)\r\n | extend OutboundBytesAtSrc = AllowedOutboundBytesAtSrc_inferred + BlockedOutboundBytesAtSrc, InboundBytesAtSrc = AllowedOutboundBytesAtDest_inferred\r\n | extend OutboundBytesAtDest = AllowedOutboundBytesAtDest_inferred + BlockedOutboundBytesAtDest, InboundBytesAtDest = AllowedOutboundBytesAtSrc_inferred\r\n | extend BytesCount = max_of(OutboundBytesAtSrc, InboundBytesAtDest) + max_of(OutboundBytesAtDest, InboundBytesAtSrc)\r\n | extend AllowedOutboundPacketsAtSrc_inferred = max_of(AllowedOutboundPacketsAtSrc, AllowedInboundPacketsAtDest + BlockedInboundPacketsAtDest)\r\n | extend AllowedOutboundPacketsAtDest_inferred = max_of(AllowedOutboundPacketsAtDest, AllowedInboundPacketsAtSrc + BlockedInboundPacketsAtSrc)\r\n | extend OutboundPacketsAtSrc = AllowedOutboundPacketsAtSrc_inferred + BlockedOutboundPacketsAtSrc, InboundPacketsAtSrc = AllowedOutboundPacketsAtDest_inferred\r\n | extend OutboundPacketsAtDest = AllowedOutboundPacketsAtDest_inferred + BlockedOutboundPacketsAtDest, InboundPacketsAtDest = AllowedOutboundPacketsAtSrc_inferred\r\n | extend PacketsCount = max_of(OutboundPacketsAtSrc, InboundPacketsAtDest) + max_of(OutboundPacketsAtDest, InboundPacketsAtSrc)\r\n | extend Connection = tostring(split(ConnectionName,\"/\")[2])\r\n| summarize TotalTraffic = sum(iff('{graphUnit:value}' =~ 'bytes', BytesCount, iff('{graphUnit:value}' =~ 'packets', PacketsCount, tolong(FlowCount))))\r\nby todatetime(ceiling(FlowStartTime / {binningTime}) * {binningTime}), Connection",
"size": 0,
"showAnalytics": true,
"noDataMessage": "No data to display",
"showExportToExcel": true,
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{workspace}"
],
"visualization": "timechart"
},
"conditionalVisibility": {
"parameterName": "top5Gateways",
"comparison": "isNotEqualTo"
},
"showPin": true,
"name": "getLineChartDataPointsForGatewayStatistics"
},
{
"type": 1,
"content": {
"json": "No data to display",
"style": "info"
},
"conditionalVisibility": {
"parameterName": "top5Gateways",
"comparison": "isEqualTo"
},
"name": "No data to display - traffic per VPN gateway"
},
{
"type": 1,
"content": {
"json": "#### ExpressRoute gateway"
},
"name": "text - 9"
},
{
"type": 9,
"content": {
"version": "KqlParameterItem/1.0",
"parameters": [
{
"id": "3df6c05c-9526-4072-842e-519cf239bf35",
"version": "KqlParameterItem/1.0",
"name": "gridUnit",
"label": "Units in",
"type": 2,
"description": "The traffic will be shown in this unit.",
"typeSettings": {
"additionalResourceOptions": [],
"showDefault": false
},
"jsonData": "[\r\n { \"value\":\"flows\", \"label\":\"Flows\", \"selected\":true },\r\n { \"value\":\"bytes\", \"label\":\"Bytes\" },\r\n { \"value\":\"packets\", \"label\":\"Packets\"}\r\n]",
"timeContext": {
"durationMs": 86400000
}
}
],
"style": "pills",
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces"
},
"name": "parameters - 11"
},
{
"type": 3,
"content": {
"version": "KqlItem/1.0",
"query": "//table - ergateway\r\nlet ValueWithUnit = (x:real, precision:int) {\r\n iff(x < 1000, strcat(tostring(round(x, precision)), ''), \r\n iff(x < 1000000, strcat(tostring(round(x/1000, precision)), ' K'), \r\n iff(x < 1000000000, strcat(tostring(round(x/1000000, precision)), ' M'), \r\n iff(x < 1000000000000, strcat(tostring(round(x/1000000000, precision)), ' B'), \r\n iff(x < 1000000000000000, strcat(tostring(round(x/1000000000000, precision)), ' t'), \r\n strcat(tostring(round(x/1000000000000000, precision)), ' q'))))))\r\n};\r\n//getTrafficDistributionForERGw\r\nlet table1 = NTATopologyDetails\r\n | where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n | where SubType == 'Topology' and AzureResourceType == 'VirtualNetworkGatewayConnection' \r\n and ConnectionType == 'ExpressRoute'\r\n | where iff(\"{erGateways}\" == \"'*'\", true, VirtualNetworkGateway1 in~ ({erGateways})) \r\n | extend ConnectionName = strcat(Subscription, '/', Name)\r\n | where iff(\"{erGatewayConnections}\" == \"'*'\", true, ConnectionName in~ ({erGatewayConnections}))\r\n | distinct ConnectionName, VirtualNetworkGateway1\r\n | join kind = inner \r\n (NTANetAnalytics\r\n | where SubType == 'FlowLog' and (FaSchemaVersion == '3') and TimeGenerated between (todatetime('{timeInterval:startISO}') .. datetime_add('day', 7, todatetime('{timeInterval:endISO}')))\r\n and FlowStartTime between (datetime('{timeInterval:startISO}') .. datetime('{timeInterval:endISO}'))\r\n | where FlowType in ('S2S', 'InterVNet') and ConnectionType == 'ExpressRoute')\r\n on ConnectionName\r\n | extend Gateway = VirtualNetworkGateway1\r\n //extension\r\n | extend AllowedInboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), AllowedInboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0)\r\n | extend AllowedInboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), AllowedInboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0)\r\n | extend AllowedOutbound_inferred = max_of(AllowedOutFlows, AllowedInFlows + DeniedInFlows)\r\n | extend Outbound = AllowedOutbound_inferred + DeniedOutFlows, Inbound = AllowedOutbound_inferred\r\n | extend FlowCount = max_of(Inbound, Outbound)\r\n //infrered \r\n | extend AllowedOutboundBytesAtSrc_inferred = max_of(AllowedOutboundBytesAtSrc, AllowedInboundBytesAtDest + BlockedInboundBytesAtDest) \r\n | extend AllowedOutboundBytesAtDest_inferred = max_of(AllowedOutboundBytesAtDest, AllowedInboundBytesAtSrc + BlockedInboundBytesAtSrc) \r\n | extend OutboundBytesAtSrc = AllowedOutboundBytesAtSrc_inferred + BlockedOutboundBytesAtSrc, InboundBytesAtSrc = AllowedOutboundBytesAtDest_inferred \r\n | extend OutboundBytesAtDest = AllowedOutboundBytesAtDest_inferred + BlockedOutboundBytesAtDest, InboundBytesAtDest = AllowedOutboundBytesAtSrc_inferred \r\n | extend BytesCount = max_of(OutboundBytesAtSrc, InboundBytesAtDest) + max_of(OutboundBytesAtDest, InboundBytesAtSrc) \r\n | extend AllowedOutboundPacketsAtSrc_inferred = max_of(AllowedOutboundPacketsAtSrc, AllowedInboundPacketsAtDest + BlockedInboundPacketsAtDest) \r\n | extend AllowedOutboundPacketsAtDest_inferred = max_of(AllowedOutboundPacketsAtDest, AllowedInboundPacketsAtSrc + BlockedInboundPacketsAtSrc) \r\n | extend OutboundPacketsAtSrc = AllowedOutboundPacketsAtSrc_inferred + BlockedOutboundPacketsAtSrc, InboundPacketsAtSrc = AllowedOutboundPacketsAtDest_inferred \r\n | extend OutboundPacketsAtDest = AllowedOutboundPacketsAtDest_inferred + BlockedOutboundPacketsAtDest, InboundPacketsAtDest = AllowedOutboundPacketsAtSrc_inferred \r\n | extend PacketsCount = max_of(OutboundPacketsAtSrc, InboundPacketsAtDest) + max_of(OutboundPacketsAtDest, InboundPacketsAtSrc)\r\n | extend trafficForSorting = iff('{gridUnit:value}' =~ 'bytes', BytesCount, iff('{gridUnit:value}' =~ 'packets', PacketsCount, tolong(FlowCount)))\r\n | summarize totalAllowedInboundBytes = sum(AllowedInboundBytesAtSrc + AllowedInboundBytesAtDest), totalBlockedInboundBytes = sum(BlockedInboundBytesAtSrc + BlockedInboundBytesAtDest),totalAllowedOutboundBytes = sum(AllowedOutboundBytesAtSrc + AllowedOutboundBytesAtDest), totalBlockedOutboundBytes = sum(BlockedOutboundBytesAtSrc + BlockedOutboundBytesAtDest), totalAllowedInboundPackets = sum(AllowedInboundPacketsAtSrc + AllowedInboundPacketsAtDest), \r\n totalBlockedInboundPackets = sum(BlockedInboundPacketsAtSrc + BlockedInboundPacketsAtDest), \r\n totalAllowedOutboundPackets = sum(AllowedOutboundPacketsAtSrc + AllowedOutboundPacketsAtDest), \r\n totalBlockedOutboundPackets = sum(BlockedOutboundPacketsAtSrc + BlockedOutboundPacketsAtDest), totalAllowedInbound = sum(AllowedInFlows), \r\n totalBlockedInbound = sum(DeniedInFlows), \r\n totalAllowedOutbound = sum(AllowedOutFlows), \r\n totalBlockedOutbound = sum(DeniedOutFlows) by Gateway, trafficForSorting \r\n | where trafficForSorting > 0\r\n | project Gateway, trafficForSorting,\r\n InboundAllowed = iff('{gridUnit:value}' =~ 'bytes', format_bytes(totalAllowedInboundBytes, 3), iff('{gridUnit:value}' =~ 'packets', ValueWithUnit(totalAllowedInboundPackets, 3), ValueWithUnit(totalAllowedInbound, 3))),\r\n InboundBlocked = iff('{gridUnit:value}' =~ 'bytes', format_bytes(totalBlockedInboundBytes, 3), iff('{gridUnit:value}' =~ 'packets', ValueWithUnit(totalBlockedInboundPackets, 3), ValueWithUnit(totalBlockedInbound, 3))),\r\n OutboundAllowed = iff('{gridUnit:value}' =~ 'bytes', format_bytes(totalAllowedOutboundBytes, 3), iff('{gridUnit:value}' =~ 'packets', ValueWithUnit(totalAllowedOutboundPackets, 3), ValueWithUnit(totalAllowedOutbound, 3))),\r\n OutboundBlocked = iff('{gridUnit:value}' =~ 'bytes', format_bytes(totalBlockedOutboundBytes, 3), iff('{gridUnit:value}' =~ 'packets', ValueWithUnit(totalBlockedOutboundPackets, 3), ValueWithUnit(totalBlockedOutbound, 3)));\r\n//getTopDestPortDataERGw\r\nlet table2 = (NTATopologyDetails\r\n | where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n | where SubType == 'Topology' and AzureResourceType == 'VirtualNetworkGatewayConnection' and ConnectionType == 'ExpressRoute'\r\n | where iff(\"{erGateways}\" == \"'*'\", true, VirtualNetworkGateway1 in~ ({erGateways}))\r\n | extend ConnectionName = strcat(Subscription, '/', Name)\r\n | where iff(\"{erGatewayConnections}\" == \"'*'\", true, ConnectionName in~ ({erGatewayConnections}))\r\n | distinct ConnectionName, VirtualNetworkGateway1)\r\n | join kind = inner (NTANetAnalytics\r\n | where SubType == 'FlowLog' and (FaSchemaVersion == '3') and TimeGenerated between (todatetime('{timeInterval:startISO}') .. datetime_add('day', 7, todatetime('{timeInterval:endISO}'))) and FlowStartTime between (datetime('{timeInterval:startISO}') .. datetime('{timeInterval:endISO}'))\r\n | where FlowType == 'S2S' and ConnectionType == 'ExpressRoute' \r\n | where isnotempty(DestVm)) \r\n on ConnectionName \r\n | extend Gateway = VirtualNetworkGateway1\r\n //extension\r\n | extend AllowedInboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), AllowedInboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0)\r\n | extend AllowedInboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), AllowedInboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0)\r\n | extend AllowedOutbound_inferred = max_of(AllowedOutFlows, AllowedInFlows + DeniedInFlows)\r\n | extend Outbound = AllowedOutbound_inferred + DeniedOutFlows, Inbound = AllowedOutbound_inferred\r\n | extend FlowCount = max_of(Inbound, Outbound)\r\n //infrered \r\n | extend AllowedOutboundBytesAtSrc_inferred = max_of(AllowedOutboundBytesAtSrc, AllowedInboundBytesAtDest + BlockedInboundBytesAtDest) \r\n | extend AllowedOutboundBytesAtDest_inferred = max_of(AllowedOutboundBytesAtDest, AllowedInboundBytesAtSrc + BlockedInboundBytesAtSrc) \r\n | extend OutboundBytesAtSrc = AllowedOutboundBytesAtSrc_inferred + BlockedOutboundBytesAtSrc, InboundBytesAtSrc = AllowedOutboundBytesAtDest_inferred \r\n | extend OutboundBytesAtDest = AllowedOutboundBytesAtDest_inferred + BlockedOutboundBytesAtDest, InboundBytesAtDest = AllowedOutboundBytesAtSrc_inferred \r\n | extend BytesCount = max_of(OutboundBytesAtSrc, InboundBytesAtDest) + max_of(OutboundBytesAtDest, InboundBytesAtSrc) \r\n | extend AllowedOutboundPacketsAtSrc_inferred = max_of(AllowedOutboundPacketsAtSrc, AllowedInboundPacketsAtDest + BlockedInboundPacketsAtDest) \r\n | extend AllowedOutboundPacketsAtDest_inferred = max_of(AllowedOutboundPacketsAtDest, AllowedInboundPacketsAtSrc + BlockedInboundPacketsAtSrc) \r\n | extend OutboundPacketsAtSrc = AllowedOutboundPacketsAtSrc_inferred + BlockedOutboundPacketsAtSrc, InboundPacketsAtSrc = AllowedOutboundPacketsAtDest_inferred \r\n | extend OutboundPacketsAtDest = AllowedOutboundPacketsAtDest_inferred + BlockedOutboundPacketsAtDest, InboundPacketsAtDest = AllowedOutboundPacketsAtSrc_inferred \r\n | extend PacketsCount = max_of(OutboundPacketsAtSrc, InboundPacketsAtDest) + max_of(OutboundPacketsAtDest, InboundPacketsAtSrc)\r\n | summarize TotalTraffic = sum(iff('{gridUnit:value}' =~ 'bytes', BytesCount, iff('{gridUnit:value}' =~ 'packets', PacketsCount, tolong(FlowCount)))) by DestPort, Gateway\r\n | top-nested of Gateway by max(1), top-nested 3 of DestPort by max(TotalTraffic) | project Gateway, DestPort\r\n | summarize topDestPort = strcat_array(make_list(DestPort), ',') by Gateway\r\n | project Gateway, topDestPort;\r\n//getTopSrcIPDataERGw\r\nlet table3 = (NTATopologyDetails\r\n | where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n | where SubType == 'Topology' and AzureResourceType == 'VirtualNetworkGatewayConnection' and ConnectionType == 'ExpressRoute'\r\n | where iff(\"{erGateways}\" == \"'*'\", true, VirtualNetworkGateway1 in~ ({erGateways})) \r\n | extend ConnectionName = strcat(Subscription, '/', Name)\r\n | distinct ConnectionName, VirtualNetworkGateway1) \r\n | join kind = inner (NTANetAnalytics\r\n | where SubType == 'FlowLog' and (FaSchemaVersion == '3') and TimeGenerated between (todatetime('{timeInterval:startISO}') .. datetime_add('day', 7, todatetime('{timeInterval:endISO}')))\r\n | where FlowType == 'S2S' and ConnectionType == 'ExpressRoute' \r\n | where isnotempty(SrcIp) or isnotempty(SrcVm) ) \r\n on ConnectionName \r\n | extend Gateway = VirtualNetworkGateway1\r\n //extension\r\n | extend AllowedInboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), AllowedInboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0)\r\n | extend AllowedInboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), AllowedInboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0)\r\n | extend AllowedOutbound_inferred = max_of(AllowedOutFlows, AllowedInFlows + DeniedInFlows)\r\n | extend Outbound = AllowedOutbound_inferred + DeniedOutFlows, Inbound = AllowedOutbound_inferred\r\n | extend FlowCount = max_of(Inbound, Outbound)\r\n //infrered \r\n | extend AllowedOutboundBytesAtSrc_inferred = max_of(AllowedOutboundBytesAtSrc, AllowedInboundBytesAtDest + BlockedInboundBytesAtDest) \r\n | extend AllowedOutboundBytesAtDest_inferred = max_of(AllowedOutboundBytesAtDest, AllowedInboundBytesAtSrc + BlockedInboundBytesAtSrc) \r\n | extend OutboundBytesAtSrc = AllowedOutboundBytesAtSrc_inferred + BlockedOutboundBytesAtSrc, InboundBytesAtSrc = AllowedOutboundBytesAtDest_inferred \r\n | extend OutboundBytesAtDest = AllowedOutboundBytesAtDest_inferred + BlockedOutboundBytesAtDest, InboundBytesAtDest = AllowedOutboundBytesAtSrc_inferred \r\n | extend BytesCount = max_of(OutboundBytesAtSrc, InboundBytesAtDest) + max_of(OutboundBytesAtDest, InboundBytesAtSrc) \r\n | extend AllowedOutboundPacketsAtSrc_inferred = max_of(AllowedOutboundPacketsAtSrc, AllowedInboundPacketsAtDest + BlockedInboundPacketsAtDest) \r\n | extend AllowedOutboundPacketsAtDest_inferred = max_of(AllowedOutboundPacketsAtDest, AllowedInboundPacketsAtSrc + BlockedInboundPacketsAtSrc) \r\n | extend OutboundPacketsAtSrc = AllowedOutboundPacketsAtSrc_inferred + BlockedOutboundPacketsAtSrc, InboundPacketsAtSrc = AllowedOutboundPacketsAtDest_inferred \r\n | extend OutboundPacketsAtDest = AllowedOutboundPacketsAtDest_inferred + BlockedOutboundPacketsAtDest, InboundPacketsAtDest = AllowedOutboundPacketsAtSrc_inferred \r\n | extend PacketsCount = max_of(OutboundPacketsAtSrc, InboundPacketsAtDest) + max_of(OutboundPacketsAtDest, InboundPacketsAtSrc)\r\n | summarize TotalTraffic = sum(iff('{gridUnit:value}' =~ 'bytes', BytesCount, iff('{gridUnit:value}' =~ 'packets', PacketsCount, tolong(FlowCount)))) by SrcIp, SrcVm, Gateway\r\n | top-nested of Gateway by max(1), top-nested 3 of partnerInfo = strcat(SrcIp, '#', SrcVm) by max(TotalTraffic)\r\n | project Gateway, partnerInfo\r\n | summarize topHostsDataSrc = strcat_array(make_list(strcat(split(partnerInfo, '#')[0],' (',split(partnerInfo, '#')[1],')')), ',') by Gateway\r\n | project Gateway, topHostsDataSrc;\r\n\r\n//getTopDestIPDataERGw\r\nlet table4 = (NTATopologyDetails\r\n | where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n | where SubType == 'Topology' and AzureResourceType == 'VirtualNetworkGatewayConnection' and ConnectionType == 'ExpressRoute'\r\n | where iff(\"{erGateways}\" == \"'*'\", true, VirtualNetworkGateway1 in~ ({erGateways})) \r\n | extend ConnectionName = strcat(Subscription, '/', Name)\r\n | distinct ConnectionName, VirtualNetworkGateway1) \r\n | join kind = inner (NTANetAnalytics\r\n | where SubType == 'FlowLog' and (FaSchemaVersion == '3') and TimeGenerated between (todatetime('{timeInterval:startISO}') .. datetime_add('day', 7, todatetime('{timeInterval:endISO}')))\r\n | where FlowType == 'S2S' and ConnectionType == 'ExpressRoute' \r\n | where isnotempty(DestIp) or isnotempty(DestVm) ) \r\n on ConnectionName \r\n | extend Gateway = VirtualNetworkGateway1\r\n //extension\r\n | extend AllowedInboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), AllowedInboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0)\r\n | extend AllowedInboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), AllowedInboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0)\r\n | extend AllowedOutbound_inferred = max_of(AllowedOutFlows, AllowedInFlows + DeniedInFlows)\r\n | extend Outbound = AllowedOutbound_inferred + DeniedOutFlows, Inbound = AllowedOutbound_inferred\r\n | extend FlowCount = max_of(Inbound, Outbound)\r\n //infrered \r\n | extend AllowedOutboundBytesAtSrc_inferred = max_of(AllowedOutboundBytesAtSrc, AllowedInboundBytesAtDest + BlockedInboundBytesAtDest) \r\n | extend AllowedOutboundBytesAtDest_inferred = max_of(AllowedOutboundBytesAtDest, AllowedInboundBytesAtSrc + BlockedInboundBytesAtSrc) \r\n | extend OutboundBytesAtSrc = AllowedOutboundBytesAtSrc_inferred + BlockedOutboundBytesAtSrc, InboundBytesAtSrc = AllowedOutboundBytesAtDest_inferred \r\n | extend OutboundBytesAtDest = AllowedOutboundBytesAtDest_inferred + BlockedOutboundBytesAtDest, InboundBytesAtDest = AllowedOutboundBytesAtSrc_inferred \r\n | extend BytesCount = max_of(OutboundBytesAtSrc, InboundBytesAtDest) + max_of(OutboundBytesAtDest, InboundBytesAtSrc) \r\n | extend AllowedOutboundPacketsAtSrc_inferred = max_of(AllowedOutboundPacketsAtSrc, AllowedInboundPacketsAtDest + BlockedInboundPacketsAtDest) \r\n | extend AllowedOutboundPacketsAtDest_inferred = max_of(AllowedOutboundPacketsAtDest, AllowedInboundPacketsAtSrc + BlockedInboundPacketsAtSrc) \r\n | extend OutboundPacketsAtSrc = AllowedOutboundPacketsAtSrc_inferred + BlockedOutboundPacketsAtSrc, InboundPacketsAtSrc = AllowedOutboundPacketsAtDest_inferred \r\n | extend OutboundPacketsAtDest = AllowedOutboundPacketsAtDest_inferred + BlockedOutboundPacketsAtDest, InboundPacketsAtDest = AllowedOutboundPacketsAtSrc_inferred \r\n | extend PacketsCount = max_of(OutboundPacketsAtSrc, InboundPacketsAtDest) + max_of(OutboundPacketsAtDest, InboundPacketsAtSrc)\r\n | summarize TotalTraffic = sum(iff('{gridUnit:value}' =~ 'bytes', BytesCount, iff('{gridUnit:value}' =~ 'packets', PacketsCount, tolong(FlowCount)))) by DestIp, DestVm, Gateway\r\n | top-nested of Gateway by max(1), top-nested 3 of partnerInfo = strcat(DestIp, '#', DestVm) by max(TotalTraffic)\r\n | project Gateway, partnerInfo\r\n | summarize topHostsDataDest = strcat_array(make_list(strcat(split(partnerInfo, '#')[0],' (',split(partnerInfo, '#')[1],')')), ',') by Gateway\r\n | project Gateway, topHostsDataDest;\r\n//getTopGatewayConnections\r\nlet table5 = (NTATopologyDetails\r\n | where iff({timeInterval:seconds} < 86400, TimeGenerated between (datetime_add('day', -1, todatetime('{timeInterval:endISO}')) .. todatetime('{timeInterval:endISO}')), TimeGenerated between (todatetime('{timeInterval:startISO}') .. todatetime('{timeInterval:endISO}')))\r\n | where SubType == 'Topology' and AzureResourceType == 'VirtualNetworkGatewayConnection' and ConnectionType == 'ExpressRoute'\r\n | where iff(\"{erGateways}\" == \"'*'\", true, VirtualNetworkGateway1 in~ ({erGateways}))\r\n | extend ConnectionName = strcat(Subscription, '/', Name)\r\n | distinct ConnectionName,VirtualNetworkGateway1)\r\n | join kind = inner (NTANetAnalytics\r\n | where SubType == 'FlowLog' and (FaSchemaVersion == '3') and TimeGenerated between (todatetime('{timeInterval:startISO}') .. datetime_add('day', 7, todatetime('{timeInterval:endISO}')))\r\n | where FlowType == 'S2S' and ConnectionType == 'ExpressRoute') \r\n on ConnectionName \r\n | extend Gateway = VirtualNetworkGateway1\r\n //extension\r\n | extend AllowedInboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(BytesSrcToDest), 0), AllowedInboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), BlockedInboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesDestToSrc), 0), AllowedOutboundBytesAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0), BlockedOutboundBytesAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(BytesSrcToDest), 0)\r\n | extend AllowedInboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtSrc = iff(FlowStatus == 'Allowed' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtSrc = iff(FlowStatus == 'Denied' and FlowDirection == 'Outbound', tolong(PacketsSrcToDest), 0), AllowedInboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), BlockedInboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsDestToSrc), 0), AllowedOutboundPacketsAtDest = iff(FlowStatus == 'Allowed' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0), BlockedOutboundPacketsAtDest = iff(FlowStatus == 'Denied' and FlowDirection == 'Inbound', tolong(PacketsSrcToDest), 0)\r\n | extend AllowedOutbound_inferred = max_of(AllowedOutFlows, AllowedInFlows + DeniedInFlows)\r\n | extend Outbound = AllowedOutbound_inferred + DeniedOutFlows, Inbound = AllowedOutbound_inferred\r\n | extend FlowCount = max_of(Inbound, Outbound)\r\n //infrered \r\n | extend AllowedOutboundBytesAtSrc_inferred = max_of(AllowedOutboundBytesAtSrc, AllowedInboundBytesAtDest + BlockedInboundBytesAtDest) \r\n | extend AllowedOutboundBytesAtDest_inferred = max_of(AllowedOutboundBytesAtDest, AllowedInboundBytesAtSrc + BlockedInboundBytesAtSrc) \r\n | extend OutboundBytesAtSrc = AllowedOutboundBytesAtSrc_inferred + BlockedOutboundBytesAtSrc, InboundBytesAtSrc = AllowedOutboundBytesAtDest_inferred \r\n | extend OutboundBytesAtDest = AllowedOutboundBytesAtDest_inferred + BlockedOutboundBytesAtDest, InboundBytesAtDest = AllowedOutboundBytesAtSrc_inferred \r\n | extend BytesCount = max_of(OutboundBytesAtSrc, InboundBytesAtDest) + max_of(OutboundBytesAtDest, InboundBytesAtSrc) \r\n | extend AllowedOutboundPacketsAtSrc_inferred = max_of(AllowedOutboundPacketsAtSrc, AllowedInboundPacketsAtDest + BlockedInboundPacketsAtDest) \r\n | extend AllowedOutboundPacketsAtDest_inferred = max_of(AllowedOutboundPacketsAtDest, AllowedInboundPacketsAtSrc + BlockedInboundPacketsAtSrc) \r\n | extend OutboundPacketsAtSrc = AllowedOutboundPacketsAtSrc_inferred + BlockedOutboundPacketsAtSrc, InboundPacketsAtSrc = AllowedOutboundPacketsAtDest_inferred \r\n | extend OutboundPacketsAtDest = AllowedOutboundPacketsAtDest_inferred + BlockedOutboundPacketsAtDest, InboundPacketsAtDest = AllowedOutboundPacketsAtSrc_inferred \r\n | extend PacketsCount = max_of(OutboundPacketsAtSrc, InboundPacketsAtDest) + max_of(OutboundPacketsAtDest, InboundPacketsAtSrc)\r\n | summarize TotalTraffic = sum(iff('{gridUnit:value}' =~ 'bytes', BytesCount, iff('{gridUnit:value}' =~ 'packets', PacketsCount, tolong(FlowCount)))) by ConnectionName, Gateway\r\n | top-nested of Gateway by max(1), top-nested 3 of ConnectionName by max(TotalTraffic)\r\n | project Gateway, ConnectionName\r\n | summarize topConnections = strcat_array(make_list(ConnectionName), ',') by Gateway\r\n | project Gateway, topConnections;\r\n//getSkuDataERGw\r\nlet table6 = NTATopologyDetails\r\n | where SubType == 'Topology' and AzureResourceType == 'VirtualNetworkGateway' \r\n | where strcat(Subscription, '/', Name) == '9cece3e3-0f7d-47ca-af0e-9772773f90b7/er-lab/er-lab-gw'\r\n | extend Gateway = strcat(Subscription, '/', Name)\r\n | distinct Sku, Gateway;\r\n//\r\ntable1\r\n| join kind= fullouter table2 on $left.Gateway == $right.Gateway\r\n| join kind=leftouter table3 on $left.Gateway == $right.Gateway\r\n| join kind=leftouter table4 on $left.Gateway == $right.Gateway\r\n| join kind=leftouter table5 on $left.Gateway == $right.Gateway\r\n| join kind=leftouter table6 on $left.Gateway == $right.Gateway\r\n| sort by trafficForSorting desc\r\n| project Gateway, Sku, topConnections, topHostsDataSrc, topHostsDataDest, topDestPort, InboundAllowed, InboundBlocked, OutboundAllowed, OutboundBlocked",
"size": 0,
"queryType": 0,
"resourceType": "microsoft.operationalinsights/workspaces",
"crossComponentResources": [
"{workspace}"
]
},
"name": "query - 15"
}
],
"fallbackResourceIds": [
"Azure Monitor"
],
"$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}