Forum Discussion
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 Identity and User etc. I do not know what position the Identity and User appear so i cannot use normal [0] or [1] indexing.
I would like the end result to be something like this.
TimeGenerated | Operation | UserId | Paramters.Identity | Parameters.User | Parameters.AccessRights |
x/x/x/ | Add-MailboxPermission | Bob | John | Peter | FullAccess |
- GaryBusheyBronze Contributor
AndrewX Look at the bag_unpack command. I have a blog post about it here: Azure KQL: Access sub-columns using the bag_unpack plugin – Yet Another Security Blog (garybushey.com)
- AndrewXIron 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?
- GaryBusheyBronze ContributorAfter 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)