none
App insights not logging EF 6.2.0 queries RRS feed

  • Question

  • I've been suggested to post to be escalated to community experts. Unfortunately, I'm not currently able to post links of images. Please see the text link below for my original query and an image of our issue.

    https://twitter.com/AzureSupport/status/1199634199705243648

    We've recently installed application insights to our MVC 5 .NET framework 4.6.1 application using entity framework 6.2.0. One of the use cases we're hoping it to help us with is finding slow performing database queries. After installation however, we're found that although it's "logging" database calls, it's not logging the full query to the database. Instead, it logs just the server and database name in the format of:

    SERVER_NAME | DATABASE_NAME

    We have one non-EF call being made in our application to a stored procedure, and this does appear to log correctly.

    Any advice regarding how to log the full database query would be appreciated.

    Wednesday, November 27, 2019 10:45 AM

All replies

  • Hi Adrian,

    I'm looking into the issue. Could you share your App Insights configuration (minus the instrumentation key)? It will make it much easier to try to reproduce your problem.

    Wednesday, November 27, 2019 4:32 PM
    Moderator
  • Hi there,

    Apologies for the delay - I wanted to provide you with as much information as possible.

    Please see below for my configuration file minus the key:

    <?xml version="1.0" encoding="utf-8"?> <ApplicationInsights xmlns="http://schemas.microsoft.com/ApplicationInsights/2013/Settings"> <TelemetryInitializers> <Add Type="Microsoft.ApplicationInsights.DependencyCollector.HttpDependenciesParsingTelemetryInitializer, Microsoft.AI.DependencyCollector" /> <Add Type="Microsoft.ApplicationInsights.WindowsServer.AzureRoleEnvironmentTelemetryInitializer, Microsoft.AI.WindowsServer" /> <Add Type="Microsoft.ApplicationInsights.WindowsServer.AzureWebAppRoleEnvironmentTelemetryInitializer, Microsoft.AI.WindowsServer" /> <Add Type="Microsoft.ApplicationInsights.WindowsServer.BuildInfoConfigComponentVersionTelemetryInitializer, Microsoft.AI.WindowsServer" /> <Add Type="Microsoft.ApplicationInsights.Web.WebTestTelemetryInitializer, Microsoft.AI.Web" /> <Add Type="Microsoft.ApplicationInsights.Web.SyntheticUserAgentTelemetryInitializer, Microsoft.AI.Web"> <!-- Extended list of bots: search|spider|crawl|Bot|Monitor|BrowserMob|BingPreview|PagePeeker|WebThumb|URL2PNG|ZooShot|GomezA|Google SketchUp|Read Later|KTXN|KHTE|Keynote|Pingdom|AlwaysOn|zao|borg|oegp|silk|Xenu|zeal|NING|htdig|lycos|slurp|teoma|voila|yahoo|Sogou|CiBra|Nutch|Java|JNLP|Daumoa|Genieo|ichiro|larbin|pompos|Scrapy|snappy|speedy|vortex|favicon|indexer|Riddler|scooter|scraper|scrubby|WhatWeb|WinHTTP|voyager|archiver|Icarus6j|mogimogi|Netvibes|altavista|charlotte|findlinks|Retreiver|TLSProber|WordPress|wsr-agent|http client|Python-urllib|AppEngine-Google|semanticdiscovery|facebookexternalhit|web/snippet|Google-HTTP-Java-Client--> <Filters>search|spider|crawl|Bot|Monitor|AlwaysOn</Filters> </Add> <Add Type="Microsoft.ApplicationInsights.Web.ClientIpHeaderTelemetryInitializer, Microsoft.AI.Web" /> <Add Type="Microsoft.ApplicationInsights.Web.OperationNameTelemetryInitializer, Microsoft.AI.Web" /> <Add Type="Microsoft.ApplicationInsights.Web.OperationCorrelationTelemetryInitializer, Microsoft.AI.Web" /> <Add Type="Microsoft.ApplicationInsights.Web.UserTelemetryInitializer, Microsoft.AI.Web" /> <Add Type="Microsoft.ApplicationInsights.Web.AuthenticatedUserIdTelemetryInitializer, Microsoft.AI.Web" /> <Add Type="Microsoft.ApplicationInsights.Web.AccountIdTelemetryInitializer, Microsoft.AI.Web" /> <Add Type="Microsoft.ApplicationInsights.Web.SessionTelemetryInitializer, Microsoft.AI.Web" /> </TelemetryInitializers> <TelemetryModules> <Add Type="Microsoft.ApplicationInsights.DependencyCollector.DependencyTrackingTelemetryModule, Microsoft.AI.DependencyCollector"> <ExcludeComponentCorrelationHttpHeadersOnDomains> <!-- Requests to the following hostnames will not be modified by adding correlation headers. Add entries here to exclude additional hostnames. NOTE: this configuration will be lost upon NuGet upgrade. --> <Add>core.windows.net</Add> <Add>core.chinacloudapi.cn</Add> <Add>core.cloudapi.de</Add> <Add>core.usgovcloudapi.net</Add> </ExcludeComponentCorrelationHttpHeadersOnDomains> <IncludeDiagnosticSourceActivities> <Add>Microsoft.Azure.EventHubs</Add> <Add>Microsoft.Azure.ServiceBus</Add> </IncludeDiagnosticSourceActivities> </Add> <Add Type="Microsoft.ApplicationInsights.Extensibility.PerfCounterCollector.PerformanceCollectorModule, Microsoft.AI.PerfCounterCollector"> <!-- Use the following syntax here to collect additional performance counters: <Counters> <Add PerformanceCounter="\Process(??APP_WIN32_PROC??)\Handle Count" ReportAs="Process handle count" /> ... </Counters> PerformanceCounter must be either \CategoryName(InstanceName)\CounterName or \CategoryName\CounterName NOTE: performance counters configuration will be lost upon NuGet upgrade. The following placeholders are supported as InstanceName: ??APP_WIN32_PROC?? - instance name of the application process for Win32 counters. ??APP_W3SVC_PROC?? - instance name of the application IIS worker process for IIS/ASP.NET counters. ??APP_CLR_PROC?? - instance name of the application CLR process for .NET counters. --> </Add> <Add Type="Microsoft.ApplicationInsights.Extensibility.PerfCounterCollector.QuickPulse.QuickPulseTelemetryModule, Microsoft.AI.PerfCounterCollector" /> <Add Type="Microsoft.ApplicationInsights.WindowsServer.AppServicesHeartbeatTelemetryModule, Microsoft.AI.WindowsServer" /> <Add Type="Microsoft.ApplicationInsights.WindowsServer.AzureInstanceMetadataTelemetryModule, Microsoft.AI.WindowsServer"> <!-- Remove individual fields collected here by adding them to the ApplicationInsighs.HeartbeatProvider with the following syntax: <Add Type="Microsoft.ApplicationInsights.Extensibility.Implementation.Tracing.DiagnosticsTelemetryModule, Microsoft.ApplicationInsights"> <ExcludedHeartbeatProperties> <Add>osType</Add> <Add>location</Add> <Add>name</Add> <Add>offer</Add> <Add>platformFaultDomain</Add> <Add>platformUpdateDomain</Add> <Add>publisher</Add> <Add>sku</Add> <Add>version</Add> <Add>vmId</Add> <Add>vmSize</Add> <Add>subscriptionId</Add> <Add>resourceGroupName</Add> <Add>placementGroupId</Add> <Add>tags</Add> <Add>vmScaleSetName</Add> </ExcludedHeartbeatProperties> </Add> NOTE: exclusions will be lost upon upgrade. --> </Add> <Add Type="Microsoft.ApplicationInsights.WindowsServer.DeveloperModeWithDebuggerAttachedTelemetryModule, Microsoft.AI.WindowsServer" /> <Add Type="Microsoft.ApplicationInsights.WindowsServer.UnhandledExceptionTelemetryModule, Microsoft.AI.WindowsServer" /> <Add Type="Microsoft.ApplicationInsights.WindowsServer.UnobservedExceptionTelemetryModule, Microsoft.AI.WindowsServer"> <!--</Add> <Add Type="Microsoft.ApplicationInsights.WindowsServer.FirstChanceExceptionStatisticsTelemetryModule, Microsoft.AI.WindowsServer">--> </Add> <Add Type="Microsoft.ApplicationInsights.Web.RequestTrackingTelemetryModule, Microsoft.AI.Web"> <Handlers> <!-- Add entries here to filter out additional handlers: NOTE: handler configuration will be lost upon NuGet upgrade. --> <Add>Microsoft.VisualStudio.Web.PageInspector.Runtime.Tracing.RequestDataHttpHandler</Add> <Add>System.Web.StaticFileHandler</Add> <Add>System.Web.Handlers.AssemblyResourceLoader</Add> <Add>System.Web.Optimization.BundleHandler</Add> <Add>System.Web.Script.Services.ScriptHandlerFactory</Add> <Add>System.Web.Handlers.TraceHandler</Add> <Add>System.Web.Services.Discovery.DiscoveryRequestHandler</Add> <Add>System.Web.HttpDebugHandler</Add> </Handlers> </Add> <Add Type="Microsoft.ApplicationInsights.Web.ExceptionTrackingTelemetryModule, Microsoft.AI.Web" /> <Add Type="Microsoft.ApplicationInsights.Web.AspNetDiagnosticTelemetryModule, Microsoft.AI.Web" /> </TelemetryModules> <ApplicationIdProvider Type="Microsoft.ApplicationInsights.Extensibility.Implementation.ApplicationId.ApplicationInsightsApplicationIdProvider, Microsoft.ApplicationInsights" /> <TelemetrySinks> <Add Name="default"> <TelemetryProcessors> <Add Type="Microsoft.ApplicationInsights.Extensibility.PerfCounterCollector.QuickPulse.QuickPulseTelemetryProcessor, Microsoft.AI.PerfCounterCollector" /> <Add Type="Microsoft.ApplicationInsights.Extensibility.AutocollectedMetricsExtractor, Microsoft.ApplicationInsights" /> <Add Type="Microsoft.ApplicationInsights.WindowsServer.TelemetryChannel.AdaptiveSamplingTelemetryProcessor, Microsoft.AI.ServerTelemetryChannel"> <MaxTelemetryItemsPerSecond>5</MaxTelemetryItemsPerSecond> <ExcludedTypes>Event</ExcludedTypes> </Add> <Add Type="Microsoft.ApplicationInsights.WindowsServer.TelemetryChannel.AdaptiveSamplingTelemetryProcessor, Microsoft.AI.ServerTelemetryChannel"> <MaxTelemetryItemsPerSecond>5</MaxTelemetryItemsPerSecond> <IncludedTypes>Event</IncludedTypes> </Add> </TelemetryProcessors> <TelemetryChannel Type="Microsoft.ApplicationInsights.WindowsServer.TelemetryChannel.ServerTelemetryChannel, Microsoft.AI.ServerTelemetryChannel" /> </Add> </TelemetrySinks> <!-- Learn more about Application Insights configuration with ApplicationInsights.config here: http://go.microsoft.com/fwlink/?LinkID=513840 Note: If not present, please add <InstrumentationKey>Your Key</InstrumentationKey> to the top of this file. --> <InstrumentationKey></InstrumentationKey> </ApplicationInsights>

     

     

     I thought some general replication steps and code might be useful too:

    1. Create a new .net franework 4.6.1 MVC project
    2. Install entity framework 6.3.0 nuget package
    3. Add a connection string to the web.config
    4. Install application insights
    5. Replace the HomeController.cs file with the contents below.
    6. Run the application
    7. My expectation is to have a useful SQL query logged against the command. Without using EF, I can see a stored procedure call, but when using EF, all I see is the server (localhost) and the DB name (EntityFrameworkDb). I'm not able to upload an image in this forum currently, but in the command, I see "localhost | EntityFrameworkDb".


      <connectionStrings>
        <add name="EntityFrameworkDb" connectionString="Server=localhost; initial catalog=EntityFrameworkDb; Integrated Security=SSPI;MultipleActiveResultSets=true;" providerName="System.Data.SqlClient" />
      </connectionStrings>


    using System; using System.Data.Entity; using System.Web.Mvc; namespace AppInsightsEfTest.Controllers { public class Log { public Guid Id { get; set; } = Guid.NewGuid(); public string LogText { get; set; } public DateTime DateCreated { get; set; } = DateTime.Now; } public class TestDbContext : DbContext { public TestDbContext() : base("name=EntityFrameworkDb") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Log>(); base.OnModelCreating(modelBuilder); } } public class HomeController : Controller { public ActionResult Index() { LogPageView(nameof(Index)); return View(); } private void LogPageView(string pageName) { using (var dbContext = new TestDbContext()) { var newLog = new Log() { LogText = $"{pageName} loaded" }; dbContext.Set<Log>().Add(newLog); dbContext.SaveChanges(); } } public ActionResult About() { ViewBag.Message = "Your application description page."; LogPageView(nameof(About)); return View(); } public ActionResult Contact() { ViewBag.Message = "Your contact page."; LogPageView(nameof(Contact)); return View(); } } }

     

    Wednesday, December 4, 2019 12:13 PM
  • Thank you for the detailed repro instructions. It certainly makes the process faster.

    After doing some more digging, it appears that the issue is not with the configuration in your app or the package versions. While SQL calls are automatically logged, SQL commands made through the standard SqlClient are not in .NET Framework. Not entirely sure why the stored proc is getting logged, but it may have something to do with how you are calling it. There was an older version of the docs that implied that 4.6+ would log them automatically, but this has been corrected.

    With .NET Core it does come through by default, but because of how .NET Framework handles SQL calls it requires some additional configuration on the server itself. There are instructions on how to do this here: https://docs.microsoft.com/en-us/azure/azure-monitor/app/asp-net-dependencies#advanced-sql-tracking-to-get-full-sql-query

    Note that IIS Express does not support this additional configuration so you won't be able to log the queries while working with the default Visual Studio debugging.

    Thursday, December 5, 2019 6:45 PM
    Moderator