Azure @ Enterprise - Application Insights string split to find distinct databases from dependencies telemetry

Problem

We have access to the application insights of a distributed application. We don't have access to the server's inventory. But we need to get the database count and names.

This looks like a fresher interview question but this kind of scenario can happen in a real work environment. There is always a reason for asking these questions in fresher interviews.

Solution

It may feel easy as below Kusto query. The database names are in the 'dependencies" telemetry table.

dependencies

| where ['type'] == "SQL"

| distinct targetYes. This gives the target. But not the database name as is. The result will include the SQL server name\instance name and the port number followed by | symbol and the database name.

It is simple to extract the right side of | by using the split() function. The query goes as follows

dependencies

| where ['type'] == "SQL"

| distinct split(target, '|',1)

The split() accepts the input, the delimiter character to split and the index to return value. But this produces errors as follows.

It says "Distinct group key 'target' is of a 'dynamic' type. Please use an explicit cast as grouping by a 'dynamic' type is not supported." What is the meaning?

The split function returns a dynamic type that cannot be given to distinct. 

Below goes the next query.

dependencies

| where ['type'] == "SQL"

| distinct tostring(split(target, '|',1)) 

It works and produce the below result.

This is final. Let the prefix [" and suffix "] be there. Have fun...