.NET, EF Core and SQL Server, end to end

This guide takes an ASP.NET Core app that uses Entity Framework Core and SQL Server, on IIS or Windows Server, from nothing to full visibility in Logthread. You will:

1. add Logthread to the app with one line (requests, traces, metrics, logs, slow queries);
2. see every request route and every normalised SQL statement in the Endpoints and Database views;
3. install the Logthread Agent on the SQL Server and web server machines for SQL Server metrics, top queries and the ERRORLOG, IIS and Windows Server;
4. create ready dashboards for ASP.NET Core, EF Core, SQL Server, IIS and Windows Server;
5. add a few alerts that catch the problems you care about.

Every command and setting below can be pasted as is. Replace YOUR_INGEST_KEY with your key. You can follow all of it, or only the parts for the machines you run.

Before you start#

• A Logthread workspace and an ingest key. Open Keys in the app, create a key and copy it. It is shown once. It is write-only: it can send data and cannot read anything.
• The ingest endpoint: https://ingest.logthread.com.
• .NET 8 or .NET 9 for the app (Logthread.OpenTelemetry targets net8.0 and net9.0) and the .NET SDK 8 or newer with NuGet 6 or newer on the build machine. Some runtime panels need .NET 9, see Dashboards.
• For the server side: administrator access on the SQL Server and IIS machines (Windows Server 2016 or later, or Linux for SQL Server on Linux).

Two settings are used everywhere:

export LOGTHREAD_INGEST_KEY=YOUR_INGEST_KEY
export LOGTHREAD_ENDPOINT=https://ingest.logthread.com

In PowerShell use $env:LOGTHREAD_INGEST_KEY = 'YOUR_INGEST_KEY' and $env:LOGTHREAD_ENDPOINT = 'https://ingest.logthread.com'.

Step 1: add Logthread to the app#

Add the package feed#

The Logthread packages are served from the Logthread downloads site, not from nuget.org. Fetch the Logthread nuget.config once, next to your .sln or .csproj:

curl -fsSLo nuget.config https://downloads.logthread.com/sdk/nuget/nuget.config

On Windows PowerShell:

Invoke-WebRequest https://downloads.logthread.com/sdk/nuget/nuget.config -OutFile nuget.config

The file maps Logthread and Logthread.* to the Logthread feed and everything else (including all OpenTelemetry.* and Microsoft.* packages) to nuget.org, so a package with the same name on a public feed can never replace ours. Already have a nuget.config? Copy its logthread source and both packageSourceMapping entries into yours.

Install the package#

dotnet add package Logthread.OpenTelemetry

This brings in the logging provider (Logthread.Extensions.Logging), the per-request correlation middleware (Logthread.AspNetCore) and the pinned OpenTelemetry packages. The current version is 0.2.1.

The one line#

In Program.cs:

var builder = WebApplication.CreateBuilder(args);

builder.AddLogthread();   // traces, metrics and logs to Logthread

// ... AddDbContext, AddControllers, and so on ...
var app = builder.Build();
app.Run();

That is the whole integration. It wires:

• Traces for ASP.NET Core requests (named by route template, such as GET /products/{id}, never by raw path), outgoing HttpClient calls, SqlClient and EF Core commands.
• Metrics for requests, Kestrel, the .NET runtime (GC, thread pool, exceptions), EF Core (on EF Core 9 and later) and the Logthread meter logthread.db.query.duration, a latency histogram for every normalised SQL statement.
• Logs from ILogger, each carrying the trace id, the span id and a per-request correlation id.

Without a key or endpoint it registers nothing and prints one line to stderr, and your app carries on.

Configure it#

Settings are read from code, then the Logthread section of configuration, then LOGTHREAD_* environment variables, in that order of precedence. Keep the key out of files that are checked in: set it as an environment variable, or use user secrets while developing.

appsettings.json (no key in it):

{
  "Logthread": {
    "Endpoint": "https://ingest.logthread.com",
    "Service": "shop-api",
    "Environment": "production",
    "SamplingRatio": 1.0,
    "ActiveUsers": true
  }
}

