Best Nexpose Queries for Exploit Automation - Slava Ukraine!

################################################
# better_dead_than_red.sql
# 
# Fuck Russia - stop the spread of collectivism!
#
# Slava Ukraine! 
################################################


## Query all vulns with reference to exploit, vuln IP + DNS name, difficulty, CVE if any

SELECT 
      da.ip_address as ip, 
      da.host_name as dns, 
      dv.title AS vuln_title, 
      dv.severity, 
   round(dv.cvss_score::numeric, 2) AS cvss_score, 
   round(dv.riskscore::numeric, 0) AS risk, 
   vr.source, 
   vr.reference,
   ve.source as sploit_type,
   ve.skill_level as difficulty,
   ve.source_key as sploit_key
FROM fact_asset_vulnerability_finding favf 
   JOIN dim_asset da USING (asset_id) 
   JOIN dim_operating_system dos USING (operating_system_id) 
   JOIN dim_vulnerability dv USING (vulnerability_id) 
   JOIN dim_site_asset dsa USING (asset_id) 
   JOIN dim_site ds USING (site_id) 
   JOIN dim_vulnerability_reference as vr USING (vulnerability_id)
   JOIN dim_vulnerability_exploit as ve USING (vulnerability_id)
ORDER BY dv.severity DESC, dv.title ASC
;

## Same thing but with malware kit info - sometimes the join takes a while 

SELECT 
      ds.name AS site,
      da.ip_address as ip, 
      da.host_name as dns, 
      dv.title AS vuln_title, 
      dv.severity, 
   round(dv.cvss_score::numeric, 2) AS cvss_score, 
   round(dv.riskscore::numeric, 0) AS risk, 
   vr.source, 
   vr.reference,
   mk.name as malware_kit,
   ve.source as sploit_type,
   ve.skill_level as difficulty,
   ve.source_key as sploit_key
FROM fact_asset_vulnerability_finding favf 
   JOIN dim_asset da USING (asset_id) 
   JOIN dim_operating_system dos USING (operating_system_id) 
   JOIN dim_vulnerability dv USING (vulnerability_id) 
   JOIN dim_site_asset dsa USING (asset_id) 
   JOIN dim_site ds USING (site_id) 
   JOIN dim_vulnerability_reference as vr USING (vulnerability_id)
   JOIN dim_vulnerability_malware_kit as mk USING (vulnerability_id)
   JOIN dim_vulnerability_exploit as ve USING (vulnerability_id)
ORDER BY dv.severity DESC, dv.title ASC
;

## Filter to only those with Metasploit Modules (easier automation but misses most exploits!):

SELECT 
      da.ip_address as ip, 
      da.host_name as dns, 
      dv.title AS vuln_title, 
      dv.severity, 
   round(dv.cvss_score::numeric, 2) AS cvss_score,
   round(dv.riskscore::numeric, 0) AS risk, 
   vr.source, 
   vr.reference,
   ve.source as sploit_type,
   ve.skill_level as difficulty,
   ve.source_key as sploit_key
FROM fact_asset_vulnerability_finding favf 
   JOIN dim_asset da USING (asset_id) 
   JOIN dim_operating_system dos USING (operating_system_id) 
   JOIN dim_vulnerability dv USING (vulnerability_id) 
   JOIN dim_site_asset dsa USING (asset_id) 
   JOIN dim_site ds USING (site_id) 
   JOIN dim_vulnerability_reference as vr USING (vulnerability_id)
   JOIN dim_vulnerability_exploit as ve USING (vulnerability_id)
WHERE ve.source LIKE 'Me%'   
ORDER BY dv.cvss_v3_exploit_score DESC, dv.title ASC
;

## De-duped and simplified for Metasploit:

SELECT distinct query.ip, query.sploit_key
FROM(SELECT 
      da.ip_address as ip, 
      da.host_name as dns, 
      dv.title AS vuln_title, 
      dv.severity, 
   round(dv.cvss_score::numeric, 2) AS cvss_score,
   round(dv.riskscore::numeric, 0) AS risk, 
   vr.source, 
   vr.reference,
   ve.source as sploit_type,
   ve.skill_level as difficulty,
   ve.source_key as sploit_key
FROM fact_asset_vulnerability_finding favf 
   JOIN dim_asset da USING (asset_id) 
   JOIN dim_operating_system dos USING (operating_system_id) 
   JOIN dim_vulnerability dv USING (vulnerability_id) 
   JOIN dim_site_asset dsa USING (asset_id) 
   JOIN dim_site ds USING (site_id) 
   JOIN dim_vulnerability_reference as vr USING (vulnerability_id)
   JOIN dim_vulnerability_exploit as ve USING (vulnerability_id)
WHERE ve.source LIKE 'Me%'   
ORDER BY dv.cvss_v3_exploit_score DESC, dv.title ASC) as query