Elastic.Apm.AspNetFullFramework not showing database calls

[sawo1337]

Only the HTTP calls are shown in Kibana in the Trace sample, there is nothing on database calls
There is nothing in the documentation specific to database queries interception for the full .net framework - https://www.elastic.co/guide/en/apm/agent/dotnet/current/setup.html#setup-asp-net

Steps to reproduce the behavior:

1. Create a new project ASP.NET Web Application (.NET Framework) with MVC and add the Elastic.Apm.AspNetFullFramework nugget (1.2)
2. Web.config:

<appSettings>
    <add key="ElasticApm:ServerUrls" value="http://IP.ADDR:8200/" />
  </appSettings>
    <modules>
      <add name="ElasticApmModule" type="Elastic.Apm.AspNetFullFramework.ElasticApmModule, Elastic.Apm.AspNetFullFramework" />
    </modules>

3. Execute any type of query using System.Data.SqlClient, for example

4. See the output in Kibana - only HTTP call is shown

Expected behavior
Trace sample to include SQL calls as well.

[gregkalapos]

Hi,

by default db monitoring is not turned on in the Full Framework setup.

If you use Entity Framework 6, then here is the doc on how to turn it on: https://www.elastic.co/guide/en/apm/agent/dotnet/current/setup.html#setup-ef6

If you use EF Core, then simply execute this code once when your app starts up:

Agent.Subscribe(new EfCoreDiagnosticsSubscriber())

EfCoreDiagnosticsSubscriber lives in this NuGet package: https://www.nuget.org/packages/Elastic.Apm.EntityFrameworkCore/

[sawo1337]

Is it possible to use this if EF6/EFCore is not used in the project?

[gregkalapos]

Currently we only have EF6 and EFCore official support.

We also have something for SqlClient - it's not released yet and not documented, but if that's what you use I can help you to setup.

So these are the 2 (+1) database libraries we support at the moment by auto instrumentation.

Alternatively, you can also manually track the db calls by the public agent API - although I admit this is less convenient.

What database do you use?

[sawo1337]

Its MSSQL, if I can get this running for SQLClient, that would be great!

Attached is a piece of code to illustrate typical db call:

[gregkalapos]

Great! Then this is something that we almost already support.

So, there is no official NuGet package, but we have this project in the repo: https://github.com/elastic/apm-agent-dotnet/tree/master/src/Elastic.Apm.SqlClient

That is the auto instrumentation for SqlClient - meaning db calls will be automatically captured for you.

If you clone the repo and build that project you can create an assembly from that.

That will give you a SqlClientDiagnosticSubscriber() class.

After that, all you need to do is to execute this once, preferably during the startup of the application:

Agent.Subscribe(new SqlClientDiagnosticSubscriber());

This way you can give it a try.

But please keep in mind this is not an officially released component yet.

The main reason it's not yet released is because we don't have a solution for scenarios when EF or EF Core is used with MSSql - in that case EF (Core) uses SqlClient internally, so db calls would be captured twice. Which in your case is not relevant, so that's good.

Once we have a solution for this problem, the plan is to have an official release of this package.

[sawo1337]

Thanks, trying this now!

Is there any specific SDK version needed, I've got SDK 3.1.101, but looks like it's unsupported for the solution?

[gregkalapos]

Oh, yeah.

We use 3.1.100 - https://github.com/elastic/apm-agent-dotnet/blob/master/global.json#L3

You can just install it additionally to your existing one. - alternatively I guess it'd also work if you change the version to yours in the global.json file.

[sawo1337]

Is there a chance I can get this prebuilt somehow? Downgraded mine to 3.1.100, but still get the same version errors. I'm probably missing a lot of the references, not sure why, though - tried on two separate build environments all with the SDKs installed, same errors on both.

[gregkalapos]

You can download it from our CI:

https://apm-ci.elastic.co/job/apm-agent-dotnet/job/apm-agent-dotnet-mbp/job/master/

On the top of the page you'll see "Last Successful Artifacts".

The Elastic.Apm.SqlClient.1.2.0.nupkg is the nuget package containing that project.

You can either setup a local nuget feed and put it there, or just unzip it and add the .dll to your project form the package.

[SergeyKleyman]

@sawo1337
In case you work with Visual Studio - when Elastic APM .NET Agent switched to .NET Core SDK 3.1.* it stopped building in my environment with Visual Studio 2017 with errors similar to the ones you posted above. I installed Visual Studio 2019 and it started to build again.

