/
MicrosoftConnectorUsage.kql
62 lines (59 loc) · 3.03 KB
/
MicrosoftConnectorUsage.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
// KQL samples - Web service requests (OData) from the Microsoft connectors for PowerBI, PowerApps, LogicApps, and Flow
// httpHeaders and httpStatusCode only available from 16.3
traces
| where timestamp > ago(14d) // adjust as needed
| where customDimensions.eventId == 'RT0008'
| where customDimensions.category == 'ODataV4'
// httpHeaders and httpStatusCode only available from 16.3
| extend httpHeadersTmp = tostring( customDimensions.httpHeaders)
| extend httpHeadersJSON = parse_json(httpHeadersTmp)
| extend msUserAgent = tostring( httpHeadersJSON.['ms-dyn-useragent'] )
| extend IsMsConnector =
msUserAgent has 'AzureConnector' or
msUserAgent has 'PowerBIConnector' or
msUserAgent has 'BusinessCentralLinkUnfurlingTeamsBot' or
msUserAgent has 'TeamsIntegrationService'
| extend httpStatusCode = customDimensions.httpStatusCode
, aadTenantId = customDimensions.aadTenantId
, environmentName = customDimensions.environmentName
, environmentType = customDimensions.environmentType
, alObjectId = customDimensions.alObjectId
, alObjectName = customDimensions.alObjectName
, alObjectType = customDimensions.alObjectType
, endpoint = customDimensions.endpoint
, IsMsConnector
, connector = case(
// ms-dyn-useragent=AzureConnector/1.0 Flow/1.0 DynamicsSmbSaas/1.0.0.0
msUserAgent matches regex 'AzureConnector/(.)+Flow', 'Power Automate'
//
// ms-dyn-useragent="PowerBIConnector/1.0 " & Host & "/1.0 Dynamics365BusinessCentral/2.0.7"
// where Host in ["PowerBIDesktop", "PowerQueryOnline", "Gateway", "PowerBI", "UnknownHost"]
, msUserAgent matches regex 'PowerBIConnector/'
and msUserAgent matches regex 'PowerBIDesktop', 'Power BI (Desktop)'
, msUserAgent matches regex 'PowerBIConnector/'
and msUserAgent matches regex 'PowerQueryOnline', 'PowerQuery Online'
, msUserAgent matches regex 'PowerBIConnector/'
and msUserAgent matches regex 'Gateway', 'Power BI (Gateway)'
, msUserAgent matches regex 'PowerBIConnector/'
and msUserAgent matches regex 'PowerBI', 'Power BI (Online)'
, msUserAgent matches regex 'PowerBIConnector/'
and msUserAgent matches regex 'UnknownHost', 'Power BI (UnknownHost)'
//
// ms-dyn-useragent=['PowerBIConnector/1.0 PowerBI/1.0 Dynamics365BusinessCentral/1.1.5']
, msUserAgent matches regex 'PowerBIConnector/', 'Power BI'
//
// ms-dyn-useragent=AzureConnector/1.0 PowerApps/3.20092.39 DynamicsSmbSaas/1.0.0.0
, msUserAgent matches regex 'AzureConnector/(.)+PowerApps', 'PowerApps'
//
// ms-dyn-useragent=AzureConnector/1.0 LogicApps/3.20092.39 DynamicsSmbSaas/1.0.0.0
, msUserAgent matches regex 'AzureConnector/(.)+LogicApps', 'LogicApps'
//
// ms-dyn-useragent=BusinessCentralLinkUnfurlingTeamsBot
, msUserAgent has 'BusinessCentralLinkUnfurlingTeamsBot', 'Microsoft Teams'
//
// ms-dyn-useragent=TeamsIntegrationService
, msUserAgent has 'TeamsIntegrationService', 'Microsoft Teams'
, msUserAgent has 'AzureConnector', 'Unknown Azure connector'
, 'Unknown connector'
)
| project timestamp, msUserAgent, httpStatusCode, aadTenantId, environmentName, environmentType, IsMsConnector, connector, endpoint, alObjectId, alObjectName, message