Can you please help with a SQL query to list obsolete software returning the following fields:
Hostname
IP Address
MAC Address
Operating System
Last Scan
Vulnerability
Date Found
Proof
Vendor
Software Name
Family
Version
I have come up with the following SQL query but the proof field does not match with the software.
SELECT DISTINCT
da.host_name AS “Hostname”,
da.ip_address AS “IP Address”,
da.mac_address AS “MAC”,
dos.description AS “OS”,
da.last_assessed_for_vulnerabilities AS “Last Scan”,
dv.title AS “Vulnerability”,
favi.date AS “Date Found”,
htmlToText (favi.proof) AS “Proof”,
ds.vendor AS “Vendor”,
ds.name AS “Software Name”,
ds.family AS Family,
ds.version AS Version
FROM fact_asset_vulnerability_instance favi
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_status dvs ON dvs.status_id = favi.status_id
JOIN dim_asset da ON da.asset_id = favi.asset_id
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_protocol dp ON dp.protocol_id = favi.protocol_id
JOIN dim_asset_software das ON das.asset_id = da.asset_id
JOIN dim_software ds ON ds.software_id = das.software_id
Someone can correct me, but as far as I can tell there is no way to map software directly to a vulnerability.
Let me try an outline an example with a single asset (host_name = workstation874 | asset_id = 27 ).
Performing a SELECT * FROM fact_asset_vulnerability_instance favi WHERE asset_id = 27 returns 1 record for 1 vulnerability on the asset.
You now join to dim_asset and dim_vulnerability to retreive the other information (da.host_name, dv.title, etc…). Again, this returns a single record as expected.
Now the software bit, you join dim_asset_software and dim_software and because this is at an asset level our hypothetical query is now returning 25 records. This is happening because the record in FAVI is being duplicated for every piece of software on the asset (25).
If I’m understanding your intentions correctly, the reason your proofs aren’t matching up is for this very reason, which can be confirmed by seeing a proof for every software record even if it doesn’t make sense for that software.
A workaround that can get you close would be to start at the vulnerability level and isolate obsolete software by filtering on CVEs known for the obsolete software.