Skip to content

Commit

Permalink
Updated queries so that they understands eventId
Browse files Browse the repository at this point in the history
  • Loading branch information
@KennieNP
KennieNP committed May 9, 2020
1 parent e0a79d0 commit 22c881a
Showing 1 changed file with 11 additions and 97 deletions.
108 changes: 11 additions & 97 deletions samples/AppInsights/KQL/Authorization.kql
View file
Original file line number Diff line number Diff line change
@@ -1,79 +1,44 @@
// KQL samples - Authorization

// successful authorization prior to openCompany trigger (raw data)
// Use extend if you need to filter on a column extracted from customDimensions
// This example uses project-away to filter out all "internal" columns
traces
| where timestamp > ago(60d) // adjust as needed
| where operation_Name == "Authorization Succeeded (Pre Open Company)" // do note that in a later version of the schema, this field will not be used (a new field in custom dimensions will be used)
// | where customDimensions.eventCode == 'TODO - coming in a later version of the schema'
| extend aadID = customDimensions.aadTenantId
| extend environmentName = customDimensions.environmentName
| extend environmentType = customDimensions.environmentType
| extend guestUser = customDimensions.guestUser
| extend userType = customDimensions.userType
| extend entitlementSetIds = customDimensions.entitlementSetIds
| project-away severityLevel, itemType, customMeasurements, client_Browser, client_City, client_CountryOrRegion, client_IP, client_OS, client_Type, client_StateOrProvince, client_Model , operation_SyntheticSource, operation_ParentId, user_Id, user_AuthenticatedId, user_AccountId , application_Version, sdkVersion, iKey, appId, appName, itemId, itemCount, operation_Name, operation_Id
// Use renames in the project operator if you don't need to filter on the columns extracted from customDimensions
// move columns up to the extend operator and add a tostring function call if you need to filter or summarize on it

// successful authorization prior to openCompany trigger (raw data)
// Use renames in the project operator if you don't need to filter on the columns extracted from customDimensions
// This example uses project to only include the columns needed in the result set
traces
| where timestamp > ago(60d) // adjust as needed
| where operation_Name == "Authorization Succeeded (Pre Open Company)" // do note that in a later version of the schema, this field will not be used (a new field in custom dimensions will be used)
// | where customDimensions.eventCode == 'TODO - coming in a later version of the schema'
or customDimensions.eventId == "RT0003" // starting from version 16.1, the eventId is used to identity signal types
| extend aadID = customDimensions.aadTenantId
| project aadID
, eventId = customDimensions.eventId
, environmentName = customDimensions.environmentName
, environmentType = customDimensions.environmentType
, guestUser = customDimensions.guestUser
, userType = customDimensions.userType
, entitlementSetIds = customDimensions.entitlementSetIds
, message, customDimensions


// failed authorization prior to openCompany trigger (raw data)
// Use extend if you need to filter on a column extracted from customDimensions
// This example uses project-away to filter out all "internal" columns
traces
| where timestamp > ago(60d) // adjust as needed
| where operation_Name == "Authorization Failed (Pre Open Company)" // do note that in a later version of the schema, this field will not be used (a new field in custom dimensions will be used)
// | where customDimensions.eventCode == 'TODO - coming in a later version of the schema'
or customDimensions.eventId == "RT0001" // starting from version 16.1, the eventId is used to identity signal types
| extend aadID = customDimensions.aadTenantId
, eventId = customDimensions.eventId
, environmentName = customDimensions.environmentName
, environmentType = customDimensions.environmentType
, guestUser = customDimensions.guestUser
, userType = customDimensions.userType
, failureReason = customDimensions.failureReason
, entitlementSetIds = customDimensions.entitlementSetIds
| project-away severityLevel, itemType, customMeasurements, client_Browser, client_City, client_CountryOrRegion, client_IP, client_OS, client_Type, client_StateOrProvince, client_Model , operation_SyntheticSource, operation_ParentId, user_Id, user_AuthenticatedId, user_AccountId , application_Version, sdkVersion, iKey, appId, appName, itemId, itemCount, operation_Name, operation_Id


// failed authorization prior to openCompany trigger (raw data)
// Use renames in the project operator if you don't need to filter on the columns extracted from customDimensions
// This example uses project to only include the columns needed in the result set
traces
| where timestamp > ago(60d) // adjust as needed
| where operation_Name == "Authorization Failed (Pre Open Company)" // do note that in a later version of the schema, this field will not be used (a new field in custom dimensions will be used)
// | where customDimensions.eventCode == 'TODO - coming in a later version of the schema'
| extend aadID = customDimensions.aadTenantId
| project aadID
, environmentName = customDimensions.environmentName
, environmentType = customDimensions.environmentType
, guestUser = customDimensions.guestUser
, userType = customDimensions.userType
, failureReason = customDimensions.failureReason
, entitlementSetIds = customDimensions.entitlementSetIds
, message, customDimensions

