Kusto Insights - December Update

and

Jan 07, 2025

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.

Wishing all followers of the Kusto Insights newsletter a happy and insightful year ahead! May your queries be sharp, your data be clear, and your detections be solid! 🚀📊

Query of the Month

Detection Of OOF Message Delivered Externally - This query is designed to monitor and analyze Out of Office (OOF) automatic reply messages that are sent externally from an organization. These messages can sometimes contain sensitive information, such as the duration of absence, alternative contact details, or phone numbers, which could be exploited if shared with external parties.

By: Sergio Albea [GitHub, LinkedIn]

EmailEvents
// add your automatic replies cases in your languages
| where Subject startswith "Automatic reply:"
| where DeliveryAction has "Delivered" and EmailDirection has "Outbound"
| extend Username = split(RecipientEmailAddress, "@")[0], Domain = tostring(split(RecipientEmailAddress, "@")[1])
| extend DomainParts = split(RecipientEmailAddress, ".")
| extend DomainExtensions = tostring(DomainParts[-1])
| summarize count() by DomainExtensions ,EmailDirection, DeliveryAction,DeliveryLocation, ThreatTypes
// if you want to have deeper information instead of a general view, you can use the next line and remove/comment the previous one
//| distinct SenderDisplayName, SenderMailFromDomain, SenderIPv4, RecipientEmailAddress,DomainExtensions,Domain,Subject, EmailDirection, DeliveryAction, DeliveryLocation, ThreatTypes

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!

Calculating the First Monday of a Given Week in Kusto Query Language (KQL) - Hey KQL heroes! 🌟 If you’ve ever had to wrestle with dates and weeks in your analysis, you’ll know it can be quite a challenge. But worry not, because today we’re diving into a nifty Kusto Query Language (KQL) script that will make calculating the first Monday of any given week a breeze. Ready to uncover some KQL magic? Let’s get started!

IOC hunting at scale - As the holiday season approaches and our schedules hopefully begin to open up, many of us find ourselves with a bit more time on our hands. This time could be perfectly spent delving into some hunting activities. And if you’re into hunting threats and sifting through vast amounts of data, the KQL External Data operator might be the holiday gift for you!

This powerful capability enables you to seamlessly incorporate external data into your KQL queries, such as GitHub IOC lists or MISP Feeds. This data can be dynamically loaded in your KQL query to hunt for matches across all your devices.

Workshop: Kusto Graph Semantics Explained - Ho, ho, ho… In Germany on the 6th of December we celebrate “Nikolaus”. Kids put out one shoe the night before in the hopes that, in the morning, it is filled with nuts, mandarin oranges, chocolate or even small gifts. Lucky for you, it seems that you also put out your shoe last night, because I have a gift for you as well. But please don’t confuse me with Nikolaus ;)

At this years DEATHCon I was fortuned enough to present my workshop about Kusto Graph (Kraph) semantics and now I want to share it with everybody.

KQL journey Part 10: Recommendations and Best Practices - As we reach the final chapter on this KQL journey, it’s time to step back and see the bigger picture. Throughout this series, we’ve equipped you techniques—from foundational concepts to advanced filtering and automation—that empower you to analyze data with precision and uncover hidden insights. In this concluding post, we’ll distill all that knowledge into actionable recommendations and best practices, giving you the ultimate guide to mastering KQL. Whether you’re a beginner or an experienced professional, these strategies will help you unlock the full potential of your data. Let’s dive in!

Updates and Blog posts from Microsoft

Leveraging ASIM-based KQL plugins in Microsoft Security Copilot for investigation scenarios - You can use these plugins to interpret complex hunting query outputs, making your analysis more efficient. By leveraging the Advanced Information Security Model (ASIM), you can create source-agnostic detection rules that simplify querying across multiple data sources. This integration empowers you to automate complex investigation tasks, leading to quicker and more informed conclusions.

KQL Migrator powered by Microsoft Security Copilot - You can use this tool to convert detection rules from languages like AQL, YARA, STIX II, and OpenIOC into KQL, streamlining your migration process. By automating these conversions, you can save time and reduce the need for specialized skills, making your security operations more efficient. This integration empowers you to adopt Microsoft's modern SIEM solutions with greater ease and confidence.