Proactive M365 Threat Detection : Live Detection of Sign-In Attempts from Unusual Locations

In modern cloud environments, protecting user identities is a top priority. Conditional Access (CA) policies are the gold standard to enforce access controls, but many organizations face a common challenge : employees frequently travel.

In such cases, enforcing a strict CA policy that only allows logins from a fixed set of countries (e.g., Canada, United States) may not be feasible. However, this doesn’t mean you should remain blind to suspicious activity. That’s where proactive monitoring with KQL (Kusto Query Language) becomes invaluable.

The Use Case

When Conditional Access cannot be applied to block countries outright, administrators still need visibility into :

• Login attempts from unexpected geographies
• Accounts targeted in brute force or password spray attacks
• Successful sign-ins from locations that should raise concern

By continuously monitoring login attempts outside of your “trusted” geography, you can stay one step ahead and detect potential compromises early.

Prerequisites

Before running this query, ensure you have the following in place:

• Entra ID P1 or P2 : Required to enable Sign-in logs and advanced identity protection features.
• Log Analytics Workspace : This is where all the sign-in logs will be ingested and queried, if you don’t have Log Analytics Workspace, I’ll explain how to set it up in the next section.
• Connected Entra ID Logs to Log Analytics
• Access Permissions :
  • Ensure your admin account has at least the following roles:
  • Security Reader or Security Administrator in Azure AD.
  • Log Analytics Contributor to run and manage queries.

Create Log Analytics Workspace

1. In the Azure portal, search for Log Analytics Workspaces.

1. Select Create.

1. Choose your Subscription and Resource Group.
2. Enter a Name for the workspace (e.g., SecurityLogs).
3. Select the Region closest to your organization.
4. Review and Create.

Connect Entra ID Logs to Log Analytics

1. In the Azure portal, go to Entra ID admin Center : https://entra.microsoft.com/
2. Navigate to Monitoring > Diagnostic settings.
3. Click Add diagnostic setting.

1. Select SignInLogs and (optionally) AuditLogs.
2. Check Send to Log Analytics Workspace.
3. Choose the workspace you just created.
4. Save the configuration.

Once this is set up, you will start seeing SigninLogs in your workspace within a few minutes to an hour, depending on activity.

Executing the KQL Query

In this example, I connected via VPN from both the United Kingdom and the Netherlands to validate the functionality of the KQL query. This approach served two key purposes: first, to ensure that the query returns actual results; and second, because we cannot move forward with creating notifications unless the query produces relevant data.

So, to be able to execute the query :

1. Go to your Log Analytics workspace (“GIT-TEST-LogAnalytics” in my case) in the top navigation bar.
2. Select “Logs” from the left-hand menu.
3. Close the Queries hub by clicking the ‘X’ in the top right corner of that section.

• Select KQL mode in your Log Analytics workspace.
• Copy/Paste the KQL query to filter sign-in attempts from locations outside Canada (CA) and the United States (US).
• Click “Run” to execute the query.

Review the results in the table below, which includes details like : LocalTime, UserPrincipalName , Location , IPAddress, RiskState, ResultDescription.

Below is the KQL query. This query detects all sign-in attempts (successful or failed) outside of Canada (CA) and the United States (US), then enriches the results with a human-readable meaning for each result type.

You can change locations (third line of code) by changing “CA” and “US” then adding your specific locations.

// Get list of all connections (success, Failed, revoked...etc) outside of of Canada (CA) and the United States (US) 
SigninLogs 
| where Location !in ("CA", "US") and isnotempty(Location) // Location different from CANADA, US 
| extend LocalTime = datetime_add('hour', -5, TimeGenerated) // UTC-5 (CANADA Time) 
| project LocalTime, UserPrincipalName, UserDisplayName , Identity, ResourceDisplayName , Location , IPAddress, RiskState, ConditionalAccessStatus, ResultType, ResultSignature, ResultDescription, MfaDetail, RiskDetail
| order by LocalTime desc

Here is the result of executing the KQL query :

Saving as KQL Query

• Enter a Query Name : Example : "List of Sign-in Attempts". This is the title under which your query will be saved for future use.
• Add a Description : Example : "Sign-in Attempts from Unusual Locations". Helps explain the purpose of the query to other users or future you.
• Resource Type: Select "Log Analytics workspaces"
• Category: Choose a relevant category like "Audit"
• Label : Optionally, select or create a label to organize your queries.
• Click “Save”.

