Kusto Insights - February Update
Welcome to a new Monthly Update. We will go through some news and the latest queries. The goal is to provide you, the reader, a quick summary of what is going on in the world of KQL including News and Blogs from the Community as well as from Microsoft.
Query of the Month
AAD User Risk Events Leaked Credentials - This query retrieves user risk events related to leaked credentials from Azure Active Directory Identity Protection. It filters the events based on a specified time period and frequency. The query then summarizes the events by the earliest time generated and the maximum value for all other columns, grouped by the user ID. It further filters the results based on the minimum time generated being within the specified frequency. Finally, it projects specific columns for the output, including the time generated, operation name, source, activity, user display name, user principal name, user ID, risk event type, risk state, risk detail, risk level, and detection timing type.
By: Jose Sebastián Canós [GitHub, Twitter]
let query_frequency = 5m;
let query_period = 2d;
AADUserRiskEvents
| where TimeGenerated > ago(query_period)
| where OperationName == "User Risk Detection" and Source == "IdentityProtection" and RiskEventType == "leakedCredentials"
| summarize minTimeGenerated = min(TimeGenerated), arg_max(TimeGenerated, *) by Id
| where minTimeGenerated > ago(query_frequency)
| project
TimeGenerated,
OperationName,
Source,
Activity,
UserDisplayName,
UserPrincipalName,
UserId,
RiskEventType,
RiskState,
RiskDetail,
RiskLevel,
DetectionTimingType
Source: GitHub
News from the Community
We've handpicked a few blog posts for their insightful content and relevance, yet we acknowledge the wealth of quality submissions from the KQL community. While we can't feature every post, each contribution is valued and vital to our collective knowledge. Stay inspired and keep sharing your perspectives!
From hybrid / fully joined devices to Entra ID - Adversaries are more and more interested in the data and infrastructure that lives in Cloud environments like Azure and Microsoft 365 solutions. Since Microsoft EntraID is the most common central IDP solution for these environments, it is important to identify the possible paths attackers can use to move from a device to possible crown jewels that live in these Cloud solutions.
Windows Built-in local security groups - Windows has several built-in local security groups that are designed to manage permissions and access rights on a computer. These groups are predefined by Windows, and each group has specific rights and permissions. The exact groups available can vary depending on the version of Windows you’re using or the features that are enabled.
Detecting Post-Exploitation Behaviour - The recent ScreenConnect vulnerability (CVE-2024-1709 & CVE-2024-1708) showed once more why it is so important to detect post-exploitation behaviour. Most of the post-exploitation behaviour is not new, thus ensuring that you detect the known is a must. This blog explains how the Huntress report on the observed post-exploitation can be translated to detection rules, which is done based on theory and practice KQL examples.
Isolated an Endpoint? Automate tag adding and notifications - If you are part of a big organization, you might need to reach out to some colleagues and teams, in case you isolate an endpoint. An end user will probably reach out to your help desk in order to identify if there is an issue with her/his endpoint. Hence, you may want to spare some time of back and forth of emails or direct messages
Updates and Blog posts from Microsoft
Microsoft Intune Suite - beyond endpoint management in 2024 - The Microsoft Intune Suite has expanded beyond traditional endpoint management to offer advanced cross-platform capabilities, which are significant for Kusto Query Language (KQL) users. It includes enhancements in application security, secure access to resources, and device operations, particularly emphasizing real-time device querying using KQL. This feature allows users to directly query device data, enhancing troubleshooting and security assessments. Thus, for KQL enthusiasts, the suite offers a more integrated and efficient way to manage and analyze endpoint data.
Update records in a Kusto Database - The article introduces a new .update
command in Kusto databases, allowing users to update records by deleting existing ones and appending new ones in a single transaction. This command, available in public preview, comes with simplified and expanded syntaxes to cater to different user needs, enhancing the efficiency of data pipelines in Kusto. It's a significant update for KQL/Kusto users, providing more flexibility in managing data.
Introducing Dashboards Base Queries: Enhancing Productivity and Consistency - The recent introduction of Base Queries in dashboards is a significant advancement for Azure Data Explorer users, particularly those utilizing KQL. This new feature enhances productivity by allowing the reuse of common query components across multiple dashboard tiles, reducing redundancy and ensuring consistency in data analysis. It's a practical tool for optimizing dashboard creation and management, making it a noteworthy update for anyone working with KQL in their data visualization tasks.
Latest Queries from the Community
Bert-Jan Pals - Twitter & GitHub
Alex Verboon - Twitter & GitHub
Thomas Naunheim - Twitter & GitHub
Jose Sebastián Canós - Twitter & GitHub
Security Incident Incidents With Automation Rule Failure Events From Sentinel Health
AWS Cloud Trail Asl Aws Multi Factor Authentication Disabled
AWS Cloud Trail Asl Aws Concurrent Sessions From Different Ips
Security Nested Recommendation Security Configuration Assessments
Matt Zorich - Twitter & GitHub
Michalis Michalos - Twitter & GitHub
Ali Hussein - Twitter & GitHub
50 New Queries for Intune Device Query
Important resources
Learn KQL with the Must Learn KQL series and book
KQLQuery.com - Blog posts about KQL and different use cases
KQLSearch.com - Search Engine for KQL Queries
Log Analytics Demo: aka.ms/LADemo
Socials
Bert-Jan Pals
Blog | Twitter | LinkedIn | GitHub
Ugur Koc