Feature request: include collection of SSL expiration from SHOW GLOBAL STATUS output

[nikosmeds]

Issue

It appears that mysqld-exporter does not collect any non-floating point values from SHOW GLOBAL STATUS;

1. Use SQL query search GLOBAL STATUS

   mysql> SHOW GLOBAL STATUS \G;
   [...]
   *************************** 389. row ***************************
   Variable_name: Ssl_finished_connects
           Value: 0
   *************************** 390. row ***************************
   Variable_name: Ssl_server_not_after
           Value: May 24 11:46:23 2020 GMT
   *************************** 391. row ***************************
   Variable_name: Ssl_server_not_before
           Value: Feb 24 11:46:23 2020 GMT
   *************************** 392. row ***************************
   Variable_name: Ssl_session_cache_hits
           Value: 0
   

2. Via PromQL I can find mysql_global_status_ssl_finished_connects and mysql_global_status_ssl_session_cache_hits metrics
3. Via PromQL I cannot find mysql_global_status_ssl_server_not_after or mysql_global_status_ssl_server_not_before metrics

My assumption is that these metrics are skipped due to the SQL command output format being timestamps and not a floating point value.

It would be great to have the SSL expiration metrics available in Prometheus, as then I can create "SSL Expiring Soon" alerts for the certificates loaded by MariaDB.

Thanks :)

Host operating system:

Linux redacted.example.net 5.0.0-36-generic #39~18.04.1-Ubuntu SMP Tue Nov 12 11:09:50 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

mysqld_exporter version:

mysqld_exporter, version 0.12.1 (branch: HEAD, revision: 48667bf7c3b438b5e93b259f3d17b70a7c9aff96)
  build user:       root@0b3e56a7bc0a
  build date:       20190729-12:35:58
  go version:       go1.12.7

MySQL server version

Percona XtraDB (i.e. MariaDB)

mysql> SELECT VERSION();
+------------------+
| VERSION()        |
+------------------+
| 5.7.28-31-57-log |
+------------------+
1 row in set (0.00 sec)

[nikosmeds]

I have minimal Go experience, but #338 looks like a good example of the changes required when adding new metrics that aren't initially floating point values.

Also blackbox-exporter collects SSL expiration with HTTP requests, https://github.com/prometheus/blackbox_exporter/blob/master/prober/http.go#L260 may be another good example.

EDIT: Also related https://programming.guide/go/format-parse-string-time-date-example.html

[nikosmeds]

Figured I'd update with a recent discovery: we can covert the value to epoch time using date command.

$ date -d "May 24 11:46:23 2020 GMT" +'%s'
1590320783

[mathieu-souchet]

Hi,

I use latest version of mysqld_exporter (v0.15.1) and the metrics mysql_global_status_ssl_server_not_before and mysql_global_status_ssl_server_not_after are not available.

So I don't think this issue is resolved ... or I have missed something 🤷

The only metrics I have are the following :

# HELP mysql_global_status_ssl_accept_renegotiates Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_accept_renegotiates untyped
mysql_global_status_ssl_accept_renegotiates 0
# HELP mysql_global_status_ssl_accepts Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_accepts untyped
mysql_global_status_ssl_accepts 40
# HELP mysql_global_status_ssl_callback_cache_hits Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_callback_cache_hits untyped
mysql_global_status_ssl_callback_cache_hits 0
# HELP mysql_global_status_ssl_client_connects Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_client_connects untyped
mysql_global_status_ssl_client_connects 0
# HELP mysql_global_status_ssl_connect_renegotiates Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_connect_renegotiates untyped
mysql_global_status_ssl_connect_renegotiates 0
# HELP mysql_global_status_ssl_ctx_verify_depth Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_ctx_verify_depth untyped
mysql_global_status_ssl_ctx_verify_depth 1.8446744073709552e+19
# HELP mysql_global_status_ssl_ctx_verify_mode Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_ctx_verify_mode untyped
mysql_global_status_ssl_ctx_verify_mode 5
# HELP mysql_global_status_ssl_default_timeout Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_default_timeout untyped
mysql_global_status_ssl_default_timeout 0
# HELP mysql_global_status_ssl_finished_accepts Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_finished_accepts untyped
mysql_global_status_ssl_finished_accepts 40
# HELP mysql_global_status_ssl_finished_connects Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_finished_connects untyped
mysql_global_status_ssl_finished_connects 0
# HELP mysql_global_status_ssl_session_cache_hits Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_session_cache_hits untyped
mysql_global_status_ssl_session_cache_hits 0
# HELP mysql_global_status_ssl_session_cache_misses Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_session_cache_misses untyped
mysql_global_status_ssl_session_cache_misses 0
# HELP mysql_global_status_ssl_session_cache_overflows Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_session_cache_overflows untyped
mysql_global_status_ssl_session_cache_overflows 0
# HELP mysql_global_status_ssl_session_cache_size Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_session_cache_size untyped
mysql_global_status_ssl_session_cache_size 128
# HELP mysql_global_status_ssl_session_cache_timeouts Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_session_cache_timeouts untyped
mysql_global_status_ssl_session_cache_timeouts 0
# HELP mysql_global_status_ssl_sessions_reused Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_sessions_reused untyped
mysql_global_status_ssl_sessions_reused 0
# HELP mysql_global_status_ssl_used_session_cache_entries Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_used_session_cache_entries untyped
mysql_global_status_ssl_used_session_cache_entries 0
# HELP mysql_global_status_ssl_verify_depth Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_verify_depth untyped
mysql_global_status_ssl_verify_depth 0
# HELP mysql_global_status_ssl_verify_mode Generic metric from SHOW GLOBAL STATUS.
# TYPE mysql_global_status_ssl_verify_mode untyped
mysql_global_status_ssl_verify_mode 0

[mathieu-souchet]

I have more information about my last comment,

I think there is an issue in Scan method of file sql.go, the variables Ssl_server_not_after and Ssl_server_not_before
are empty after call Scan method

cf. following screens (I have edited the source code locally to debug the issue) :

For information, with a SQL Client, the 2 variables are as following :

MariaDB [(none)]> show global status where variable_name like "%_server_not_%";
+-----------------------+--------------------------+
| Variable_name         | Value                    |
+-----------------------+--------------------------+
| Ssl_server_not_after  | Jul  7 23:59:59 2025 GMT |
| Ssl_server_not_before | Jul 19 00:00:00 2024 GMT |
+-----------------------+--------------------------+
2 rows in set (0,001 sec)

But the sql.go file is hard to debug 😓
Someone can help ?