Azure Function + Application Insights - not tracking SQL Queries

Question

We have an Http Triggered Azure Function (.NET Core 3.1). For whatever reason we can't get the detailed SQL Dependency Tracking working, all we see is: tcp:ourdbserver.database.windows.net,1433 | TestDB.

It doesn't work both when debugging locally and when deployed to Azure. We've installed the latest ApplicationInsights nuget package (see below):

And in the StartUp we opt-in to SQL Text collection as suggested by Microsoft docs here:

Could anyone please shed some light on what we are missing?

UPDATE:

This is what we have in the host.json:

{
  "version": "2.0",
  "logging": {
    "applicationInsights": {
      "samplingExcludedTypes": "Request",
      "samplingSettings": {
        "isEnabled": true
      }
    }
  }
}

And here is what gets outputted into the Debug console when debugging locally:

ApplicationInsightsLoggerOptions
{
  "SamplingSettings": {
    "EvaluationInterval": "00:00:15",
    "InitialSamplingPercentage": 100.0,
    "MaxSamplingPercentage": 100.0,
    "MaxTelemetryItemsPerSecond": 20.0,
    "MinSamplingPercentage": 0.1,
    "MovingAverageRatio": 0.25,
    "SamplingPercentageDecreaseTimeout": "00:02:00",
    "SamplingPercentageIncreaseTimeout": "00:15:00"
  },
  "SamplingExcludedTypes": null,
  "SamplingIncludedTypes": null,
  "SnapshotConfiguration": null,
  "EnablePerformanceCountersCollection": true,
  "HttpAutoCollectionOptions": {
    "EnableHttpTriggerExtendedInfoCollection": true,
    "EnableW3CDistributedTracing": true,
    "EnableResponseHeaderInjection": true
  },
  "LiveMetricsInitializationDelay": "00:00:15",
  "EnableLiveMetrics": true,
  "EnableDependencyTracking": true
}

Can you post your logging configuration from host.json. I suspect it might be because of loglevel filter at first glance. Dependency Tracking in function requires to have Information level log enabled as mentioned in the highlighted Note here https://learn.microsoft.com/en-us/azure/azure-functions/functions-monitoring?tabs=cmd#dependencies — krishg, Aug 28 '20 at 12:23

score 3 · Accepted Answer · answered Feb 04 '21 at 21:49

You need to configure dependencyTrackingOptions.enableSqlCommandTextInstrumentation in the host.json file:

{
  "logging": {
    "applicationInsights": {
      "dependencyTrackingOptions": {
        "enableSqlCommandTextInstrumentation": true
      }
    }
  }
}

krishg · Answer 2 · 2020-08-31T11:33:41.163

1

Update your logging section in host.json as below to allow Information level log (note I added logLevel in the existing config you posted above). By default it's Warning if you do not specify. As mentioned in the Note here, dependencies are logged with Information level. Also note excludedTypes (not samplingExcludedTypes) should be inside samplingSettings as per documentation.

{
  "version": "2.0",
  "logging": {
    "applicationInsights": {
        "samplingSettings": {
            "isEnabled": true,
            "excludedTypes": "Dependency;Request"
        }
    },
    "logLevel": {"default": "Information"}
  }
}

edited Aug 31 '20 at 11:33

answered Aug 29 '20 at 12:14

krishg

5,935
2
12
19

Hmm. Now I'm seeing the SQL in the Debug console when running locally, but still nothing in ApplicationInsights. – KenR Aug 29 '20 at 12:58
When you say nothing in App Insights, do you mean while debugging locally? – krishg Aug 29 '20 at 13:07
hopefully you have APPINSIGHTS_INSTRUMENTATIONKEY set in your localsettings.json? – krishg Aug 29 '20 at 21:03
The AppInsights InstrumentationKey is set up correctly, all Requests, Traces and Dependencies are getting logged in AppInsights when debugging locally. The only thing missing is the actual SQL commands in the SQL dependencies. – KenR Aug 30 '20 at 01:54

Azure Function + Application Insights - not tracking SQL Queries

2 Answers2