Auri's Blog

I log a lot of event data in Microsoft AppCenter. Thousands of events per day. Unfortunately, AppCenter has a hard limit of 200 distinct custom events for their event data. So, when you look at that great event filter they have, you’re not actually seeing all the data. HOWEVER, that does NOT mean AppCenter is limited to a scant 200 events. Hardly. Behind the scenes, AppCenter is powered by Application Insights. As long as you have storage available, you can keep all your events in App Insights. The problems I ran into were:

1. I didn’t know Kusto, or KQL, the query language used by App Insights, and
2. It’s not obvious how to access all the metadata I log for each event.

What’s metadata, you may ask? Well, it’s the additional data you log alongside an event. In AppCenter, the term for metadata is Properties, but I digress. For example, if you’re logging an Software Update Success or Failure, you may also include metadata about a product ID, model number, firmware version, and so forth. Finding the event data is easy. Finding and reporting on the metadata is not so straightforward.

So, dear reader, I have an example query for you. You can copy & paste this into your App Insights query editor and be good to go.

So here’s the query I used to extract a summary of how many software updates succeeded, for specific model numbers, with a count by model number:

customEvents
| where name in ("Software Update: Succeeded")
| extend jsonData = tostring(parse_json(tostring(customDimensions.Properties)).['Model Number'])
| where jsonData in ("model1", "model2", "model3", "model4")
| summarize count() by jsonData

So what’s happening here? Let me explain:

customEvents

This is the table App Insights stores all events sent by AppCenter.
| where name in ("Software Update Succeeded")

This filters those events by the event name.
| extend jsonData = tostring(parse_json(tostring(customDimensions.Properties)).['Model Number'])

This converts the metadata field – aka customDimensions.Properties – from JSON, extracts a particular metadata field – in this case, Model Number – and then returns that metadata value as a string.
| where jsonData in ("model1", "model2", "model3", "model4")

This is a simple filter query. I found if I wanted to get all model numbers I could simply use the following, though your mileage may vary:

| where jsonData !in ("")

And then finally:
| summarize count() by jsonData

This takes the results, by model number, and summarizes with counts. App Insights will even automatically generate a graph for you!

Refining the query above, and as you use more extend keywords to extract more data, you may want to use a more meaningful variable name than jsonData 😁 For example, here’s a more robust query I wrote, identifying the unique counts by total number of users performing the action:

customEvents
| where name in ("Software Update: Succeeded")
| where timestamp > ago(30d)
| extend modelNumber = tostring(parse_json(tostring(customDimensions.Properties)).['Model Number'])
| extend modelName = case(
modelNumber == "model1", "Headphones",
modelNumber == "model2", "Soundbars",
modelNumber == "model3", "Powered Speakers",
"n/a")
| where modelNumber !in ("")
| summarize count() by user_Id, modelName
| summarize count() by modelName

The two summarize filters let me break things down by total number of users with total number of product models. Here you can see how I used proper variable names and used the other useful customEvents data. This also helps me get data similar to the cool, useful graphs AppCenter shows on their dashboard.

You can do a lot more with this data, but it should get you started. I hope it helps you as it did me.

Additional tip:

Application Insights only stores data for 30 days by default. If you want to retain and report on your events beyond that timeframe, make sure you update your App Insights instance settings.