Forum Discussion
How can I get a specific parameter field using KQL ?
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
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"
6 Replies
- 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 eventsArjunPrasad Take a look at the parse operator. https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/parseoperator
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 GaryBushey, that solves it for me
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.resourceWhere "resource" in "tmp.resource" is the name of a field in the Properties column
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.
