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 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?
<span class="pln">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;</span>This part looks okay but how to get the information in the Alert...
...
Thanks,
Dom
Dominique
Posted 6 years ago·Last reply 3 years ago
42 comments
eliananova
·3 years agoWhen a new design emerges, best fake ids have teams set out to recreate every security feature from the original state-issued card. Recreating IDs takes a few months and hundreds of hours in manpower, along with dozens of rejected prototypes. Before a customer orders a card, each available product is reprinted with as many security features as possible to improve creation and delivery time once it is ordered.
Dominique
OP5 years agoThanks a lot Stuart. Let me read this several times!!!
LM User
·5 years agoAh, 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:
And I updated your collection script to this:
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.
Dominique
OP5 years agoreading https://www.logicmonitor.com/support/monitoring/applications-databases/microsoft-sql-server i am confuse if I have to use these properties on each device or is there a way to do it for all devices at one...
Thanks,
Dom
Dominique
OP5 years agoHello,
"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:
Thanks,
Dom
Dominique
OP5 years agoYes that was the issue I had... forcing the port works now...
Let me try for the global
Thanks,
Dom
LM User
·5 years agoShould just be a matter of changing this line:
To something like this:
However, in order to make it resilient and reusable:
And then set the jdbc.port property on the device. That way different servers can have different ports but still work.
Dominique
OP5 years agoHello
I am not using the port 1433 so it is closed.
Let me check how to redirect the port in the script to another port...
Thanks,
Dom
LM User
·5 years agoYeah, 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?
Dominique
OP5 years agoHello,
I changed back the DNS name to the IP I am getting a new error:
Thanks,
Dom
Vitor_Santos
·5 years agoNot sure if it helps you but, I had a similar issue ( might not be you case ) & I replaced the actual DNS name with the actual IP (on the resource level itself), it worked for me.
See if that helps as well.
Dominique
OP5 years agoHello,
I saved the xml on my laptop then import it under Settings > Add> From File to _SSL_Certificates_All_Support
I will have to dig more as now I have an error I never saw before!!!
'
Thanks,
Dom
LM User
·5 years agoYou 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.
Dominique
OP5 years agoHello,
I created a new DS _SSL_Certificates_all
I am getting error for missing instance..
there might be something else preventing these scripts to work... I tried on several devices....
Thanks.
Dom
Vitor_Santos
·5 years agoThis 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,
LM User
·6 years agoAh, 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:
And the collection script would look like this:
Then a datapoint that looks like this:
Dominique
OP6 years agoBetter after the change :)/emoticons/smile@2x.png 2x" title=":)" width="20"> ...
But it shows no instance for the device!!!
LM User
·6 years agowhoops, i called the difference timetoexpire then tried to print daystoexpire. Change one to the other.
Dominique
OP6 years agoHello,
The error changed:
Reviewing it...
change timetoexpire to daystoexpire
LM User
·6 years agoSo, 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.
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.
Dominique
OP6 years agoreviewing the error when using "Test Script"
The collector is "JDBC" for now, trying to change it!!
Thanks,
Dom
LM User
·6 years agoSo close again:
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):
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
Dominique
OP6 years agoThis is the groovy script
Thanks
LM User
·6 years agoOk, 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.
Dominique
OP6 years agoI do not see any # in the display...
Dominique
OP6 years agoHello Stuart,
Yes the script is in Active Directory discovery.
No more property source I deleted it.
Yes I am using Datasource only. Does these collection impact the server performances? I noticed last night I got about 200 emails, texts and call regarding the server used for this testing... I attached one example...
I did not do any calculation in the datapoint yet as I was trying to fix the display of all certificates...
Thanks,
Dom
LM User
·6 years agoThat 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:
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?
Dominique
OP6 years agoHello,
let me summarize the day:
- Only the certificates starting by ##MS are displayed not the other ones !!! ConfigMgrEndPointCert, mirror_certificate, principal_certificate, SPOKCert and witness_certificate are not displayed!!!
- I am receiving alerts for certificates expired very old, not sure of the range of date they were expired e.g.: 2017 !!! How to get only the certificates which are expiring in the last 90 days or the next 90 days....
Thanks,
Dom
Dominique
OP6 years agosame thing for the first servers only 7 certificates were displayed from a list of 8!!!
Dominique
OP6 years agoAll of them are expired but only the first 7 were sending alerts!!! nothing for mirror_certificate, principal_certificate, SpokCert or witness_certificate
Dominique
OP6 years agoFind 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
Dominique
OP6 years agoHello,
The 2nd server has the same access, so not sure what is missing
Thanks,
Dom
Dominique
OP6 years agoExcellent 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:
Dominique
OP6 years agoExcellent 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:
LM User
·6 years agoSo close. The .eachRow is your loop statement:
As for the connection string, looks like it should take this format:
Dominique
OP6 years agoHello,
This script works but do not loop for all instances (all certificates!)
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
Dominique
OP6 years agoHello,
This script works but do not loop for all instances (all certificates!)
Any idea how to do the loop?
Thanks,
Dom
Dominique
OP6 years agoI just updated the Datasource to use the groovy script
but the "Test Script" is giving errors:
LM User
·6 years agoBoth 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.
Dominique
OP6 years agoThanks Stuart.
So no need to go to "PropertySources". I will try option 2.
Thanks,
Dom
LM User
·6 years agoThe 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.