How can I get a specific parameter field using KQL ?

[Alexander_Ceyran]

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

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

Thanks a lot

Alexander

[#2]

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.

[#3 · Accepted 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"

[#4]

Thanks @Gary Bushey, that solves it for me

[#5]

@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

[#6]

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

[#7]

@ArjunPrasad Take a look at the parse operator.   https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/parseoperator