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
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(): 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.
- Under the Monitoring section, select Workbooks and create a new Workbook
- Add Time Range parameter:
- Add query and paste the query you used before to the Query Editor.
- This time we can refer to the
TimeRangeparameter you just created
- To display the data in a heatmap format, change Column Settings
- Save your work
You may find the Gallery Template here.
To use it, create a new workbook, open it in Edit mode, switch to Advanced Editor
Top comments (0)