SOLVED

How can I get a specific parameter field using KQL ?

Occasional Contributor

Hello everyone,

 

I'd like to make a little table dashboard with the following request

OfficeActivity
| where OfficeWorkload == "Exchange"
| where Operation == "Add-MailboxPermission"

Then project the columns TimeGenerated, Parameters.Value (for the Identity field) and Parameters.Value (for the AccessRight field), and UserId.

 

I can't get to the parameters part because sometimes the fields I'm interested in are in the table in position 0 or 1 or 2 or 3 (constantly changing for same log type).

 

Capture1.PNG

 

Do you have any solution to get the specific parameter field (example the Value when Name = Identity) for every log ?

 

Thanks a lot

Alexander

6 Replies

Hi @Alexander_Ceyran,

 

If you move your mouse in front of the value you want, you see 3 dots, if you then click on the 3 dots you have the options: Include/Exclude/Extend Column.

 

If you select extend column, the following will be added to your query:

 

| extend Name_ = tostring(parse_json(Parameters)[1].Name)
 
Name_ will be the name of the column.
best response confirmed by Alexander_Ceyran (Occasional Contributor)
Solution

@Alexander_Ceyran you can do something like this. Since Parameters stores a JSON array you can convert it to a dynamic type and then use the mv-expand command to expand each entry in the array into its own row and then filter the rows

 

OfficeActivity
| where OfficeWorkload == "Exchange"
| where Operation == "Add-MailboxPermission"
| extend test = (todynamic(Parameters))
| mv-expand(test)
| where test contains "DomainController"

Thanks @Gary Bushey, that solves it for me :smile:

@Alexander_Ceyran Something else I just stumbled across.  If you do not want to create a new row per item but rather a new column you can do something like:

 

| extend tmp = parse_json(Properties)
| extend newResource = tmp.resource
 
Where "resource" in "tmp.resource" is the name of a field in the Properties column
Hi Everyone,

Is there any way to extract the values of Identity/Access Rights as a new field? Parse_json based functions are not suitable in this scenario as the position of those values are changing based on different events