Introduction
In modern cloud environments, protecting user identities is an absolute priority. Conditional Access policies represent the gold standard for strengthening access controls. However, many organizations face a recurring challenge: their employees travel frequently.
In such contexts, applying a strict conditional access policy limiting sign-ins to a fixed set of countries can prove impractical. Nevertheless, this does not mean you must remain blind to suspicious activities. This is where proactive monitoring with KQL (Kusto Query Language) comes in.
Why this approach?
When conditional access policies cannot block certain countries, administrators still need visibility into sign-in attempts from unexpected geographies, brute force attacks, and successful sign-ins from concerning locations.
Use Cases and Objectives
When conditional access cannot be applied to completely block certain countries, administrators need visibility into:
- Sign-in attempts from unexpected geographies
- Accounts targeted by brute force or password spray attacks
- Successful sign-ins from locations that should raise concerns
By continuously monitoring sign-in attempts outside your "trusted" geography, you can stay ahead and detect potential compromises early.
Technical Prerequisites
Before executing this solution, ensure you have the following:
Required Licenses
- Entra ID P1 or P2: Necessary to enable sign-in logs and advanced identity protection features
Azure Infrastructure
- Log Analytics Workspace: Centralizes ingestion and querying of sign-in logs
- Entra ID logs connected to Log Analytics
Access Permissions
Your administrator account must have at least the following roles:
- Security Reader or Security Administrator in Azure AD
- Log Analytics Contributor to execute and manage queries
Configuring the Log Analytics Workspace
Create the workspace
In the Azure portal, search for "Log Analytics Workspaces" and select Create.
Configure settings
Fill in the following information:
- Subscription: Select your Azure subscription
- Resource Group: Choose or create a resource group
- Name: Enter a descriptive name (e.g., "SecurityLogs")
- Region: Select the region closest to your organization
Validate and create
Review the configuration and click Create to deploy the workspace.
Connecting Entra ID Logs
Access diagnostic settings
Go to the Entra ID admin center at https://entra.microsoft.com/, then navigate to Monitoring > Diagnostic Settings.
Add a diagnostic setting
Click Add diagnostic setting and configure:
- Select SignInLogs and optionally AuditLogs
- Check Send to Log Analytics workspace
- Choose the workspace created previously
Save the configuration
Save the configuration. SigninLogs will appear in your workspace within a few minutes to an hour, depending on activity.
Ingestion delay
Once configured, you will begin to see SigninLogs in your workspace within a few minutes to an hour, depending on sign-in activity.
Executing the KQL Query
Access the workspace
In your Log Analytics workspace, select Logs from the left menu and close the Query hub by clicking the 'X'.
Configure KQL mode
Select KQL mode in your Log Analytics workspace.
Execute the query
Copy and paste the following KQL query to filter sign-in attempts from locations outside Canada (CA) and the United States (US):
1// Get a list of all sign-ins (successful, failed, revoked, etc.) outside of Canada (CA) and the United States (US)2SigninLogs 3| where Location !in ("CA", "US") and isnotempty(Location) // Location different from CANADA, US4| extend LocalTime = datetime_add('hour', -5, TimeGenerated) // UTC-5 (CANADA Time)5| project LocalTime, UserPrincipalName, UserDisplayName, Identity, ResourceDisplayName, Location, IPAddress, RiskState, ConditionalAccessStatus, ResultType, ResultSignature, ResultDescription, MfaDetail, RiskDetail6| order by LocalTime descClick Run to execute the query.
Customizing locations
You can modify the locations by changing "CA" and "US" in the third line of code and adding your specific locations.
The results will include details such as: LocalTime, UserPrincipalName, Location, IPAddress, RiskState, ResultDescription.
Saving the KQL Query
Save the query
Click the save button and fill in:
- Query name: "Sign-in attempts from unusual locations"
- Description: "Detection of sign-in attempts from suspicious geographic locations"
- Resource type: "Log Analytics Workspaces"
- Category: "Audit"
Memorization
Keep the query name in a notepad, as we will use it later for alert configuration.
Configuring Automated Alerts
Create an alert rule
In the Log Analytics workspace, click "..." (three dots) in the top right, then select New alert rule.
Configure the condition
In the Condition section:
- Signal name: "Custom log search"
- Query type: Aggregated logs
- Paste your KQL query into the text area
In the Measurement section:
- Measurement: "Table rows"
- Aggregation type: "Count"
- Aggregation granularity: "30 minutes"
Define alert logic
Configure the following settings:
- Operator: "Greater than"
- Threshold value: 0
- Time aggregation: 30 minutes
- Override query time range: 30 min
Configure actions
In the Action section:
- Select Quick Actions (Preview)
- Action group name: "Suspicious Sign-In Email Notification"
- Display name: "Conn-Suspicious"
- Notification emails: Add the email addresses that should receive the alert
Customize the alert email subject, for example: "Alert - Sign-in from an unusual location".
Finalize the configuration
Configure the final details:
- Azure Subscription: Select the appropriate subscription
- Resource Group: Choose the resource group
- Severity Level: 2 – Warning
- Descriptive name: "Sign-in attempts from unusual locations"
- Region: Choose the same region as your other resources
Click Create to finalize and deploy the alert rule.
Testing and Validating the System
To validate that your detection system is working properly, you can simulate a sign-in from an unusual location. In the example below, a sign-in attempt with an incorrect password from a VPN located in the United Kingdom immediately generates a notification.
Email Notification
Here is an example of an email notification sent to the help desk:
Verifying Results
To verify the query results directly:
- Return to the Log Analytics workspace
- Select "Logs" from the left menu
- Type the name of your saved KQL query in the search bar
- Click "Run"
Detailed results display in the table:
Advanced Optimizations
Adding to Favorites
For quick access to your query:
- Type the name of your query in the search bar
- Click the star icon to add it to favorites
Excluding Trusted IP Addresses
If you want to exclude certain trusted locations or IP addresses (offices, approved IP addresses), use this modified KQL query:
1// Office IP addresses to exclude from triggering2let AllowedIPs = dynamic([ "1.1.1.1", "8.8.8.8" ]);3// Get a list of all sign-ins outside the country list + IP list4SigninLogs 5| where Location !in ("CA", "US") and isnotempty(Location) // Location different from CANADA, US6| where IPAddress !in~ (AllowedIPs) // exclude office IP addresses7| extend LocalTime = datetime_add('hour', -5, TimeGenerated) // UTC-5 (CANADA Time)8| project LocalTime, UserPrincipalName, UserDisplayName, Identity, ResourceDisplayName, Location, IPAddress, RiskState, ConditionalAccessStatus, ResultType, ResultSignature, ResultDescription, MfaDetail, RiskDetail9| order by LocalTime descIP customization
Do not forget to replace the example IP addresses ("1.1.1.1", "8.8.8.8") with the actual IP addresses of your offices or trusted addresses.
Conclusion
This proactive monitoring solution enables you to maintain visibility over suspicious sign-in activities while preserving the flexibility needed for traveling employees. By combining KQL, Log Analytics, and Azure alerts, you have a robust detection system that effectively complements your existing conditional access policies.
Implementing this continuous monitoring constitutes an essential additional security layer in your Microsoft 365 identity protection strategy.
