Kusto Insights - November 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
UnifiedIdentityInfo - This query retrieves a list of non-privileged and privileged users, as well as workload identities. It uses external data to get information about privileged roles, filters out unclassified roles, and projects relevant fields. It then joins this data with identity information, summarizes the results, and creates separate lists for privileged users and non-privileged users. It also includes information about synchronization with on-premises accounts and assigns a default permission classification. Finally, it retrieves information about privileged workload identities and combines all the lists into one.
By: Thomas Naunheim [GitHub, Twitter]
// Function to get a list of all non-privileged and privileged users but also workload identities
let SensitiveEntraDirectoryRoles = externaldata(RoleName: string, RoleId: string, isPrivileged: bool, Classification: dynamic)["https://raw.githubusercontent.com/Cloud-Architekt/AzurePrivilegedIAM/main/Classification/Classification_EntraIdDirectoryRoles.json"] with(format='multijson')
| where Classification.EAMTierLevelName != "Unclassified"
| project RoleName, isPrivileged, Classification;
let SensitiveUsers = IdentityInfo
| where TimeGenerated > ago(14d)
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| mv-expand AssignedRoles
| extend RoleName = tostring(AssignedRoles)
| join kind=inner ( SensitiveEntraDirectoryRoles ) on RoleName
| summarize Classification = make_set(parse_json(Classification.EAMTierLevelName)), RoleAssignments = make_set(RoleName) by AccountObjectId, AccountDisplayName, OnPremisesAccountObjectId;
let PrivilegedUsers = SensitiveUsers
| extend OnPremSynchronized = iff(isnotempty(OnPremisesAccountObjectId), "true", "false")
| project
ObjectId = tostring(AccountObjectId),
ObjectType = "User",
ObjectDisplayName = AccountDisplayName,
OnPremSynchronized,
tostring(Classification),
EntraIdRoles = RoleAssignments;
let AllUsers = IdentityInfo
| where TimeGenerated > ago(14d)
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| extend ObjectId = tostring(AccountObjectId)
| join kind=leftanti ( PrivilegedUsers ) on ObjectId
| extend DefaultPermissionClassification = "UserAccess"
| extend OnPremSynchronized = iff(isnotempty(OnPremisesAccountObjectId), "true", "false")
| summarize Classification = tostring(make_set(DefaultPermissionClassification)) by
ObjectId,
ObjectType = "User",
ObjectDisplayName = AccountDisplayName,
OnPremSynchronized;
let PrivilegedWorkloads = PrivilegedWorkloadIdentityInfo
| where isnotempty(EntraIdRoles) or isnotempty(AppRolePermissions)
| project
ObjectId = tostring(ServicePrincipalObjectId),
ObjectType = WorkloadIdentityType,
ObjectDisplayName = WorkloadIdentityName,
OnPremSynchronized = "false",
Classification = tostring(EnterpriseAccessModelTiering),
EntraIdRoles = EntraIdRoles,
AppRoles = AppRolePermissions;
union AllUsers, PrivilegedUsers, PrivilegedWorkloads
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!
Detect threats using Microsoft Graph activity logs - Part 2 - In part one I focused mostly on detecting offensive security tools like AzureHound, GraphRunner, and PurpleKnight. In part two I will go into more depth how you can use the now available information for hunting and how to correlate it with other datasets to gain deeper insights. Correlate Graph activities with other log sources While the MicrosoftGraphActivityLogs alone is a trove of information, correlating it with other logs makes it an even more interesting data source.
KQL Functions For Network Operations - Do you have logs that contain IP addresses? Probably the answer is yes. Whether logs are DeviceNetworkEvents, API calls, Firewall or signin events the data can be analysed using KQL. This blog explains various tips and tricks to further develop your knowledge to effectively analyse logs containing IP addresses. Besides that multiple functions that can be leveraged in daily network related operations are discussed. One very interesting spoiler, you never have to calculate your own subnets again!
The KQL Mysteries: Prologue - In the shadowy corners of the digital universe, where menacing threats lurk behind every line of code, there exists a breed of professionals tasked with a mission no less than the preservation of order itself. Among these cyber guardians, one name stood out, striking fear into the hearts of hackers and evoking reverence from his peers: Jon Block. As a top-tier security analyst, Jon had developed an uncanny ability to root out even the most elusive of cyber threats. His weapon of choice? A powerful and versatile query language known as KQL - Kusto Query Language.
The KQL Mysteries: Chapter 1 - Jon loved his job. He enjoyed hunting for threats and anomalies in the vast amount of data collected by his company, using the powerful KQL search
operator. The search operator was Jon’s go-to first step when trying to expose potential threats in his company’s environment. It is simple, yet powerful.
A Defenders Guide to GraphRunner — Part II - In the second and final part we will look at the remaining modules and share some advice on how to prevent Graph API abuse. Since the release of the first blog, the creators of GraphRunner did a talk on the inner workings and it’s available on YouTube.
From Threat Report to (KQL) Hunting Query - Everyone reads threat intelligence reports, but translating the content into actionable information is difficult. Especially how the information of those reports can be translated to hunting queries to determine if your organisation is targeted. This blog explains the process from the threat report to the hunting query and the response to the results. Besides the process, this blog would not be complete without KQL queries.
Updates and Blog posts from Microsoft
How Microsoft Security Copilot works - Check out this cool article and its videos about how AI is making it easier to come up with KQL Queries. It's pretty neat – the Security Copilot can actually write these queries for you just by using everyday language. This makes sifting through complex data and handling security stuff a breeze. Plus, it shows how AI and KQL are joining forces to really up the game in managing security and analyzing data.
Announcing Microsoft Intune Advanced Analytics - The new Microsoft Intune Advanced Analytics is a cool update for folks who use KQL. It's part of the Microsoft Intune Suite and it makes IT jobs a bit easier with stuff like spotting weird things happening, focusing on specific devices, and a better way to see a device's history. The big deal for KQL users is being able to run Kusto queries whenever they want, which helps fix device problems faster and more effectively. If you're into getting the best out of your device's performance and settings, this update is pretty handy.
Simplify IT management with Microsoft Copilot for Azure - The Microsoft Copilot for Azure is like a smart buddy for managing your cloud stuff. It mixes cool AI with Azure's setup to make handling everything in Azure way easier. The coolest part? It helps you make and use Kusto Query Language (KQL) queries in the Azure Resource Graph. This is super handy for anyone using KQL – it makes getting insights about your Azure resources and setups faster and simpler, no matter if you're just starting out or already a pro with Azure.
Kusto Schema Validation - The article on Kusto Schema Validation highlights the introduction of a new Kusto command designed to validate database schemas and detect inconsistencies. This command is crucial for KQL users as it allows for efficient validation of database schemas, ensuring data integrity and consistency. The feature can be integrated into CI/CD automation for schema validation in development environments before production deployment, serving as a quality gate. Additionally, the command is easily accessible in the Kusto.Explorer Windows client, enhancing productivity and database quality for KQL practitioners.
Latest Queries from the Community
Bert-Jan Pals - Twitter & GitHub
Alex Verboon - Twitter & GitHub
Thomas Naunheim - Twitter & GitHub
Added Credential to privileged workload by lower or non-privileged user (WorkloadIdentityInfo
Token Replay from workload identity with privileges in Microsoft Azure (WorkloadIdentityInfo)
Token Replay from workload identity with privileges in Microsoft Azure (WorkloadIdentityInfo)
MDA Threat detection policy with Enriched Information (WorkloadIdentityInfo)
Jose Sebastián Canós - Twitter & GitHub
Michalis Michalos - Twitter & GitHub
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