how to sum max values in application insights analytics

Question

we are going to collect usage metrics (meaning customMeasurements via customEvents) from our application via application insights. The data is collected if our (windows) service starts and then on a daily base via a timer.

I Know that app insights is not meant to use for "on premises" software, but we do it anyway ;-)

So we get the data from all the services that are installed at our clients. Each client has a unique id (GUID) which allows us to group by customer. (fyi we don't know which customer is behind this guid, it's just a value for "grouping" by customer)

the event looks like this:

1. I now want to group by customerId,
2. get the maximum value for a specific metricValue
3. and create the sum for all customers.

I got 1 and 2 already but have no clue how to sum the max values...

demonstrate what I'm trying to do I added a example in SQL:

CREATE TABLE [dbo].[metricData]
(
    [RecId] [int] IDENTITY(1,1) NOT NULL,
    [customerId] [int], 
   [metricValue1] [int],
   [metricValue2] [int]
)


INSERT INTO [dbo].[metricData]     VALUES ( 1234, 1,1)
INSERT INTO [dbo].[metricData]     VALUES ( 1234, 1,2)
INSERT INTO [dbo].[metricData]     VALUES ( 1234, 1,1)
INSERT INTO [dbo].[metricData]     VALUES ( 2345, 6,4)
INSERT INTO [dbo].[metricData]     VALUES ( 2345, 8,7)
INSERT INTO [dbo].[metricData]     VALUES ( 3456, 1,1)
INSERT INTO [dbo].[metricData]     VALUES ( 3456, 1,2)
INSERT INTO [dbo].[metricData]     VALUES ( 3456, 1,1)
INSERT INTO [dbo].[metricData]     VALUES ( 4576, 20,30)


select sum(maxVal1),sum(maxVal2) from
(
  select max(metricValue1) as maxVal1, max(metricValue2) as maxVal2 from metricData
  group by customerId
) t

Basically the same as also asked here, but for app insights :-) SQL: SUM the MAX values of results returned

Thanks for any hints

Answer 1

I got a solution:

customEvents
|where name == "usageMetrics" 
|extend cn = tostring(customDimensions.["CustomerId"])
|summarize maxValTotal=  max(toint(customMeasurements.['metric_Total'])), maxValFree=  max(toint(customMeasurements.['metric_Free']))  by cn
|summarize dcount(cn),sum(maxValTotal), sum(maxValFree)
|render barchart  kind=unstacked    

Hope this helps someone else, as this query language is not so intuitive...

anyway let me know if you have any better solutions to this problem...