How to aggregate to one custom event from multiple events based on a property value

New Contributor

We have tracked custom telemetries to Application Analytics,

Each custom event has customDimensions with below schema:

customDimensions:{"ProjectName":"projectname1",

                  "SystemName:"systemname1,

                  "OverallStatus": "true",

                  "SubStatus1":"true",

                  "SubStatus1":"false",

}

 

now we have traced events show as below, and I have had the scripts to get below list.

ProjectName   SystemName  OverallStatus   SubStatus1  SubStatus2

project1      system1     true            true        true

project1      system2     false           true        false

project1      system3     false           false       false

project2      system4     true            true        true

project2      system5     false           false       true

 

I am struggling to work out a query to get an event for each project(which OverallStatus is false, or the first entry that OverallStatus is false), instead of multiple events as above, the expected one output is:

ProjectName   SystemName  OverallStatus   SubStatus1  SubStatus2

project1      system2     false           true        false

project2      system5     false           false       true

 

Could someone give me a light what the query would be? or what script or function I can refer to?

Many thanks

Denny

1 Reply

or is it possible to go through each item in a dataset, and then re-create a new dataset with each project has one item.