# Query Logs SQL Server App

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

## Overview

- **Async logging** – writes log entries through a bounded queue
- **Cleanup support** – prunes old records by age/count
- **Retained schema** – uses a database name and SQL Server connection string for storage

## 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 | `false` | Enables or disables query logging. |
| `maxQueueSize` | number | `1000000` | Maximum number of log entries allowed in the in-memory queue before new entries are dropped. |
| `maxLogDays` | number | `0` | Maximum age (days) to retain. `0` disables age-based cleanup. |
| `maxLogRecords` | number | `0` | Maximum number of records to retain. `0` disables count-based cleanup. |
| `databaseName` | string | `"DnsQueryLogs"` | Database name used to store logs. |
| `connectionString` | string | *(required)* | SQL Server connection string **without** selecting a database/initial catalog. The app uses `databaseName` separately. |

### Example

```json
{
  "enableLogging": false,
  "maxQueueSize": 1000000,
  "maxLogDays": 0,
  "maxLogRecords": 0,
  "databaseName": "DnsQueryLogs",
  "connectionString": "Data Source=tcp:192.168.10.101,1433; User ID=username; Password=password; TrustServerCertificate=true;"
}
```

## Runtime behavior

1. Queries are buffered in a bounded channel.
2. A background consumer thread bulk inserts records into SQL Server storage.
3. A periodic cleanup timer removes old records.

## Risks / operational notes

- Queue overflow drops writes (`DropWrite` behavior).
- Database connectivity issues can stop logging.
- High traffic deployments should monitor write latency.

## Troubleshooting

- Confirm the database is reachable and credentials are valid.
- Check the connection string and `databaseName`.
- Review server logs for SQL client errors.
