Run an Analytics query

The Query REST API allows you to execute the same queries you run in Application Insights powerful Analytics search experience, so that you can consume the results programmatically. You can learn more about Application Insights Analytics and how to form queries in Analytics.

The easiest want to use the query API is to write your query in the Analytics user experience, paste the query into this site's API Explorer which will properly encode the query for use in the API and provide you with the required query URL.

Create an Analytics query

Let's take a look at a query similar to the one used in the Analytics documentation. Specifically, let's find out what time of day users in Redmond are using our web app. And while we're there, let's see what result codes are returned to their HTTP requests.

requests
    | where timestamp > ago(30d) and client_City == "Redmond"
    | summarize clients = dcount(client_IP) 
        by tod_UTC=bin(timestamp % 1d, 1h), resultCode
    | extend local_hour = (tod_UTC - 8h) % 24h

Running this query in the Analytics portal, we see a successful result and hourly counts segmented by the return codes:

Executing this query in the Analytics portal.

You can see the different hours, return codes and counts in the first three columns.

Execute this same query through the query API using GET

If we paste this same query into the API Explorer and use the demo app, we see the URL-encoded version of the query path has only a single query string parameter, query=:

GET /v1/apps/{app-id}/query?query=requests | where timestamp > ago(30d) and client_City == "Redmond" | summarize clients = dcount(client_IP) by tod_UTC=bin(timestamp % 1d, 1h), resultCode | extend local_hour = (tod_UTC - 8h) % 24h

Try it

Executing this returns the same data returned as we saw in the Analytics portal:

