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:

I now want to group by customerId,
get the maximum value for a specific metricValue
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

also, "I Know that app insights is not meant to use for "on premises" software, but we do it anyway" is not true :). Application Insights is intended for telemetry on anything that can send data to application insights. the only place it won't work is inside a closed network where the telemetry can't get out. for an on-premise app some application insights features like webtests won't work, since the webtest can't see inside your network. — John Gardner

Mat Mat · Accepted Answer · 2017-10-06T14:45:07

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

how to sum max values in application insights analytics

1 Answers