Application insights middleware - Track SQL parameters

Question

Problem: Application insights by default does not track SQL parameters in dependencies telemetry. I want to either enable parameter tracking or manually add parameters.

Little background: I had .Net Core 2 app with application insights. I was reading request body and saving it as custom property with TelemetryInitializer. I migrated to .Net Core 3 where I was not able to use TelemetryInitializer anymore as .Net Core 3 disabled synchronous IO. Similar issue described here. So I have implemented application insights middleware as per this example. I am using EF Core for all my database access.

Question: I found this issue which says that it is possible to "access SqlCommand operation detail in TelemetryInitializer". But how can I achieve this using application insights middleware?

Answer 1

You do not have to write asp.net core middleware for this. Instead you should be using a TelemetryInitializer. Given an intializer like this:

public class CustomInitializer : ITelemetryInitializer
{
    public void Initialize(ITelemetry telemetry)
    {
        if (!(telemetry is DependencyTelemetry supportedTelemetry))
            return;

        if (supportedTelemetry.Type == "SQL" && supportedTelemetry.TryGetOperationDetail("SqlCommand", out var command))
        {
            foreach (DbParameter parameter in ((SqlCommand)command).Parameters)
            {
                supportedTelemetry.Properties.Add(parameter.ParameterName, parameter.Value.ToString());
            }
        }
    }
}

I am not sure why you say

I migrated to .Net Core 3 where I was not able to use TelemetryInitializer anymore as .Net Core 3 disabled synchronous IO

I use TelemetryInitializers all the time.

I see the parameters as custom properties in App Insights:

For reference: I used this code in my controller, based on this tutorial:

using (var bc = new BloggingContext())
{
    bc.Database.EnsureCreated();
    await bc.Blogs.AddAsync(new Blog
    {
        Url = "www.blank.com",
        Rating = 0
    });
    await bc.SaveChangesAsync();
}