Application Insights on Sitecore – Filtering the SQL telemetry

Microsoft Application Insights is a great solution to monitor telemetry data from your Sitecore installs.

The only problem is that if you enable all of the normal telemetry modules, you’ll end up flooding your data points with SQL calls.  There are thousands of SQL calls every minute in an average Sitecore database (especially in the EventQueue table).
We wanted to filter out all of those SQL calls, because we have not seen performance issues with Sitecore and SQL in our setup.


This requires at least v2.0 of the AppInsights SDK.  And then you need to create a custom filter.  I followed an example from the AppInsights documention.

namespace Sitecore.Website.AppInsights.Filters
{ 
    public class SQLFilter : ITelemetryProcessor
    {
        private ITelemetryProcessor Next { get; set; }

        // Link processors to each other in a chain.
        public SQLFilter(ITelemetryProcessor next)
        {
            this.Next = next;
        }
        public void Process(ITelemetry item)
        {
            // To filter out an item, just return 
            if (!OKtoSend(item)) { return; }

            this.Next.Process(item);
        }

        // Example: replace with your own criteria.
        private bool OKtoSend(ITelemetry item)
        {
            var dependency = item as DependencyTelemetry;
            if (dependency != null
                && dependency.DependencyKind == "SQL")
            {
                return false;
            }

            return true;
        }
    }
}

Once you have your filter class built, you need to add it to the Process 
pipeline of AppInsights in your ApplicationInsights.config file.

<TelemetryProcessors>
  <Add Type="Sitecore.Website.AppInsights.Filters.SQLFilter, Sitecore.Website" />
</TelemetryProcessors>

That’s it!  This prevents any SQL calls from flooding your AppInsights Azure
resource.

If collecting some of this SQL data is important to you, you could also look 
into the Sampling features of the SDK, which allows you to throttle the 
data that is sent.

Another option is to inspect the CommandName property and possibly just filter out the chattiness to the EventQueue table, but allow the other commands through.  I chose not to do this for now, because the goal is to fail fast in this processor, so not to cause a lot of extra logic to happen on the data collection and slow things down.  If you decide to do the extra logic in here to allow some of the SQL data through, make sure to order you conditional tests correctly, so if the DependencyTelemetry object is not a SQL kind, short-circuit and skip testing the other conditionals.