Knowledge Base

Ask A Question

Questions

1

SQL for vulnerability report against CVE

I need help with the following query, check vulnerabilities against Robot. I am also pulling asset group and Fix. The query runs but results doesn't match my dynamic asset group - the number of asset on vulnerabilities from query doesn't match against the number of asset in the dynamic asset group for Robot. Below is the query: SELECT dsi.name AS site, dag.name AS asset_group, da.ip_address, da.host_name, dv.title, dv.description, favi.port, dos.description AS operating_system, favi.key, da.last_assessed_for_vulnerabilities AS last_scanned, favi.date AS scan_finished, proofAsText(ds.fix) AS solution, proofAsText(favi.proof) AS proof FROM fact_asset_vulnerability_instance favi JOIN dim_vulnerability_solution dvs USING (vulnerability_id) JOIN dim_vulnerability dv USING (vulnerability_id) JOIN dim_asset da USING (asset_id) JOIN dim_operating_system dos USING (operating_system_id) JOIN dim_solution ds USING (solution_id) JOIN dim_site_asset dsa USING (asset_id) JOIN dim_site dsi USING (site_id) JOIN dim_asset_group_asset daga USING (asset_id) JOIN dim_asset_group dag USING (asset_group_id) WHERE dv.title ILIKE '%2017-6168%' OR dv.title ILIKE '%2017-17382%' OR dv.title ILIKE '%2017-17427%' OR dv.title ILIKE '%2017-17428%' OR dv.title ILIKE '%2017-12373%' OR dv.title ILIKE '%2017-%13098' OR dv.title ILIKE '%2017-%1000385' OR dv.title ILIKE '%2017-%13099' OR dv.title ILIKE '%2017-%17841' OR dv.title ILIKE '%2017-%6883' OR dv.title ILIKE '%2017-%5081' OR dv.description ILIKE '%2017-6168%' OR dv.description ILIKE '%2017-17382%' OR dv.description ILIKE '%2017-17427%' OR dv.description ILIKE '%2017-17428%' OR dv.description ILIKE '%2017-12373%' OR dv.description ILIKE '%2017-%13098%' OR dv.description ILIKE '%2017-%1000385%' OR dv.description ILIKE '%2017-%13099%' OR dv.description ILIKE '%2017-%17841%' OR dv.description ILIKE '%2016-%6883%' OR dv.description ILIKE '%2012-%5081%'

Posted by Kheun Chan about a year ago

0

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 dag.name 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 dag.name ASC

Posted by EverythingShines about a year ago