[sawo1337]

Thanks for the tip @SergeyKleyman! I've just read that 3.1 is only for VS 2019 as well, we only use 2017 at the moment.

@gregkalapos thanks for the workaround, I've got all packages to install locally - Elastic.Apm.1.2.0, Elastic.Apm.AspNetFullFramework.1.2.0 and Elastic.Apm.SqlClient.1.2.0 from Master, but after running the web code, still just HTTP calls are shown. I've tried this on an empty MVC project and on one of our test solutions, but both aren't showing any DB calls in the trace sample in Kibana.
I've included the agent code and I've confirmed that it is in a method that is executed at startup. Is there a way to troubleshoot if data is being captured at all via the SqlClient?

edit: here is the sample code that I'm using to test this: (basically a asp.net page that displays the current time as taken from the database)

namespace apm_test.Controllers
{
    public class HomeController : Controller
    {

        public ActionResult Index()
        {
            Agent.Subscribe(new SqlClientDiagnosticSubscriber());
            return View();
        }

        public ActionResult About()
        {          
            using (var sqlConnection = new SqlConnection("Data Source=host;Initial Catalog=database;"
        + "User id=user; Password=password;"))
            {
                var query = "select getdate() as date";
                using (var sqlCommand = new SqlCommand(query, sqlConnection))
                {
                    sqlConnection.Open();
                    SqlDataReader sdr = sqlCommand.ExecuteReader();
                    while (sdr.Read())
                    {
                        ViewBag.Message = sdr["date"];
                    }
                    sqlConnection.Close();                   
                }
            }
            return View();
        }
    }
}

[vhatsura]

@sawo1337, I'm afraid of Elastic.Apm.SqlClient package cannot be helpful in the case of .Net Framework. Unfortunately, SQLClient for .Net Framework doesn't use DiagnosticSource, which allows to Elastic.Apm capture information about calls in user code. So, at the moment, Elastic.Apm.SqlClient package can be used only for SQLClient in .Net Core applications.
So, the only possible way to capture db calls in .Net Framework is to use Public Agent API, which was mentioned by @gregkalapos.

Alternatively, you can also manually track the db calls by the public agent API - although I admit this is less convenient.

@gregkalapos, FYI, I created an issue #702 to add documentation for Elastic.Apm.SqlClient,

[gregkalapos]

Hmm, I'm afraid what @vhatsura says is true here.

You could turn the loglevel to debug to maybe see more, but probably the outcome would be that no span creation is started for the db call.

I'm not sure if this is true for all versions... I thought that some code flows back to full framework from .NET Core - it'd be useful to test newer .NET Framework versions too.

[sawo1337]

@gregkalapos that is unfortunate indeed. Do you have plans for implementing this in the future? I can speak to some of our software developers and see if we can help implementing it as well. Just to see where we stand, I've enabled debugging, just not sure where the logs would go for the .NET agent? I've looked over the documentation, but the information is mostly for the java agent from what I can see. When viewing the host logs from Kibana, it doesn't look like there is any debug data:

@vhatsura thanks for these details, do you mean that the DiagnosticSource implemented in Microsoft.Data.SqlClient is incompatible, or that it is not there at all? From what I can see Application Insights are using it, so it looks like it is there even in the SqlClient for the full .NET framework?

[anderssonjohan]

@sawo1337 It seems it's only dotnet core :( https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.diagnosticsource?view=netcore-3.1 No traces of it in full framework https://github.com/microsoft/referencesource/search?q=DiagnosticSource&unscoped_q=DiagnosticSource Would also love to see this working on full framework, following!

[sawo1337]

@anderssonjohan strange, wondering how application insights work then? I've seen people refer to EventSource as well, which is deeper trace than DiagnosticSource

edit:
yes, looks like Microsoft use EventSource for tracing db calls in full framework:
https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.tracing.eventsource?view=netframework-4.6.1
@gregkalapos @vhatsura do you think it would be feasible using this as alternative for the full framework?

[vhatsura]

@sawo1337, first of all, to be clear. I'm not working for Elastic, I'm just an external contributor.

What I see from ApplicationInsights code. For the .Net 4.5 there is the usage of EventListener. However, there is also SqlClientDiagnosticSourceListener which enabled by default for .Net Core and .Net Framework targets.
So, I'll look into this area if the Elastic team isn't minding.

[gregkalapos]

So, I'll look into this area if the Elastic team isn't minding.

