The Definitive Guide to KQL: Using Kusto Query Language for operations, defending, and threat hunting (Business Skills)

Turn the avalanche of raw data from Azure Data Explorer, Azure Monitor, Microsoft Sentinel, and other Microsoft data platforms into actionable intelligence with KQL (Kusto Query Language). Experts in information security and analysis guide you through what it takes to automate your approach to risk assessment and remediation, speeding up detection time while reducing manual work using KQL. This accessible and practical guide―designed for a broad range of people with varying experience in KQL―will quickly make KQL second nature for information security. Solve real problems with Kusto Query Language― and build your competitive advantage: Learn the fundamentals of KQL―what it is and where it is used Examine the anatomy of a KQL query Understand why data summation and aggregation is important See examples of data summation, including count, countif, and dcount Learn the benefits of moving from raw data ingestion to a more automated approach for security operations Unlock how to write efficient and effective queries Work with advanced KQL operators, advanced data strings, and multivalued strings Explore KQL for day-to-day admin tasks, performance, and troubleshooting Use KQL across Azure, including app services and function apps Delve into defending and threat hunting using KQL Recognize indicators of compromise and anomaly detection Learn to access and contribute to hunting queries via GitHub and workbooks via Microsoft Entra ID Cover Title Page Copyright Page Contents at a Glance Contents Acknowledgments About the Authors Foreword Introduction Chapter 1 Introduction and Fundamentals Why You Need to Learn KQL Where KQL Is Used How to Use This Book Good Operations Are Good Security Setting Up the Environment Log Analytics Demo Setup Diagnostic Settings Kusto.Explorer Azure Data Studio KQL from the Command Line Fundamental Concepts What Is KQL? The KQL Query Structure The getschema Function Data Types and Statements Searching and Filtering Search Operator Take and Limit Operators Where Operator Project and Extend Operators Data Manipulation Sort By and Order By Dealing with Nulls Top Operator Splitting and Trimming Parse Functions Numerical Operators Time Operators Ago, Between, and Now Date and Time Formatting and Extracting Just Enough User Interface Miscellaneous Fundamentals Summary Chapter 2 Data Aggregation We Are Dealing with a Lot of Data Here Obfuscating Results Distinct and Count Distinct Summarize By Count Min, Max, Average, and Sum Determining the Min and Max Determining the Average and Sum Bins, Percentages, and Percentiles Grouping Data By Values (Binning) Percentage Percentiles Lists and Sets Lists Sets Visualizing Data with the Render Operator Pie Chart Bar Chart Column Chart Time Chart Area Chart Line Chart Scatter Chart Additional Charts Optional Rendering Values Make Series Aggregation Functions Usage in Other Operators Summary Chapter 3 Unlocking Insights with Advanced KQL Operators Using KQL Variables in KQL Creating Constants with let Calculated Values with let Reusable Functions with let Using Multiple Variables in Queries Working with Default Values in Functions Creating Views with let Optimizing Queries with Materialization Best Practices for Using Variables in KQL Uniting Queries with KQL Unions The union Operator Advanced Techniques with the union Operator Best Practices for Using the union Operator union Operator versus join Operator Best Practices and Performance Optimization Joining Data Innerunique Inner Join Leftouter Rightouter Fullouter Leftsemi Rightsemi Leftanti Rightanti The externaldata Operator Syntax and Parameters externaldata Operator Use Cases Best Practices and Considerations Query IP Ranges Using KQL Understanding IP-Prefix Notation ipv4_is_in_range() Function ipv4_is_match() Function ipv6_compare() Function ipv6_is_match() Function Using the ipv4_is_private() Function IP-Prefix Notation The ipv4_is_private() Function in Real-World Scenarios Getting Geolocation from an IP Address Using KQL The geo_info_from_ip_address() Function Limitations and Considerations Working with Multivalued Strings in KQL mv-expand Operator parse Operator When to Use mv-expand and parse base64_decode_tostring() Function Best Practices for Using base64_decode_tostring() Error Handling and Validation Chaining Functions for Complex Decoding Handling Large Base64 Strings Decoding Base64 Strings in Log Analysis Base64 Decoding in Data Transformation Pipelines Working with JSON Filtering JSON Data Aggregating JSON Data Best Practices for Optimizing JSON Processing Advanced JSON Processing Techniques Time-Series Analysis Regular Expressions in KQL Regular Expressions in Microsoft Sentinel Testing Regular Expressions Enhancing Detection Rules and Migrating from Other SIEM Tools bin() Function Numeric bins Timespan bins Datetime bins Pad a Table with Null bins Understanding Functions in Kusto Query Language Syntax and Naming Conventions for User-DefinedFunctions Creating and Declaring User-Defined Functions Invoking User-DefinedFunctions Default Values in Functions Materialize Function Advantages of the Materialize Function Performance Improvement Examples Using Materialize() in Let Statements Best Practices for Using Materialize() Common Mistakes to Avoid Summary Chapter 4 Operational Excellence with KQL Getting Started with KQL Advanced Hunting with KQL Key Operators and Statements in KQL Advanced Hunting Query Examples Common Security Challenges in the Cloud Integrating Security into DevOps Pipelines with KQL Using KQL for Infrastructure and Application Scanning Hardening Cloud Security with KQL Hands-on Training: Mastering KQL Setting Up an ADX Cluster with KQL Ingesting and Exploring Data Using KQL Writing Complex KQL Queries for Advanced Analytics Best Practices for KQL in IT Operations Case Studies: Real-World Applications of KQL Advancing Your KQL Skills Enabling Diagnostic Settings in Azure Enabling Diagnostic Settings in Azure Services Using KQL for Microsoft Intune for Diagnostics and Compliance Understanding Intune Diagnostics Settings Setting Up a Log Analytics workspace Configuring Intune Diagnostics Settings Exploring Intune Audit Logs with KQL Incident Management and Automation Using KQL Queries for Advanced Hunting in Microsoft Defender The Power of KQL Queries in Advanced Hunting Leveraging Detection Rules Best Practices for Advanced Hunting Using KQL to Create Powerful Azure Monitor Workbooks Key Features and Benefits How Workbooks Can Enhance Your Data Analysis Getting Started with Azure Workbooks Exploring Data Sources in Azure Workbooks Mastering Visualization in Azure Workbooks Advanced Techniques for KQL Workbook Queries Styling and Customization in Azure Workbooks Tips and Tricks for Effective Workbook Creation Real-World Use Cases and Examples Azure Data Explorer and Power BI Optimizing Log Queries in Azure Monitor Using Notebooks in Azure Data Studio Export Kusto to M/Using Web Connector Using the Kusto/Data Explorer Connector Enhancing Data Management and Efficiency Why Use Transformations? Supported Tables for Transformations How Transformations Work in Azure Monitor Implementing Transformations with Data Collection Rules (DCR) Workspace Transformation DCR Working with Multiple Destinations Creating Transformations for Different Data Collection Methods Cost Considerations for Transformations Sample Templates for Creating DCRs with Transformations Best Practices for Optimizing Query Performance Reduce the Amount of Data Processed Efficient Extraction of Fields from Dynamic Objects Reshaping Queries for Conversions on Large Data Best Practices for New Queries Optimizing String Operators Query Performance Awareness with the Query Performance Pane Leveraging Materialize for Performance Optimization Using Materialized Views for Commonly Used Aggregations Monitoring and Troubleshooting Query Performance Summary Chapter 5 KQL for Cybersecurity—Defending and Threat Hunting Why KQL for Security? Flexible for Sources and Data Structures Easy Pivoting between Datasets Efficient with Large Data Volumes Out-of-the-Box Data Aggregation and Summation Querying Against Time Ad Hoc Digital Forensics and Investigations An Array of Inbuilt Visualization Tools Forgiving Query Crafting Versatility Cybersecurity-Focused Operators Searching Operators Time Operators Data Summation Operators Data Manipulation Operators User Compromise in Microsoft 365 Phishing Attacks Firewall Log Parsing split() parse Timestamps Auditing Security Posture Multifactor Authentication User Accounts Endpoint Devices Microsoft Entra ID (Azure Active Directory) Compromise Ransomware Tactics, Techniques, and Procedures Summary Chapter 6 Advanced KQL Cybersecurity Use Cases and Operators mv-expand and mv-apply mv-expand mv-apply Joins Inner Join Innerunique Join Fullouter Join Leftouter Join Leftanti Join Leftsemi Join Rightouter Join Rightanti Join Rightsemi Join Joining Data on Multiple Fields Joining on Multiple Tables let and Nested lets iff() case() coalesce() More Parsing Operators parse-where parse_json() parse_xml() parse_user_agent() parse_url() regex extract extract_all Advanced Time datetime_diff(), prev() next() Time-series Analysis series_stats Geolocation IP Address Queries base64_decode_tostring() toscalar() evaluate pivot() Functions Contributing to the KQL Community Official and Author GitHub Repositories Community Repos Other Resources Summary Index A B C D E F G H I J K L M N O P Q R S T U V W X Y Z