Turning off SQL Query logs from settings

[tinonetic]

Hi,

We have an elastic setup that's working well in .NET Core (Elastic Search + Kibana + APM) for our microservices

But now we have an application that is logging alot of SQL Queries that are spamming the "observability".

We are using EF Core as our ORM

Is there a way of turning off SQL Query logging through config? We don't want totally remove the span as it may be useful in future troubleshooting and it gives us a view of which process is running at a given time, as well as the intstrumentation that APM does so well.

Packages:

Elastic.Apm.NetCoreAll 1.5.1
Elastic.Apm.SerilogEnricher 1.5.0

Code:

           var transaction = Agent.Tracer.CurrentTransaction;
            await transaction.CaptureSpan("Getting DeActivated Items", ApiConstants.TypeRequest, async () =>
            {
                deActivatedItems = await _myRepository
                         .Where(x => !x.Activated) 
                         .ToListAsync();

            });    

The appsettings.json with the config:

  "ElasticApm": {
    "ServiceName": "myService",
    "ServerUrls": "http://my_url:8200",
    "TransactionSampleRate": 1.0,
    "ServiceVersion": "1.0.0",
    "Environment": "Development",
    "TransactionMaxSpans": 500,
    "SecretToken": "SomeSecret",
    "LogLevel": "Error"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning",
      "Microsoft": "Warning",
      "System": "Information",
      "Elastic.Apm": "Error"
    }
  },
  "Serilog": {
    "MinimumLevel": {
      "Default": "Warning",
      "Override": {
        "Microsoft": "Warning",
        "IdentityServer4": "Information",
        "Hangfire": "Information",
        "Volo": "Information",
        "System": "Information",
        "Elastic.Apm": "Error"
      }
    }
  }

What do I need to do to turn of SQL Query logging without having to remove the Span?

[GregKalapos]

Hi @tinonetic, welcome on discuss.

You have a couple of options here.

You can use the Filter API and do something like this:

Agent.AddFilter((ISpan span) =>
{
	if (span.Context?.Db != null // only true for db spans
		&& span.Duration < 100) // filter based on duration
		return null; // if it's a db span and less than 100 milliseconds, we drop it
	return span;
});

You have access to other properties as well, so you can also filter based on something other than duration and you can also implement more complex logic in the filter.

Another options would be to completely turn off database capturing. By default the Elastic.Apm.NetCoreAl turns on everything, so you'll need to configure the agent this way:

using Elastic.Apm.AspNetCore;

//...in the Configure method:
app.UseElasticApm( Configuration,new HttpDiagnosticsSubscriber() /* turn off:, new EfCoreDiagnosticsSubscriber(),
new SqlClientDiagnosticSubscriber() */, new ElasticsearchDiagnosticsSubscriber());

I also opened an issue in the .NET APM Agent repo - this is not implemented yet, but the idea is to cut spans automatically based on duration with a setting. The Java agent already has this setting and the motivation was to solve something similar to your issue, so maybe it's worth following that issue as well.