neon-hippo opened a new pull request, #4141:
URL: https://github.com/apache/hertzbeat/pull/4141
## [monitoring-template] feat(sqlserver): add database account expiry metrics
Adds an `account_expiry` metric group to the SQL Server monitoring template,
matching the existing MySQL implementation.
### Query
```sql
SELECT name AS login_name,
ISNULL(LOGINPROPERTY(name, "DaysUntilExpiration"), -1) AS days_left,
CASE LOGINPROPERTY(name, "IsExpired") WHEN 1 THEN "Y" ELSE "N" END AS
password_expired,
CASE is_disabled WHEN 1 THEN "Y" ELSE "N" END AS is_disabled
FROM sys.sql_logins
WHERE type_desc = "SQL_LOGIN";
```
### What is Included
- **`app-sqlserver.yml`** — new `account_expiry` metric group (priority 100,
`multiRow`)
- **`SqlServerJdbcTemplateIntegrationTest`** — 34 tests across SQL Server
2017, 2019, 2022, and 2025
- **`pom.xml`** — `mssql-jdbc` test dependency
### Design Decisions
- Uses `CASE WHEN` returning `Y`/`N` instead of raw `CAST AS VARCHAR` —
matches MySQL `mysql.user.password_expired` format and avoids implicit VARCHAR
lengths
### Permissions Required
```sql
GRANT VIEW SERVER STATE TO [monitoring_user];
GRANT VIEW ANY DEFINITION TO [monitoring_user];
```
Equivalent to the MySQL implementation which requires `SELECT ON mysql.*`.
### Compatibility
| Version | Status |
|---------|--------|
| SQL Server 2025 (17.0.4045.5, CU5) | ✅ Tested |
| SQL Server 2022 (16.x) | ✅ Tested |
| SQL Server 2019 (15.x) | ✅ Tested |
| SQL Server 2017 (14.x) | ✅ Tested |
| SQL Server 2016 | ⚠️ Not tested — no Linux container image on MCR |
All three catalog objects (`sys.sql_logins`,
`LOGINPROPERTY("DaysUntilExpiration")`, `LOGINPROPERTY("IsExpired")`) are
documented in Microsoft Learn for SQL Server 2025 with no deprecation warnings.
### Known Limitation
HertzBeat hardcodes a 1,000-row limit for `multiRow` queries. Servers with
>1,000 SQL logins will see truncated results. This is a platform limitation,
not specific to this template.
### References
- Closes #3737
- Microsoft Learn:
[sys.sql_logins](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-logins-transact-sql?view=sql-server-ver17)
- Microsoft Learn:
[LOGINPROPERTY](https://learn.microsoft.com/en-us/sql/t-sql/functions/loginproperty-transact-sql?view=sql-server-ver17)
<hr>
<sub>🤖 This patch was developed with assistance from DeepSeek-v4-pro.</sub>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
