22

I'm using Azure Application Insights for a website (Azure App Service). On that I'm using a clustered Umbraco setup and hangfire. These two alone keep hitting the database every minute and are flooding my 'App Insights'.

So my question is, how do I disable the Sql Dependency Tracker? I've had a look at the ApplicationInsights.config and couldn't find anything obvious. I can see Microsoft.ApplicationInsights.DependencyCollector which is probably responsible, but I don't want to remove all types of dependency telemetry, only sql.

Thanks

Improve this question

2 Answers 2

Reset to default
35

Your best bet here is to use a Telemetry Processor to filter out certain types of dependency requests. Check out these resources below for information.

Sampling, filtering and preprocessing telemetry in the Application Insights SDK

Request filtering in Application Insights with Telemetry Processor

An example processor might look like this.

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

public class NoSQLDependencies : ITelemetryProcessor
{
    private ITelemetryProcessor Next { get; set; }

    // Link processors to each other in a chain.
    public NoSQLDependencies(ITelemetryProcessor next)
    {
        this.Next = next;
    }
    public void Process(ITelemetry item)
    {
        if (IsSQLDependency(item)) { return; }
        this.Next.Process(item);
    }

    private bool IsSQLDependency(ITelemetry item)
    {
        var dependency = item as DependencyTelemetry;
        if (dependency?.DependencyTypeName == "SQL")
        {
            return true;
        }
        return false;
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Would you happen to know what the possible values for DependencyTypeName are? I'd like to stop tracking access to blob storage
@batmaci yes i did. I created a ITelemetryProcessor ran the application in the debugger, put a conditional break point in the processor until I found the telemetry I wanted to block. Inspect the properties of the ITelemetry item allowed me to define the criteria to filter out the particular telemetry I wanted to filter out, as illustrated in this answer
Found the source at github.com/Microsoft/ApplicationInsights-dotnet-server/blob/… public const string SQL = "SQL"; public const string HTTP = "Http"; public const string AzureBlob = "Azure blob"; public const string AzureTable = "Azure table"; public const string AzureQueue = "Azure queue";
can one disable for only hangfire sql. I still want to see standard sql queries?
Both DependencyTypeName and DependencyKind are obsolete now. Use just Type
|
2

We filter out just hangfire SQL using a telemetry processor like this, in a .net5 asp.net project. Note that we use a different database for Hangfire so the telemetry processor can easily out hangfire SQL by checking if it's connecting to the Hangfire database.

Within Startup.Configure():

var hangFireConnectionString = // ... get from somewhere

configuration.TelemetryProcessorChainBuilder
    .Use(next => new IgnoreHangfireTelemetry(next,hangFireConnectionString))
    .Build();

Here's the processor class:

public class IgnoreHangfireTelemetry : ITelemetryProcessor
{
    private readonly ITelemetryProcessor next;
    private readonly string hangfireDashboardPath;
    private readonly string sqlDatabase;  // name of the hangfire db

    public IgnoreHangfireTelemetry(
        ITelemetryProcessor next,
        string sqlConnectionString = null,
        string hangfireDashboardPath = "/hangfire")
    {
        this.next = next ?? throw new ArgumentNullException(nameof(next));

        if (!string.IsNullOrEmpty(sqlConnectionString))
        {
            var builder = new SqlConnectionStringBuilder(sqlConnectionString);

            sqlDatabase = builder.InitialCatalog;
        }

        this.hangfireDashboardPath = hangfireDashboardPath ?? throw new ArgumentNullException(nameof(hangfireDashboardPath));
    }

    public void Process(ITelemetry item)
    {
        var request = item as RequestTelemetry;

        // If it's a request for Hangfire Dashboard don't record it
        if (request != null
            && request.Url.AbsolutePath.StartsWith(hangfireDashboardPath))
        {
            return;
        }

        var telemetry = item as DependencyTelemetry;

        // If it's a SQL dependency to the Hangfire db don't record it
        if (telemetry != null)
        {
            if (sqlDatabase != null  && telemetry.Type == "SQL"
                && telemetry.Target.EndsWith($"| {sqlDatabase}", StringComparison.OrdinalIgnoreCase))
            {
                return;
            }
        
            if (telemetry.Type == "SQL"
                && telemetry.Name.ToLower().Contains("hangfire")
                && telemetry.Success.GetValueOrDefault(false))
            {
                return;
            }
        }

        // Looks like it's not Hangfire, process the telemetry as usual.
        next.Process(item);
    }
}

If you don't use a separate Hangfire db, you could achieve the same thing by inspecting other DependencyTelemetry properties, e.g. look at DependencyTelemetry.Data or .CommandName (which holds the SQL statement) and check if it contains [Hangfire] (or another db schema name if you've changed Hangfire to use a different schema). If you're just filtering on sql you'll need to filter out a few more commands. Just step through using the debugger and see which ones you need to exclude. For example, in above code my sqlBackend object looks like this (note that "." is my SQL Server name, i.e. running locally, and "Hangfire" is the DB name where my Hangfire tables are):

debug-info

Sometimes the sqlBackend.Name property has values like . | Hangfire | Open, or . | Hangfire | sp_getapplock, but the Target property is still just ``. | Hangfire` e.g.:

enter image description here

Improve this answer

9 Comments

how do we do this in .NET 6?
I don't think it's changed in .net 6
@Steve I suspect it’s the line matching telemetry.Target to our Hangfire db that does it for us, not the telemetry.Name.
@Steve - is "myservices" the name of the database where Hangfire tables are? In that case it's the same as what I see, it's just that I use a separate database named "Hangfire". As I mentioned in my answer above, you could inspect other properties like CommandName to decide whether to filter out the command.
ok, then I guess there's nothing in there that says "I'm doing Hangfire stuff" for all of the events. If you check for "[Hangfire]" and "sp_getapplock" etc in the command you'll probably be able to filter out most of them and perhaps that's good enough for you? Best to ask on hangfire.io forum and/or find out where that info comes from. Perhaps there's a way you can get Hangfire to do it differently, either putting something into the telemetry name or object, or something else.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.