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

Question

&nbsp;I want to query the OfficeActivity table and pull out values from the Parameters field.&nbsp; 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.&nbsp; I do not know what position the Identity and User appear so i cannot use normal [0] or [1] indexing.&nbsp;&nbsp;I would like the end result to be something like this.&nbsp;TimeGeneratedOperationUserIdParamters.IdentityParameters.UserParameters.AccessRightsx/x/x/Add-MailboxPermissionBobJohnPeterFullAccess&nbsp;&nbsp;

garybushey · Answer

AndrewX&nbsp;Look at the bag_unpack command.&nbsp; I have a blog post about it here:&nbsp;Azure KQL: Access sub-columns using the bag_unpack plugin – Yet Another Security Blog (garybushey.com)

andrewx · Answer

GaryBushey&nbsp;Hi Gary thank you for your reply.&nbsp; 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.&nbsp;I'm trying to convert those multiple rows into 1 row with 4 columns instead?&nbsp; Would you know how i can achieve that?&nbsp;&nbsp;&nbsp;

garybushey · Answer

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)

andrewx · Answer

GaryBushey&nbsp;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.&nbsp; I only want one row per record but with multiple columns.&nbsp;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"}]&nbsp;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&nbsp;Add-MailboxPermission command.&nbsp;I only care about the Identity, User and AccessRights fields, which WILL be present in each record.&nbsp;I want an end result of this.IdentityUserAccessRightsBobJohnFullAccessJasonBarryFullAccess&nbsp;I will also add other columns from the record as well, but i am just stuck with this part.

andrewx · Answer

GaryBushey&nbsp;This is as far as i can go.&nbsp;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)&nbsp;&nbsp;It results in this output of multiple tables.&nbsp;&nbsp;&nbsp;I need to now take those rows and convert them to columns.&nbsp; The Name fields should be Column headers.&nbsp;

Forum Discussion

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

Share

Resources

TimeGenerated	Operation	UserId	Paramters.Identity	Parameters.User	Parameters.AccessRights
x/x/x/	Add-MailboxPermission	Bob	John	Peter	FullAccess