Application insights in Console/WPF application with DependencyTracking

Question

I have a application insights configuration I am trying to get working in a .NET 4.8 WPF application. Everything seems to be transmitting fine (including the live metrics data) BUT unfortunately I cannot successfully get the system to transmit my SQL queries along with the Dependency information which are being executed by EntityFramework6.

I note that on the following link it makes reference to

For ASP.NET applications, full SQL query text is collected with the help of byte code instrumentation, which requires using the instrumentation engine or by using the Microsoft.Data.SqlClient NuGet package instead of the System.Data.SqlClient library.

Obviously this is not possible for me (I dont think?) given that I am using EntityFramework (which depends on System.Data.SqlClient) but I have installed Microsoft.ApplicationInsights.Agent_** which I believe is the workaround for this issue as the link above suggests.

Further when I look at the data being provided in Azure I note that it is marked as rddf:2.17.0-32 which suggests that the agent is not working correctly.

My initialization code looks like this:

public static TelemetryConfiguration CreateConfig(string instrumentationKey, string authenticationApiKey)
{
    var config = new TelemetryConfiguration()
    {
        ConnectionString = $"InstrumentationKey={instrumentationKey};IngestionEndpoint=https://australiaeast-0.in.applicationinsights.azure.com/",
        TelemetryChannel = new ServerTelemetryChannel()
        {
            DeveloperMode = true
        },
               
    };

    var dependencyTrackingModule = new DependencyTrackingTelemetryModule()
    {
        EnableSqlCommandTextInstrumentation = true
    };

    // prevent Correlation Id to be sent to certain endpoints. You may add other domains as needed.
    dependencyTrackingModule.ExcludeComponentCorrelationHttpHeadersOnDomains.Add("core.windows.net");

    // enable known dependency tracking, note that in future versions, we will extend this list. 
    dependencyTrackingModule.IncludeDiagnosticSourceActivities.Add("Microsoft.Azure.ServiceBus");
    dependencyTrackingModule.IncludeDiagnosticSourceActivities.Add("Microsoft.Azure.EventHubs");

    // initialize the module
    dependencyTrackingModule.Initialize(config);

    QuickPulseTelemetryProcessor quickPulseProcessor = null;
    config.DefaultTelemetrySink.TelemetryProcessorChainBuilder
        .Use((next) =>
        {
            quickPulseProcessor = new QuickPulseTelemetryProcessor(next);
            return quickPulseProcessor;
        })
        .Build();

    var quickPulseModule = new QuickPulseTelemetryModule()
    {
        AuthenticationApiKey = authenticationApiKey
    };

    quickPulseModule.Initialize(config);
    quickPulseModule.RegisterTelemetryProcessor(quickPulseProcessor);

    config.TelemetryInitializers.Add(new HttpDependenciesParsingTelemetryInitializer());
    config.TelemetryInitializers.Add(new BuildInfoConfigComponentVersionTelemetryInitializer());

    return config;
}

Can anyone provide any input on what I might be doing wrong?

Answer 1

While we all wait for a potential resolution from the insights team I thought I'd share my solution.

Primarily my concern was to have more control about what data is provided when I tracked database operations. I was able to find a neat little feature in EntityFramework called DbInterception.

This singleton allows you to register interceptors which can track and profile queries provided to EntityFramework.

The implementation ended up being as simple as this:

internal class InsightsOperation 
{
    public InsightsOperation(DbCommand command, Stopwatch stopwatch)
    {
        this.Command = command;
        this.Stopwatch = stopwatch;
    }

    public DbCommand Command { get; }
    public Stopwatch Stopwatch { get; }
    public DateTime StartTime { get; } = DateTime.UtcNow;
}

public class AppInsightsInterceptor : IDbCommandInterceptor
{

    private ConcurrentDictionary<Guid, InsightsOperation> _pendingOperations = new ConcurrentDictionary<Guid, InsightsOperation>();
    private ITelemetryManager _telemetryManager;

    public AppInsightsInterceptor(ITelemetryManager telemetryManager)
    {
        this._telemetryManager = telemetryManager ?? throw new ArgumentNullException(nameof(telemetryManager));
    }

    private void StartTimingOperation<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext)
    {
        if (!(command is SQLiteCommand))
        {
            var id = Guid.NewGuid();
            var stopwatch = Stopwatch.StartNew();

            interceptionContext.SetUserState(nameof(AppInsightsInterceptor), id);
            this._pendingOperations.TryAdd(id, new InsightsOperation(command, stopwatch));
        }
    }

    private void StopTimingOperation<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext)
    {
        if (!(command is SQLiteCommand))
        {
            var id = (Guid)interceptionContext.FindUserState(nameof(AppInsightsInterceptor));
            if (this._pendingOperations.TryRemove(id, out InsightsOperation operation))
            {
                operation.Stopwatch.Stop();
                this._telemetryManager.TrackDependency(command.CommandType.ToString(),
                    "SQL",
                    command.CommandText,
                    new DateTimeOffset(operation.StartTime),
                    operation.Stopwatch.Elapsed,
                    interceptionContext.Exception == null);
            }
        }
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        this.StopTimingOperation(command, interceptionContext);
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        this.StartTimingOperation(command, interceptionContext);            
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        this.StopTimingOperation(command, interceptionContext);
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        
        this.StartTimingOperation(command, interceptionContext);
            
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        this.StopTimingOperation(command, interceptionContext);
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        this.StartTimingOperation(command, interceptionContext);
    }
}

Note that this registration is a singleton and if you want to exclude data you need to understand all your instances of DbContext will hit the interceptor.

In my case I wanted to exclude SQLite queries as seen in the code.