SQL Query Export Example: Vulnerability Coverage

Document created by zeroorone Employee on Dec 17, 2013Last modified by zeroorone Employee on Mar 12, 2014
Version 2Show Document
  • View in full screen mode

Have you ever wondered how much coverage Nexpose has? Want to know what vulnerabilities were recently published? Well, with the SQL Query Export feature, this is an easy task. Let's walk through it.

 

All Vulnerabilities

Vulnerabilities that Nexpose has defined are dimensional, and can be located within the dim_vulnerability dimension. This dimension exposes the common attributes of the vulnerabilities that Nexpose has coverage for. To get a listing of all vulnerabilities, you can perform a simple query against this dimension:

 

SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits 
FROM dim_vulnerability 
ORDER BY title ASC

 

The query above projects several of the columns available in the dimension, formatting and aliasing to make the output clear. The proofAsText function strips the HTML markup in the description column into a plain text format. The built-in round function is used to round the risk_score and cvss_score columns to two digits of precision. A simple ORDER BY expression sorts the results alphabetically by title in the ascending order.

 

Recently Published Vulnerabilities

To refine what vulnerabilities are returned, a WHERE clause can be added that only returns the vulnerabilities published in the last two weeks. To set the range for the date comparison, the interval data type is used. Refer to PostgreSQL Date/Time Types for more information on using custom date intervals.

 

SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits 
FROM dim_vulnerability 
WHERE now() - date_published < INTERVAL '2 weeks' 
ORDER BY title ASC

 

Recently Published Microsoft Vulnerabilities

This query can be further customized to include filtering on vulnerability type. The dim_vulnerability_category dimension provides the associations between a vulnerability and its categories. This query returns the recently published vulnerabilities that are defined within Microsoft-related categories:

 

SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits 
FROM dim_vulnerability
WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (
   SELECT DISTINCT vulnerability_id 
   FROM dim_vulnerability_category 
   WHERE lower(category_name) LIKE '%microsoft%'
)
ORDER BY title ASC

 

As the dim_vulnerability_category dimension contains all category associations, the sub-select returns only the distinct identifiers of the vulnerabilities which match (case insensitive) the term "microsoft". The sub-select technique is ideal to avoid returning duplicate rows that might have been returned if dim_vulnerability dimension was naturally joined with the dim_vulnerability_category dimension.

 

Recently Published Severe Microsoft Vulnerabilities

The search for vulnerabilities can be narrowed down even further by using the Nexpose severity description:

 

SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits
FROM dim_vulnerability
WHERE now() - date_published < INTERVAL '2 weeks' AND severity = 'Critical' AND vulnerability_id IN (
   SELECT DISTINCT vulnerability_id 
   FROM dim_vulnerability_category 
   WHERE lower(category_name) LIKE '%microsoft%'
)
ORDER BY title ASC

 

Recently Published Microsoft Vulnerabilities With CVEs

Ok, that's great, but my what if my security team speaks in CVE talk? Well, let's remove that severity filter and add CVEs to our output. The relationship between a vulnerability and CVEs are stored as "references" in the dim_vulnerability_reference dimension. This is a one-to-many dimension, meaning that if a vulnerability has multiple references, it will have multiple records in this table. As a result, there are several ways in SQL to extract this data: (1) sub-select (2) JOIN with a GROUP BY expression or for the not-so weak at heart (3) WITH expressions. The approach you choose is a design preference. In either situation, we will be collapsing multiple references into a single column, which means that we have to perform grouping aggregation either way. The aggregate function array_agg and the array_to_string function are invaluable to formatting the output when performing this denormalization. Let's see all of these options:

 

Using a sub-select:


SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits,
   (SELECT array_to_string(array_agg(reference), ',') FROM dim_vulnerability_reference WHERE source = 'CVE' AND vulnerability_id = dv.vulnerability_id) AS cves
FROM dim_vulnerability dv
WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (
   SELECT DISTINCT vulnerability_id 
   FROM dim_vulnerability_category 
   WHERE lower(category_name) LIKE '%microsoft%'
)
ORDER BY title ASC

 

In this approach, a sub-select is used to find all references from the dim_vulnerability_reference dimension which have a matching source name of "CVE". To compare the vulnerability_id from the dim_vulnerability dimension, an alias called "dv" is to used to avoid ambiguity. As there are multiple potential CVE references per vulnerability, they need to be flattened into a single output string. This is accomplished by using the array_agg aggregate function. This takes individual values and puts them into an array. However, the array will be formatted with square brackets in the output, so that isn't very clean in the CSV format. To make the output even more presentable, that array can be parsed into a string with custom delimiters using the array_to_string function. In this case, the fields in the array are flattened into a string using the comma as the separator.

 

Using a JOIN:

 

SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, vcves.cves
FROM dim_vulnerability dv
   JOIN (
      SELECT vulnerability_id, array_to_string(array_agg(reference), ',') AS cves
      FROM dim_vulnerability_reference 
      WHERE source = 'CVE'
      GROUP BY vulnerability_id
   ) AS vcves USING (vulnerability_id)
WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (
   SELECT DISTINCT vulnerability_id 
   FROM dim_vulnerability_category 
   WHERE lower(category_name) LIKE '%microsoft%'
)
ORDER BY title ASC

 

In this approach a nested JOIN performs a search to find all CVEs for each vulnerability (with the same aggregation technique I described above). This resulting data is then naturally joined to pull out the CVEs.

 

Using a WITH expression:


WITH
   vulnerability_cves AS (
      SELECT vulnerability_id, array_to_string(array_agg(reference), ',') AS cves
      FROM dim_vulnerability_reference 
      WHERE source = 'CVE'
      GROUP BY vulnerability_id
   )
SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, vcves.cves
FROM dim_vulnerability dv
   JOIN vulnerability_cves vcves USING (vulnerability_id)
WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (
   SELECT DISTINCT vulnerability_id 
   FROM dim_vulnerability_category 
   WHERE lower(category_name) LIKE '%microsoft%'
)
ORDER BY title ASC

 

In this approach we leverage the PostgresSQL WITH expressions, or more formally known as Common Table Expressions. Sounds fancy and complicated, but this technique is simple and I expanded upon it in the SQL Query Export: Development Guide and Best Practices. The vulnerability_cves expression first performs a search to find all CVEs for each vulnerability (with the same aggregation technique I described above). This output is then naturally joined against the dim_vulnerability dimension by vulnerability_id to pull out the cves column. As you can test yourself, the output is the same as the previous two approaches. When compared to the JOIN approach, you'll see some remarkable similarities, but many SQL developers would claim this approach is more readable.

 

Recently Published Severe Microsoft Vulnerabilities With KBs

Wait, actually these are Microsoft advisories, so where are the KBs? I wan the KBs!! Try expanding the example above for CVEs to pull out Microsoft KB references yourself. If you have trouble, just ask us for help for check out the other posts in this forum for guidance.

 

 

As you can see, customizing the query to retrieve the vulnerabilities of interest is very simple and can be customized to your needs. Use these as inspiration to build your own SQL queries and share them with other users on the Reporting side street. Try looking at other dimensions that start with the name "dim_vulnerability_"  to see what other metadata is available for filtering.

Attachments

    Outcomes