SQL example - vulnerability coverage

These example queries can give you an idea on how much vulnerability coverage the Security Console has and which vulnerabilities were recently added.

All Vulnerabilities

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

sql
1
SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
2
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
3
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits 
4
FROM dim_vulnerability 
5
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.

sql
1
SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
2
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
3
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits 
4
FROM dim_vulnerability 
5
WHERE now() - date_published < INTERVAL '2 weeks' 
6
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:

sql
1
SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
2
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
3
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits 
4
FROM dim_vulnerability
5
WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (
6
   SELECT DISTINCT vulnerability_id 
7
   FROM dim_vulnerability_category 
8
   WHERE lower(category_name) LIKE '%microsoft%'
9
)
10
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 Security Console severity description:

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

Recently Published Microsoft Vulnerabilities With CVEs

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 (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

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

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

sql
1
SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
2
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
3
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, vcves.cves
4
FROM dim_vulnerability dv
5
   JOIN (
6
      SELECT vulnerability_id, array_to_string(array_agg(reference), ',') AS cves
7
      FROM dim_vulnerability_reference 
8
      WHERE source = 'CVE'
9
      GROUP BY vulnerability_id
10
   ) AS vcves USING (vulnerability_id)
11
WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (
12
   SELECT DISTINCT vulnerability_id 
13
   FROM dim_vulnerability_category 
14
   WHERE lower(category_name) LIKE '%microsoft%'
15
)
16
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 described above). This resulting data is then naturally joined to pull out the CVEs.

Using a WITH expression

sql
1
WITH
2
   vulnerability_cves AS (
3
      SELECT vulnerability_id, array_to_string(array_agg(reference), ',') AS cves
4
      FROM dim_vulnerability_reference 
5
      WHERE source = 'CVE'
6
      GROUP BY vulnerability_id
7
   )
8
SELECT nexpose_id, title, proofAsText(description) AS description, date_published, cvss_vector, 
9
   severity_score, severity, pci_severity_score, pci_status, round(riskscore::numeric, 0) AS risk_score, 
10
   round(cvss_score::numeric, 2) AS cvss_score, exploits, malware_kits, vcves.cves
11
FROM dim_vulnerability dv
12
   JOIN vulnerability_cves vcves USING (vulnerability_id)
13
WHERE now() - date_published < INTERVAL '2 weeks' AND vulnerability_id IN (
14
   SELECT DISTINCT vulnerability_id 
15
   FROM dim_vulnerability_category 
16
   WHERE lower(category_name) LIKE '%microsoft%'
17
)
18
ORDER BY title ASC

In this approach we leverage the PostgresSQL WITH expressions, or more formally known as Common Table Expressions. This technique is simple and is expanded upon in the SQL Query Export examples article. The vulnerability_cves expression first performs a search to find all CVEs for each vulnerability (with the same aggregation technique 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

Try expanding the example above for CVEs to pull out Microsoft KB references yourself.

As you can see, customizing the query to retrieve the vulnerabilities of interest is very simple and can be customized to your needs. Try looking at other dimensions that start with the name "dim_vulnerability_" to see what other metadata is available for filtering.