How to view sql queries in APM

Juan_David_Jaramillo · August 13, 2021, 4:42pm

Hello, how are you?

I am having a problem with the SQL queries in APM, what happens is that I am trying to show me the queries that occur in SQL and send to APM. I have already installed the agent 'elastic.apm.sql.client' in my ASP.NET framework 4.6.2 application, and I have this structured as it is in the documentation.
I added the code of sql.client in the start of the application that according to this in the class: Mvc.aplication.cs according to the documentation of APM ASP.NET.

CODE:

using Elastic.Apm;
using Elastic.Apm.AspNetFullFramework;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Elastic.Apm.Api;
using Elastic.Apm.SqlClient;

namespace VentasWeb.App_Start
{
    public class MvcApplication : HttpApplication
    {

        protected void Application_Start()
        {
            // other application startup e.g. RouteConfig, etc.
            if (Agent.IsConfigured) Agent.Subscribe(new SqlClientDiagnosticSubscriber());

            // set up agent with components
            var agentComponents = ElasticApmModule.CreateAgentComponents();
            Agent.Setup(agentComponents);

            // add transaction filter
            Agent.AddFilter((ITransaction t) =>
            {
                t.SetLabel("foo", "bar");
                return t;
            });
        }
    }
}

So, I don't know if I need to add anything else, can you please guide me?

forloop · August 16, 2021, 2:08am

Juan_David_Jaramillo:

        protected void Application_Start()
        {
            // other application startup e.g. RouteConfig, etc.
            if (Agent.IsConfigured) Agent.Subscribe(new SqlClientDiagnosticSubscriber());

            // set up agent with components
            var agentComponents = ElasticApmModule.CreateAgentComponents();
            Agent.Setup(agentComponents);

            // add transaction filter
            Agent.AddFilter((ITransaction t) =>
            {
                t.SetLabel("foo", "bar");
                return t;
            });
        }

The call to subscribe SqlClientDiagnosticSubscriber needs to happen after the agent has been set up. The following should work

        protected void Application_Start()
        {
            // set up agent with components
            var agentComponents = ElasticApmModule.CreateAgentComponents();
            Agent.Setup(agentComponents);

            // subscribe to capture database spans to SQL server
            Agent.Subscribe(new SqlClientDiagnosticSubscriber());

            // add transaction filter
            Agent.AddFilter((ITransaction t) =>
            {
                t.SetLabel("foo", "bar");
                return t;
            });
        }

Juan_David_Jaramillo · August 17, 2021, 2:18pm

Hi, I made the change, but I still don't see the SQL queries in the APM.

namespace VentasWeb.App_Start
{
    public class MvcApplication : HttpApplication
    {

        protected void Application_Start()
        {
           

            {
                // set up agent with components
                var agentComponents = ElasticApmModule.CreateAgentComponents();
                Agent.Setup(agentComponents);

                // subscribe to capture database spans to SQL server
                Agent.Subscribe(new SqlClientDiagnosticSubscriber());

                // add transaction filter
                Agent.AddFilter((ITransaction t) =>
                {
                    t.SetLabel("foo", "bar");
                    return t;
                });
            }
        }
    }
}

there are only the same ones of the application but not the SQL ones.

forloop · August 18, 2021, 7:28am

DB spans won't appear on the transactions view, as they are spans.

If the application is running in IIS, did you recycle the Application pool? If the Application pool is recycled and you're still not seeing DB spans, you might want to troubleshoot by taking a look at the agent logs, to ensure that the SQL integration is running as expected. You might want to set the agent log level to Trace with the following app setting in web.config

<configuration>
	<!-- other settings -->
	<appSettings>
		<add key="ElasticApm:LogLevel" value="Trace"/>
	</appSettings>
</configuration>

so that every log message is logged. For ASP.NET Full Framework, we would expect to see log messages from the SqlEventListener scoped logger if the SqlClientDiagnosticSubscriber is subscribed and running.

Juan_David_Jaramillo · August 18, 2021, 3:10pm

ok, I already configured the trace, but I still do not know how to see if it is subscribed sqlclient, I have no code errors, but that's why I get complicated to know if it is running or not, this is my configuration:

