AdvisorSQL Certificates
Hello,
I am trying to collect the SQL Certificates obtain by "Select name, expiry_date from sys.certificates" to have an alert when their expiration date arrived.
I tried by datasource but apparently it is not feasible after consultation with LM Support. I was directed to Property Sources but not finding my way in this new environment...
Any groowvy script to be used for that?
import com.santaba.agent.groovyapi.expect.Expect;
import com.santaba.agent.groovyapi.snmp.Snmp;
import com.santaba.agent.groovyapi.http.*;
import com.santaba.agent.groovyapi.jmx.*;
import org.xbill.DNS.*;
import groovy.sql.Sql // needed for SQL connection and query
def hostname = hostProps.get("system.hostname");
import groovy.sql.Sql // needed for SQL connection and query
def url = "jdbc:sqlserver://" + hostname + ";databaseName=master;integratedSecurity=true";
def driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
sql = Sql.newInstance(url, driver) // connects to SQL server
def query_result = sql.firstRow("SELECT name, expiry_date from sys.certificates")
full_name_certificate = query_result.name
full_date_certificate = query_result.expiry_date
sql.close() // Close connection
...
return 0;
This part looks okay but how to get the information in the Alert...
...
Thanks,
Dom
The problem is that DataSources only take in numeric data, meaning that text data can't be included in an alert generated by a DataSource. However, there is a way to work around this since you should have lots of warning before a certificate expires, right? Here are a couple places to start:
1. You could use groovy to calculate the number of days remaining and use that as the datapoint. You could threshold on that data and open alerts whenever it's less than 21, 14, and 7 (or whatever values you want). The value of the datapoint can easily be included in the alert message.
2. You could change the DS to be multi-instance use active discovery to discover all the certificates. Your script only uses sql.firstRow, but you could loop over sql.eachRow and output each row as an instance. You could put the expiration date as the instance description. Description can be included in the alert message. This only works because it's expected that the expiration does not change with any kind of volatility.
