AnsweredAssumed Answered

SQL Query Export:For VA report

Question asked by Vittawat Masaree on May 18, 2017
Latest reply on Jun 12, 2017 by glytch dome


Hi, everyone.

I have code sql query, But i not sure it correct or incorrect.

Can someone check it for me and improve this code.

 

WITH
  /*For create VCE ID*/
   vuln_cves_ids AS (
      SELECT vulnerability_id, csv(reference) AS cves
      FROM dim_vulnerability_reference
      WHERE source = 'CVE'
      GROUP BY vulnerability_id
   )
   ,/*For create vulnerability proof*/
   vulnerability_proofs AS (
      SELECT asset_id, vulnerability_id, csv(htmlToText(proof, false)) AS proofs,port,service_id,protocol_id,site_id
      FROM fact_asset_vulnerability_instance
  JOIN dim_site_asset USING (asset_id)
      GROUP BY asset_id, vulnerability_id,port,service_id,protocol_id,site_id
   ),
   /*For create vulnerability solution*/
   vulnerability_solutions AS (
      SELECT asset_id, vulnerability_id, csv(htmlToText(ds.fix, false)) AS solutions
      FROM vulnerability_proofs
         JOIN dim_asset_vulnerability_solution USING (asset_id, vulnerability_id)
         JOIN dim_solution_highest_supercedence dshs USING (solution_id)
         JOIN dim_solution ds ON ds.solution_id = dshs.superceding_solution_id
      GROUP BY asset_id, vulnerability_id
   )
SELECT 
 dsite.name AS "Site Name", /*For show site name*/
 da.ip_address AS "IP Address",/*For show IP Address*/
 da.host_name AS "Hostname", /*For show host name*/
 dos.description AS "OS", /*For show OS*/
 dse.name AS "Service", /*For show name of service*/
 dp.name AS "Protocol", /*For show protocol type*/
 CASE WHEN port = -1 THEN NULL ELSE port END AS "Port", /*For show port of service*/
 dv.title AS "Vulnerability Title",/*For show Vulnerability Title*/
 dv.severity AS "Vulnerability Severity",/*Vulnerability Severity*/
 ROUND(dv.cvss_score::numeric, 0) AS "CVSS", /*For show CVSscore*/
   htmlToText(dv.description) AS "Vulnerability Description",/*For show Vulnerability Description*/
   proofs AS "Proof",/*For show Vulnerability proof*/
   solutions AS "Solution",/*For show Vulnerability solution*/
   cves AS "CVEs"/*For show CVS ID*/
   
   /*Select table of all data*/
FROM vulnerability_proofs
   JOIN dim_asset da USING (asset_id)
   JOIN dim_site dsite USING (site_id)
   JOIN dim_operating_system dos USING (operating_system_id)
   JOIN dim_vulnerability dv USING (vulnerability_id)
   LEFT OUTER JOIN dim_service dse USING (service_id)
   LEFT OUTER JOIN dim_protocol dp USING (protocol_id)
   LEFT OUTER JOIN vuln_cves_ids USING (vulnerability_id)
   LEFT OUTER JOIN vulnerability_solutions USING (asset_id, vulnerability_id)
ORDER BY da.ip_address ASC,dv.cvss_score DESC

 

requirement is Site Name,Hostname,IP Address,OS,Service,Protocol,Port,Vulnerability Title,Vulnerability Severity,CVSS,Vulnerability Description,Proof,Solution,CVEs

 

Thanks.

Outcomes