Hello, I have a dynamic column and I just need expand all results in columns like example below: It's possible convert this json values to single columns like below ?

@VolginRnB Thanks for reaching out. Yes, absolutely. The mv-expand operator and bag_unpack() plugin can be very useful utilities for handling dynamic data in KQL. To replicate your requirement, I worked up the following query with some sample data having similar structure as shown in your screenshot above: print dynamic_properties = dynamic( [ { "tenantId": "t1", "objectId": "o1", "permissions": { "keys": [ "Get", "List", "Update", "Create", "Import", "Delete" ], "certificates": [ "Get", "List" ], "secrets": ["Get", "List", "Set", "Delete", "Recover"] }}, { "tenantId": "t2", "objectId": "o2", "permissions": { "keys": [ "Get", "List", "Update" ], "certificates": [ "Get" ], "secrets": ["Get", "List", "Set" ] }} ]) | mv-expand dynamic_properties | mv-expand dynamic_properties.tenantId | evaluate bag_unpack(dynamic_properties) | evaluate bag_unpack(permissions) | mv-expand keys, certificates, secrets | project-away dynamic_properties_tenantId This would produce output similar to the following: Feel free to modify the query as required to suit your use-case. Hope this helps. Do let us know if you have further questions. ---------- If an answer is helpful, please " Accept answer " and/or " Up-Vote " which might help other community members reading this thread.

Azure KQL - expand dynamics column

Accepted answer

BhargaviAnnadevara-MSFT 5,446 Reputation points

2021-07-12T09:20:45.447+00:00
@VolginRnB Thanks for reaching out. Yes, absolutely. The mv-expand operator and bag_unpack() plugin can be very useful utilities for handling dynamic data in KQL.

To replicate your requirement, I worked up the following query with some sample data having similar structure as shown in your screenshot above:

print dynamic_properties = dynamic( [ { "tenantId": "t1", "objectId": "o1", "permissions": { "keys": [ "Get", "List", "Update", "Create", "Import", "Delete" ], "certificates": [ "Get", "List" ], "secrets": ["Get", "List", "Set", "Delete", "Recover"] }}, { "tenantId": "t2", "objectId": "o2", "permissions": { "keys": [ "Get", "List", "Update" ], "certificates": [ "Get" ], "secrets": ["Get", "List", "Set" ] }} ]) | mv-expand dynamic_properties | mv-expand dynamic_properties.tenantId | evaluate bag_unpack(dynamic_properties) | evaluate bag_unpack(permissions) | mv-expand keys, certificates, secrets | project-away dynamic_properties_tenantId

This would produce output similar to the following:

Feel free to modify the query as required to suit your use-case.
Hope this helps. Do let us know if you have further questions.

----------

If an answer is helpful, please "Accept answer" and/or "Up-Vote" which might help other community members reading this thread.
Please sign in to rate this answer.
VolginRnB 256 Reputation points

2021-07-12T12:27:18.157+00:00

Hello @BhargaviAnnadevara-MSFT

Thanks for the help!

I runned the same query and worked perfectly, the data is same structure, but when I tried run on my case, I receive the error below. Can you help me please ?

BhargaviAnnadevara-MSFT 5,446 Reputation points

2021-07-12T12:50:04.78+00:00

@VolginRnB Thanks for the response. I had used dynamic_properties to hold the sample data to test the query. :)
Please replace it with the appropriate column names from the table you are working with, to customize it for your use-case.

From the screenshot posted above, this might be what you're looking for:

testekv_CL | mv-expand properties_accessPolicies_s | mv-expand properties_accessPolicies_s.tenantId | evaluate bag_unpack(properties_accessPolicies_s) | evaluate bag_unpack(permissions) | mv-expand keys, certificates, secrets | project-away properties_accessPolicies_s_tenantId

Feel free to modify it as required. Hope this helps!

VolginRnB 256 Reputation points

2021-07-12T15:33:05.567+00:00

@BhargaviAnnadevara-MSFT

Thanks again, but for one reason that I dont know, I can't run this query, I can not use mv-expand with my propertie "properties_accessPolicies_s", I'm going to send the error below:

Can you help me, please ? I'm going to send my data exactly format (maybe it can help us)
113958-mydata.txt

BhargaviAnnadevara-MSFT 5,446 Reputation points

2021-07-12T16:08:50.27+00:00

@VolginRnB The A mv-expand operator only works with columns, or scalar expressions, that are of type dynamic.
Since your table testekv_CL appears to be aggregating custom logs, could you please let me know the datatype of the column properties_accessPolicies_s?

You can run the following and share the output here:

testekv_CL | getschema

BhargaviAnnadevara-MSFT 5,446 Reputation points

2021-07-13T03:48:51.517+00:00

@VolginRnB Any further update here?

VolginRnB 256 Reputation points

2021-07-14T12:44:50.917+00:00

Hello @BhargaviAnnadevara-MSFT

I'm going to send the result from this query.

Can you help me how to expand this column "properties_accessPolicies_s" ? Thanks!

BhargaviAnnadevara-MSFT 5,446 Reputation points

2021-07-14T14:11:34.97+00:00

@VolginRnB Thanks for getting back with the requested details. As suspected, the properties_accessPolicies_s column seems to be a string while the mv-expand operator expects input of type dynamic, and hence the error.

So to resolve the error, you would first have to turn the JSON string into a dynamic object using parse_json() function and then mv-expand it.

The updated query would look like the following:

testekv_CL | extend properties_accessPolicies = parse_json(properties_accessPolicies_s) //Interprets a string as a JSON value and returns as dynamic | mv-expand properties_accessPolicies | mv-expand properties_accessPolicies.tenantId | evaluate bag_unpack(properties_accessPolicies) | evaluate bag_unpack(permissions) | mv-expand keys, certificates, secrets | project-away properties_accessPolicies_tenantId, properties_accessPolicies_s

Please tweak the query as needed to get the desired output. Hope this helps. :)

VolginRnB 256 Reputation points

2021-07-15T11:28:08.913+00:00

@BhargaviAnnadevara-MSFT

This is exactly was I need. Thank you very much, I researched a lot over the internet and I can not found this result for my case.

Thanks again!!!!

BhargaviAnnadevara-MSFT 5,446 Reputation points

2021-07-15T11:33:42.093+00:00

@VolginRnB Thanks for confirming. Glad that helped!
Sign in to comment

Azure KQL - expand dynamics column

0 additional answers