none
Filtering SQL Dependency doesn't work

    Question

  • I'd need to filter dependency tracking when the kind is SQL and the duration is less than 50ms so I followed this article: https://azure.microsoft.com/documentation/articles/app-insights-api-filtering-sampling/#filtering

    and wrote the OktoSend method as:

    private bool OKtoSend(ITelemetry item)
            {
                var dependency = item as DependencyTelemetry;
                if (dependency == null) 
                    return false;
                else if (dependency.DependencyKind == "SQL" && dependency.Duration.Milliseconds < 50)
                    return false;
                else
                    return true;
            }

    but nothing reached my Azure AI. I have version 2.1 installed btw.

    After a lot of change&try to try and understand where's the problem, I got to a point that doesn't make any sense to me. This works and I got data on Azure AI, everything's fine:

            private bool OKtoSend(ITelemetry item)
            {
                var dependency = item as DependencyTelemetry;
                return true;
            }

    But this doesn't work:

            private bool OKtoSend(ITelemetry item)
            {
                var dependency = item as DependencyTelemetry;
                if (dependency.DependencyKind == "SQL" && dependency.Duration.Milliseconds < 50)
                    return true;
                else
                    return true;
            }

    Please note that I always return true (as said it is a test), in any case. It looks like the "if" breaks something.

    Any idea?

    Monday, June 27, 2016 12:14 PM

Answers

  • Sample that you provided has the following issue:

    If it is not a dependency you return false, that means that all traces, requests, exception will be filtered out.

    if (dependency == null)
       return false;
    So if you have only fast dependencies you filter everything out.


    Anastasia


    Wednesday, June 29, 2016 7:58 PM
    Moderator

All replies

  • Hello pmonte,

    I think that in the last example you provided the telemetry processor crashes silently with NullReference in dependency.DependencyKind (in case if dependency is null) or in Milliseconds if Duration object is not initialized by some reason. That will cause all events to drop - it does not even get to the return statement.

    The first example that you used initially returns false for all non DependencyTelemetry items, so they are not sent. Also, I'm not sure if DependencyKind is string enumeration of SQL/HTTP/OTHER but not 0/1/2 where "0" is SQL. If it's the case - then comparison to "SQL" will filter out the rest and you'd need to use comparison with "0" instead. I never used Duration.Milliseconds in the filter, so I would suggest to try without it and then add it back with some variations if everything works smoothly without it.


    Dmitry Matveev

    Tuesday, June 28, 2016 12:45 AM
    Owner
  • Thanks Dmitry,

    sorry for my last example, copy and paste and I forgot the first line that reads:

                if (dependency == null)
                    return true;

    so basically I always return true.

    Also, in debug I get this so "SQL" looks fine:

    So a check if Duration != null is missing but probably this wouldn't change much.

    I'm really our of ideas here

    Tuesday, June 28, 2016 7:28 AM
  • Do you call next telemetry processor as in the example?..

    this.Next.Process(item);

    Could you please post the whole sample?


    Anastasia

    Tuesday, June 28, 2016 6:30 PM
    Moderator
  • Do you call next telemetry processor as in the example?..

    this.Next.Process(item);

    Yes

    Could you please post the whole sample?

    Sure, here you are:

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

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

                this.Next.Process(item);
            }

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

        }

    and then in Application_Start:

                var builder = TelemetryConfiguration.Active.TelemetryProcessorChainBuilder;
                builder.Use((next) => new SuccessfulDependencyFilter(next));
                builder.Build();

                Microsoft.ApplicationInsights.Extensibility.TelemetryConfiguration.Active.InstrumentationKey = ConfigurationManager.AppSettings["ApplicationInsightsKey"];

    Wednesday, June 29, 2016 8:21 AM
  • Sample that you provided has the following issue:

    If it is not a dependency you return false, that means that all traces, requests, exception will be filtered out.

    if (dependency == null)
       return false;
    So if you have only fast dependencies you filter everything out.


    Anastasia


    Wednesday, June 29, 2016 7:58 PM
    Moderator
  • Ouch, what a stupid bug. Thanks!
    Thursday, June 30, 2016 1:41 PM