Kusto query question, expanding multi-row, getting values from named keys

%3CLINGO-SUB%20id%3D%22lingo-sub-3366151%22%20slang%3D%22en-US%22%3EKusto%20query%20question%2C%20expanding%20multi-row%2C%20getting%20values%20from%20named%20keys%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3366151%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20query%20the%20OfficeActivity%20table%20and%20pull%20out%20values%20from%20the%20Parameters%20field.%26nbsp%3B%20The%20field%20is%20a%20JSON%20string%2C%20so%20i%20know%20i%20need%20to%20convert%20to%20to%20Dynamic%2C%20and%20then%20i%20need%20to%20get%20values%20for%20Identity%20and%20User%20etc.%26nbsp%3B%20I%20do%20not%20know%20what%20position%20the%20Identity%20and%20User%20appear%20so%20i%20cannot%20use%20normal%20%5B0%5D%20or%20%5B1%5D%20indexing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202022-05-12%20at%2012.16.26%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371226iADD39EC4F9BC75E0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202022-05-12%20at%2012.16.26%20PM.png%22%20alt%3D%22Screen%20Shot%202022-05-12%20at%2012.16.26%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20the%20end%20result%20to%20be%20something%20like%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3ETimeGenerated%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EOperation%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EUserId%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EParamters.Identity%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EParameters.User%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EParameters.AccessRights%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3Ex%2Fx%2Fx%2F%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EAdd-MailboxPermission%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EBob%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EJohn%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EPeter%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EFullAccess%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3366151%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnalytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EInvestigation%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMonitoring%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3367060%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20query%20question%2C%20expanding%20multi-row%2C%20getting%20values%20from%20named%20keys%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3367060%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F4821%22%20target%3D%22_blank%22%3E%40Andrew%20Huddleston%3C%2FA%3E%26nbsp%3BLook%20at%20the%20bag_unpack%20command.%26nbsp%3B%20I%20have%20a%20blog%20post%20about%20it%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.garybushey.com%2F2022%2F04%2F16%2Fazure-kql-access-sub-columns-using-the-bag_unpack-plugin%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EAzure%20KQL%3A%20Access%20sub-columns%20using%20the%20bag_unpack%20plugin%20%E2%80%93%20Yet%20Another%20Security%20Blog%20(garybushey.com)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3367152%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20query%20question%2C%20expanding%20multi-row%2C%20getting%20values%20from%20named%20keys%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3367152%22%20slang%3D%22en-US%22%3E%3CP%3E%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%26nbsp%3BHi%20Gary%20thank%20you%20for%20your%20reply.%26nbsp%3B%20I%20have%20read%20your%20blog%20and%20found%20it%20helpful%2C%20however%20i%20am%20already%20using%20the%20bag_unpack%20plugin%2C%20however%20mv-expand%20and%20bag_unpack%20seems%20to%20expand%20out%20the%20array%20into%20multiple%20rows%2C%20per%20event.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20convert%20those%20multiple%20rows%20into%201%20row%20with%204%20columns%20instead%3F%26nbsp%3B%20Would%20you%20know%20how%20i%20can%20achieve%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3369850%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20query%20question%2C%20expanding%20multi-row%2C%20getting%20values%20from%20named%20keys%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3369850%22%20slang%3D%22en-US%22%3EAfter%20the%20mv_expand%2C%20you%20should%20just%20be%20able%20to%20access%20the%20entries%20like%3CBR%20%2F%3E%7C%20extend%20Name%20%3D%20Parameters.Name%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20also%20see%20if%20the%20pivot%20plugin%20will%20work%20for%20your%20needs%20(I%20don't%20have%20the%20needed%20data%20to%20be%20able%20to%20test%20it%20for%20you)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3370854%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20query%20question%2C%20expanding%20multi-row%2C%20getting%20values%20from%20named%20keys%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370854%22%20slang%3D%22en-US%22%3E%3CP%3E%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%26nbsp%3BI%20can%20access%20items%20using%20Parameters.Name%2C%20but%20I%20must%20still%20be%20missing%20something%2C%20as%20it%20still%20results%20in%20multiple%20rows%20for%20a%20single%20record.%26nbsp%3B%20I%20only%20want%20one%20row%20per%20record%20but%20with%20multiple%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20some%20example%20data%20from%20the%20%22Add-MailboxPermission%22%20Parameters%20Field.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%5B%7B%22Name%22%3A%22Identity%22%2C%22Value%22%3A%22Bob%22%7D%2C%7B%22Name%22%3A%22User%22%2C%22Value%22%3A%22John%22%7D%2C%7B%22Name%22%3A%22AccessRights%22%2C%22Value%22%3A%22FullAccess%22%7D%5D%0A%5B%7B%22Name%22%3A%22Confirm%22%2C%22Value%22%3A%22False%22%7D%2C%7B%22Name%22%3A%22AutoMapping%22%2C%22Value%22%3A%22True%22%7D%2C%7B%22Name%22%3A%22Identity%22%2C%22Value%22%3A%22Jason%22%7D%2C%7B%22Name%22%3A%22AccessRights%22%2C%22Value%22%3A%22FullAccess%22%7D%2C%7B%22Name%22%3A%22User%22%2C%22Value%22%3A%22Barry%22%7D%5D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Parameters%20field%20is%20a%20string%2C%20it%20contains%20an%20array%20of%20JSON%20objects%2C%20sometimes%203%20objects%2C%20sometimes%20more%2C%20depending%20on%20how%20many%20Parameters%20are%20selected%20in%20the%26nbsp%3BAdd-MailboxPermission%20command.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20care%20about%20the%20Identity%2C%20User%20and%20AccessRights%20fields%2C%20which%20WILL%20be%20present%20in%20each%20record.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20an%20end%20result%20of%20this.%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CSTRONG%3EIdentity%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CSTRONG%3EUser%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CSTRONG%3EAccessRights%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EBob%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EJohn%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EFullAccess%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EJason%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EBarry%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EFullAccess%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20also%20add%20other%20columns%20from%20the%20record%20as%20well%2C%20but%20i%20am%20just%20stuck%20with%20this%20part.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3371024%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20query%20question%2C%20expanding%20multi-row%2C%20getting%20values%20from%20named%20keys%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3371024%22%20slang%3D%22en-US%22%3E%3CP%3E%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%26nbsp%3BThis%20is%20as%20far%20as%20i%20can%20go.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3Edatatable(d%3Astring)%0A%5B%0A%20%20%20%20'%5B%7B%22Name%22%3A%22Identity%22%2C%22Value%22%3A%22Bob%22%7D%2C%7B%22Name%22%3A%22User%22%2C%22Value%22%3A%22John%22%7D%2C%7B%22Name%22%3A%22AccessRights%22%2C%22Value%22%3A%22FullAccess%22%7D%5D'%2C%0A%20%20%20%20'%5B%7B%22Name%22%3A%22Confirm%22%2C%22Value%22%3A%22False%22%7D%2C%7B%22Name%22%3A%22AutoMapping%22%2C%22Value%22%3A%22True%22%7D%2C%7B%22Name%22%3A%22Identity%22%2C%22Value%22%3A%22Jason%22%7D%2C%7B%22Name%22%3A%22AccessRights%22%2C%22Value%22%3A%22FullAccess%22%7D%2C%7B%22Name%22%3A%22User%22%2C%22Value%22%3A%22Barry%22%7D%5D'%0A%5D%0A%7C%20mv-expand%20kind%3Darray%20todynamic(d)%20to%20typeof(dynamic)%0A%2F%2F%20%7C%20extend%20User%20%3D%20d.Name%0A%7C%20evaluate%20bag_unpack(d)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20results%20in%20this%20output%20of%20multiple%20tables.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22multiplerows.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371467iE5157B6A25B38D1A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22multiplerows.png%22%20alt%3D%22multiplerows.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20now%20take%20those%20rows%20and%20convert%20them%20to%20columns.%26nbsp%3B%20The%20Name%20fields%20should%20be%20Column%20headers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3379563%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20query%20question%2C%20expanding%20multi-row%2C%20getting%20values%20from%20named%20keys%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3379563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F4821%22%20target%3D%22_blank%22%3E%40AndrewXyz%3C%2FA%3E%26nbsp%3BTry%20adding%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20evaluate%20pivot(Name)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eat%20the%20end%20to%20see%20if%20that%20gives%20you%20want%20you%20are%20looking%20for%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

 

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.

 

Screen Shot 2022-05-12 at 12.16.26 PM.png

 

I would like the end result to be something like this.

 

TimeGeneratedOperationUserIdParamters.IdentityParameters.UserParameters.AccessRights
x/x/x/Add-MailboxPermissionBobJohnPeterFullAccess

 

 

6 Replies

@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?

 

 

 

After the mv_expand, you should just be able to access the entries like
| extend Name = Parameters.Name

You could also see if the pivot plugin will work for your needs (I don't have the needed data to be able to test it for you)

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

IdentityUserAccessRights
BobJohnFullAccess
JasonBarryFullAccess

 

I will also add other columns from the record as well, but i am just stuck with this part.

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

 

multiplerows.png

 

I need to now take those rows and convert them to columns.  The Name fields should be Column headers.

 

@AndrewX Try adding

| evaluate pivot(Name)

at the end to see if that gives you want you are looking for,