azure-logic-apps | app-insights | devops

Azure Logic Apps - Get Application Insights/Azure Monitor Logs Query Result on Microsoft Teams

Use Azure Logic Apps to create daily reports from Application Insights or Azure Monitor Logs and receive it on Microsoft Teams.

Abhith RajanNovember 17, 2020 · 9 min read · Last Updated:

I love to monitor things. Application Insights is one of my favorite tools in this category. Checking Failures and Performance sections on the Application Insights were my first thing in the morning for some time in the past. Since some apps especially built by third parties lack Application Insights integration, we check Application Gateway access logs too.

Instead of manually checking every day, to ease the process what I did is configured a couple of logic apps to send a message on the Teams channel with a list of results from Application Insights and Azure Monitor Logs.

The configured daily reports include,

  • Application Insights - Performance
  • Application Gateway - Failures

The steps involved in configuring these are almost similar.

First, you need to define the query, in the logs section of each service try out your query and see if the expected results coming. Then we need to design an Adaptive card for teams. This can be easily done on adaptivecards.io.

Adaptive Cards

Adaptive Cards are platform-agnostic snippets of UI, authored in JSON, that apps and services can openly exchange. When delivered to a specific app, the JSON is transformed into native UI that automatically adapts to its surroundings. It helps design and integrate light-weight UI for all major platforms and frameworks.

Adaptive Cards
Adaptive Cards

The easiest way to design the adaptive card is to pick the matching sample from the adaptivecards.io website and edit it in the live designer there itself to adjust it to your needs.

Couple of cards which I designed that suited my needs shared below,

Simple card

A simple adaptive card
A simple adaptive card

The schema of the same given below,

1{
2 "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
3 "type": "AdaptiveCard",
4 "version": "1.2",
5 "speak": "<s>Order update.</s>",
6 "body": [
7 {
8 "type": "ColumnSet",
9 "columns": [
10 {
11 "type": "Column",
12 "width": "stretch",
13 "items": [
14 {
15 "type": "TextBlock",
16 "text": "Order Update",
17 "horizontalAlignment": "right",
18 "isSubtle": true
19 },
20 {
21 "type": "TextBlock",
22 "text": "@{triggerBody()?['Label']}",
23 "horizontalAlignment": "Right",
24 "spacing": "None",
25 "size": "Large",
26 "color": "Attention"
27 }
28 ]
29 }
30 ]
31 },
32 {
33 "type": "ColumnSet",
34 "spacing": "Medium",
35 "separator": true,
36 "columns": [
37 {
38 "type": "Column",
39 "width": 1,
40 "items": [
41 {
42 "type": "TextBlock",
43 "text": "Order",
44 "isSubtle": true,
45 "weight": "Bolder"
46 },
47 {
48 "type": "TextBlock",
49 "text": "@{json(base64ToString(triggerBody()?['ContentData'])).OrderId}",
50 "spacing": "Small"
51 }
52 ]
53 }
54 ]
55 }
56 ],
57 "actions": [
58 {
59 "type": "Action.OpenUrl",
60 "title": "View",
61 "url": "https://my-website.com/path/to/resource"
62 }
63 ]
64}

The above card is used to notify when there is a new message in an Azure Service Bus Topic and the @{triggerBody()?['Label']} is used to get the Message Label.

Similarly, @{json(base64ToString(triggerBody()?['ContentData'])).OrderId} is used to get the OrderId from the message body.

Our focus in this article is on a card with a list of items.

Card with a list of items

Initial design with a single row of information will look like,

Card with single row
Card with single row

And it’s schema,

