SQL - help with formatting output

InsightVM SQL Queries
1

I’m trying to export a report via SQL to gather cert information from hosts. The query below returns the information but the export puts all of the cert data in 1 column for the results
I’d like to capture:
Endpoint’s hostname/IP
Cert Details:

  • Subject
  • Issuer
  • Serial
  • Expiration date
    Here is the SQL query i found here that i’ve been using:
    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;

thanks

2

Can you clarify what you mean with it putting all the data in one column? My understanding is that the dim_asset_service_configuration table holds key (or in this case “name”) value pairs and extracts them for the report as such.

If you’re trying to improve formatting, you could add a GROUP BY clause to see your assets and their cert values all together in the report.

GROUP BY das.asset_id, da.host_name, da.ip_address, das.service_id, das.port, dasc.name, dasc.value
3

Thanks for helping Holly
the data in dasc.name and dasc.value, instead of multiple rows for the same asset, id like to get 1 line for each asset with columns for: ip address, hostname, port, common name, ciphers, organization, CA, serial #, protocols and expiration.

Thanks