Query Logs SQLite App

A DNS App for Technitium DNS Server that logs DNS queries to a SQLite database.

Overview

Async logging – writes log entries through a bounded queue
Cleanup support – prunes old records by age/count
Optional in-memory mode – can use an in-memory database
Vacuum support – can vacuum after cleanup when enabled

Integration / extension points

Implements: IDnsApplication, IDnsQueryLogger, IDnsQueryLogs
Runs as a DNS query logger with asynchronous persistence.

Database

The database table stores values for some fields in numeric format. The fields and the values are described as below.

Protocol Field

Protocol	Value	Description
0	UDP	The standard DNS over UDP protocol
1	TCP	The standard DNS over TCP protocol
2	TLS	DNS-over-TLS RFC 7858
3	HTTPS	DNS-over-HTTPS RFC 8484
5	QUIC	DNS-over-QUIC RFC 9250
253	UdpProxy	PROXY Protocol over UDP
254	TcpProxy	PROXY Protocol over TCP

Response Type Field

Response Type	Value	Description
1	Authoritative	Response generated by the DNS server itself
2	Recursive	Response received from a recursive query to upstream
3	Cached	Response generated by DNS server's cache
4	Blocked	Response generated by DNS server to block a request
5	UpstreamBlocked	Response received from an upstream blocking a request
6	UpstreamBlockedCached	Response generated by DNS server's cache containing blocked response from upstream
7	Dropped	A `null` response generated by the DNS server indicating that the request was dropped

Configuration

dnsApp.config contains these keys:

Property	Type	Default	Description
`enableLogging`	boolean	`true`	Enables or disables logging.
`maxQueueSize`	number	`200000`	Size of the bounded log queue.
`maxLogDays`	number	`0`	Maximum age of retained logs (days). `0` disables age-based cleanup.
`maxLogRecords`	number	`0`	Maximum number of retained log records. `0` disables count-based cleanup.
`enableVacuum`	boolean	`false`	Runs `VACUUM` after cleanup when records were deleted.
`useInMemoryDb`	boolean	`false`	Uses an in-memory SQLite database.
`sqliteDbPath`	string	`querylogs.db`	Path to the SQLite database file.
`connectionString`	string	`Data Source='{sqliteDbPath}'; Cache=Shared;`	SQLite connection string template.

The example below matches the shipped dnsApp.config values, which enable 7-day and 10,000-record cleanup by default in the distributed package.

Example

json

{
  "enableLogging": true,
  "maxQueueSize": 200000,
  "maxLogDays": 7,
  "maxLogRecords": 10000,
  "enableVacuum": false,
  "useInMemoryDb": false,
  "sqliteDbPath": "querylogs.db",
  "connectionString": "Data Source='{sqliteDbPath}'; Cache=Shared;"
}

Runtime behavior

Queries are buffered in a bounded channel.
A background consumer thread bulk inserts records into SQLite.
A periodic cleanup timer removes old records.
If enableVacuum is enabled and cleanup deleted records, the database is vacuumed.

Risks / operational notes

Queue overflow drops writes (DropWrite behavior).
SQLite write contention can affect high traffic deployments.
In-memory mode does not persist across restarts.

Troubleshooting

Confirm the database path is writable.
Confirm connectionString still contains the {sqliteDbPath} token.
Check server logs for SQLite errors if logging stops.