Application Insights - How to refer All Tables in a Cross Reference Query

Question

I'm have multiple Application Insights being referred by related multiple API apps as shown below. API1 - AI1 (Application 1 links to Application Insights 1 named ai1) API2 - AI2 (Application 2 links to Application Insights 2 named ai2) API3 - AI3 (Application 3 links to Application Insights 3 named ai3) and so on. API1 calls API2 which calls API3 Now, I need to pull ALL the information (ex: Requests, Dependencies, Events etc) from All the three Application Insights in a cross reference query.

union     
(
app('ai1').requests
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
),
(
app('ai1').dependencies
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
),
(
app('ai2').requests
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
),
(
app('ai2').dependencies
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
),
(
app('ai3').requests
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
),
(
app('ai3').dependencies
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
)

The above query perfectly works. As you might notice the query is not precise. Is there a way, I can union all type of tables into one so that my query is simple and precise something as shown below.

union 
(
app('ai1').**union**
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
),
(
app('ai2').**union**
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
),
(
app('ai3').**union**
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
)

score 3 · Answer 1 · answered Aug 04 '20 at 18:02

you could also do this in Azure Workbooks in the portal, though, instead of passing all the app ids in the query text, your query would literally just be

union requests, dependencies
| where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"

and then you'd pick which apps to run the query against from the resource picker, which allows multiselect.

you could also turn the operation id, tables, time range, resources into parameters etc:

a working example of that workbook as json:

{
  "version": "Notebook/1.0",
  "items": [
    {
      "type": 9,
      "content": {
        "version": "KqlParameterItem/1.0",
        "crossComponentResources": [
          "value::selected"
        ],
        "parameters": [
          {
            "id": "75168f58-a753-45aa-97d9-89bf8f99d18e",
            "version": "KqlParameterItem/1.0",
            "name": "timeRange",
            "label": "Time Range",
            "type": 4,
            "isRequired": true,
            "value": {
              "durationMs": 86400000
            },
            "typeSettings": {
              "selectableValues": [
                {
                  "durationMs": 300000
                },
                {
                  "durationMs": 900000
                },
                {
                  "durationMs": 1800000
                },
                {
                  "durationMs": 3600000
                },
                {
                  "durationMs": 14400000
                },
                {
                  "durationMs": 43200000
                },
                {
                  "durationMs": 86400000
                },
                {
                  "durationMs": 172800000
                },
                {
                  "durationMs": 259200000
                },
                {
                  "durationMs": 604800000
                },
                {
                  "durationMs": 1209600000
                },
                {
                  "durationMs": 2419200000
                },
                {
                  "durationMs": 2592000000
                },
                {
                  "durationMs": 5184000000
                },
                {
                  "durationMs": 7776000000
                }
              ],
              "allowCustom": true
            },
            "timeContext": {
              "durationMs": 86400000
            }
          },
          {
            "id": "4e02493e-dbbe-478a-8992-d0897a4cb92c",
            "version": "KqlParameterItem/1.0",
            "name": "operationId",
            "type": 1,
            "isRequired": true,
            "value": "eec42c35781a8e4199c420b8fda7bf87",
            "timeContext": {
              "durationMs": 86400000
            }
          },
          {
            "id": "6a545372-8f82-43bc-b77c-9074c1ff18d0",
            "version": "KqlParameterItem/1.0",
            "name": "tables",
            "label": "Tables",
            "type": 2,
            "isRequired": true,
            "multiSelect": true,
            "quote": "",
            "delimiter": ",",
            "value": [
              "requests",
              "dependencies"
            ],
            "typeSettings": {
              "additionalResourceOptions": []
            },
            "jsonData": "[\"requests\", \"dependencies\", \"traces\", \"exceptions\"]",
            "timeContext": {
              "durationMs": 86400000
            }
          },
          {
            "id": "da2d5bd6-cdfe-43c1-b6d6-0a211d74953b",
            "version": "KqlParameterItem/1.0",
            "name": "Resources",
            "type": 5,
            "isRequired": true,
            "multiSelect": true,
            "quote": "'",
            "delimiter": ",",
            "query": "resources | where type =~ \"microsoft.insights/components\"\r\n| project id, name, selected=0, group=resourceGroup | order by 1 asc",
            "crossComponentResources": [
              "value::selected"
            ],
            "value": [
            ],
            "typeSettings": {
              "limitSelectTo": 10,
              "additionalResourceOptions": []
            },
            "timeContext": {
              "durationMs": 86400000
            },
            "queryType": 1,
            "resourceType": "microsoft.resourcegraph/resources"
          }
        ],
        "style": "above",
        "queryType": 1,
        "resourceType": "microsoft.resourcegraph/resources"
      },
      "name": "parameters - 0"
    },
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "union {tables}\r\n| where operation_Id == \"{operationId}\"",
        "size": 0,
        "title": "Operation \"{operationId}\" in {tables} over {timeRange:label}",
        "timeContext": {
          "durationMs": 0
        },
        "timeContextFromParameter": "timeRange",
        "queryType": 0,
        "resourceType": "microsoft.insights/components",
        "crossComponentResources": [
          "{Resources}"
        ]
      },
      "name": "query - 1"
    }
  ],
  "fallbackResourceIds": [
    "Azure Monitor"
  ],
  "$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}

score 2 · Accepted Answer · answered Aug 04 '20 at 02:05

Actually, you cannot union all type of tables into one, like using app('ai1').**union**. The requests, dependencies etc. must be explicitly specified. And please correct me if I misunderstood you.

Alternative, you can use the query like below:

union 
(
 union app("ai1").requests, app("ai1").dependencies
 | where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
),
(
 union app("ai2").requests, app("ai2").dependencies
 | where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
),
(
 union app("ai3").requests, app("ai3").dependencies
 | where operation_Id == "eec42c35781a8e4199c420b8fda7bf87"
)

Yeah. i think, i was looking for this only. Thanks Ivan – Prawin Aug 04 '20 at 07:30 — Prawin, Aug 04 '20 at 07:30

Application Insights - How to refer All Tables in a Cross Reference Query

2 Answers2