1{
2 "type": "AdaptiveCard",
3 "body": [
4 {
5 "type": "Container",
6 "style": "emphasis",
7 "items": [
8 {
9 "type": "ColumnSet",
10 "columns": [
11 {
12 "type": "Column",
13 "items": [
14 {
15 "type": "TextBlock",
16 "size": "Large",
17 "weight": "Bolder",
18 "text": "**Performance (App Insights)**"
19 }
20 ],
21 "width": "stretch"
22 }
23 ]
24 }
25 ],
26 "bleed": true
27 },
28 {
29 "type": "Container",
30 "spacing": "Large",
31 "style": "emphasis",
32 "items": [
33 {
34 "type": "ColumnSet",
35 "columns": [
36 {
37 "type": "Column",
38 "spacing": "Large",
39 "items": [
40 {
41 "type": "TextBlock",
42 "weight": "Bolder",
43 "text": "Operation Name"
44 }
45 ],
46 "width": "stretch"
47 },
48 {
49 "type": "Column",
50 "items": [
51 {
52 "type": "TextBlock",
53 "weight": "Bolder",
54 "text": "Count"
55 }
56 ],
57 "width": "auto"
58 },
59 {
60 "type": "Column",
61 "items": [
62 {
63 "type": "TextBlock",
64 "weight": "Bolder",
65 "text": "Avg",
66 "horizontalAlignment": "Right"
67 },
68 {
69 "type": "TextBlock",
70 "text": "Percentiles 50 / 99",
71 "horizontalAlignment": "Right"
72 }
73 ],
74 "width": "stretch",
75 "horizontalAlignment": "Right"
76 }
77 ]
78 }
79 ],
80 "bleed": true
81 },
82 {
83 "type": "Container",
84 "items": [
85 {
86 "columns": [
87 {
88 "items": [
89 {
90 "text": "some operation",
91 "type": "TextBlock",
92 "wrap": true
93 },
94 {
95 "size": "Small",
96 "text": "my app cloud role name",
97 "type": "TextBlock",
98 "wrap": true
99 }
100 ],
101 "spacing": "Large",
102 "type": "Column",
103 "width": "stretch"
104 },
105 {
106 "items": [
107 {
108 "horizontalAlignment": "Right",
109 "separator": true,
110 "text": "321 ",
111 "type": "TextBlock"
112 }
113 ],
114 "type": "Column",
115 "width": "auto"
116 },
117 {
118 "items": [
119 {
120 "color": "Attention",
121 "horizontalAlignment": "Right",
122 "text": "123ms",
123 "type": "TextBlock"
124 },
125 {
126 "horizontalAlignment": "Right",
127 "size": "Small",
128 "text": "456 / 789",
129 "type": "TextBlock"
130 }
131 ],
132 "type": "Column",
133 "width": "stretch"
134 }
135 ],
136 "type": "ColumnSet"
137 }
138 ],
139 "separator": true
140 }
141 ],
142 "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
143 "version": "1.2",
144 "fallbackText": "This card requires Adaptive Cards v1.2 support to be rendered properly."
145}

In the schema, there are three Container,

  • First one contains the main heading
  • Second column headings
  • The last one with the data.

We will be dynamically populating the third container items. And the result looks like,

Card with rows
Card with rows

And for a reference, A slightly altered design also given below,

Card with a row of data
Card with a row of data

And the schema for this one is,

1{
2 "type": "AdaptiveCard",
3 "body": [
4 {
5 "type": "Container",
6 "style": "emphasis",
7 "items": [
8 {
9 "type": "ColumnSet",
10 "columns": [
11 {
12 "type": "Column",
13 "items": [
14 {
15 "type": "TextBlock",
16 "size": "large",
17 "weight": "bolder",
18 "text": "**Application Gateway - Failed Requests**"
19 }
20 ],
21 "width": "stretch"
22 }
23 ]
24 }
25 ],
26 "bleed": true
27 },
28 {
29 "type": "Container",
30 "spacing": "Large",
31 "style": "emphasis",
32 "items": [
33 {
34 "type": "ColumnSet",
35 "columns": [
36 {
37 "type": "Column",
38 "spacing": "Large",
39 "items": [
40 {
41 "type": "TextBlock",
42 "weight": "Bolder",
43 "text": "URL"
44 }
45 ],
46 "width": "stretch"
47 },
48 {
49 "type": "Column",
50 "items": [
51 {
52 "type": "TextBlock",
53 "weight": "Bolder",
54 "text": "Status",
55 "horizontalAlignment": "Right"
56 }
57 ],
58 "width": "auto"
59 },
60 {
61 "type": "Column",
62 "items": [
63 {
64 "type": "TextBlock",
65 "weight": "Bolder",
66 "text": "Count",
67 "horizontalAlignment": "Right"
68 }
69 ],
70 "width": "auto"
71 }
72 ]
73 }
74 ],
75 "bleed": true
76 },
77 {
78 "type": "Container",
79 "items": [
80 {
81 "columns": [
82 {
83 "items": [
84 {
85 "text": "/path/to/resource",
86 "type": "TextBlock",
87 "wrap": true
88 },
89 {
90 "color": "Attention",
91 "size": "Small",
92 "text": "demo.myapp.com",
93 "type": "TextBlock",
94 "wrap": true
95 }
96 ],
97 "spacing": "Large",
98 "type": "Column",
99 "width": "stretch"
100 },
101 {
102 "items": [
103 {
104 "horizontalAlignment": "Right",
105 "separator": true,
106 "text": "404 ",
107 "type": "TextBlock"
108 }
109 ],
110 "type": "Column",
111 "width": "auto"
112 },
113 {
114 "items": [
115 {
116 "horizontalAlignment": "Right",
117 "separator": true,
118 "text": "123 ",
119 "type": "TextBlock"
120 }
121 ],
122 "type": "Column",
123 "width": "auto"
124 }
125 ],
126 "type": "ColumnSet"
127 }
128 ],
129 "separator": true
130 }
131 ],
132 "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
133 "version": "1.2",
134 "fallbackText": "This card requires Adaptive Cards v1.2 support to be rendered properly."
135}

When fed with data, the result is,

Application Gateway - Failed Requests
Application Gateway - Failed Requests

Once we are done with adaptive card design, it’s time to move on to Logic App designer.

