Kusto Insights - January 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
Identity CA Policies Notin Use - This query is searching for conditional access (CA) policies in Azure Active Directory that are not actively being used. It looks at signin logs from the past 180 days and filters for users who are members. It expands the conditional access policies and creates two new columns: CAResult (the result of the policy) and CAName (the display name of the policy). It then summarizes the data by counting the total number of policies and creating a set of unique policy results for each policy name. It filters out policies that have any "success" or "failure" results and sorts the results by policy name in ascending order.
By: Matthew Zorich [GitHub, Twitter]
SigninLogs
| where TimeGenerated > ago(180d)
| where UserType == "Member"
| mv-expand todynamic(ConditionalAccessPolicies)
| extend CAResult=tostring(ConditionalAccessPolicies.result), CAName=tostring(ConditionalAccessPolicies.displayName)
| summarize TotalCount=count(),ResultSet=make_set(CAResult) by CAName
| where not(ResultSet has_any ("success","failure"))
| sort by CAName asc
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!
Create Analytic Rules to Query External Data in Microsoft Sentinel - Azure Data Explorer (ADX) and Azure Blob storage have long been recommended as long-term archival solutions for Sentinel. Microsoft recommended using ADX and Blob storage for long term retention, and the good news is that Azure Monitor lets you query data externally from your Log Analytics workspace for a full SOC experience.
KQL Security Sources - 2024 Update - It is great to see that more and more repositories, blogs and other sources share security-related KQL content. Therefore this post provides an updated list of to start the new year. These sources can help you to kickstart your KQL knowledge for the upcoming year, by providing learning material, detection rules, hunting queries and many more.
Kusto's Parse-Kv Operator: A Deep Dive - Hey there, data enthusiasts! Ever found yourself knee-deep in data, trying to make sense of endless logs and entries? Well, buckle up, because today we're talking about the Kusto Parse-Kv operator – a true lifesaver in the world of Microsoft Sentinel especially for Network Security Devices logs. This nifty operator is like a Swiss Army knife for data extraction, especially when dealing with the Commonsecuritylog table's Additional extension. Let's break it down, shall we?
Updates and Blog posts from Microsoft
Visualizing narrow Kusto tables with Azure Managed Grafana - The article breaks down how to make the most out of narrow Kusto tables for cool visualizations in Azure Managed Grafana. It's all about those few columns that pack a punch, letting you add new metrics without messing with the schema. You'll get the scoop on creating Kusto functions for a broad view, setting up your Grafana dashboard for visualizing multiple metrics at once, and tweaking everything for custom views. It's a handy guide for anyone looking to level up their data visualization game.
Advanced Monitoring for PostgreSQL Using Log Analytics - The post dives into how to up your PostgreSQL monitoring game with Azure, splitting focus between Azure Monitor for quick metric checks and Azure Log Analytics for deeper dive with KQL magic. It's all about getting those detailed insights and alerts, especially handy for the big setups. In a nutshell, it's a guide to using both tools in tandem to keep everything running smoothly and spotting issues before they blow up.
Migrating from the Azure MMA to AMA Agent - The article is about switching from Azure's old monitoring tool (MMA) to the new, better one (AMA). It walks you through setting up new data collection rules, using Azure Policy to get the new agent on your systems easily, and how to keep both agents running smoothly during the change. The upgrade is a smart move to get more efficient and cost-effective monitoring before the old tool is phased out.
Collect Event Ids using Azure Monitor Agent - The blog post provides a step-by-step guide on using the Azure Monitor Agent to collect specific event IDs from Windows client machines and upload them to a Log Analytics Workspace. It covers creating a Data Collection Rule (DCR), setting up a monitored object, and installing the agent on client devices. The focus is on collecting Event ID 4624 for security monitoring, demonstrating the process from the initial setup in the Azure portal to verifying the data collection in Log Analytics Workspace. The approach offers a scalable and efficient method for monitoring logon events and analyzing security-related data.
Latest Queries from the Community
Bert-Jan Pals - Twitter & GitHub
Alex Verboon - Twitter & GitHub
Fabian Bader - Twitter & GitHub
Thomas Naunheim - Twitter & GitHub
Jose Sebastián Canós - Twitter & GitHub
Security Nested Recommendation Virtual Machines MDVM Vulnerability Assessments
Security Nested Recommendation Container Registries MDVM Vulnerability Assessments
Matt Zorich - Twitter & GitHub
Michalis Michalos - Twitter & GitHub
Ali Hussein - 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