Press "Enter" to skip to content

Category: KQL

Updating Records in a Kusto Database

Published 2024-02-21 by Kevin Feasel

Vincent-Philippe Lauzon shows off a feature now in public preview:

Kusto databases, either in Azure Data Explorer or in Fabric KQL Database, are optimize for append ingestion.

In recent years, we’ve introduce the .delete command allowing you to selectively delete records.

Today we are introducing the .update command.  This command allows you to update records by deleting existing records and appending new ones in a single transaction.

This command comes with two syntaxes, a simplified syntax covering most scenarios efficiently and an expanded syntax giving you the maximum of control.

Read on for more information and a pair of examples of how updating works.

Comments closed

Using KQL in Azure SQL DB Audits

Published 2023-12-27 by Kevin Feasel

Josephine Bush tracks what’s happening on that Azure SQL Database:

According to Microsoft, “Kusto Query Language (KQL) is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQLs: databases, tables, and columns.”

Note: KQL is case-sensitive for everything. Also, remember to refrain from querying everything just like you wouldn’t with SQL — don’t do the equivalent of SELECT * from gianttable.

Microsoft also has a lot of documentation with best practices and a quick reference guide to the Kusto commands. This blog post covers the ones I use the most.

Read on for a primer on the language, specifically some of the things you can do when reading Azure SQL Database audit information.

Comments closed

Exposing KQL Data in OneLake

Published 2023-11-28 by Kevin Feasel

Brian Bønk gets in on the Microsoft Fabric fun:

Microsoft has released the final piece of the current puzzle around the OneLake as a one-stop-shopping service for dat in Fabric. Until now we had only access to the KQL data in the KQL database.

With this addition, we can now finally say that OneLake is the one place for your data in Fabric.

Read on to see how you can make data in an existing KQL database usable in OneLake.

Comments closed

Schema Validation with Kusto Databases

Published 2023-11-24 by Kevin Feasel

Vincent-Philippe Lauzon tests the schema:

Kusto allows you to very quickly get productive.  You can setup an ingestion pipeline in minutes that will ingest Terabytes (TBs) of data per day.

Like any piece of code, your database schema is as good as the intent you convey when you wrote it.  But over time, the intent diffuses and different priorities, authors and just plain miscommunication can diminish the quality of your code.

Read on to see what it does and the benefits it provides.

Comments closed

Many-to-Many Power BI Relationships and Table Refreshes

Published 2023-11-20 by Kevin Feasel

Dany Hoter gives us a reason to minimize use of many-to-many relationships in Power BI:

I must admit that in the last two years I’ve told many Power BI/Kusto customers not to worry about relationships that are created as M:M.

I was pretty sure that with Direct Query, such relationships are fine,

Indeed, the generated queries looked fine and performed as expected.

I recently became aware that the number of queries generated for some visuals e.g. Matrix and tables can be affected by the type of relationships between the participating tables.

Read on for a description of why you shouldn’t load your Power BI semantic models with many-to-many relationships, especially once Kusto is involved.

Comments closed

ApplicationContext Updates for Power BI

Published 2023-10-30 by Kevin Feasel

Chris Webb shares an update with us:

If you’re a fan of using Log Analytics for monitoring Power BI activity, then you may have noticed there’s some new data in the ApplicationContext column of the PowerBIDatasetsWorkspace table. Up until recently the ApplicationContext column only contained IDs that identify the report and the visual that generated a DAX query (something I blogged about here); it now contains additional information on the type of Power BI report that generated the query and an ID for the user session.

This is quite useful for seeing not just that people are using your Power BI services, but also who and what they are using to consume the information. Though do beware Chris’s warning about consumption method before you get too excited.

Comments closed

(Near)-Real-Time Analysis with Microsoft Fabric

Published 2023-10-19 by Kevin Feasel

Reza Rad continues a series on Microsoft Fabric:

Microsoft Fabric offers a workload for real-time solutions. Real-time Analytics can be used for streaming data, such as the data coming from IoT devices. It can be used not only to ingest the data but also to analyze it and use it for other Fabric workloads, such as data science. In this article and video, you will learn what is Real-Time Analytics in Microsoft Fabric and how it works.

Read on for a detailed demo.

Comments closed

Visualizing Kusto Graphs with Plotly and Python

Published 2023-10-05 by Kevin Feasel

Henning Rauch creates some plots:

Graphs are a powerful way to model and analyse complex relationships between entities, such as cybersecurity incidents, network traffic, social networks, and more. Kusto, the query and analytics engine of Azure Data Explorer, Microsoft Fabric Real-Time Analytics and many more recently introduced a new feature that enables users to contextualize their data using graphs. In this blog post, we will show you how to use graph semantics to create and explore graph data in Kusto, and how to visualize it using Plotly, a popular library for interactive data visualization in Python.

Graph semantics are a set of operators that allow users to work with graph data in Kusto, without the need to use a separate graph database or framework.

Click through for the KQL you’ll need, as well as how to display that in Plotly.

Comments closed

Distinct Counts in Power BI and KQL

Published 2023-10-04 by Kevin Feasel

Dany Hoter needs a distinct count:

Calculating distinct counts on massive distributed datasets is not trivial.

Kusto (aka Azure Data Explorer/KQL database in Fabric) dcount and dcountif functions use a special algorithm to return an estimate of distinct counts

The new functions count_distinct and count_distinctif were recently added to calculate exact distinct counts. These two functions are much more expensive than the original ones.

Read on for more details on how this all works.

Comments closed

Sending Azure Cost Management Data to Azure Data Explorer

Published 2023-09-12 by Kevin Feasel

Brad Watts writes out some cost data:

Understanding your Azure Spend is one of the most important things you do as an Azure customer. Azure Cost Management is built into the platform to provide you insights. But we live in a world of data and looking at the Azure Cost Management data in a silo may not meet your organization’s needs. In those situations, we can solve that need by putting your Cost Management data into an analytical platform like Azure Data Explorer or Microsoft Fabric KQL Database. Here we can bring in or join additional data that’s useful, run ad-hoc queries and build visualization tying it all together.

Using the below repository, you’ll be able to utilize Azure Cost Management exports to setup an automated process that ingests the cost data into ADX or Fabric KQL Database.

There are several steps involved, but as Brad points out, you can do this either with Microsoft Fabric or with classic Azure Data Factory + Azure Data Explorer. I’d also throw in Azure Synapse Analytics, but that’s not as in vogue anymore.

Werner Zirkel also has a great comment showing how you can cut out most of the steps with Event Grid.

Comments closed