Скачать или смотреть How to Pivot Log Analytics Data in Kusto Using any() Aggregation

Discover how to effectively pivot log analytics data in Kusto. Learn to transform rows into columns and manage multiple settings efficiently.
---
This video is based on the question https://stackoverflow.com/q/63339448/ asked by the user 'SpeedBird527' ( https://stackoverflow.com/u/1016070/ ) and on the answer https://stackoverflow.com/a/63341281/ provided by the user 'Alexander Sloutsky' ( https://stackoverflow.com/u/10151794/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to pivot log analytics data (Kusto)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Pivot Log Analytics Data in Kusto Using any() Aggregation

When working with log analytics data in Kusto, you might encounter a common challenge: transforming a dataset structured in rows into a more manageable format with columns. Specifically, many users find themselves grappling with the need to pivot data representing key-value pairs into a schema that better suits their reporting or analytical needs. This guide explains how to accomplish this using practical examples and simple KQL statements.

Understanding the Problem

Let's illustrate the problem with a sample dataset that includes information about different locations and their respective settings. The data is structured such that each setting appears as a row, but we need to rearrange it so that each setting becomes a column instead. The ideal output would look something like this:

[[See Video to Reveal this Text or Code Snippet]]

However, running a simple pivot without the right aggregation method leads to unexpected results, producing additional rows with confusing data. This usually happens when there is no aggregation method applied during the pivoting process.

The Solution: Using any() Aggregation

The solution to this problem is quite straightforward. By using the any() function during the pivot operation, we can ensure that Kusto knows how to handle duplicate values. Here’s how you can implement it step-by-step:

Step 1: Define Your Data

You start by defining your data as a datatable, which in this case includes location, setting name, and setting value.

[[See Video to Reveal this Text or Code Snippet]]

This structure captures the key-value pairs effectively, providing a foundation for analysis.

Step 2: Use the Pivot Function with Aggregation

Next, you apply the pivot() function while incorporating any() as the aggregation method. The final query will look like this:

[[See Video to Reveal this Text or Code Snippet]]

This code tells Kusto to create columns based on unique values from the SettingName column and to fill them with any available values from the SettingValue column.

Expected Output

Using the modified query, you will obtain a structured output that neatly aligns with your expectations:

[[See Video to Reveal this Text or Code Snippet]]

This output makes the dataset much easier to digest and analyze, especially when dealing with larger datasets containing numerous settings.

Conclusion

Pivoting log analytics data in Kusto can be simplified by using the any() aggregation method when applying the pivot function. This ensures that duplicate values are handled correctly and results in a clean, readable output that better meets analytical needs. By following the steps outlined in this post, you can efficiently manipulate and transform your data regardless of its complexity.

If you have any questions or would like further clarification on this topic, feel free to leave a comment below!