This saves the query with all the metadata so it can be reused or referenced later.

Keep the query name in Notepad because we’ll use it later (List of Sign-in Attempts).

Create new Alert

• Always in the log Analitycs Workspace.
• On the top right, click on “…” (three points), then select “New Alert Rule“.

In the Condition section:

• Set the Signal name to “Custom log search”.
• Choose the appropriate Query type (e.g., Aggregated logs, Single log, or Multiple logs).
• Paste your KQL query in the text box to define the condition that will trigger the alert.

In the Measurement section:

• Set Measure to “Table rows”.
• Choose “Count” as the Aggregation type.
• Set the Aggregation granularity to “30 minutes” (or another interval depending on your needs).

Aggregation granularity Defines the time window over which the data is aggregated. Azure will evaluate the query every 30 minutes and count how many matching rows appear in that period.

Click “Next: Actions” to configure what happens when the alert is triggered (e.g., email, webhook, automation).

Define Alert Logic

• Set the operator to “Greater than” to define the condition.
• Enter the threshold value (0) that will trigger the alert if the KQL query rows are greater than 0.
• Choose the time aggregation (e.g., 30 minutes) to specify how data is grouped over time.
• Override query time range : 30 min (or change it as your company needs)
• Click “Next Action” to continue.

In the “Action” section :

1. Select Quick Actions (Preview) : Choose the “Use quick actions (preview)” option to simplify the setup of notification actions.
2. Configure Action Group Details
   • Action Group Name : Set a name like "Unexpected Signin Email Notification" to identify the group.
   • Display Name: Provide a short label like "Unexp-Signin" for easier reference.
   • Notification Emails : Add email addresses that should receive the alert (e.g., helpdesk@globalitnow.com, admin@globalitnow.com).
   • Optionally, enable:
     • Email Azure Resource Manager Role
     • Azure mobile app notifications
3. Click the “Save” button to confirm and apply the notification settings.
4. Customize the subject line for alert emails, e.g., "Alert - Connection from Unusual Location".
5. Use “Next: Details >” to proceed with alert rule configuration.

• Choose the Azure subscription under which the alert will be created (e.g., Azure subscription 1).
• Select the resource group
• Set the severity level (e.g., 2 – Warning) to indicate the importance of the alert.
• Provide a descriptive name (e.g., Sign-in Attempts from Unusual Locations).
• Add a brief explanation of the alert’s purpose.
• Choose the region where the alert will be deployed (Keep in mind all ressources should be in same location)
• Click “Review + create”.

• Click Create to finalize and deploy the alert rule.

Test Connecting from Unusual Location

In this example, I’ll try to connect with wrong Password, I should receive notification in my system Ticket immediately

Here is the notification Email sent to HelpDesk :

Lets verify directly the Query results :

• Go back to Log Analytics Workspace.
• Select “Logs” from left hand menu.
• In search bar type the name of your KQL Saved Query.
• Click “Run“.

From this window you can see all KQL query results :

Bonus 1

Add your Query to Favorites for quick access.

• Type your Query Name in the search bar “List of Sign-in Attempts“.
• Click on the Star button to add your Query to Favorites.

Bonus 2

If you want to exclude some locations from Query results (Office location or Trusted IPs), you can use this KQL Query :

You need to change these IP Addresses (“1.1.1.1”, “8.8.8.8”) by your Office location or Trusted IPs.

// allowed offices public IP to be excluded from trigger 
let AllowedIPs = dynamic([ "1.1.1.1", "8.8.8.8" ]); 
// Get list of all connections (success, Failed, revoked...etc) outside of coutry list + IP list 
SigninLogs 
| where Location !in ("CA", "US") and isnotempty(Location) // Location different from CANADA, US 
| where IPAddress !in~ (AllowedIPs) // exclude Office IP Addresses 
| extend LocalTime = datetime_add('hour', -5, TimeGenerated) // UTC-5 (CANADA Time) 
| project LocalTime, UserPrincipalName, UserDisplayName , Identity, ResourceDisplayName , Location , IPAddress, RiskState, ConditionalAccessStatus, ResultType, ResultSignature, ResultDescription, MfaDetail, RiskDetail
| order by LocalTime desc

Thanks