Forum Discussion

AndrewX's avatar
AndrewX
Iron Contributor
May 12, 2022

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

  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 Id...
Analytics
investigation
KQL
kusto
monitoring
AndrewX's avatar
AndrewX
Iron Contributor
May 12, 2022

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

 

 

 

  • GaryBushey's avatar
    GaryBushey
    Bronze Contributor
    May 13, 2022
    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)
    • AndrewX's avatar
      AndrewX
      Iron Contributor
      May 13, 2022

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

      • AndrewX's avatar
        AndrewX
        Iron Contributor
        May 13, 2022

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

         

         

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

         

Resources