Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community

Issue with parsing array of JSON values

Copper Contributor


I am working on an ASIM User Management parser for the Okta System log. I have hit issues with trying to parse the target field. As below this is a list of JSON objects.


"target": [ List of Objects of the form:
               "id": String, Required
               "type": String, Required
               "alternateId": String, Optional
               "displayName": String, Optional
               "detailEntry" = {
                    String -> String/Resource Map


 Goal is to map the id, displayName and alternateId fields to different columns depending on the contents of type. The output needs to be returned as one row.

First attempt at doing this is below. This extracts the TargetUser fields correctly but I can't replicate it for the Group fields. 


      target = parse_dynamic(target_s)
    | mv-apply target on (
      where target.type == "User"
      | extend 
        TargetUserId =,
        TargetUserIdType = "OktaId",
        TargetUsername = target.alternateId,
        TargetUsernameType = "Simple"


Second attempt at doing this is below. This extracts all the right fields but returns the results as two rows - one where the target user fields are populated and the other where the group fields are populated.


    | extend target = todynamic(target_s)
    | mv-apply target on (
        TargetUserId = iff(target.type == "User",, ""),
        TargetUserIdType = iff(target.type == "User", "OktaId", ""),
        TargetUsername = iff(target.type == "User", target.alternateId, ""),
        TargetUsernameType = iff(target.type == "User", "Simple", ""),
        GroupId = iff(target.type == "UserGroup",, ""),
        GroupName  = iff(target.type == "UserGroup", target.displayName, ""),
        GroupNameType = iff(target.type == "UserGroup", "Simple", "")


 I am after any suggestions on how to do this correctly. I have had a look through default parsers but can't see a workable solution for this.



1 Reply

If the order of object types in the array is guaranteed, this can be done in a relatively simple and performant manner. Do you have access to Okta documentation on the log format to confirm if that is the case? It would also help to know if there is maximum of 1 of each object type in the array.

The solutions that comes to mind for when neither of this is guaranteed involves serialize -> row_number() -> mv-expand -> your extend extraction -> summarize by row_number. This would be fine for a one-off query, but I don't think it's suitable for a parser because of performance issues. How many logs are you receiving in this table per hour?

btw, you have multiple rows in your output because mv-apply "returns the union of the results of all subqueries".

Kind regards,