CODEWITHSUNDEEP
azure-log-analyticsazure-data-explorerkql
blaurens
blaurens

Reputation: 1

KQL: query all variables in dynamic column additional to existing columns

I'm working with Microsoft azure Appinsights, and want to export Logs data to a CSV so I can analyze it with python. The problem is that there is one column named customDimensions, which is of dynamic type with a lot of important variables, which I want to have as separate columns. Furthermore, am I working with a lot of data, so I can't just hardcode the variables I want using 'extend', because every customDimensions value could consist of different variables.

I found online that using bag_unpack comes near the solution I want:

union customEvents, pageViews
| project customDimensions
| evaluate bag_unpack(customDimensions)

However, this outputs only the new columns, not additional to the already existing columns. But when I don't use the 'project customDimensions' line, I get the following error:

("evaluate bag_unpack(): the following error(s) occurred while evaluating the output schema: evaluate bag_unpack(): cannot add column named 'cloud_RoleName' as it already exists in expression source")

The question is: How do I query to get all the variables in customDimensions as additional columns, instead of having it as a dictionary in the dataframe like this:

Dataframe with nested data instead of separate columns

Want I want from the query

Upvotes: 0

Views: 3289

Answers (1)

Alexander Sloutsky
Alexander Sloutsky

Reputation: 3017

It seems you're having a collision between bag_unpack() output and columns already existing in the source of bag_unpack(). Depending on what you end-goal is:

  • If you want to keep both columns: use project-rename to rename the existing column to another name (e.g. .. | project-rename orig_cloud_RoleName = cloud_RoleName )
  • If you want to remove column from the source: use project-away (e.g. .. | project-away cloud_RoleName)

Upvotes: 1

Related Questions