Logic App

The only tricky part of the Logic app was to generate the rows of items. The other steps are pretty much straight forward. And the overall steps are,

All steps
All steps

Let’s go through each step.

1. Recurrence trigger

Configured it to trigger daily.

Recurrence trigger
Recurrence trigger

2. Getting a query result

For Application Insights query results, use the Application Insights connector and action Run Analytics Query

Run Analytics Query
Run Analytics Query

Query to get performance metrics from Application insights

1let dataset=requests
2| where timestamp > ago(1d);
3dataset
4| summarize count_=sum(itemCount), avg(duration), percentiles(duration, 50, 99) by operation_Name, cloud_RoleName
5| union(dataset
6| summarize count_=sum(itemCount), avg_duration=avg(duration), percentiles(duration, 50, 99)
7| extend operation_Name="Overall")
8| top 30 by avg_duration desc

And in case of Application Gateway access logs, we need to use the Azure Monitor Logs connector and it’s Run query and list results action, details here.

Run query and list results
Run query and list results

Note: Diagnostic setting for Application Gateway should be configured to feed access log data to the log analytics workspace before.

Query to get failed requests summary from Application Gateway

1AzureDiagnostics
2| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "ApplicationGatewayAccessLog"
3| where httpStatus_d >= 400
4| where TimeGenerated > ago(1d)
5| summarize count_= count(httpStatus_d) by httpStatus_d,requestUri_s, host_s
6| order by count_ desc
7| top 30 by count_
Info

In both queries, I am limiting the number of results since the Teams post message action has a message size limit.

3. Initialize an array variable

Since we are expecting a list of rows from the above query, to map it to rows in the Teams card message, we use this.

Initialize an array variable
Initialize an array variable

4. For each row in the query result, we map the result item to an Adaptive card row.

For each
For each

Here the value is an array of query results from step 2. And for each item in the array, we are appending the JSON object that represents the row of data in the adaptive card (under the third Container items).

5. Append the rows to the rest of the Adaptive card schema in the Teams connector channel.

Initialize an array variable
Initialize an array variable

The full Message is,

1{
2 "type": "AdaptiveCard",
3 "body": [
4 {
5 "type": "Container",
6 "style": "emphasis",
7 "items": [
8 {
9 "type": "ColumnSet",
10 "columns": [
11 {
12 "type": "Column",
13 "items": [
14 {
15 "type": "TextBlock",
16 "size": "Large",
17 "weight": "Bolder",
18 "text": "**Performance (App Insights)**"
19 }
20 ],
21 "width": "stretch"
22 }
23 ]
24 }
25 ],
26 "bleed": true
27 },
28 {
29 "type": "Container",
30 "spacing": "Large",
31 "style": "emphasis",
32 "items": [
33 {
34 "type": "ColumnSet",
35 "columns": [
36 {
37 "type": "Column",
38 "spacing": "Large",
39 "items": [
40 {
41 "type": "TextBlock",
42 "weight": "Bolder",
43 "text": "Operation Name"
44 }
45 ],
46 "width": "stretch"
47 },
48 {
49 "type": "Column",
50 "items": [
51 {
52 "type": "TextBlock",
53 "weight": "Bolder",
54 "text": "Count"
55 }
56 ],
57 "width": "auto"
58 },
59 {
60 "type": "Column",
61 "items": [
62 {
63 "type": "TextBlock",
64 "weight": "Bolder",
65 "text": "Avg",
66 "horizontalAlignment": "Right"
67 },
68 {
69 "type": "TextBlock",
70 "text": "Percentiles 50 / 99",
71 "horizontalAlignment": "Right"
72 }
73 ],
74 "width": "stretch",
75 "horizontalAlignment": "Right"
76 }
77 ]
78 }
79 ],
80 "bleed": true
81 },
82 {
83 "type": "Container",
84 "items": @{variables('rows')},
85 "separator": true
86 }
87 ],
88 "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
89 "version": "1.2",
90 "fallbackText": "This card requires Adaptive Cards v1.2 support to be rendered properly."
91}

Notice the @{variables('rows')}, that’s where the magic happens. So the array we prepared in the previous step gets appended here, and hence we get to see the multiple rows in the adaptive card. To understand better, cross-check the above one with the original schema mentioned in the very beginning here.

Info

The adaptive card schema version kept to 1.2 even though the latest version is 1.3 since Microsoft Teams currently does not support 1.3. That may change in the future.

That’s all. Run the logic app and see a message popping up on Teams.

Written by Abhith Rajan
Abhith Rajan is an aspiring software engineer with more than 8 years of experience and proven successful track record of delivering technology-based products and services.
Buy me a coffee

Was this helpful?

Show some ❤️

Share this page on Twitter to appear on the webmentions* 💡

This page is open source. Noticed a typo? Or something unclear?
Improve this page on GitHub

Related VideosView All

How to perform usage analysis with Azure Monitor Application Insights

Related StoriesView All