// successful authorization after the openCompany trigger (raw data)
// Use extend if you need to filter on a column extracted from customDimensions
// This example uses project-away to filter out all "internal" columns
traces
| where timestamp > ago(60d) // adjust as needed
| where operation_Name == "Authorization Succeeded (Open Company)" // do note that in a later version of the schema, this field will not be used (a new field in custom dimensions will be used)
// | where customDimensions.eventCode == 'TODO - coming in a later version of the schema'
or customDimensions.eventId == "RT0004" // starting from version 16.1, the eventId is used to identity signal types
| extend aadID = customDimensions.aadTenantId
, eventId = customDimensions.eventId
| extend environmentName = customDimensions.environmentName
| extend environmentType = customDimensions.environmentType
| extend clientType = customDimensions.clientType
Expand All @@ -84,39 +49,15 @@ traces
| extend sqlExecutes = customDimensions.sqlExecutes
| extend sqlRowsRead = customDimensions.sqlRowsRead
| extend totalTime = customDimensions.totalTime
| project-away severityLevel, itemType, customMeasurements, client_Browser, client_City, client_CountryOrRegion, client_IP, client_OS, client_Type, client_StateOrProvince, client_Model , operation_SyntheticSource, operation_ParentId, user_Id, user_AuthenticatedId, user_AccountId , application_Version, sdkVersion, iKey, appId, appName, itemId, itemCount, operation_Name, operation_Id


// successful authorization after the openCompany trigger (raw data)
// Use renames in the project operator if you don't need to filter on the columns extracted from customDimensions
// This example uses project to only include the columns needed in the result set
traces
| where timestamp > ago(60d) // adjust as needed
| where operation_Name == "Authorization Succeeded (Open Company)" // do note that in a later version of the schema, this field will not be used (a new field in custom dimensions will be used)
// | where customDimensions.eventCode == 'TODO - coming in a later version of the schema'
| extend aadID = customDimensions.aadTenantId
| project aadID
, environmentName = customDimensions.environmentName
, environmentType = customDimensions.environmentType
, clientType = customDimensions.clientType
, companyName = customDimensions.companyName
, result = customDimensions.result
// server timings are available from 16.0
, serverExecutionTime = customDimensions.serverExecutionTime
, sqlExecutes = customDimensions.sqlExecutes
, sqlRowsRead = customDimensions.sqlRowsRead
, totalTime = customDimensions.totalTime
, message, customDimensions


// failed authorization after the openCompany trigger (raw data)
// Use extend if you need to filter on a column extracted from customDimensions
// This example uses project-away to filter out all "internal" columns
traces
| where timestamp > ago(60d) // adjust as needed
| where operation_Name == "Authorization Failed (Open Company)" // do note that in a later version of the schema, this field will not be used (a new field in custom dimensions will be used)
// | where customDimensions.eventCode == 'TODO - coming in a later version of the schema'
or customDimensions.eventId == "RT0002" // starting from version 16.1, the eventId is used to identity signal types
| extend aadID = customDimensions.aadTenantId
, eventId = customDimensions.eventId
| extend environmentName = customDimensions.environmentName
| extend environmentType = customDimensions.environmentType
| extend clientType = customDimensions.clientType
Expand All @@ -126,31 +67,4 @@ traces
| extend serverExecutionTime = customDimensions.serverExecutionTime
| extend sqlExecutes = customDimensions.sqlExecutes
| extend sqlRowsRead = customDimensions.sqlRowsRead
| extend totalTime = customDimensions.totalTime
| project-away severityLevel, itemType, customMeasurements, client_Browser, client_City, client_CountryOrRegion, client_IP, client_OS, client_Type, client_StateOrProvince, client_Model , operation_SyntheticSource, operation_ParentId, user_Id, user_AuthenticatedId, user_AccountId , application_Version, sdkVersion, iKey, appId, appName, itemId, itemCount, operation_Name, operation_Id


// failed authorization after the openCompany trigger (raw data)
// Use renames in the project operator if you don't need to filter on the columns extracted from customDimensions
// This example uses project to only include the columns needed in the result set
traces
| where timestamp > ago(60d) // adjust as needed
| where operation_Name == "Authorization Failed (Open Company)" // do note that in a later version of the schema, this field will not be used (a new field in custom dimensions will be used)
// | where customDimensions.eventCode == 'TODO - coming in a later version of the schema'
| extend aadID = customDimensions.aadTenantId
| project aadID
, environmentName = customDimensions.environmentName
, environmentType = customDimensions.environmentType
, clientType = customDimensions.clientType
, companyName = customDimensions.companyName
, failureReason = customDimensions.failureReason
// server timings are available from 16.0
, serverExecutionTime = customDimensions.serverExecutionTime
, sqlExecutes = customDimensions.sqlExecutes
, sqlRowsRead = customDimensions.sqlRowsRead
, totalTime = customDimensions.totalTime
, message
//, customDimensions



| extend totalTime = customDimensions.totalTime

0 comments on commit 22c881a

Please sign in to comment.