I am trying make a SQL report that shows EOL software, the one I put together shows software that is not EOL. Looking into it I realized that I could narrow it down to only EOL if I can figure out how to list on solutions that contain the word “upgrade” as this implies that there isn’t an Update to apply because there isn’t any being provided by the vendors so this would mean that the software has reached the EOL.
This is the query I ran that I copied mostly from one of the Obsolete SQL reporting queries that was posted here and I slightly modified it, but how can I edit the SQL query so that it only shows vulnerabilities that have the word “Upgrade” in the solution summary:
SELECT
da.mac_address AS “MAC ID”,
da.ip_address AS “IP Address”,
da.host_name AS “Host Name”,
ds.“name” AS “Software Name”,
ds.“version” AS “Version”
FROM
fact_asset AS fa
JOIN dim_asset AS da ON fa.asset_id = da.asset_id
AND da.mac_address IS NOT NULL
JOIN dim_asset_software AS das ON fa.asset_id = das.asset_id
JOIN dim_software AS ds ON das.software_id = ds.software_id
JOIN fact_asset_scan_vulnerability_instance fasvi ON fa.asset_id = fasvi.asset_id – Correction here: should join ‘fasvi’ to ‘fa’ or ‘da’, not redeclare ‘da’
JOIN dim_vulnerability dv ON fasvi.vulnerability_id = dv.vulnerability_id
JOIN dim_vulnerability_category dvc ON dv.vulnerability_id = dvc.vulnerability_id
WHERE
dvc.category_name = ‘Obsolete Software’
GROUP BY
da.ip_address,
da.host_name,
da.mac_address,
ds.“name”,
ds.“version”
ORDER BY
da.mac_address,
da.ip_address ASC,
da.host_name ASC,
ds.“name” ASC,
ds.“version” ASC
How do I Join the fact_asset_scan_vulnerability_finding to the dim_solution to make the columns fix and summary show up on a report. I just need the query to show me all assets and all their vulnerabilities as well as the solutions and fixes for each of the vulnerabilities. The diagram that is listed for the dim_solution table is not working so I can see what to link it to.
I put this query together but it lists out fixes for the wrong software and it repeats the same software or vulnerability on each hosts multiple times:
SELECT
da.mac_address AS “MAC ID”,
da.ip_address AS “IP Address”,
da.host_name AS “Host Name”,
ds.“name” AS “Software Name”,
ds.“version” AS “Version”,
sol.summary AS “Solution Summary”,
sol.fix AS “Solution Fix”
FROM
fact_asset AS fa
JOIN dim_asset AS da ON fa.asset_id = da.asset_id
AND da.mac_address IS NOT NULL
JOIN dim_asset_software AS das ON fa.asset_id = das.asset_id
JOIN dim_software AS ds ON das.software_id = ds.software_id
JOIN fact_asset_vulnerability_finding AS favf ON fa.asset_id = favf.asset_id
JOIN dim_vulnerability AS dv ON favf.vulnerability_id = dv.vulnerability_id
JOIN dim_vulnerability_solution AS dvs ON dv.vulnerability_id = dvs.vulnerability_id
JOIN dim_solution AS sol ON dvs.solution_id = sol.solution_id
WHERE
sol.fix LIKE ‘%Upgrade%’; – Case-insensitive
GROUP BY
da.ip_address,
da.host_name,
da.mac_address,
ds.“name”,
ds.“version”,
sol.summary,
sol.fix
ORDER BY
da.mac_address,
da.ip_address ASC,
da.host_name ASC,
ds.“name” ASC,
ds.“version” ASC
Hi,
Does this help? I use something similar to get all hosts, all vulns and all best solutions, and then use Excel to pivot it to get a list of best solutions for each host. To only show obsolete software you could add WHERE dv.title LIKE ‘%obsolete%’ to the end.
SELECT DISTINCT
now() as data_date,
da.host_name AS "Hostname",
REPLACE(dos.description,',',' ') AS "Operating System",
REPLACE(dv.title,',',' ') as "Vuln Title",
REPLACE(ds.summary,',',' ') AS "Best Solution",
dv.nexpose_id,
date_published,
cvss_score,
riskscore,
exploits,
malware_kits
FROM
dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN dim_asset_vulnerability_best_solution davbs ON davbs.asset_id = da.asset_id
JOIN dim_solution ds ON ds.solution_id = davbs.solution_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = davbs.vulnerability_id
