Nexpose - Scan Results - Export of ssl.cert information

Hello everybody

Together with my IT colleagues, we try to integrate SSL certificate information from the scan results into our CMDB tool. To be more precise, each asset should then be assigned the certificates found by the scanner.

Thanks to the excellent documentation of the warehouse schema, I was able to locate the table in which the information is stored relatively quickly:
ssl.cert.subject.dn
ssl.cert.not.valid.after
ssl.cert.not.valid.before

Afterwards I displayed the information in PowerBI in tabular form. The columns host_name, configuration_name and configuration_value were used accordingly. The idea we had was to export the required information via DB queries into a .csv file and feed it into our CMDB. So far, so simple.

Now we come to our problem. If the server has only one SSL certificate installed, the assignment of the information ssl.cert.not.valid.after and ssl.cert.not.valid.before is clear.However, if an asset has multiple certificates installed, then I have not seen any way to map the valid.after and valid.before information explicitly to one certificate (see print screen for example).
2020-07-24_15-40-01

I would like to add that we have only been using Rapid7 Nexpose for about 2 months and my knowledge of the capabilities is not yet that advanced. I was wondering if someone from the community has already built something similar and could help me. It is quite possible that I missed a small detail.

Thanks in advance

David

Hi @david_altanian,

Does the following help? I think to specifically get the mapping that you’re looking for, you’ll want to join on the asset_id, service_id, AND port. I’m guessing that this is a case of having one certificate on multiple asset services, apologies if that’s not quite what you’re working with.

SELECT das.asset_id, da.host_name, da.ip_address, das.service_id, das.port, dasc.name, dasc.value
FROM dim_asset da
         JOIN dim_asset_service das ON da.asset_id = das.asset_id
         JOIN dim_asset_service_configuration dasc
              ON das.asset_id = dasc.asset_id AND das.service_id = dasc.service_id AND das.port = dasc.port;
1 Like

Hi @tyler_schmidtke
Thank you very much for the quick response. I will take a look at your suggestion and get back to you when I was successful. :slight_smile:

Hi @tyler_schmidtke
I missed to thank you for your help. Your approach worked. Just had to adapt and add a little bit to the query.
In the meantime, our IT department imports this information on a daily basis into our ITSM where the asset inventory is maintained. This way the asset owners are informed about expiring SSL certificates in time.
Best regards
David

2 Likes