Forum Discussion
AndrewX
May 12, 2022Iron Contributor
Kusto query question, expanding multi-row, getting values from named keys
I want to query the OfficeActivity table and pull out values from the Parameters field. The field is a JSON string, so i know i need to convert to to Dynamic, and then i need to get values for Id...
AndrewX
May 12, 2022Iron Contributor
GaryBushey Hi Gary thank you for your reply. I have read your blog and found it helpful, however i am already using the bag_unpack plugin, however mv-expand and bag_unpack seems to expand out the array into multiple rows, per event.
I'm trying to convert those multiple rows into 1 row with 4 columns instead? Would you know how i can achieve that?
GaryBushey
May 13, 2022Bronze Contributor
After the mv_expand, you should just be able to access the entries like
| extend Name = Parameters.Name
You could also see if the pivot plugin will work for your needs (I don't have the needed data to be able to test it for you)
| extend Name = Parameters.Name
You could also see if the pivot plugin will work for your needs (I don't have the needed data to be able to test it for you)
- AndrewXMay 13, 2022Iron Contributor
GaryBushey I can access items using Parameters.Name, but I must still be missing something, as it still results in multiple rows for a single record. I only want one row per record but with multiple columns.
Here is some example data from the "Add-MailboxPermission" Parameters Field.
[{"Name":"Identity","Value":"Bob"},{"Name":"User","Value":"John"},{"Name":"AccessRights","Value":"FullAccess"}] [{"Name":"Confirm","Value":"False"},{"Name":"AutoMapping","Value":"True"},{"Name":"Identity","Value":"Jason"},{"Name":"AccessRights","Value":"FullAccess"},{"Name":"User","Value":"Barry"}]
The Parameters field is a string, it contains an array of JSON objects, sometimes 3 objects, sometimes more, depending on how many Parameters are selected in the Add-MailboxPermission command.
I only care about the Identity, User and AccessRights fields, which WILL be present in each record.
I want an end result of this.
Identity User AccessRights Bob John FullAccess Jason Barry FullAccess I will also add other columns from the record as well, but i am just stuck with this part.
- AndrewXMay 13, 2022Iron Contributor
GaryBushey This is as far as i can go.
datatable(d:string) [ '[{"Name":"Identity","Value":"Bob"},{"Name":"User","Value":"John"},{"Name":"AccessRights","Value":"FullAccess"}]', '[{"Name":"Confirm","Value":"False"},{"Name":"AutoMapping","Value":"True"},{"Name":"Identity","Value":"Jason"},{"Name":"AccessRights","Value":"FullAccess"},{"Name":"User","Value":"Barry"}]' ] | mv-expand kind=array todynamic(d) to typeof(dynamic) // | extend User = d.Name | evaluate bag_unpack(d)
It results in this output of multiple tables.
I need to now take those rows and convert them to columns. The Name fields should be Column headers.
- pshahi12Dec 29, 2022Brass ContributorI think you might find this link helpful I was trying to achive this as well further my requirements changed but nevertheless a great to know (check for Clive's comment in below link)
https://techcommunity.microsoft.com/t5/azure-observability/custom-query-for-finding-vms-without-software-installed/m-p/3694593/highlight/true#M4267