Ensuring the security of user accounts is critical in today’s digital environment. One effective method is to analyze sign-in logs for unusual patterns, such as a user logging in from multiple devices and IP addresses within a short time frame. This blog post presents a Kusto Query Language (KQL) query designed to detect such suspicious login activities, which could indicate compromised accounts in Microsoft Sentinel (Analytical Rule).
Use Case:
User Login from Two or More Different Devices and IPs
This KQL query focuses on identifying instances where a single user logs in from at least two different devices and IP addresses within a short period, raising a potential security concern iin Microsoft Sentinel (Analytical Rule).
Kusto Query Language (KQL)
SigninLogs | where TimeGenerated >= ago(1d) | where ipv4_is_private(IPAddress) == false | extend NetworkTrust = tostring(parse_json(tostring(parse_json(NetworkLocationDetails)[0].networkNames))[0]) | extend InternalRange = iff(NetworkTrust == "provide internal range", true, false) | extend displayName_ = tostring(DeviceDetail.displayName) | extend browser_ = tostring(DeviceDetail.browser) | extend operatingSystem_ = tostring(DeviceDetail.operatingSystem) | extend Device = iff(isnotempty(displayName_), displayName_, browser_) | extend Device_Details = iff(isnotempty(Device), Device, operatingSystem_) | extend Time = bin(TimeGenerated, 1m) | summarize count(), loc = make_set(LocationDetails) by UserPrincipalName, Device_Details, Time, IPAddress | where count_ > 1 | summarize distinct_devices = dcount(Device_Details), Distinct_IPs = dcount(IPAddress), Device = make_set(Device_Details), location = make_set(loc), IPAddress = make_set(IPAddress) by UserPrincipalName, Time | where distinct_devices > 1 and Distinct_IPs > 1
Step-by-Step Breakdown
Filter by Time and Public IP Address:
SigninLogs
| where TimeGenerated >= ago(1d)
| where ipv4_is_private(IPAddress) == false- SigninLogs: Refers to the table containing sign-in logs.
- where TimeGenerated >= ago(1d): Filters logs to include only those from the last day.
- where ipv4_is_private(IPAddress) == false: Excludes logs with private IP addresses, focusing on public IPs which are more relevant for detecting suspicious activity.
Parse and Extend Network and Device Details:
| extend NetworkTrust = tostring(parse_json(tostring(parse_json(NetworkLocationDetails)[0].networkNames))[0])
| extend InternalRange = iff(NetworkTrust == "provide internal range", true, false)
| extend displayName_ = tostring(DeviceDetail.displayName)
| extend browser_ = tostring(DeviceDetail.browser)
| extend operatingSystem_ = tostring(DeviceDetail.operatingSystem)
| extend Device = iff(isnotempty(displayName_), displayName_, browser_)
| extend Device_Details = iff(isnotempty(Device), Device, operatingSystem_)- NetworkTrust: Extracts and converts the network name from NetworkLocationDetails to a string.
- InternalRange: Sets a boolean flag if the network name is “provide internal range”.
- displayName_, browser_, operatingSystem_: Extracts device display name, browser, and operating system details respectively.
- Device: Prioritizes display name over browser for a unified device identifier
- Device_Details: Uses the most specific device information available (either display name or operating system).
Bin the Time and Summarize by User and Device:
| extend Time = bin(TimeGenerated, 1m)
| summarize count(), loc = make_set(LocationDetails) by UserPrincipalName, Device_Details, Time, IPAddress- Time: Rounds TimeGenerated to the nearest minute.
- summarize: Groups data by user, device, time, and IP address, counting occurrences and creating a set of location details.
Filter and Summarize by User:
| where count_ > 1
| summarize distinct_devices = dcount(Device_Details), Distinct_IPs = dcount(IPAddress), Device = make_set(Device_Details), location = make_set(loc), IPAddress = make_set(IPAddress) by UserPrincipalName, Time- where count_ > 1: Keeps only records with more than one occurrence.
- summarize: For each user and time, calculates distinct counts of devices and IP addresses, and aggregates sets of devices, locations, and IP addresses.
Filter for Multiple Devices and IPs:
| where distinct_devices > 1 and Distinct_IPs > 1- where distinct_devices > 1 and Distinct_IPs > 1: Filters to find users with logins from multiple devices and IP addresses within the same time frame, indicating potential suspicious activity.
Summary
By leveraging this KQL query, security teams can effectively identify suspicious login patterns where users are accessing the system from multiple devices and IP addresses in a short period. This can be an indicator of compromised accounts or other security issues that require immediate attention.