While both Microsoft Sentinel and Advanced Hunting leverage KQL, there are differences in schema in certain tables. For instance, TimeGenerated is used in Sentinel while Timestamp is used in Advanced Hunting.
The below tables are designed to help you convert queries between the two products.
For Microsoft Sentinel sign in logs are kept in two separate tables.
SigninLogs - for interactive signins
SigninLogs
| take 10AADNonInteractiveUserSignInLogs - for non-interactive signins
AADNonInteractiveUserSignInLogs
| take 10For Advanced Hunting both types of logs are kept in the same table, but distinguised by a field.
For interactive signins
AADSignInEventsBeta
| where LogonType == @"[""interactiveUser""]"
| take 10For non-interactive sigins
AADSignInEventsBeta
| where LogonType == @"[""nonInteractiveUser""]"
| take 10Differences in schema are noted below.
| Field Description | Sentinel Examples | Advanced Hunting Examples | Notes |
|---|---|---|---|
| Time | SigninLogs | where TimeGenerated > ago (7d) |
AADSignInEventsBeta | where Timestamp > ago(7d) |
TimeGenerated becomes Timestamp |
| Username | SigninLogs | where UserPrincipalName == "[email protected]" |
AADSignInEventsBeta | where AccountUpn == "[email protected]" |
UserPrincipalName becomes AccountUpn |
| Account Display Name | SigninLogs | where UserDisplayName== "Jane Citizen" |
AADSignInEventsBeta | where AccountDisplayName == "Jane Citizen" |
UserDisplayName becomes AccountDisplayName |
| Azure AD Object Id | SigninLogs | where UserId == "33d3f3dd-1e47-4cbd-82dd-93e17e0107f2" |
AADSignInEventsBeta | where AccountObjectId == "33d3f3dd-1e47-4cbd-82dd-93e17e0107f2" |
UserId becomes AccountObjectId |
| Application Name | SigninLogs | where AppDisplayName == "Microsoft Teams" |
AADSignInEventsBeta | where Application == "Microsoft Teams" |
AppDisplayName becomes Application |
| Application Id | SigninLogs | where AppId == "00000002-0000-0ff1-ce00-000000000000" |
AADSignInEventsBeta | where ApplicationId == "00000002-0000-0ff1-ce00-000000000000" |
AppId becomes ApplicationId |
| Guest User | SigninLogs | where UserType == "Guest" |
AADSignInEventsBeta | where IsGuestUser == "1" |
UserType = guest becomes IsGuestUser = 1 |
| Status | SigninLogs | where ResultType == "50126" |
AADSignInEventsBeta | where ErrorCode == "50126" |
ResultType becomes ErrorCode |
| Conditional Access Status | SigninLogs | where ConditionalAccessStatus == "success" |
AADSignInEventsBeta | where ConditionalAccessStatus == "0" |
Sentinel uses strings vs advanced hunting numbers. success = 0, failure = 1, notApplied = 2 |
| Device Name | SigninLogs | extend DeviceName = tostring(DeviceDetail.displayName) | where DeviceName == "LAPTOP1234" |
ADSignInEventsBeta | where DeviceName == "LAPTOP1234" |
Sentinel keeps device name in a nested field and has to be extracted first |
| Device Id | SigninLogs | extend deviceId = tostring(DeviceDetail.deviceId) | where deviceId = "33f17af6-82c4-4a91-8e4f-6b5ba417efbf" |
AADSignInEventsBeta | where AadDeviceId == "33f17af6-82c4-4a91-8e4f-6b5ba417efbf" |
Sentinel keeps device id in a nested field and has to be extracted first |
| Device Trust Type | SigninLogs | extend trustType = tostring(DeviceDetail.trustType) | where trustType == "Azure AD registered" |
AADSignInEventsBeta | where DeviceTrustType == "Azure AD registered" |
Sentinel keeps trust type in a nested field and has to be extracted first |
| Device OS | SigninLogs | extend DeviceOS = tostring(DeviceDetail.operatingSystem) | where DeviceOS = "Windows 10" |
AADSignInEventsBeta | where OSPlatform == "Windows 10" |
Sentinel keeps OS in a nested field and has to be extracted first |
| Browser | SigninLogs | extend Browser = tostring(DeviceDetail.browser) | where Browser == "Edge 98.0.1108" |
AADSignInEventsBeta | where Browser == "Edge 98.0.1108" |
Sentinel keeps browser in a nested field and has to be extracted first |
| City | SigninLogs | extend City = tostring(LocationDetails.city) | where City == "Melbourne" |
AADSignInEventsBeta | where City == "Melbourne" |
Sentinel keeps City in a nested field and has to be extracted first |
| Country | SigninLogs | extend Country = tostring(LocationDetails.countryOrRegion) | where Country == "AU" |
AADSignInEventsBeta | where Country == "AU" |
Sentinel keeps Country in a nested field and has to be extracted first |
| Latitude | SigninLogs | extend Latitude = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).latitude) | where Latitude contains "49.25" |
AADSignInEventsBeta | where Latitude contains "49.25" |
Sentinel keeps Latitude in a nested field and has to be extracted first |
| Longitude | SigninLogs | extend Longitude = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).longitude) | where Longitude contains "-122" |
AADSignInEventsBeta | where Longitude contains "-122" |
Sentinel keeps Longitude in a nested field and has to be extracted first |
| State | SigninLogs | extend State = tostring(LocationDetails.state) | where State == "British Columbia" |
AADSignInEventsBeta | where State == "British Columbia" |
Sentinel keeps State in a nested field and has to be extracted first |
| Combined Example | SigninLogs | extend State = tostring(LocationDetails.state) | where UserType == "Guest" and State == "British Columbia" and ResultType == "0" and AppDisplayName == "Microsoft Teams" |
AADSignInEventsBeta | where IsGuestUser == "1" and State == "British Columbia" and ErrorCode == "0" and Application == "Microsoft Teams" |
Look for guests in British Columbia successfully signing into Teams |
A number of fields are the same across both tables such as UserAgent or ClientAppUsed, and some only exist in one. For example, Advanced Hunting has LastPasswordChangeTimestamp whereas Sentinel does not. In general, the Sentinel logs are more detailed.
Microsoft Sentinel keeps service principal sign in logs in two tables. For regular service principals they are sent to the AADServicePrincipalSignInLogs table.
AADServicePrincipalSignInLogs
| take 10For managed identities they are sent to the AADManagedIdentitySignInLogs
AADManagedIdentitySignInLogs
| take 10For Advanced Hunting both types of logs are kept in the same table, but distinguised by a field.
For regular service principals.
AADSpnSignInEventsBeta
| where IsManagedIdentity == 0For managed identities
AADSpnSignInEventsBeta
| where IsManagedIdentity == 1Differences in schema are noted below.
| Field Description | Sentinel Example | Advanced Hunting Example | Notes |
|---|---|---|---|
| Time | AADServicePrincipalSignInLogs | where TimeGenerated > ago (7d) |
AADSpnSignInEventsBeta | where Timestamp > ago(7d) |
TimeGenerated becomes Timestamp |
| Application Id | AADServicePrincipalSignInLogs | where AppId == "00000002-0000-0ff1-ce00-000000000000" |
AADSpnSignInEventsBeta | where ApplicationId == "00000002-0000-0ff1-ce00-000000000000" |
AppId becomes ApplicationId |
| Status | AADServicePrincipalSignInLogs | where ResultType == "7000215" |
AADSpnSignInEventsBeta | where ErrorCode == "7000215" |
ResultType becomes ErrorCode |
| City | AADServicePrincipalSignInLogs | extend City = tostring(LocationDetails.city) | where City == "Melbourne" |
AADSpnSignInEventsBeta | where City == "Melbourne" |
Sentinel keeps City in a nested field and has to be extracted first |
| Country | AADServicePrincipalSignInLogs | extend Country = tostring(LocationDetails.countryOrRegion) | where Country == "AU" |
AADSpnSignInEventsBeta | where Country == "AU" |
Sentinel keeps Country in a nested field and has to be extracted first |
| Latitude | AADServicePrincipalSignInLogs | extend Latitude = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).latitude) | where Latitude contains "49.25" |
AADSpnSignInEventsBeta | where Latitude contains "49.25" |
Sentinel keeps Latitude in a nested field and has to be extracted first |
| Longitude | AADServicePrincipalSignInLogs | extend Longitude = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).longitude) | where Longitude contains "-122" |
AADSpnSignInEventsBeta | where Longitude contains "-122" |
Sentinel keeps Longitude in a nested field and has to be extracted first |
| State | AADServicePrincipalSignInLogs | extend State = tostring(LocationDetails.state) | where State == "British Columbia" |
AADSpnSignInEventsBeta | where State == "British Columbia" |
Sentinel keeps State in a nested field and has to be extracted first |
A number of fields are the same across both products such as ServicePrincipalId and ServicePrincipalName. The Advanced Hunting logs for Service Principals have quite a bit less information than Microsoft Sentinel, and omit things like Conditional Access entirely.