The key, in development, with user secrets:

dotnet user-secrets init
dotnet user-secrets set "Logthread:ApiKey" "YOUR_INGEST_KEY"

The key, in production, as an environment variable (Linux, containers, Azure App Service settings):

export LOGTHREAD_INGEST_KEY=YOUR_INGEST_KEY

Or the same in code, for settings that are not secret:

builder.AddLogthread(o =>
{
    o.Service = "shop-api";
    o.Environment = "production";
    o.ActiveUsers = true;          // opt-in: distinct signed-in users in a 5 minute window
});

The settings you will use most:

Everything else, with defaults, is in the .NET SDK documentation.

Run IIS-hosted apps with the key#

An ASP.NET Core app on IIS reads machine environment variables when its worker process starts. Set them once on the server, from an elevated PowerShell, and restart IIS:

[Environment]::SetEnvironmentVariable('LOGTHREAD_INGEST_KEY', 'YOUR_INGEST_KEY', 'Machine')
[Environment]::SetEnvironmentVariable('LOGTHREAD_ENDPOINT', 'https://ingest.logthread.com', 'Machine')
iisreset

Avoid putting the key in web.config: that file is plain text on disk and often copied around.

Step 2: EF Core and SQL Server from the app#

Nothing more to add: the SqlClient and EF Core instrumentation is on by default. A statement that goes through EF Core and SqlClient is counted once.

For every database call, Logthread records:

• the normalised statement, with every literal and IN (...) list replaced by ? (SELECT * FROM Users WHERE Id = ? AND Name = ?), and the EF Core SET NOCOUNT ON; preamble removed;
• a hash of that statement (db.query.hash), db.system and db.name;
• the duration, in the trace and in the logthread.db.query.duration histogram (the first 500 distinct statements; the rest are grouped as other).

It never records parameter values. This is the same for every EF Core provider, so the Database view also works for PostgreSQL or SQLite apps.

Try it with the example app#

The example app is a small shop API with EF Core on SQLite by default, and SQL Server when you give it a connection string. The app is the zip logthread-dotnet-aspnetcore-efcore-0.2.1.zip. Fetch and run it:

curl -fsSLO https://downloads.logthread.com/examples/dotnet-aspnetcore-efcore/0.2.1/logthread-dotnet-aspnetcore-efcore-0.2.1.zip
unzip logthread-dotnet-aspnetcore-efcore-0.2.1.zip
cd dotnet-aspnetcore-efcore
export LOGTHREAD_INGEST_KEY=YOUR_INGEST_KEY
export LOGTHREAD_ENDPOINT=https://ingest.logthread.com
dotnet run

To use SQL Server instead of SQLite, set a connection string first (the shop database is created on first run, so the login needs permission to create it):

export ConnectionStrings__Shop='Server=localhost;Database=shop;User Id=shop_app;Password=YOUR_PASSWORD;TrustServerCertificate=true'

