Knowledge Base

Ask A Question



SQL Query - Exploit count

Hello, I am hoping someone can help me out. I am trying to replicate the information I get by running multiple canned reports with a SQL query, so I can do it all in one shot. Specifically, I want to replicate the Statistics and Vulnerabilities sections of the Risk Scorecard template along with the number of Critical, Severe and Moderate vulns from the Executive Summary template. I have put together the below SQL query to do this, and most of it works as expected, but the exploit number does not match what I see in the Risk Scorecard report. As an example of the problem, if I have 6 assets in an asset group, and all 6 have the same single vulnerability, which in turn has two Metaploit modules to exploit it, the SQL query shows that I have 12 "exploits" for the asset group or 6 "vulns with exploits", but the Risk Scorecard shows that I have 2 "exploits available for vulnerabilities", which is the number I am interested in. Does anyone either know the underlying query for the scorecard report, or can you help me figure out how to pull that value in? Here's my existing query: SELECT AS group_name, fag.assets, fag.vulnerabilities, fag.critical_vulnerabilities, fag.severe_vulnerabilities, fag.moderate_vulnerabilities, fag.exploits AS exploits, fag.malware_kits AS malware_kits, fag.vulnerabilities_with_exploit AS vulns_w_exploits, fag.vulnerabilities_with_malware_kit AS vulns_w_malware, fag.riskscore FROM fact_asset_group fag JOIN dim_asset_group dag USING (asset_group_id) ORDER BY ASC

Posted by EverythingShines about a year ago