May 12 2022 09:40 AM
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 |
May 12 2022 12:59 PM
@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.co...
May 12 2022 01:14 PM - edited May 12 2022 09:22 PM
@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?
May 13 2022 03:59 AM
May 13 2022 07:15 AM
@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.
[{"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.
May 13 2022 07:42 AM
@Gary Bushey 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.
May 16 2022 04:02 AM
@AndrewX Try adding
| evaluate pivot(Name)
at the end to see if that gives you want you are looking for,
Dec 29 2022 07:56 AM