# How to view sql queries in APM

**URL:** <https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388>\
**Category:** APM\
**Tags:** dotnet\
**Created:** [August 13, 2021, 4:42pm UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388 "2021-08-13T16:42:58Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Juan\_David\_Jaramillo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/juan_david_jaramillo/32/76831_2.png) [@Juan\_David\_Jaramillo](https://discuss.elastic.co/u/Juan_David_Jaramillo)\
**Post date:** [August 13, 2021, 4:42pm UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/1 "2021-08-13T16:42:58Z")

</div>

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](http://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](http://ASP.NET).

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/0/d099300cc8adc8a08758cb8687123aadecd6e236.png)

CODE:

```auto
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?

---

<div class="post-metadata">

**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\
**Post date:** [August 16, 2021, 2:08am UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/2 "2021-08-16T02:08:37Z")

</div>

> [@Juan\_David\_Jaramillo](#):
>
> ```auto
> 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

```auto
        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;
            });
        }

```

---

<div class="post-metadata">

**Author:** ![Juan\_David\_Jaramillo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/juan_david_jaramillo/32/76831_2.png) [@Juan\_David\_Jaramillo](https://discuss.elastic.co/u/Juan_David_Jaramillo)\
**Post date:** [August 17, 2021, 2:18pm UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/3 "2021-08-17T14:18:34Z")

</div>

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

```auto
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;
                });
            }
        }
    }
}

```

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/2/925912f678647a48cf66a7a1a7ecd654d8c55752.png)  
there are only the same ones of the application but not the SQL ones.  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/6/c/6c42e59cb1cb256c0e796f6830c86f4b2b23f120.png)

---

<div class="post-metadata">

**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\
**Post date:** [August 18, 2021, 7:28am UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/4 "2021-08-18T07:28:22Z")

</div>

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

> [@Juan\_David\_Jaramillo](#):
>
> Hi, I made the change, but I still don't see the SQL queries in the APM.

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](https://www.elastic.co/guide/en/apm/agent/dotnet/current/troubleshooting.html#collect-logs-classic), 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

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

```

so that every log message is logged. For [ASP.NET](http://ASP.NET) Full Framework, we would expect to see log messages from the `SqlEventListener` scoped logger if the `SqlClientDiagnosticSubscriber` is subscribed and running.

---

<div class="post-metadata">

**Author:** ![Juan\_David\_Jaramillo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/juan_david_jaramillo/32/76831_2.png) [@Juan\_David\_Jaramillo](https://discuss.elastic.co/u/Juan_David_Jaramillo)\
**Post date:** [August 18, 2021, 3:10pm UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/5 "2021-08-18T15:10:12Z")

</div>

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:

```auto
<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:

```auto
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](http://ASP.NET) Classic

---

<div class="post-metadata">

**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\
**Post date:** [August 18, 2021, 11:52pm UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/6 "2021-08-18T23:52:28Z")

</div>

> [@Juan\_David\_Jaramillo](#):
>
> I already configured the trace, but I still do not know how to see if it is subscribed sqlclient

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"](https://www.elastic.co/guide/en/apm/agent/dotnet/current/troubleshooting.html#collect-logs-classic), so configure a [`TraceListener`](https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.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

```xml
<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

```auto
[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:

> **[GitHub - russcam/ApmFullFrameworkExample at discuss/281388](https://github.com/russcam/ApmFullFrameworkExample/tree/discuss/281388)**
>
> discuss/281388

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

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/c/8c1e7dbb98a848f2b06cfa708505aec476f682bd.png)

---

<div class="post-metadata">

**Author:** ![Juan\_David\_Jaramillo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/juan_david_jaramillo/32/76831_2.png) [@Juan\_David\_Jaramillo](https://discuss.elastic.co/u/Juan_David_Jaramillo)\
**Post date:** [August 19, 2021, 1:45pm UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/7 "2021-08-19T13:45:30Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/2/227aaa25ceff87ac21bd5d4fc12fcbf258864712.png)

---

<div class="post-metadata">

**Author:** ![Juan\_David\_Jaramillo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/juan_david_jaramillo/32/76831_2.png) [@Juan\_David\_Jaramillo](https://discuss.elastic.co/u/Juan_David_Jaramillo)\
**Post date:** [August 19, 2021, 7:46pm UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/8 "2021-08-19T19:46:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![forloop](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forloop/32/9021_2.png) [@forloop](https://discuss.elastic.co/u/forloop)\
**Post date:** [August 21, 2021, 2:42am UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/9 "2021-08-21T02:42:00Z")

</div>

> [@Juan\_David\_Jaramillo](#):
>
> but how do I make it capture the other pages, it only appears in home?

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 10, 2021, 10:42pm UTC](https://discuss.elastic.co/t/how-to-view-sql-queries-in-apm/281388/10 "2021-09-10T22:42:41Z")

</div>

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