Application Insights API: Log Analytics queries using PowerShell

It shouldn’t come as a surprise that I have another blog post on Application Insights- I can’t help it, I’m smitten. The Log Analytics interface can be a bit intimidating, and same goes for the charts for Application Insight resources. It has been frustrating that I can’t really get my colleagues to use them more, so I decided to spend an hour and put together a simple app that pulls out some data for us while we are testing a major refactoring that we recently published to QA. Working with the Application Insights API is very easy and straightforward.

The query below gets all SQL queries executed the last 15 minutes (excluding Hangfire queries), it takes the last 100 queries and then grabs the timestamp, target and a substring of the SQL data.

To use the API, you need two things:

The application (Application Insights resource) id, and an API key. The API key can be generated in the Azure portal.

Go to the Application Insight resource, scroll down to API access, and grab the id, and generate a key. Copy and save the key somewhere safe- you won’t be able to retrieve it afterwards.

You can do different types of queries and the documentation is the best place to go for the information. In this post I’m focusing on doing queries, which is what I use the most. The API URI lets you specify the type of operation and for queries it’s the ‘query’ operation. Afterwards you pass in a parameter, and it needs the actual query so that is what you pass in as a query string.

 <span style="color: #333399;">$operation = "query"</span>

$sqlQuery=[uri]::EscapeUriString("?query=dependencies|where timestamp >= ago(15h) and type ==‘SQL’ and target notcontains ‘Hangfire’ | order by timestamp desc | take 100 | project timestamp, target,sql = substring(data, 0, 25) “)

$key = “mykey”

$appId = “myAppId”

$headers = @{ “X-Api-Key” = $key; “Content-Type” = “application/json” }

$response = Invoke-WebRequest -uri “https://api.applicationinsights.io/v1/apps/$appId/$operation$sqlQuery" -Headers $headers

$json = ConvertFrom-Json $response.Content

If you’ve specififed the headers you will get a JSON result in return, and PowerShell has the fabulous ConertFrom-Json cmdlet that you can use to parse out the result. The result itself is an array of tables, and not so surprisingly each table (in my case just one table) has rows, and a row an array of items.

It works wonderfully, and once you start building those queries it’s hard to stop, it’s a lot of fun!

If you need a quick GUI for your PowerShell script try out PoshGUI- a really neat online service (free, no registering at the time of writing) that lets you quickly create a GUI, with events and everything.

PoSHGUI

Here is a quick and dirty example for the above query

application-insight-api5

Application Insights API: Log Analytics queries using PowerShell

Comments