Recently I wanted to display a summary of application activity per calendar week, and preferably render it as a Heatmap.
It took me longer than I care to admit, so I'm posting my results here. Hopefully it will save you some time =)
Query Application Insights Logs
Let's make a quick test first.
- Sign in to the Azure portal.
- Under the Monitoring section, select Logs.
- Paste the following in the query editor and Run ```kusto
let start = startofweek(ago(1h));
let end= endofweek(now());
let dow = dynamic(["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"]);
pageViews
| where timestamp >= start
| where client_Type == 'Browser'
| make-series Metric=sum(itemCount) default=0 on timestamp in range(start, end, 1d)
| mvexpand timestamp to typeof(datetime), Metric to typeof(long)
| extend WeekDay = toint(dayofweek(timestamp) / 1d), KW=week_of_year(timestamp)
| extend WeekDayName=tostring(dow[WeekDay])
| order by timestamp asc
| project-away timestamp,WeekDay
| evaluate pivot(WeekDayName, sum(Metric))
| project KW,column_ifexists("Mon",""),column_ifexists("Tue",""),column_ifexists("Wed",""),column_ifexists("Thu",""),column_ifexists("Fri",""),column_ifexists("Sat",""),column_ifexists("Sun","")
Depending on the data range defined in the first two lines, and presence of logs ingested to the Application Insights workspace, you may either get the _No results found_
or a table aggregating page views across calendar weeks and weekdays
### What happened here?
Few pieces here are important:
[`dayofweek()`](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/dayofweekfunction): returns the integer number of days since the preceding Sunday, as a timespan. Sunday=0
`let dow = dynamic(["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"])` is a [dictionary](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/scalar-data-types/dynamic) that allows translating weekday numbers into names
`| order by timestamp asc` makes sure that if your timespan is across two years, the last year's calendar week is displayed before this year (51,52,1,2)
`| project KW,column_ifexists("Mon","")` this is the part where we make sure that Monday is displayed as the first day of the week; `column_ifexists` ensures no errors are thrown in no data is returned
## Application Insights Workbook
We are not done yet.
1. Under the **Monitoring** section, select **Workbooks** and create a new Workbook
1. Add Time Range parameter:
1. Add query and paste the query you used before to the Query Editor.
1. This time we can refer to the `TimeRange` parameter you just created
1. To display the data in a **heatmap** format, change **Column Settings**
1. Save your work
## Gallery Template
You may find the Gallery Template [here](https://gist.github.com/kkazala/36d48bdae610089179c8058a97070cb6).
To use it, create a new workbook, open it in **Edit** mode, switch to **Advanced Editor**
paste the contents and apply
Top comments (0)