Graph Activity From Other Ip Address
Query
// Microsoft Graph Activity from IP Address which is different from sign-in
MicrosoftGraphActivityLogs
| project TimeGenerated, RequestId, ApiVersion, RequestMethod, ResponseStatusCode, ActivityIpAddress = IPAddress, UserAgent, RequestUri, Roles, AppId, Wids, SignInActivityId
| join kind=inner (union AADServicePrincipalSignInLogs, AADManagedIdentitySignInLogs
| project ConditionalAccessPolicies, ConditionalAccessStatus, ServicePrincipalCredentialKeyId, SignInIpAddress = IPAddress, UniqueTokenIdentifier, Type
// Correlation between Activity and Sign-in based on Unique Token Identifier
) on $left.SignInActivityId == $right.UniqueTokenIdentifier
// AADManagedIdentitySignInLogs does not include SignInIpAddress
| where ActivityIpAddress != SignInIpAddress and isnotempty(SignInIpAddress)
| project TimeGenerated, RequestId, ActivityIpAddress, SignInIpAddress, ConditionalAccessStatus, ServicePrincipalCredentialKeyId, UserAgent, RequestMethod, RequestUriExplanation
This query is designed to identify instances where Microsoft Graph activities are performed from an IP address that differs from the IP address used during the sign-in process. Here's a simplified breakdown of what the query does:
-
Source Data: It starts by pulling data from
MicrosoftGraphActivityLogs, which contains logs of activities performed using Microsoft Graph. -
Projection: It selects specific fields from these logs, such as the time the activity was generated, the request ID, the API version, the method of the request, the response status code, the IP address used for the activity, and other relevant details.
-
Joining Data: The query then joins this data with logs from
AADServicePrincipalSignInLogsandAADManagedIdentitySignInLogs. These logs contain information about sign-ins, including the IP address used during the sign-in. -
Correlation: The join is based on a unique token identifier that links the activity to the corresponding sign-in event.
-
Filtering: It filters the results to find cases where the IP address used for the activity (
ActivityIpAddress) is different from the IP address used during sign-in (SignInIpAddress). It also ensures that the sign-in IP address is not empty, which means it only considers records where a sign-in IP address is available. -
Final Output: The query outputs a list of these mismatched IP address events, including details like the time of the activity, request ID, both IP addresses, the status of conditional access, the credential key ID, user agent, request method, and request URI.
In essence, this query helps in identifying potential security issues where activities are being performed from unexpected IP addresses, which could indicate unauthorized access or misuse.
Details
Thomas Naunheim
Released: October 18, 2023
Tables
Keywords
Operators