Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community

Kusto query question, expanding multi-row, getting values from named keys

Iron Contributor


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 Identity and User etc.  I do not know what position the Identity and User appear so i cannot use normal [0] or [1] indexing.


Screen Shot 2022-05-12 at 12.16.26 PM.png


I would like the end result to be something like this.





7 Replies

@Gary Bushey 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?




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)

@Gary Bushey 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.



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.



I will also add other columns from the record as well, but i am just stuck with this part.

@Gary Bushey This is as far as i can go.


| 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.


@AndrewX Try adding

| evaluate pivot(Name)

at the end to see if that gives you want you are looking for,

I 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)