• 1

SQL Certificates


Go to solution Solved by Stuart Weenig,

Question

Posted (edited)

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

 

Edited by Dominique
Link to post
Share on other sites

Recommended Posts

  • 1
  • Administrators
  • Solution

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.

Link to post
Share on other sites
  • 2
  • Administrators

You have a couple problems here.

1. Your script is written in groovy, but the language you had selected was PowerShell.

2. Your DS was setup as a script but your collection script is setup to output like a batch script. Batch script is actually better in this case since your collection query is capable of collecting all the data for all the instances in one go. Unfortunately, you can't change this after the DS is created. I created a new DS with those corrections. Save this snippet as an XML file and import it into your portal (Settings>>DataSources>>Add>>From File). Then compare the differences. If you like this one, fix the name and you'll be good to go.

<?xml version="1.0" encoding="UTF-8" ?>
<feed  version="1.0" hasPendingRequests="false" >
  <company></company>
  <status>200</status>
  <errmsg>OK</errmsg>
  <interval>0</interval>
    <entry type="predatasource">
        <version>1601394083</version>
        <name>_SSL_Certificates_All_Weenig</name>
        <displayedas>_SSL_Certificates_All_Weenig</displayedas>
        <description></description>
        <collector>batchscript</collector>
        <hasMultiInstances>true</hasMultiInstances>
        <schedule>180</schedule>
        <appliesTo>join(system.groups,&#34;,&#34;) =~ &#34;IT Ops Systems Management Group&#34;</appliesTo>
        <wildcardauto>true</wildcardauto>
        <wildcardpersist>false</wildcardpersist>
        <wildcardlinuxscript>ad_script</wildcardlinuxscript>
        <wildcardlinuxcmdline>type=&#34;embeded&#34; </wildcardlinuxcmdline>
        <wildcardwinscript>ad_script</wildcardwinscript>
        <wildcardwincmdline>type=&#34;embeded&#34; </wildcardwincmdline>
        <wildcardgroovyscript>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(&#34;system.hostname&#34;);
user = &#39;&#39;
pass = &#39;&#39;

SQLUrl = &#34;jdbc:sqlserver://&#34; + hostname + &#34;;databaseName=master;integratedSecurity=true&#34; //
sql = Sql.newInstance(SQLUrl, user, pass, &#39;com.microsoft.sqlserver.jdbc.SQLServerDriver&#39;)

sql.eachRow( &#39;SELECT name, expiry_date from sys.certificates&#39;){
	certname = it.name.toString().replaceAll(&#34;#&#34;,&#34;&#34;)
    println(certname + &#34;##&#34; + certname)
}
sql.close() // Close connection</wildcardgroovyscript>
        <wildcardschedule>0</wildcardschedule>
        <wildcarddisable>false</wildcarddisable>
        <wildcarddeleteinactive>true</wildcarddeleteinactive>
        <agdmethod>none</agdmethod>
        <agdparams></agdparams>
        <group>_Certificates</group>
        <tags></tags>
        <technology></technology>
        <adlist><![CDATA[{"agdmethod":"none","method":"ad_script","agdparams":"","id":0,"filters":[],"params":{"type":"embeded","groovyscript":"import com.santaba.agent.groovyapi.expect.Expect;\r\nimport com.santaba.agent.groovyapi.snmp.Snmp;\r\nimport com.santaba.agent.groovyapi.http.*;\r\nimport com.santaba.agent.groovyapi.jmx.*;\r\nimport org.xbill.DNS.*;\r\nimport groovy.sql.Sql // needed for SQL connection and query\r\nimport groovy.time.*\r\n\r\nhostname = hostProps.get(\"system.hostname\");\r\nuser = ''\r\npass = ''\r\n\r\nSQLUrl = \"jdbc:sqlserver://\" + hostname + \";databaseName=master;integratedSecurity=true\" //\r\nsql = Sql.newInstance(SQLUrl, user, pass, 'com.microsoft.sqlserver.jdbc.SQLServerDriver')\r\n\r\nsql.eachRow( 'SELECT name, expiry_date from sys.certificates'){\r\n\tcertname = it.name.toString().replaceAll(\"#\",\"\")\r\n    println(certname + \"##\" + certname)\r\n}\r\nsql.close() // Close connection"}}]]></adlist>
        <schemaVersion>2</schemaVersion>
        <dataSourceType>1</dataSourceType>
        <attributes>
        <attribute>
            <name>scripttype</name>
            <value>embed</value>
            <comment></comment>
        </attribute>
        <attribute>
            <name>scriptgroovy</name>
            <value>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(&#34;system.hostname&#34;);
user = &#39;&#39;
pass = &#39;&#39;

SQLUrl = &#34;jdbc:sqlserver://&#34; + hostname + &#34;;databaseName=master;integratedSecurity=true&#34; //
sql = Sql.newInstance(SQLUrl, user, pass, &#39;com.microsoft.sqlserver.jdbc.SQLServerDriver&#39;)

sql.eachRow( &#39;SELECT name, expiry_date from sys.certificates&#39;){
	certname = it.name.toString().replaceAll(&#34;#&#34;,&#34;&#34;)
    daystoexpire = TimeCategory.minus(new Date(),Date.parse(&#34;yyy-MM-dd HH:mm:ss.S&#34;,it.expiry_date.toString()))
    println(certname + &#34;.daystoexpire: &#34; + daystoexpire.getDays())
}
sql.close() // Close connection</value>
            <comment></comment>
        </attribute>
        <attribute>
            <name>windowsscript</name>
            <value></value>
            <comment></comment>
        </attribute>
        <attribute>
            <name>linuxscript</name>
            <value></value>
            <comment></comment>
        </attribute>
        <attribute>
            <name>windowscmdline</name>
            <value></value>
            <comment></comment>
        </attribute>
        <attribute>
            <name>linuxcmdline</name>
            <value></value>
            <comment></comment>
        </attribute>
        </attributes>
        <datapoints>
        <datapoint>
            <name>DaystoExpire</name>
            <dataType>7</dataType>
            <type>2</type>
            <postprocessormethod>namevalue</postprocessormethod>
            <postprocessorparam>##WILDVALUE##.daystoexpire</postprocessorparam>
            <usevalue>output</usevalue>
            <alertexpr></alertexpr>
            <alertmissing>1</alertmissing>
            <alertsubject></alertsubject>
            <alertbody></alertbody>
            <enableanomalyalertsuppression></enableanomalyalertsuppression>
            <adadvsettingenabled>false</adadvsettingenabled>
            <warnadadvsetting></warnadadvsetting>
            <erroradadvsetting></erroradadvsetting>
            <criticaladadvsetting></criticaladadvsetting>
            <description></description>
            <maxvalue></maxvalue>
            <minvalue></minvalue>
            <userparam1></userparam1>
            <userparam2></userparam2>
            <userparam3></userparam3>
            <iscomposite>false</iscomposite>
            <rpn></rpn>
            <alertTransitionIval>0</alertTransitionIval>
            <alertClearTransitionIval>0</alertClearTransitionIval>
        </datapoint>
        </datapoints>
        <graphs>
        </graphs>
        <overviewgraphs>
        </overviewgraphs>
        <scripts>
        </scripts>
    </entry>
</feed>

 

Link to post
Share on other sites
  • 2
  • Administrators

Yeah, you have it set as script instead of batch script. I originally built it in your portal and tested it on that box and it was working fine. However, when i import and test it now, it looks like that server is refusing to connect. New firewall between the collector and the server? The script uses integrated security, so the same credentials running the collector process need to have access to the DB. Did that change recently?

Link to post
Share on other sites
  • 2
  • Administrators

Should just be a matter of changing this line:

SQLUrl = "jdbc:sqlserver://" + hostname + ";databaseName=master;integratedSecurity=true" //

 

To something like this:

SQLUrl = "jdbc:sqlserver://" + hostname + ":1432;databaseName=master;integratedSecurity=true" //

However, in order to make it resilient and reusable:

port = hostProps.get("jdbc.port") ?: 1433
SQLUrl = "jdbc:sqlserver://" + hostname + ":" + port + ";databaseName=master;integratedSecurity=true" //

And then set the jdbc.port property on the device. That way different servers can have different ports but still work.

Link to post
Share on other sites
  • 1
  • Administrators

Both options i listed use DataSources. Instance level properties can only be set inside Active Discovery in a DataSource. PropertySources will only set properties on the device level. PropertySources also don't open alerts at all.

Link to post
Share on other sites
  • 1
  • Administrators

So close. The .eachRow is your loop statement:

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'){
	println "full_name_certificate=" + it.toString()
}


sql.close() // Close connection

As for the connection string, looks like it should take this format:

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

 

Link to post
Share on other sites
  • 1
  • Administrators
10 hours ago, Dominique said:

- Only the certificates starting by ##MS are displayed not the other ones !!! ConfigMgrEndPointCert, mirror_certificate, principal_certificate, SPOKCert and witness_certificate are not displayed!!!

That likely has to do with the output format of your script. "#" should be avoided in WILDVALUEs. You switched to scripted active discovery right? If so, then your output should be like this for each certificate:

##WILDVALUE##WILDALIAS##DESCRIPTION

So, if your WILDVALUE or WILDALIAS contain double ##, that may be screwing up the parsing of the output. I recommend a .replaceAll("#",""). 

I'm curious if you're still going the propertysource route or if you've done it in a datasource. I think it can all be done in a datasource with a datapoint that calculates the difference between now and the expiration date. Is that what you did?

Link to post
Share on other sites
  • 1
  • Administrators

Ok, looks like you're using the PropertySource output format (propertyname=value) for the ActiveDiscovery output format (certid##certdisplayname). If you can post your AD script here, I can show you what changes need to be made to get it to work.

Link to post
Share on other sites
  • 1
  • Administrators

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

 

Link to post
Share on other sites
  • 1
  • Administrators

So, you can't change the collector once the DS has been saved, unfortunately. You will have to start a new DS to change it.

I think the problem might just be that the date coming from the sql query is somehow not a string, which the parser expects. Try casting it as a string so the parser can pick it up.

timetoexpire = TimeCategory.minus(new Date(),Date.parse("yyy-MM-dd HH:mm:ss.S",it.expiry_date.toString()))

Casting it as a string then parsing into a date may be redundant as it may be that it's already a date, but this makes sure.

Link to post
Share on other sites
  • 1
  • Administrators

Ah, looks like you put the collection script in the active discovery field. What you needed was a new BATCHSCRIPT DS with two scripts:

The active discovery script looks like 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 = ''

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

And the collection script would look like 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 = ''

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

Then a datapoint that looks like this:

image.png.738ad7dfcbee19979c1df0b493febc20.png

Link to post
Share on other sites
  • 1

This is actually useful. One of our SQL engineers requested something similar (only worried about TDE certs for SQL boxes) & we ended up using this logic to accomplish that.
I actually had to tweak the 'timeToExpire' since it was returning a negative value (for certificates that are yet to expire). Since we'll want to use a < 30 30 15 (or similar) threshold, we had to revert the result(s).

Published it to my GitHub -> HERE 

Regards,

  • Like 1
Link to post
Share on other sites
  • 1
  • Administrators
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.

Link to post
Share on other sites
  • 0
1 hour ago, Stuart Weenig said:

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.

Thanks Stuart.

So no need to go to "PropertySources". I will try option 2.

Thanks,

Dom

Link to post
Share on other sites
  • 0

I will check the "DataSources" created previously as LM Support was sending me to "PropertySources" and now I am back to "DataSources"...

I attached the previous "Datasources"... I was not using this groovy script but a SQL Statement "Select name, expiry_date from sys.certificates" so I will try to change it to use a groovy script which will allow the strings...

Thanks,

Dom

2020-05-27_8-52-41 _SQL Certificates 05.png

2020-05-27_8-51-33 _SQL Certificates 04.png

2020-05-27_8-50-38 _SQL Certificates 03.png

2020-05-27_8-49-23 _SQL Certificates 02.png

2020-05-27_8-48-41 _SQL Certificates 01.png

  • Upvote 1
Link to post
Share on other sites
  • 0
Posted (edited)

I just updated the Datasource to use the groovy script

 

2020-05-27_12-58-54 _SQL Cerificates 06 (Script).png

but the "Test Script" is giving errors:

MissingMethodException: No signature of method: groovy.sql.Sql.eachRow() is applicable for argument types: (java.lang.String) values: [SELECT name, expiry_date from sys.certificates]
Possible solutions: eachRow(java.lang.String, groovy.lang.Closure), eachRow(groovy.lang.GString, groovy.lang.Closure), eachRow(java.lang.String, groovy.lang.Closure, groovy.lang.Closure), eachRow(java.lang.String, java.util.List, groovy.lang.Closure), eachRow(java.lang.String, java.util.Map, groovy.lang.Closure), eachRow(java.util.Map, java.lang.String, groovy.lang.Closure)
com.logicmonitor.common.sse.utils.exception.ScriptExecutingFailedException: MissingMethodException: No signature of method: groovy.sql.Sql.eachRow() is applicable for argument types: (java.lang.String) values: [SELECT name, expiry_date from sys.certificates]
Possible solutions: eachRow(java.lang.String, groovy.lang.Closure), eachRow(groovy.lang.GString, groovy.lang.Closure), eachRow(java.lang.String, groovy.lang.Closure, groovy.lang.Closure), eachRow(java.lang.String, java.util.List, groovy.lang.Closure), eachRow(java.lang.String, java.util.Map, groovy.lang.Closure), eachRow(java.util.Map, java.lang.String, groovy.lang.Closure)
	at com.logicmonitor.common.sse.utils.GroovyScriptHelper.execute(GroovyScriptHelper.java:197)
	at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.logicmonitor.common.sse.executor.impl.GroovyScriptHelperWrapper.execute(GroovyScriptHelperWrapper.java:86)
	at com.logicmonitor.common.sse.executor.GroovyScriptExecutor.execute(GroovyScriptExecutor.java:75)
	at com.logicmonitor.common.sse.SSEScriptExecutor$ScriptExecutingTask.call(SSEScriptExecutor.java:212)
	at com.logicmonitor.common.sse.SSEScriptExecutor$ScriptExecutingTask.call(SSEScriptExecutor.java:155)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
 elapsed time: 0 seconds

 

Checking this issue...
Edited by Dominique
Link to post
Share on other sites
  • 0

Hello,

This script works but do not loop for all instances (all certificates!)

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'){
full_name_certificate = it.toString()
}
println "full_name_certificate=" + full_name_certificate

sql.close() // Close connection

Any idea how to do the loop?

 

Thanks,
Dom

2020-05-27_16-59-15 _SQL Certificates Test by LM Support.png

Link to post
Share on other sites
  • 0

Hello,

This script works but do not loop for all instances (all certificates!)

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'){
full_name_certificate = it.toString()
}
println "full_name_certificate=" + full_name_certificate

sql.close() // Close connection

Any idea how to do the loop?

Also on the line:

SQLUrl = "jdbc:sqlserver://" + hostname + ";databaseName=master;integratedSecurity=true" 

how do you specify the SQL instance parameter to be able to get:

hostname= VRPSCCMSQL01

instance = confimgr

I need to connect to \\VRPSCCMSQL01\Configmgr

For now I have a connetion to \\VRPSCCMSQL01 ONLY!!!

 

Thanks,
Dom

Link to post
Share on other sites
  • 0

Excellent thanks a lot Stuart.

What are the permissions for the collector towards the database I need to get the information?

On my test machine I have 'AD\svcMonLogic1' as sysadmin but could I have less permissions?

On my second machine it failed for access:

Quote

SQLServerException: Login failed for user 'AD\svcMonLogic1'. ClientConnectionId:48c07bd6-3940-4ad0-8128-9f05f57f649d
com.logicmonitor.common.sse.utils.exception.ScriptExecutingFailedException: SQLServerException: Login failed for user 'AD\svcMonLogic1'. ClientConnectionId:48c07bd6-3940-4ad0-8128-9f05f57f649d
	at com.logicmonitor.common.sse.utils.GroovyScriptHelper.execute(GroovyScriptHelper.java:197)
	at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.logicmonitor.common.sse.executor.impl.GroovyScriptHelperWrapper.execute(GroovyScriptHelperWrapper.java:86)
	at com.logicmonitor.common.sse.executor.GroovyScriptExecutor.execute(GroovyScriptExecutor.java:75)
	at com.logicmonitor.common.sse.SSEScriptExecutor$ScriptExecutingTask.call(SSEScriptExecutor.java:212)
	at com.logicmonitor.common.sse.SSEScriptExecutor$ScriptExecutingTask.call(SSEScriptExecutor.java:155)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
 elapsed time: 0 seconds

 

 
Thanks,
Dom

 

Link to post
Share on other sites
  • 0

Excellent thanks a lot Stuart.

Now I need to place an alert on the expiration date ....

What are the permissions for the collector towards the database I need to get the information?

On my test machine I have 'AD\svcMonLogic1' as sysadmin but could I have less permissions?

On my second machine it failed for access:

Quote

SQLServerException: Login failed for user 'AD\svcMonLogic1'. ClientConnectionId:48c07bd6-3940-4ad0-8128-9f05f57f649d
com.logicmonitor.common.sse.utils.exception.ScriptExecutingFailedException: SQLServerException: Login failed for user 'AD\svcMonLogic1'. ClientConnectionId:48c07bd6-3940-4ad0-8128-9f05f57f649d
	at com.logicmonitor.common.sse.utils.GroovyScriptHelper.execute(GroovyScriptHelper.java:197)
	at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.logicmonitor.common.sse.executor.impl.GroovyScriptHelperWrapper.execute(GroovyScriptHelperWrapper.java:86)
	at com.logicmonitor.common.sse.executor.GroovyScriptExecutor.execute(GroovyScriptExecutor.java:75)
	at com.logicmonitor.common.sse.SSEScriptExecutor$ScriptExecutingTask.call(SSEScriptExecutor.java:212)
	at com.logicmonitor.common.sse.SSEScriptExecutor$ScriptExecutingTask.call(SSEScriptExecutor.java:155)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
 elapsed time: 0 seconds

 

 
Thanks,
Dom

 

Link to post
Share on other sites
  • 0

Find out I was opening both servers 1 & 2 on the same SSMS and my eyes were crossing... so no problem for the access ... working on the alerts now...

Apparently it is sending alerts for old certificates not cleaned up from the server ... expired on 02/15/2018 !!! 

How to ignore them?

Thanks,
Dom

Link to post
Share on other sites
  • 0
Posted (edited)

2020-05-27_19-36-37 VRPSPOKDBP1 Certificates.png

All of them are expired but only the first 7 were sending alerts!!! nothing for mirror_certificate, principal_certificate, SpokCert or witness_certificate

Edited by Dominique
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.