{
  "Tables": [
    {
      "TableName": "Table_0",
      "Columns": [
        {
          "ColumnName": "tod_UTC",
          "DataType": "TimeSpan"
        },
        {
          "ColumnName": "resultCode",
          "DataType": "String"
        },
        {
          "ColumnName": "clients",
          "DataType": "Int64"
        },
        {
          "ColumnName": "local_hour",
          "DataType": "TimeSpan"
        }
      ],
      "Rows": [
        [
          "10:00:00",
          "200",
          2,
          "02:00:00"
        ],
        [
          "07:00:00",
          "200",
          1,
          "-01:00:00"
        ],
        [
          "00:00:00",
          "200",
          3,
          "-08:00:00"
        ],
        [
          "01:00:00",
          "200",
          1,
          "-07:00:00"
        ],
        [
          "11:00:00",
          "200",
          2,
          "03:00:00"
        ],
        [
          "17:00:00",
          "200",
          1,
          "09:00:00"
        ],
        [
          "18:00:00",
          "200",
          3,
          "10:00:00"
        ],
        [
          "21:00:00",
          "500",
          1,
          "13:00:00"
        ],
        [
          "13:00:00",
          "200",
          1,
          "05:00:00"
        ],
        [
          "16:00:00",
          "500",
          1,
          "08:00:00"
        ],
        [
          "18:00:00",
          "500",
          1,
          "10:00:00"
        ],
        [
          "12:00:00",
          "200",
          2,
          "04:00:00"
        ],
        [
          "13:00:00",
          "500",
          1,
          "05:00:00"
        ],
        [
          "04:00:00",
          "200",
          1,
          "-04:00:00"
        ],
        [
          "03:00:00",
          "200",
          2,
          "-05:00:00"
        ],
        [
          "05:00:00",
          "200",
          1,
          "-03:00:00"
        ],
        [
          "07:00:00",
          "500",
          2,
          "-01:00:00"
        ],
        [
          "08:00:00",
          "200",
          2,
          "00:00:00"
        ],
        [
          "04:00:00",
          "500",
          2,
          "-04:00:00"
        ],
        [
          "15:00:00",
          "200",
          2,
          "07:00:00"
        ],
        [
          "14:00:00",
          "200",
          2,
          "06:00:00"
        ],
        [
          "09:00:00",
          "200",
          2,
          "01:00:00"
        ],
        [
          "00:00:00",
          "302",
          1,
          "-08:00:00"
        ],
        [
          "00:00:00",
          "404",
          1,
          "-08:00:00"
        ],
        [
          "01:00:00",
          "500",
          2,
          "-07:00:00"
        ],
        [
          "19:00:00",
          "200",
          1,
          "11:00:00"
        ],
        [
          "20:00:00",
          "200",
          2,
          "12:00:00"
        ],
        [
          "20:00:00",
          "500",
          2,
          "12:00:00"
        ],
        [
          "23:00:00",
          "200",
          3,
          "15:00:00"
        ],
        [
          "00:00:00",
          "304",
          2,
          "-08:00:00"
        ],
        [
          "16:00:00",
          "200",
          2,
          "08:00:00"
        ],
        [
          "02:00:00",
          "200",
          1,
          "-06:00:00"
        ],
        [
          "05:00:00",
          "500",
          1,
          "-03:00:00"
        ],
        [
          "06:00:00",
          "200",
          3,
          "-02:00:00"
        ],
        [
          "06:00:00",
          "500",
          1,
          "-02:00:00"
        ],
        [
          "06:00:00",
          "304",
          1,
          "-02:00:00"
        ],
        [
          "21:00:00",
          "200",
          1,
          "13:00:00"
        ],
        [
          "22:00:00",
          "200",
          2,
          "14:00:00"
        ],
        [
          "22:00:00",
          "500",
          1,
          "14:00:00"
        ],
        [
          "04:00:00",
          "404",
          1,
          "-04:00:00"
        ],
        [
          "06:00:00",
          "404",
          1,
          "-02:00:00"
        ],
        [
          "14:00:00",
          "500",
          1,
          "06:00:00"
        ],
        [
          "17:00:00",
          "500",
          1,
          "09:00:00"
        ]
      ]
    },
    {
      "TableName": "Table_1",
      "Columns": [
        {
          "ColumnName": "Value",
          "DataType": "String"
        }
      ],
      "Rows": [
        [
          "{\"Visualization\":\"table\",\"Title\":\"\",\"Accumulate\":false,\"IsQuerySorted\":false,\"Annotation\":\"\"}"
        ]
      ]
    },
    {
      "TableName": "Table_2",
      "Columns": [
        {
          "ColumnName": "Timestamp",
          "DataType": "DateTime"
        },
        {
          "ColumnName": "Severity",
          "DataType": "Int32"
        },
        {
          "ColumnName": "SeverityName",
          "DataType": "String"
        },
        {
          "ColumnName": "StatusCode",
          "DataType": "Int32"
        },
        {
          "ColumnName": "StatusDescription",
          "DataType": "String"
        },
        {
          "ColumnName": "Count",
          "DataType": "Int32"
        },
        {
          "ColumnName": "RequestId",
          "DataType": "Guid"
        },
        {
          "ColumnName": "ActivityId",
          "DataType": "Guid"
        },
        {
          "ColumnName": "SubActivityId",
          "DataType": "Guid"
        },
        {
          "ColumnName": "ClientActivityId",
          "DataType": "String"
        }
      ],
      "Rows": [
        [
          "2016-04-07T17:23:41.9823689Z",
          4,
          "Info",
          0,
          "Query completed successfully",
          1,
          "2e2d4e85-d3a1-4506-abb9-4194d51813d1",
          "2e2d4e85-d3a1-4506-abb9-4194d51813d1",
          "14fa11a5-b557-45f8-8603-e4648f4e7f28",
          "701a0248-a580-49ea-ae8a-620423d63561"
        ],
        [
          "2016-04-07T17:23:41.9823689Z",
          6,
          "Stats",
          0,
          "{\"ExecutionTime\":0.0781298,\"resource_usage\":{\"cache\":{\"memory\":{\"hits\":0,\"misses\":0,\"total\":0},\"disk\":{\"hits\":0,\"misses\":0,\"total\":0}},\"cpu\":{\"user\":\"00:00:00.6718750\",\"kernel\":\"00:00:00.5468750\",\"total cpu\":\"00:00:01.2187500\"}}}",
          1,
          "2e2d4e85-d3a1-4506-abb9-4194d51813d1",
          "2e2d4e85-d3a1-4506-abb9-4194d51813d1",
          "14fa11a5-b557-45f8-8603-e4648f4e7f28",
          "701a0248-a580-49ea-ae8a-620423d63561"
        ]
      ]
    },
    {
      "TableName": "Table_3",
      "Columns": [
        {
          "ColumnName": "Ordinal",
          "DataType": "Int64"
        },
        {
          "ColumnName": "Kind",
          "DataType": "String"
        },
        {
          "ColumnName": "Name",
          "DataType": "String"
        },
        {
          "ColumnName": "Id",
          "DataType": "String"
        }
      ],
      "Rows": [
        [
          0,
          "QueryResult",
          "PrimaryResult",
          "d66d5010-f406-413f-8553-cc7ffe82e212"
        ],
        [
          1,
          "QueryResult",
          "@ExtendedProperties",
          "2da22372-677d-44b2-9909-01b3965905a6"
        ],
        [
          2,
          "QueryStatus",
          "QueryStatus",
          "00000000-0000-0000-0000-000000000000"
        ]
      ]
    }
  ]
}

The first table (Table_0) first defines the columns just as shown when this query is run in the Analytics portal:

      "TableName": "Table_0",
      "Columns": [
        {
          "ColumnName": "tod_UTC",
          "DataType": "TimeSpan"
        },
        {
          "ColumnName": "resultCode",
          "DataType": "String"
        },
        {
          "ColumnName": "clients",
          "DataType": "Int64"
        },
        {
          "ColumnName": "local_hour",
          "DataType": "TimeSpan"
        }

Each row of this table has these values, for example in the 10:00 hour (2:00 local time), we see that for return code 200 there were 2 counts, just as we saw in the results from the Analytics portal above:

        [
          "10:00:00",
          "200",
          2,
          "02:00:00"
        ],

The additional tables give more context about the query and are used by the Application Insights Analytics user interface.

Using POST with /query

To use POST, you need to

  1. provide the header Content-Type: application/json; charset=utf-8, and
  2. specify the Analytics query in json, e.g. {"query": "analytics-query"}.

To test this with curl, create a file named params.json which has an Analytics query, for example to get the most recent 5 requests:

{
  "query": "requests | limit 5"
}

The following curl command will run this query from the command line:

curl "https://api.applicationinsights.io/v1/apps/{app-id}/query" -H "X-Api-Key: {api-key}" -H "Content-Type: application/json; charset=utf-8" --data "@params.json"