DEV Community


Posted on • Updated on

Application Insights: Display Heatmap of Page Views per calendar week

Recently I wanted to display a summary of application activity per calendar week, and preferably render it as a Heatmap.

Image description

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.

  1. Sign in to the Azure portal.
  2. Under the Monitoring section, select Logs.
  3. Paste the following in the query editor and Run
let start = startofweek(ago(1h));
let end=  endofweek(now());
let dow = dynamic(["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"]);
| 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","")
Enter fullscreen mode Exit fullscreen mode

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

Image description

or a table aggregating page views across calendar weeks and weekdays

Image description

What happened here?

Few pieces here are important:

dayofweek(): 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 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
  2. Add Time Range parameter: Image description
  3. Add query and paste the query you used before to the Query Editor.
  4. This time we can refer to the TimeRange parameter you just created Image description
  5. To display the data in a heatmap format, change Column Settings Image description
  6. Save your work

Image description

Gallery Template

You may find the Gallery Template here.
To use it, create a new workbook, open it in Edit mode, switch to Advanced Editor

Image description
paste the contents and apply

Top comments (0)