Forum Discussion

Dominique's avatar
Dominique
Icon for Advisor rankAdvisor
5 years ago
Solved

SQL 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 ...
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

Anonymous's avatar
Anonymous
5 years ago
12 hours ago, Dominique said:

"set the jdbc.port property on the device." working on this now... Should it be a custom property as the jdbc properties already define for the cluster:

mssql.mssqlserver.mssql_url    jdbc:sqlserver://10.12.172.26:1438;integratedSecurity=true;applicationIntent=ReadOnly
mssql.sqlclent1.mssql_url    jdbc:sqlserver://10.12.172.26:1440;integratedSecurity=true;applicationIntent=ReadOnly
mssql.sqlclent2.mssql_url    jdbc:sqlserver://10.12.172.26:1436;integratedSecurity=true;applicationIntent=ReadOnly
mssql.sqlclent3.mssql_url    jdbc:sqlserver://10.12.172.26:1442;integratedSecurity=true;applicationIntent=ReadOnly
mssql.sqlclent4.mssql_url    jdbc:sqlserver://10.12.172.26:1445;integratedSecurity=true;applicationIntent=ReadOnly
mssql.sqlclent5.mssql_url    jdbc:sqlserver://10.12.172.26:1447;integratedSecurity=true;applicationIntent=ReadOnly
mssql.sqlclent6.mssql_url    jdbc:sqlserver://10.12.172.26:1449;integratedSecurity=true;applicationIntent=ReadOnly
mssql.sqlclentgp1.mssql_url    jdbc:sqlserver://10.12.172.26:1450;integratedSecurity=true;applicationIntent=Readnl

Ah, you have multiple ports on the server, this is new. This will take a bit of modification to the discovery script so that it can discover all the certificates from each DB instance. You don't need to store each full URL as a property on the server. You only need to list the port numbers, because that's the only thing that is different.

I've updated your discovery script to this: 

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
import groovy.time.*

hostname = hostProps.get("system.hostname");
user = ''
pass = ''
portList = hostProps.get("jdbc.mssql.portlist").tokenize(", ")

portList.each{port ->
    SQLUrl = "jdbc:sqlserver://" + hostname + ":" + port + ";databaseName=master;integratedSecurity=true" //
    sql = Sql.newInstance(SQLUrl, user, pass, 'com.microsoft.sqlserver.jdbc.SQLServerDriver')
    sql.eachRow( 'SELECT name, expiry_date from sys.certificates'){
	    certname = it.name.toString().replaceAll("#","")
        println(port + "_" + certname + "##" + certname + " (" + port + ")######jdbc.mssql.port=" + port)
    }
}
sql.close() // Close connection}
return 0;

 

And I updated your collection script to this: 

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
import groovy.time.*

hostname = hostProps.get("system.hostname");
user = ''
pass = ''
portList = hostProps.get("jdbc.mssql.portlist").tokenize(", ")

portList.each{port->
    SQLUrl = "jdbc:sqlserver://" + hostname + ":" + port + ";databaseName=master;integratedSecurity=true" //
    sql = Sql.newInstance(SQLUrl, user, pass, 'com.microsoft.sqlserver.jdbc.SQLServerDriver')
    
    sql.eachRow( 'SELECT name, expiry_date from sys.certificates'){
    	certname = it.name.toString().replaceAll("#","")
        daystoexpire = TimeCategory.minus(new Date(),Date.parse("yyy-MM-dd HH:mm:ss.S",it.expiry_date.toString()))
        println(port + "_" + certname + ".daystoexpire: " + daystoexpire.getDays())
    }
    sql.close() // Close connection
}
return 0

 

Also, since the script won't work without the property jdbc.mssql.portlist, I've added that to the AppliesTo. I also set the instances to be automatically grouped by port number. This is all on _SSL_Certificates_All_Weenig in your portal.