SQL query to fetch TLS certificates from a report in Greenbone

How To
1

Hello,

I am accessing the pg-gvm database directly for a specific need. Currently, I am trying to query the TLS certificates captured by a scan. In the Greenbone UI, the report shows 346 certificates, but I never manage to retrieve the same number via SQL. The results always fluctuate, usually between 306 and 108.

I understand that the TLS Certificates tab in the report does not show duplicate certificates per host and also displays the port. Without considering the port, there are roughly 150 certificates.

I have tried various approaches, including different types of joins, but I still cannot match the number shown in the frontend. I have even looked at the gvm and gsa code to try to find a solution, but without success.

Here is one of the SQL queries I am testing (just for testing purposes, so it may have some issues):

select distinct r.uuid, tcs.origin, tcl.host_ip, tcl.port, tc.*
from tls_certificates tc
join report_host_details rhd on SPLIT_PART(rhd.name, ‘:’, 2) = tc.name
join tls_certificate_sources tcs on tcs.tls_certificate = tc.id
join tls_certificate_locations tcl on tcl.id = tcs.“location”
join tls_certificate_origins tco on tco.id = tcs.origin
join reports r on r.uuid = tco.origin_id
where rhd.report_host in (select id from report_hosts rh where rh.report = 136)
and rhd.source_description = ‘SSL/TLS Certificate Details’;

This query only returns 306 results when it should be 346.

By accident, I also found certificates in the report_hosts table that do not exist in the tls_certificates table.

I would appreciate any guidance on resolving this issue.

Thank you in advance.

2

Is there any reason you don’t want to use python-gvm and the get_tls_certificates() function? Here is a pre-built Python script for this task.

3

Hello, first of all thank you for your reply.

Actually, I am using python-gvm, but I also rely on direct SQL queries for some cases where python-gvm does not fully meet my needs.

I tested get_tls_certificates, but it does not return all the certificates and it also does not include the host and port information. There is a discrepancy between what the UI shows and what get_tls_certificates returns — in the UI I see 346 of 367 certificates, but with get_tls_certificates, even when separating by ports as the UI does, I only get 306.

The workaround I found was to use get_report from python-gvm in XML format with ignore_paginated, and then parse the response the same way the Greenbone frontend receives it. However, this approach is not very performant, since I need to fetch XML, convert it to JSON, and then separate the ports in order to get the same result as in the frontend.

Thanks again for your reply.

4

You should be aware that the SQL Syntax is only internal and NOT any stable API. Only use API calls and never try to get the information direct from the SQL Database, that might break with any update instantly.

1 Like
5

Yes, I am aware. I created a REST API layer that uses GVM underneath to access the data, but not all the data I need comes through python-gvm.
Since there is no documentation for a Greenbone Community REST API, the only solution was to create a REST API myself; however, for some data, it is necessary to have read-only access directly to the database.

6

This topic was automatically closed after 90 days. New replies are no longer allowed.