<configuration>
	<system.webServer>
		<modules>
			<!--<add name="ElasticApmModule" type="Elastic.Apm.AspNetFullFramework.ElasticApmModule, Elastic.Apm.AspNetFullFramework" /> -->
			<add name="ElasticApmModule" type="Elastic.Apm.AspNetFullFramework.ElasticApmModule, Elastic.Apm.AspNetFullFramework" preCondition="managedHandler" />
		</modules>
	</system.webServer>
  <appSettings>
	<add key="ElasticApm:ServerUrl" value="https://d2349e51823d44eaa0c3af1ba3601b5d.apm.us-central1.gcp.cloud.es.io:443" />
	<add key="ElasticApm:SecretToken" value="xxxxxxxxxxxxx" />
	<add key="ElasticApm:LogLevel" value="Trace"/>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <system.web.extensions>
    <scripting>
      <webServices>
        <jsonSerialization maxJsonLength="50000000" />
      </webServices>
    </scripting>
  </system.web.extensions>
  <connectionStrings>
    <add name="miconexion" connectionString="Data Source=DESKTOP-1RP0D2N;Initial Catalog=DBVENTAS_WEB;Integrated Security=True" />
  </connectionStrings>
  <!--
    For a description of web.config changes see http://go.microsoft.com/fwlink/?LinkId=235367.

    The following attributes can be set on the <httpRuntime> tag.
      <system.Web>
        <httpRuntime targetFramework="4.6.2" />
      </system.Web>
  -->
  <system.web>
    <sessionState timeout="30" />
    <compilation debug="true" targetFramework="4.6.2" />
    <httpRuntime targetFramework="4.6" />
  </system.web>
  </configuration>

My class APP_start:

using Elastic.Apm;
using Elastic.Apm.AspNetFullFramework;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Elastic.Apm.Api;
using Elastic.Apm.SqlClient;

namespace VentasWeb.App_Start
{
    public class MvcApplication : HttpApplication
    {

        protected void Application_Start()
        {
           

            {
                // set up agent with components
                var agentComponents = ElasticApmModule.CreateAgentComponents();
                Agent.Setup(agentComponents);

                // subscribe to capture database spans to SQL server
                Agent.Subscribe(new SqlClientDiagnosticSubscriber());

                // add transaction filter
                Agent.AddFilter((ITransaction t) =>
                {
                    t.SetLabel("foo", "bar");
                    return t;
                });
            }
        }
    }
}

I am beginning to think that it is the application that does not have the necessary methods to capture SQL queries, I do not know if it has to do with being ASP.NET Classic

forloop · August 18, 2021, 11:52pm

Have a look in the agent logs. The ASP.NET Full Framework integration will emit log messages to a System.Diagnostics.TraceSource with the source name "Elastic.Apm", so configure a TraceListener in web.config to write these messages somewhere you can view them. A simple example would be to write them to the console/stdout, which can be done by adding the following to web.config

<configuration>
  <!-- other configuration... -->
  <system.diagnostics>
    <sources>
      <source name="Elastic.Apm">
        <listeners>
          <add name="consoleListener" type="System.Diagnostics.ConsoleTraceListener" />
        </listeners>
      </source>
    </sources>
  </system.diagnostics>
</configuration>

Now agent logs will be written to the console/stdout. If the SQL integration is working, you should expect to see log messages like the following when a Sql command is executed

[2021-08-19 08:56:06.284 +10:00][Trace] - {SqlEventListener} Process BeginExecute event. Id: 59487907. Data source: .. Database: master. CommandText: .
[2021-08-19 08:56:06.322 +10:00][Trace] - {SqlEventListener} Process EndExecute event. Id: 59487907. Composite state: 5. Sql exception number: 0.

I've created an example application that shows a working SQL integration and writes agent logs to console/stdout:

A DB span is captured in the example for the Home/Index transaction

Juan_David_Jaramillo · August 19, 2021, 1:45pm

ok, I have already configured the example app with my APM and it works correctly, apparently it was like the application that would not let me, thank you very much.

Juan_David_Jaramillo · August 19, 2021, 7:46pm

but how do I make it capture the other pages, it only appears in home?

forloop · August 21, 2021, 2:42am

HTTP requests to the application will be captured as transactions. Database calls to SQL server will be automatically captured as DB spans associated with a transaction. So, if a controller action contains a call to SQL server, this will be captured.

A transaction might not be captured, for example, because the HTTP request path matches a wildcard value of the TransactionIgnoreUrls configuration value. In this case, a DB span won't be captured either.

system · September 10, 2021, 10:42pm

This topic was automatically closed 20 days after the last reply. New replies are no longer allowed.