Full SQL query commands in Application Insights with AspNetCore

DefaultSqlFilter.cs

using Microsoft.ApplicationInsights.Channel;
using Microsoft.ApplicationInsights.DataContracts;
using Microsoft.ApplicationInsights.Extensibility;

namespace Gist {

    /// <summary>
    /// Telemetry Processor to filter built-in SQL dependency tracking
    /// </summary>
    public class DefaultSqlFilter : ITelemetryProcessor {
        private ITelemetryProcessor Next { get; set; }

        // Link processors to each other in a chain.
        public DefaultSqlFilter(ITelemetryProcessor next) {
            Next = next;
        }
        public void Process(ITelemetry item) {
        
            if (item is DependencyTelemetry dependency) {
                // Filter out default SQL, but keep the SQL Commands from our custom Logger
                if (dependency.Type == "SQL" && !dependency.Name.StartsWith("SQL Command")) {
                    return;
                }
            }

            Next.Process(item);
        }

    }
}

SqlCommandTelemetryProvider.cs

using Microsoft.ApplicationInsights;
using Microsoft.ApplicationInsights.DataContracts;
using Microsoft.Extensions.Logging;
using System;
using System.Data.SqlClient;
using System.Linq;
using System.Collections.Generic;

namespace Gist {

    /// <summary>
    /// Logger Provider that will register Logger for Entity Framework Commands
    /// </summary>
    public class SqlCommandTelemetryProvider : ILoggerProvider {

        public ILogger CreateLogger(string categoryName) {
            if (categoryName == "Microsoft.EntityFrameworkCore.Database.Command") {
                return new EntityFrameworkCommandLogger();
            }
            return new NullLogger();
        }

        public void Dispose() { }

        /// <summary>
        /// Logger that will send SQL Command Telemetry to Application Insights
        /// </summary>
        private class EntityFrameworkCommandLogger : ILogger {

            public bool IsEnabled(LogLevel logLevel) {
                return true;
            }

            public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter) {
                if (state is IEnumerable<KeyValuePair<string, object>> stateData) {
                    // Get the data from the logger
                    var data = stateData.ToList();
                    // Handle the data if it's SQL command
                    if (data.Exists(o => o.Key == "commandText")) {
                        var dependencyTelemetry = new DependencyTelemetry();
                        dependencyTelemetry.Timestamp = DateTimeOffset.Now;
                        dependencyTelemetry.Name = "SQL Command";

                        // Get the SQL command
                        var command = (string)data.Single(o => o.Key == "commandText").Value;
                        try {
                            // Get parameters array from data
                            var parameters = Split(data.Single(o => o.Key == "parameters").Value.ToString(), ", ", "'", false);
                            // Replace parameters values in the command
                            foreach (var parameter in parameters) {
                                var name = parameter.Substring(0, parameter.IndexOf('='));
                                var valueStart = parameter.IndexOf('\'') + 1;
                                var valueEnd = parameter.LastIndexOf('\'');
                                if (valueEnd > valueStart) {
                                    var value = parameter.Substring(valueStart, valueEnd - valueStart);
                                    command = command.Replace(name, $"'{value}'");
                                }
                            }

                            // Give additional info in command Name
                            if (command.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase)) {
                                dependencyTelemetry.Name += ", | SELECT";
                                var fromStart = command.IndexOf("from ", 0, StringComparison.OrdinalIgnoreCase);
                                var fromEnd = command.IndexOf("]", fromStart, StringComparison.OrdinalIgnoreCase);
                                if (fromEnd > fromStart) dependencyTelemetry.Name += " " + command.Substring(fromStart, fromEnd - fromStart + 1);
                            }
                            if (command.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase) || command.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase)) {
                                var operationEnd = command.IndexOf(']', StringComparison.OrdinalIgnoreCase);
                                if (operationEnd > 0) dependencyTelemetry.Name += ", | " + command.Substring(0, operationEnd + 1);
                            }

                        } catch (Exception ex) {
                            command = (string)data.Single(o => o.Key == "commandText").Value + Environment.NewLine + "Error: " + ex.Message;
                        }
                        // Define if successful and exception if not
                        dependencyTelemetry.Success = exception == null;
                        if (exception != null) {
                            command = command + Environment.NewLine + "Error: " + exception.Message;
                        }
                        if (exception is SqlException sqlException) {
                            dependencyTelemetry.ResultCode = sqlException.Number.ToString();
                        }

                        // Finally set telemetry data
                        dependencyTelemetry.Data = command;

                        // Other telemetry values
                        var elapsedMilliseconds = long.Parse(data.Single(o => o.Key == "elapsed").Value.ToString().Replace(",", ""));
                        dependencyTelemetry.Duration = TimeSpan.FromMilliseconds(elapsedMilliseconds);
                        dependencyTelemetry.Type = "SQL";

                        // Send telemetry
                        var telemetryClient = new TelemetryClient();
                        telemetryClient.TrackDependency(dependencyTelemetry);
                    }
                }
            }

            // Helper method to split command parameters
            private string[] Split(string expression, string delimiter,
            string qualifier, bool ignoreCase) {
                var qualifierState = false;
                var startIndex = 0;
                var values = new System.Collections.ArrayList();

                for (var charIndex = 0; charIndex < expression.Length - 1; charIndex++) {
                    if ((qualifier != null)
                     & (string.Compare(expression.Substring
                    (charIndex, qualifier.Length), qualifier, ignoreCase) == 0)) {
                        qualifierState = !(qualifierState);
                    } else if (!(qualifierState) & (delimiter != null)
                            & (string.Compare(expression.Substring
                      (charIndex, delimiter.Length), delimiter, ignoreCase) == 0)) {
                        values.Add(expression.Substring
                    (startIndex, charIndex - startIndex));
                        startIndex = charIndex + delimiter.Length;
                    }
                }

                if (startIndex < expression.Length)
                    values.Add(expression.Substring
                    (startIndex, expression.Length - startIndex));

                var _returnValues = new string[values.Count];
                values.CopyTo(_returnValues);
                return _returnValues;
            }


            public IDisposable BeginScope<TState>(TState state) {
                return null;
            }
        }

        private class NullLogger : ILogger {
            public bool IsEnabled(LogLevel logLevel) {
                return false;
            }

            public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter) { }

            public IDisposable BeginScope<TState>(TState state) {
                return null;
            }
        }
    }
}

startup.cs

// ...

namespace Gist
{

// ...

    public class Startup
    {

        // ...

        public IConfiguration Configuration { get; }

        public Startup(IConfiguration configuration) {
            Configuration = configuration;

            // ...
            
        }
        
        // ...

        public void ConfigureServices(IServiceCollection services) {

            // ...

            services.AddApplicationInsightsTelemetry(Configuration);
            // Register the custom TelemetryProcessor that filters default SQL telemetry
            services.AddApplicationInsightsTelemetryProcessor<DefaultSqlFilter>();

            // ...

            services.AddDbContext<MyContext>(options => {
                options.UseSqlServer(Configuration.GetConnectionString("MyConnection"));
                // Not secured, but allow you to see query parameters
                options.EnableSensitiveDataLogging();
            });
            
            // ...
            
        }

        public void Configure(IApplicationBuilder app, ILoggerFactory loggerFactory)
        {

            // Add Entity Framework logger for Application Insights
            loggerFactory.AddProvider(new SqlCommandTelemetryProvider());

            // ...
            
        }
    }
}