Forum Discussion

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

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

So close again: 

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

hostname = hostProps.get("system.hostname");
user = ''
pass = ''

SQLUrl = "jdbc:sqlserver://" + hostname + ";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(certname + "##" + certname)
}
sql.close() // Close connection

The problem was two-fold: 1) you were not outputting in the AD format and 2) you had "##" in your certificate names, which interferes with the built in parsing mechanism that parses the output.  Make sure discovery is working properly first because the collector script will look very similar up until the println statement.

Your collection script would be very similar (assuming you've set batchscript as the collector type on the DS): 

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 = ''

SQLUrl = "jdbc:sqlserver://" + hostname + ";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("#","")
    timetoexpire = TimeCategory.minus(new Date(),Date.parse("yyy-MM-dd HH:mm:ss.S",it.expiry_date))
    println(certname + ".daystoexpire: " + daystoexpire.getDays())
}
sql.close() // Close connection

The timetoexpire variable might need some tweaking to make sure it parses properly. This should give you collection output that looks like this (fake values):

MS_AgentSigningCertificate.daystoexpire: 23
MS_PolicySigningCertificate.daystoexpire: 45
etc.

You'd create a datapoint and set "Content the script writes to the standard output" and set Interpret output with to "multi-line key-value pairs". Then set the key to ##WILDVALUE##.daystoexpire