If course, happy to take any input!

EventListener is also something we can look into - so, DiagnosticSource isn't the only thing we are ok with - whatever gives us the traces with acceptable overhead is ok.

[sawo1337]

@vhatsura appreciate all your help here! Even if it's mostly brainstorming and ideas, the Elastic team could still benefit from all the research we've done so far.
I've read through that file you provided, one comment stands out:

NET45 referencing .net core System.Net.Http supports diagnostic listener

If I understand it correctly, one needs to reference the Http assembly from the dotnet core in order to use diagnostic source listener in .NET 4.5?

@gregkalapos from what I've seen, Stackify is reading the db calls in our environment, most likely they are using EventListener, the performance is pretty good overall, I believe it will be comparable to DiagnosticSource. If SqlClientDiagnosticSourceListener is a feasible option on the other hand, most likely that will require less work on changing the existing code.

[vhatsura]

@vhatsura appreciate all your help here! Even if it's mostly brainstorming and ideas, the Elastic team could still benefit from all the research we've done so far.

I totally agree with you. My mention was related to not retranslate my words on behalf of the owners.

NET45 referencing .net core System.Net.Http supports diagnostic listener

So, this mention is related to the case, when you reference .net core System.Net.Http as a NuGet package in .Net Framework application. To be honest, DiagnosticsSource is available for .Net Framework as a NuGet package. However, .Net Framework version of SqlClient, don't spread trace events through DiagnosticsSource.

[vhatsura]

Small update:

SqlClient for .Net Framework doesn't spread events via DiagnosticSource. I checked all .Net Framework versions from 461 to 472. So, it can be a little chance for .Net Framework 4.8, however, I think most of the users want tracking db calls also for 461 or 472.

SqlClient for .Net Framework fire events via EventListener under Microsoft-AdoNet-SystemData name. I've been started to implement such functionality in Elastic.Apm.

[sawo1337]

Great work @vhatsura ! I've been experimenting with different versions of the packages, tried the latest SqlClient 2.0.20021.1 released just days ago, but there isn't any change as expected. Tried .NET Framework 4.8 as a target for my test project just for the test, it behaves the same as 4.6.1.
By the way, I believe the Elastic team can eventually use EventSource for all net frameworks, it will make things easier for maintenance as compared to keeping EventSource for NET Framework and DiagnosticSource for NET Core.

[vhatsura]

@sawo1337, unfortunately, to use EventListener for listening events from SqlClient in .Net Core isn't possible.
There are available event sources in .Net Framework and .Net Core, which I got in runtime

.NET Framework 4.8.4075.0

Microsoft-Diagnostics-DiagnosticSource
System.Diagnostics.Eventing.FrameworkEventSource
System.Threading.Tasks.TplEventSource
Microsoft-AdoNet-SystemData

.NET Core 3.1.1

Microsoft-Windows-DotNETRuntime
System.Runtime
System.Diagnostics.Eventing.FrameworkEventSource
System.Threading.Tasks.TplEventSource
Microsoft-Diagnostics-DiagnosticSource
System.Transactions.TransactionsEventSource
System.Data.DataCommonEventSource

edit: System.Threading.Tasks.TplEventSource and Microsoft-Diagnostics-DiagnosticSource were added to the list of event sources for .Net Framework. System.Data.DataCommonEventSource was added to the list of event sources for .Net Core.

[sawo1337]

@vhatsura is it possible that the events are fired, just not from Microsoft-AdoNet-SystemData? I've read blog posts from Microsoft that EventSource should be possible to use for both .NET and Core?

[gregkalapos]

#704 merged - so in theory this could be tested again.

One unfortunate finding is that there isn't a way to capture the sql command itself on Full Framework if you use System.Data.SqlClient - originally Microsoft.Data.SqlClient had the same behavior, but then it was changed here - but I'm afraid this wasn't change for System.Data.SqlClient.

[sawo1337]

I'll give it a try on my test project - definitely still better than nothing!

[diegodfsd]

Hi, I would like to try to use the Elastic.Apm.SqlClient package, but it is not available in nugget yet. So, how can I use this package in my .net core app?

[gregkalapos]

@diegodfsd there is no official release yet, but you can try already.

Here is a comment on how to do so.

You can find the nuget package here in our CI. Some docs are here.

I think all we planned to do in this issue is done - closing now. If something is missing here feel free to reopen.

Again, huge thanks to @vhatsur 👏 .