SOLVED

How can I get a specific parameter field using KQL ?

%3CLINGO-SUB%20id%3D%22lingo-sub-1321415%22%20slang%3D%22en-US%22%3EHow%20can%20I%20get%20a%20specific%20parameter%20field%20using%20KQL%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321415%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20make%20a%20little%20table%20dashboard%20with%20the%20following%20request%3C%2FP%3E%3CP%3EOfficeActivity%3CBR%20%2F%3E%7C%20where%20OfficeWorkload%20%3D%3D%20%22Exchange%22%3CBR%20%2F%3E%7C%20where%20Operation%20%3D%3D%20%22Add-MailboxPermission%22%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20project%20the%20columns%20TimeGenerated%2C%20%3CSTRONG%3EParameters.Value%20(for%20the%20Identity%20field)%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EParameters.Value%20(for%20the%20AccessRight%20field)%3C%2FSTRONG%3E%2C%20and%20UserId.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20get%20to%20the%20parameters%20part%20because%20sometimes%20the%20fields%20I'm%20interested%20in%20are%20%3CSTRONG%3Ein%20the%20table%20in%20position%200%20or%201%20or%202%20or%203%20(constantly%20changing%20for%20same%20log%20type).%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Capture1.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185371i9AE61ED8C58F7587%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Capture1.PNG%22%20alt%3D%22Capture1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20solution%20to%20get%20the%20specific%20parameter%20field%20(example%20the%20Value%20when%20Name%20%3D%20Identity)%20for%20every%20log%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%3C%2FP%3E%3CP%3EAlexander%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1321779%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20get%20a%20specific%20parameter%20field%20using%20KQL%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321779%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F588497%22%20target%3D%22_blank%22%3E%40Alexander_Ceyran%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20move%20your%20mouse%20in%20front%20of%20the%20value%20you%20want%2C%20you%20see%203%20dots%2C%20if%20you%20then%20click%20on%20the%203%20dots%20you%20have%20the%20options%3A%20Include%2FExclude%2FExtend%20Column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20select%20extend%20column%2C%20the%20following%20will%20be%20added%20to%20your%20query%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Name_%20%3D%20tostring(parse_json(Parameters)%5B%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3E%5D.Name)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EName_%20will%20be%20the%20name%20of%20the%20column.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322654%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20get%20a%20specific%20parameter%20field%20using%20KQL%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F588497%22%20target%3D%22_blank%22%3E%40Alexander_Ceyran%3C%2FA%3E%26nbsp%3Byou%20can%20do%20something%20like%20this.%20Since%20Parameters%20stores%20a%20JSON%20array%20you%20can%20convert%20it%20to%20a%20dynamic%20type%20and%20then%20use%20the%20mv-expand%20command%20to%20expand%20each%20entry%20in%20the%20array%20into%20its%20own%20row%20and%20then%20filter%20the%20rows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EOfficeActivity%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%7C%20where%20OfficeWorkload%20%3D%3D%20%22Exchange%22%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Operation%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Add-MailboxPermission%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20test%20%3D%20(todynamic(Parameters))%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20mv-expand(test)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20test%20%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%20%3CSPAN%3E%22DomainController%22%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322808%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20get%20a%20specific%20parameter%20field%20using%20KQL%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322808%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46875%22%20target%3D%22_blank%22%3E%40Gary%20Bushey%3C%2FA%3E%2C%20that%20solves%20it%20for%20me%20%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Fhtml%2Fimages%2Femoticons%2Fsmile_40x40.gif%22%20alt%3D%22%3Asmile%3A%22%20title%3D%22%3Asmile%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1405352%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20get%20a%20specific%20parameter%20field%20using%20KQL%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1405352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F588497%22%20target%3D%22_blank%22%3E%40Alexander_Ceyran%3C%2FA%3E%26nbsp%3BSomething%20else%20I%20just%20stumbled%20across.%26nbsp%3B%20If%20you%20do%20not%20want%20to%20create%20a%20new%20row%20per%20item%20but%20rather%20a%20new%20column%20you%20can%20do%20something%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20tmp%20%3D%20parse_json(Properties)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20newResource%20%3D%20tmp.resource%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EWhere%20%22resource%22%20in%20%22tmp.resource%22%20is%20the%20name%20of%20a%20field%20in%20the%20Properties%20column%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
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