Then call it a few times (the port is printed when it starts, usually http://localhost:5000):

curl "http://localhost:5000/products?take=20"
curl "http://localhost:5000/products/42"
curl "http://localhost:5000/products/search?name=Product%201&ids=1,2,3"
curl "http://localhost:5000/slow"
curl "http://localhost:5000/boom"
curl -X POST "http://localhost:5000/login?user=alice"
curl -H "X-Demo-User: alice" "http://localhost:5000/me"

/slow runs a deliberately slow query, /boom returns a 500 with an error log, and the signed-in calls feed the active-users and sign-in counters.

What you then see#

Give it a minute for the first metrics export, then open Traces in the app. It has three tabs:

• Traces finds slow and failing requests; open one to see the waterfall, the SQL under each span and the logs written during that request.
• Database lists the slowest statements, normalised, with calls, errors, p50, p95, p99 and total time. Pick a statement for its latency and calls-per-second charts and the slowest example traces. Use the filters for service, database system and database name. By default only the innermost span of a query is counted, so EF Core and SqlClient do not double up.
• Endpoints lists every route with requests per second, p50, p95, p99, error rate and Apdex. Pick one for its charts and slowest example traces.

When the Logthread Agent runs on the SQL Server, the Database tab also shows a From SQL Server panel (next step).

Step 3: SQL Server itself, with the Logthread Agent#

The app sees what it asks of the database. The server sees everything else: batch requests per second, locks, deadlocks, page life expectancy, the transaction log, the most expensive statements from all clients, and its error log. The Logthread Agent's sqlserver profile collects them.

The profile collects:

• Metrics: batch requests, compilations, connections, lock waits and deadlocks, IO, buffer cache and page life expectancy, wait statistics, transaction log use and growth, database sizes.
• Top queries: the slowest statements by elapsed and CPU time over the last minute (db.server.top_query events), statements running now with blocking and waits (db.server.query_sample) and top stored procedures.
• The ERRORLOG, parsed into log lines with severity.

Statement text is obfuscated at the source (literals and parameter values become ?) and the execution plan is removed on the machine before anything is sent, because plans can contain compiled parameter values.

Create a least-privilege login#

Run this in SQL Server Management Studio, sqlcmd or Azure Data Studio, as a sysadmin. The login can read server state and nothing else: no data, no writes.

CREATE LOGIN logthread_agent WITH PASSWORD = 'CHOOSE_A_STRONG_PASSWORD';
GRANT VIEW SERVER STATE TO logthread_agent;        -- SQL Server 2022 and later: GRANT VIEW SERVER PERFORMANCE STATE TO logthread_agent;
GRANT VIEW ANY DEFINITION TO logthread_agent;
GRANT VIEW ANY DATABASE TO logthread_agent;

The receiver supports SQL Server Standard, Enterprise and Express and Azure SQL; it does not support Azure SQL Edge.

The connection string (DSN)#

The agent reads a connection string from LOGTHREAD_SQLSERVER_DSN:

server=db01;port=1433;user id=logthread_agent;password=CHOOSE_A_STRONG_PASSWORD;encrypt=true;TrustServerCertificate=true

• server is the host name of the SQL Server instance (. or localhost on the same machine) and port its TCP port.
• TrustServerCertificate=true accepts the server's own certificate. If you have a certificate from a trusted authority, remove it.
• On Windows, with the agent on the same machine, integrated authentication also works (the service runs as LocalSystem, so that account needs the same grants): server=.;port=1433;Integrated Security=SSPI;encrypt=true;TrustServerCertificate=true.
• Choose a password for the monitoring login without semicolons or quotes: they break the connection string.

Without a DSN the profile collects only the Windows performance counters and there are no top queries.

Install on Windows (SQL Server on Windows Server)#

From an elevated PowerShell on the SQL Server machine. The Windows parts of the agent are in beta.

Invoke-WebRequest https://downloads.logthread.com/agent/install.ps1 -OutFile install.ps1
.\install.ps1 -Key YOUR_INGEST_KEY -Profiles windows-server,sqlserver `
  -SqlServerDsn 'server=.;port=1433;user id=logthread_agent;password=CHOOSE_A_STRONG_PASSWORD;encrypt=true;TrustServerCertificate=true'

It installs the LogthreadAgent Windows service, verifies the download's SHA-256, and stores the key and the DSN in the service's own environment, readable only by SYSTEM and Administrators. The ERRORLOG is read from C:\Program Files\Microsoft SQL Server\MSSQL*\MSSQL\Log\ERRORLOG; set LOGTHREAD_SQLSERVER_ERRORLOG_DIR to the folder that contains the MSSQL* folders if SQL Server is installed elsewhere. Re-run the same command to change profiles or the DSN.

Install on Linux (SQL Server on Linux)#

curl -sSL https://downloads.logthread.com/agent/install.sh | sudo sh -s -- --key YOUR_INGEST_KEY --profiles linux-host,sqlserver
sudo sh -c 'echo "LOGTHREAD_SQLSERVER_DSN=server=localhost;port=1433;user id=logthread_agent;password=CHOOSE_A_STRONG_PASSWORD;encrypt=true;TrustServerCertificate=true" >> /etc/logthread-agent/agent.env'
sudo systemctl restart logthread-agent

The Linux ERRORLOG is read from /var/opt/mssql/log/errorlog; make sure the logthread-agent user can read that file.

Check it#

• In the app open Sources: the host appears with the sqlserver service within a couple of minutes.
• Open Traces, Database: the From SQL Server panel lists the top queries with executions, elapsed time, average, CPU and logical reads.
• In Explore, search service:sqlserver for ERRORLOG lines and the db.server.top_query events.

If the profile reports The sqlserver profile is enabled without a DSN, the DSN was not set. If it logs a login failure, check the password and that the three GRANT statements ran. More in the agent troubleshooting table.

Step 4: IIS and Windows Server#

On each web server (and the SQL Server machine, if you want its OS metrics), install the agent with the windows-server and iis profiles. From an elevated PowerShell:

Invoke-WebRequest https://downloads.logthread.com/agent/install.ps1 -OutFile install.ps1
.\install.ps1 -Key YOUR_INGEST_KEY -Profiles windows-server,iis

For a machine that runs SQL Server too, combine them:

.\install.ps1 -Key YOUR_INGEST_KEY -Profiles windows-server,iis,sqlserver `
  -SqlServerDsn 'server=.;port=1433;user id=logthread_agent;password=CHOOSE_A_STRONG_PASSWORD;encrypt=true;TrustServerCertificate=true'

What each profile collects:

• windows-server: the Windows Event Log (Application and System), host metrics (CPU, memory, paging, disk, file systems, network, processes) and performance counters (CPU queue length, memory pages per second, available memory, disk queue length and latency).
• iis: the IIS W3C request logs, parsed in the agent into fields (method, status, time_taken_ms and the rest, with severity by status code), plus IIS metrics per site and application pool: requests per second, connections, queue length, rejected requests and application pool state. The logs are read from %SystemDrive%\inetpub\logs\LogFiles\W3SVC*\*.log; pass -IisLogDir D:\Logs\IIS if you moved them. Cookies are never sent, and LOGTHREAD_IIS_DROP_QUERY=true also drops query strings.
• The Security event log is a separate, opt-in profile (windows-security) because it is noisy.

Check it:

Get-Service LogthreadAgent
Invoke-RestMethod http://127.0.0.1:13133/

The first returns Running, the second the agent's health. In Sources the host appears within a minute or two, and the IIS log lines are in Explore under the iis service.

To manage many machines from the app (versioned configs, rollouts, rollback), install with -Fleet -Labels env=prod,role=web and use Fleet; see Fleet mode.

Ready dashboards#

Open Dashboards, choose From template, pick a template and choose Create. Each has a service or host selector at the top.

Notes: the runtime and GC panels need the app on .NET 9 or later, which publishes those meters; EF Core metrics come from EF Core 9 and later; the statement hash on the EF Core charts identifies a normalised statement, and Traces, Database shows its text; some SQL Server counters (deadlocks, blocked processes) are optional receiver metrics and stay empty if your server does not report them.

Alerts worth setting up#

Open Alerts, choose New rule, pick the rule type, set the severity and a channel (email, Slack, Teams, PagerDuty, a webhook and more), and save. These are good starting points; adjust the numbers to your normal.

Metric names are those shown in the metric picker of the rule editor, so type a few letters to confirm the name your data uses.

Troubleshooting#

Privacy#

No request or response bodies, headers or query strings are captured by the app. SQL statements have their literals removed and parameter values are never recorded. Exception messages are not attached to spans unless you set RecordExceptions. Active users are only counted, hashed in memory. The agent sends only what its profiles list, SQL Server statement text is obfuscated and the execution plan is removed before sending, and IIS cookies are never sent. See what the agent collects.

Next steps#

• Logthread for .NET: every setting, the cardinality guard and the upstream package versions.
• Logthread Agent: all profiles, Fleet mode, uninstall and troubleshooting.
• Query language and Parse logs at ingest for the logs you now have.

Next: search and follow your logs in Explore and live tail, then set up an alert with a channel.