Kusto Insights - August 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
Risky External Privileged Users With Enrichment Of Known Attack Paths And Tiering - This query is designed to generate a comprehensive list of sensitive directory roles in an Azure environment, enriched with various details and classifications.
By: Thomas Naunheim [GitHub, Twitter]
// Early draft: List of Directory Roles including known attack paths (defined by Emilien Socchi repository: https://github.com/emiliensocchi/azure-tiering/blob/main/Entra%20roles/tiered-entra-roles.json), classification by EntraOps, categories and rich details by Graph API and their role members with flags for Guest, Risky User and count of role members from IdentityInfo.
let SensitiveEntraDirectoryRoles = externaldata(RoleName: string, RoleId: string, Categories: string, RichDescription: string, isPrivileged: bool, Classification: dynamic, RolePermissions:dynamic)["https://raw.githubusercontent.com/Cloud-Architekt/AzurePrivilegedIAM/main/Classification/Classification_EntraIdDirectoryRoles.json"] with(format='multijson')
| where Classification.EAMTierLevelName != "Unclassified"
| mv-expand RolePermissions
| extend Categories = split(Categories,',')
| summarize EntraOpsCategory = make_set(RolePermissions.Category), Categories = make_set(Categories) by RoleName, RoleId, isPrivileged, EntraOpsClassification = tostring(Classification.EAMTierLevelName), RichDescription;
let KnownAttackPaths = externaldata(id: string, pathType: string, knownShortestPath: string, example: string)["https://raw.githubusercontent.com/emiliensocchi/azure-tiering/main/Entra%20roles/tiered-entra-roles.json"] with(format='multijson')
| where isnotempty(knownShortestPath) or isnotempty(example)
| project-rename RoleId = id, AttackPathType = pathType, ShortestAttackPath = knownShortestPath, AttackPathExample = example;
let PrivilegedUsers = IdentityInfo
| where TimeGenerated > ago(14d)
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| mv-expand AssignedRoles
| extend RoleName = tostring(AssignedRoles);
SensitiveEntraDirectoryRoles
| join kind=inner ( PrivilegedUsers ) on RoleName
| extend RoleAssignment = bag_pack_columns(AccountName, AccountUPN, UserType, Tags, IsAccountEnabled, RiskState)
| summarize RoleMembers = count(), RoleAssignments = make_list(RoleAssignment), RiskState = make_list(RiskState), UserType = make_list(UserType) by RoleName, RoleId, tostring(Categories), tostring(EntraOpsCategory), isPrivileged, tostring(EntraOpsClassification), tostring(RichDescription)
| extend RiskyAdmins = iff(RiskState has "atRisk", true, false)
| extend GuestAsAdmins = iff(UserType has "Guest", true, false)
| project-reorder RiskState, RoleName, RichDescription, EntraOpsClassification, isPrivileged, EntraOpsCategory,Categories, RoleMembers, RoleAssignments
| sort by RoleName asc
| join kind=inner ( KnownAttackPaths) on RoleId
| project-away RiskState, UserType, RoleId1
// Filter for risky or external users only
| where RiskyAdmins = true or GuestAsAdmins == true
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!
The Cloud Threat Hunting Field Manual: Azure - "The Cloud Threat Hunting Field Manual: Azure" is your essential companion for mastering proactive cybersecurity strategies within Microsoft's cloud platform. This book dives deep into Azure Fundamentals, offering a comprehensive understanding of its architecture and services. Readers will explore PowerShell for automation, Kusto Query Language (KQL) for advanced log analysis, and Azure's logging capabilities for effective threat detection and response. With a focus on practical threat hunting concepts and insights into the MITRE ATT&CK framework, this manual provides actionable techniques to hunt and mitigate threats effectively in Azure environments.
Advanced KQL for Threat Hunters - Welcome to the Advanced KQL (Kusto Query Language) Training repository! This repo is designed to help you master KQL, with a focus on advanced concepts and practical examples that you can use to query and analyze data in Azure Data Explorer, Azure Monitor, and Microsoft Sentinel.
You always trust your CSP - Cross Tenant MFA and GDAP - Entra ID Multifactor Authentication is on everyone’s mind, as Microsoft will enforce the usage of MFA for most of the Admin portals starting October 2024. But many in the industry are a step ahead and had MFA enforced already and are thinking about how to make MFA more convenient for everybody involved.
Updates and Blog posts from Microsoft
Visualizing Data as Graphs with Fabric and KQL - You’ll explore how to turn complex data relationships into easy-to-understand visual graphs using Azure Data Explorer and the Kusto.Explorer tool. By applying graph semantics to your data, you can better visualize network relationships, like flight patterns or product recommendations, in a way that's much more intuitive than looking at raw data tables.
Harnessing the power of KQL Plugins for enhanced security insights with Copilot for Security - You can easily bring in data from various sources like Log Analytics, Microsoft 365 Defender, and Azure Data Explorer, and create customized insights tailored to your security needs. KQL plugins help you leverage existing data and queries to quickly build efficient, real-time security detections, making your data analysis faster and more precise.
Latest Queries from the Community
Bert-Jan Pals - Twitter & GitHub
Thomas Naunheim - Twitter & GitHub
Michalis Michalos - Twitter & GitHub
Exposure Management Browser Cookies With Credentials Of Privileged Users
Use Exposure Management To Chart User Groups With Local Admin Privileges
Identify Endpoint Browser Extensions With Can Turnoff Malware Protections Permissions
Ali Hussein - Twitter & GitHub
Sergio Albea - LinkedIn & GitHub
Steven Lim - LinkedIn & GitHub
We have added more than 100 Queries by Steven. Have a look at KQLSearch.com and use the “Author” Filter to find his queries. Its too many to list them all in this newsletter :)
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 Lab: aka.ms/LADemo
Socials
Bert-Jan Pals | Microsoft Security MVP
Blog | Twitter | LinkedIn | GitHub
Ugur Koc | Microsoft Security MVP
Blog | Twitter | LinkedIn | GitHub